Problem updating a database :(

[SteMc]

I have a slight problem updating a table in a database (vb.net)

my code looks like the following

Dim new_row As DataRow = {datatable}.NewRow
..
..
..
//the following are the only columns in my table//
line 1: new_row("coming_from") = "1000"
line 2: new_row("text") = "blah blah blah"
line 3: new_row("going_to") = "-1"
line 4: new_row("solution") = true
line 5: new_row("q_a_s") = "answer"

line 6: {data_table}.Rows.Add(new_row)

line 7: {data_adapter}.Update(data_table)***

This is where I am having a problem (Line 7). My database has about 5
tables and all of them have been updated in some fashion, all in this
exact manner and have worked perfectly - but this one won't.

I've checked the datatable in the command window after line 6 has
completed and the new row has been added and all the data is correct.
However when the prog reaches line 7 it gives the following error
message:

"An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in System.data.dll"

When I use the command window and type in "?
{data_adapter}.Update(data_table)" I get the following message:

"Run-time exception thrown : System.Data.OleDb.OleDbException - Syntax
error in INSERT INTO statement."

What this means I just do not know and am hoping that somebody here can
help me?

As I've used the same method of code as when trying to update other
tables I assumed it should work but it just doesn't want to

thanks,

Steve

 

[Armin Zingler]

//the following are the only columns in my table//
line 1: new_row("coming_from") = "1000"
line 2: new_row("text") = "blah blah blah"

[...]

"Run-time exception thrown : System.Data.OleDb.OleDbException -
Syntax
error in INSERT INTO statement."

What this means I just do not know and am hoping that somebody here
can
help me?

It means that you didn't enclose the field name "text" in square brackets:

insert into bla (coming_from, [text], ....) values (...)


If you use a Commandbuilder, set it's quoteprefix and quotesuffix properties
to "[" and "]".


Armin

 

[Guest]

Steve,

I wonder if one of your column names, perhaps "text", is a reserved word and
must therefore be placed inside square brackets: [text], for example.

Kerry Moorman

 

[SteMc]

Briliant! I tried changing the column heading from "text" to
"desired_text" and it worked perfectly.

I've encountered a few problems with the database but I didn't even
consider the problem of reserved words, good stuff

thank you both for your help,

Steve

 

[Joergen Bech]

Briliant! I tried changing the column heading from "text" to
"desired_text" and it worked perfectly.

I've encountered a few problems with the database but I didn't even
consider the problem of reserved words, good stuff

thank you both for your help,

Steve

For future reference: SQL Server, ODBC, and future keywords:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

Add to that the data types (including "text").

Merging those lists with similar lists for Oracle, MySQL, Access, etc.
should help prevent problems in the future if the system should ever
support those as well.

Note: QuotePrefix/QuoteSuffix are new in .Net 2.0, hence not available
in 2002/2003 (version was not specified).

/JB

 

[Guest]

For future reference: SQL Server, ODBC, and future keywords:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_9oj7.asp

Add to that the data types (including "text").

Merging those lists with similar lists for Oracle, MySQL, Access, etc.
should help prevent problems in the future if the system should ever
support those as well.

Note: QuotePrefix/QuoteSuffix are new in .Net 2.0, hence not available
in 2002/2003 (version was not specified).

/JB

Also if you ran the query in Query Analyzer it would have told you the
issue.

Chirs