Problem updating a database :(

  • Thread starter Thread starter SteMc
  • Start date Start date
S

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
 
SteMc said:
//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?
Click to expand...



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
 
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
 
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
 
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
Click to expand...

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
 
Joergen Bech said:
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
Click to expand...

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

Chirs
 
Back
Top