Inserting Data into MS Access Database with Masked Column

Question

0.00/5 (No votes)

See more:

I am trying to develop an improved UI for an existing database. I need to edit a table which has a masked field for phone numbers to show in (123) 456-7899 format. I think this is what is causing the "Syntax error in INSERT INTO statement." error as I am not sure if I need to format my data entries as this format before trying to update the dataadapter or will the table do the necessary conversion of I input the phone number as a string (note: field data types for the phone numbers are listed as Text). Also, the first column is autonumber column which I read in a previous post that I do not need to provide any information to.

Currently, I am trying to simply read one row and pass the data to another row to ensure that the method works.

The syntax is correct as with the same information I am able to pass data into another table with only one datafield.

VB

dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source = \\usykgw\ycaus\Newnan\service\Share\1_T_&_m\T&m service\Service20000TM.accde"
        If (con.State = ConnectionState.Open) Then
        Else
            con.ConnectionString = dbProvider & dbSource
            con.Open()
        End If


        'Load customer information'
        sql = "SELECT * FROM Customers"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "RRCustomers")
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("RRCustomers").NewRow()
        'dsNewRow.Item("CustomerID") =  This is the autonumber field, not necessary to input
        dsNewRow.Item("Company Name") = ds.Tables("RRCustomers").Rows(0).Item(1).ToString & "test"
        dsNewRow.Item("Customer Phone") = ds.Tables("RRCustomers").Rows(0).Item(2)
        dsNewRow.Item("Customer FAX") = ds.Tables("RRCustomers").Rows(0).Item(3)
        dsNewRow.Item("Def Bill To") = ds.Tables("RRCustomers").Rows(0).Item(4).ToString
        dsNewRow.Item("Def Bill Address1") = ds.Tables("RRCustomers").Rows(0).Item(5).ToString
        dsNewRow.Item("Def Bill Address2") = ds.Tables("RRCustomers").Rows(0).Item(6).ToString
        dsNewRow.Item("Def Bill City") = ds.Tables("RRCustomers").Rows(0).Item(7).ToString
        dsNewRow.Item("Def Bill State") = ds.Tables("RRCustomers").Rows(0).Item(8).ToString
        dsNewRow.Item("Def Bill Zip") = ds.Tables("RRCustomers").Rows(0).Item(9).ToString
        dsNewRow.Item("Def Ship To") = ds.Tables("RRCustomers").Rows(0).Item(10).ToString
        dsNewRow.Item("Def Ship Address1") = ds.Tables("RRCustomers").Rows(0).Item(11).ToString
        dsNewRow.Item("Def Ship Address2") = ds.Tables("RRCustomers").Rows(0).Item(12).ToString
        dsNewRow.Item("Def Ship City") = ds.Tables("RRCustomers").Rows(0).Item(13).ToString
        dsNewRow.Item("Def Ship State") = ds.Tables("RRCustomers").Rows(0).Item(14).ToString
        dsNewRow.Item("Def Ship Zip") = ds.Tables("RRCustomers").Rows(0).Item(15).ToString
        ds.Tables("RRCustomers").Rows.Add(dsNewRow)
        da.Update(ds, "RRCustomers")

Posted 30-Dec-13 8:27am

ayaraneri3

Updated 30-Dec-13 8:29am

v2

Add a Solution

Comments

Kschuler 30-Dec-13 15:22pm

Is there anything more to the error statment other than "Syntax error in INSERT INTO statement."? Are you able to view the insert statment that the command builder creates while debugging? If so, what is it?

ayaraneri3 30-Dec-13 16:04pm

The entirety of the error statement is:
"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in INSERT INTO statement."

I haven't been able to see the insert statement at this time. I do see the item array which matches what I provide.

Update: Under "InsertCommand" section of the da, i see "Nothing".

Maciej Los 31-Dec-13 3:49am

Please, provide more details about field data type...

ayaraneri3 31-Dec-13 8:24am

There are 16 columns, 1 of which is AutoNumber (long integer) field which I do not pass any arguments into.
The remaining 15 are "TEXT" fields for which I do not pass the field size limit in any of my inputs.

Customer Phone field has the mask on the table as follows: !\(999") "000\-0000" X"0000;0;_
Customer FAX field has the mask on the table as follows: !\(999") "000\-0000;0;_

Unfortunately, I have no control over the table so I need to work with the maskings.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-12-30T21:57:00

Solution 1

I suspect that you're trying to add string: (123) 456-7899 into numeric field. How to go around it?

add only numeric data 1234567899 (without any brackets, spaces, etc.)

change data type for that column to CHAR and insert data in exact form: (123) 456-7899

The choice is yours ;)

Posted 30-Dec-13 21:57pm

Maciej Los

ayaraneri3 · Answer 2 · 2013-12-31T02:36:00

I gave up on this process and went with straight SQL command with executeNonQuery option:

VB

Dim Str As String = "insert into Customers ([Company Name], [Customer Phone], [Customer FAX], [Def Bill To], [Def Bill Address1], [Def Bill Address2], [Def Bill City], [Def Bill State], [Def Bill Zip], [Def Ship To], [Def Ship Address1], [Def Ship Address2], [Def Ship City], [Def Ship State], [Def Ship Zip]) values ('a','1231234567','1231234567','a','a','a','a','a','a','a','a','a','a','a','a')"
   Dim cmd As New OleDbCommand(Str, con)
   cmd.ExecuteNonQuery()

Worked beautifully.