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

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()
 
S

Steve Letford

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
 
P

PJ

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.
 
K

Kathy Burke

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
 
K

Kathy Burke

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
 
K

Kevin Spencer

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.
 
M

Munsifali Rashid

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top