OleDbException on INSERT query

[cmstoner]

Hi all,
In the program I'm working on, I am making a form that submits data to an Access 2007 database. I can connect to the database fine and run a select query, but when I try an insert query I get an error: "statement contains the following unknown field name: 'Last_Name' (see details below). This error occurs at my ExecuteReader command. The field is present in my table, and I've double checked for extra spaces and the like. Any ideas what could be causing this error?

The relevent code is:

Dim custstr As String = "INSERT INTO [Customer] ([Company], [Last_Name], [First_Name], [Business_Phone]," & _  
    " [Home_Phone], [Mobile_Phone], [Fax], [Email]) SELECT '" & txt_company.Text.ToString & _
    "', '" & txt_lastname.Text.ToString & "', '" & txt_firstname.Text.ToString & "', '" & _
    txt_busphone.Text.ToString & "', '" & txt_homephone.Text.ToString & "', '" & _
    txt_mobilephone.Text.ToString & "', '" & txt_fax.Text.ToString & "', '" & txt_email.Text.ToString & "'"

MsgBox("Company: " & txt_company.Text & " Last_Name: " & txt_lastname.Text)

cmd = New OleDbCommand(custstr, con)
rdr = cmd.ExecuteReader()  'error points here
If Not (rdr.Read()) Then
    MsgBox("Could not insert record into database, please try again or contact system admin.\n\n",
          MsgBoxStyle.OkOnly, "Error:\n\n")
End If

Error message and Customer table:

[Nutster]

What is the value of custstr when you get this error message? I think the SELECT statement may be malformed. Try using VALUES instead.

BTW, this is VB.Net, not VB4/5/6; please post in the correct forum for a faster and more accurate response.

[AndreRet]

After some reading, it seems that access 2007 (never happened in 2003) has a bug when you enclose the field name in enclosed [] brackets. Remove the brackets and see if that solves your problem...

@Nutster, the SELECT code is fine, used in ms access 2007

Also try and run the INSERT with Last_Name removed.

[cmstoner]

Thanks for the quick responses.

Nutster - I actually pulled the whole insert statement from Access, because I was having this problem before when I was using VALUES(). The Insert -> Select statement ran successfully in Access 2010, so I figured it should work in VB.

AndreRet - I removed the brackets from the field name and removed Last_Name, and now the error just moved to point at First_Name. The value of custstr is below. I'm thinking it has something to do with the null values. I've set up Access so that it should accept a 0 length string, but I'm thinking there must be some other option I missed to allow a null field.

INSERT INTO [Customer] (Company, First_Name, Business_Phone, Home_Phone, Mobile_Phone, Fax, Email) SELECT 'test', '', '1234657890', '', '', '', ''

[Nutster]

Check the properties of First_Name and your other Text fields. Does it allow zero-length strings? You may need to replace the '' values with NULL.

[cmstoner]

Check the properties of First_Name and your other Text fields. Does it allow zero-length strings? You may need to replace the '' values with NULL.

Yes, I set them so they allow zero-length strings. The values I'm inserting are taken from text fields in my vb program, so the only way to set those values to null (that I can see) is a really long if/else statement, which I'd like to avoid if I can.

[kRod]

I may be off the edge of the map here but Why are you using the cmd.ExecuteReader to Insert values into a table when you should be using cmd.ExecuteNonQuery?