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

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


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[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

"Kathy Burke" <kathyburke40@attbi.com> wrote in message
news:e6F%23dZ5XDHA.2020@TK2MSFTNGP10.phx.gbl...

> 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()
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

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

"Kathy Burke" <kathyburke40@attbi.com> wrote in message
news:e6F%23dZ5XDHA.2020@TK2MSFTNGP10.phx.gbl...

> 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()
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[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

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[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

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[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
[url]http://www.takempis.com[/url]
Complex things are made up of
lots of simple things.

"Kathy Burke" <kathyburke40@attbi.com> wrote in message
news:u9ngNiBYDHA.1784@TK2MSFTNGP09.phx.gbl...

> 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
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!