Going nuts...a sql insert statement error...how to debug from here?

  • Thread starter Thread starter Kathy Burke
  • Start date Start date
K

Kathy Burke

Ugh. I'm using the following in an asp.net. I get an Syntax Error in
INSERT INTO Statement on line Cmd1.ExecuteNonQuery().

I've made all my database fields text (just to eliminate that as a
potential problem). I changed all my variables in the insert statement
to text to test as shown below. Still getting the error! I've checked
again and again my db table and field names. Could someone please tell
me where to go from here??? THANKS, Kathy

Dim Conn1 As New OleDbConnection() ' conn string set globally
Dim Adapter As OleDbDataAdapter
Dim Cmd1 As OleDbCommand
Dim strSQL1 As String

strSQL1 = "INSERT INTO tblAnomaly (Date, WorkOrder, Customer, Line,
Station, Assy, SerialNo, User, Item, Type, Problem, ProblemDesc,
Comments, LineStop) VALUES ('08/10/03', '11233', 'XYZ', '07',
'Station1', '865-7446-03BA', '222', 'burkek', 'ToolA not used',
'Problem--Line Stop', 'Material Discrepancy', 'Does not meet spec',
'none', 'true')"

Conn1 = New OleDbConnection(strConn)
Cmd1 = New OleDbCommand(strSQL1, Conn1)
Conn1.Open()
Cmd1.ExecuteNonQuery()
Conn1.Close()
 
Without knowing your error message its a bit of a guess.

But I notice that one of your column names is 'Date' this is a reserved word
and may be causing issues.

Steve
 
query analyzer and similar db clients are your friends. just set a
breakpoint before you execute the query and spit out the sql string. cut
and paste the sql string to your db client and attempt running the query
from there.
 
Ugh. Changed the Date field to DateEntered...just to be safe. Then I
copied the string verbatim into an Access query and ran
it...successfully...several times.

The error I get in vb.net is [OleDbException (0x80040e14): Syntax error
in INSERT INTO statement.]

After the string worked directly in an Access query, I copied it back to
the strSQL1 statement and tried again. SAME ERROR EVERY TIME. Where does
one go from here?

Any further suggestions...or workaround? Is there a string length limit
in vb.net for an insert statement that I don't know about?

Thanks again.

Kathy
 
Went back and did an insert by adding each field on...the problem was
the User field name...changed that and works great. It's always the
simple things. I'm going now to memorize all the KEYWORDS!

Thanks again for replying.

Kathy
 
You can save yourself the trouble of memorizing all the keywords by putting
square brackets around your table and column names. Example:

SELECT [Date] FROM [User]

The square brackets indicate that the value enclosed is a database object,
not a reserved word.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Complex things are made up of
lots of simple things.
 
You can use keywords, by wrapping them in square brackets. E.g. INSERT INTO
tbl_test ([User], [Date]) VALUES ('SomeUser', '13 August 2003'). But, it's
generally not recommended. Usually causes more hassle than it's worth...

Just thought I'd mention it :)

Mun
 
Back
Top