When a datarow set with a new row is passed .... Or "syntax error of insert into statement"

"Syntax error of insert into statement"

Some time ago, I used VB.net + Access for exercises and found a problem. I have defined a very simple SELECT query in the SQL Server Enterprise Manager.. Net ide debugging fails, and the prompt "solution to" syntax error of insert into statement "appears when oledbcommandbuilder is used ". later I found that the field name of the table in the database is incorrect. Baidu will find the following

When we use C # To develop database access programs, there are usually three methods: ODBC, oledb, and ado.net. In fact, I think this problem will occur for these three methods, but this time I encountered a problem using oledb. Let's describe it using oledb.

 

Problem description:

I use oledb to write data to access data. The sample source code is as follows:

String SQL = "select * From multitable ";
Oledbdataadapter olesub = new oledbdataadapter (SQL, olecn );
Oledbcommandbuilder CB1 = new oledbcommandbuilder (olesub );

Dataset DS = new dataset ();

Olesub. Fill (Ds. "multitable ");

Datatable dt = Ds. Tables ["multitable"];

Datarow DR = DT. newrow ();

Dr ["prserv"] = "ws" + index. tostring (). padleft (6, '''0 '''');

Dr ["Number"] = "00063 ";

....................................

DT. Rows. Add (DR );

Olesub. Update (DS, "mulittable ");
This code is correct during compilation, but a runtime error is reported during execution: "syntax error of insert into statement".

When using oledbadapter, I did not specify an insert statement, but used oledbcommandbuilder to automatically generate an insert statement. I thought for a moment, why is this error? My conclusion is that the field names in this table may use reserved words in the access system. So I created a query in access and wrote an insert SQL statement, confirming that my conclusion is correct. Number is a reserved word of the system. How can I modify it?

In general, the simplest way is to change the field name to a non-system reserved word name, but the database structure is provided by the customer and cannot be modified. You only need to find another method. Considering previous experience, when operating access and SQL Server, if the table field contains the reserved words of the system, we can add brackets to the field, for example, insert into tblmultitable (prserv, [number]) values (.......) you can. But from the code above, we can see that there is nothing we can specify insert
Statement. I think oledbcommandbuilder should automatically generate an insert statement based on the SELECT statement used by the adapter. Therefore, you only need to add square brackets to the fields in the SELECT statement, so I made the following changes:

String SQL = "select prserv, [number], priorref, grantor, grantee from multitable ";

After the modification, the previous "insert into statement syntax error" is still generated after the test. What is the problem? I think it should be on oledbcommanbuilder. Generally, you only need to use oledbcommanbuilder class like this:

Oledbdataadapter olesub = new oledbdataadapter (SQL, olecn );
Oledbcommandbuilder CB1 = new oledbcommandbuilder (olesub); this sentence is less than possible -- the error "when a datarow set with a new line is passed..." will be reported

Open msdn and check out oledbcommanbuilder class members. Two key attributes are found: quoteprefix and quotesuffix. Think about it. There are many data types that oledb can access. Therefore, the prefixes and suffixes of key fields must be handled differently, for example, when accessing excel, it indicates that the [sheet1 $ method] should be used, so this method is quite flexible. Next, I modify the code again and assign values to these two attributes:

Oledbdataadapter olesub = new oledbdataadapter (SQL, olecn );
Oledbcommandbuilder CB1 = new oledbcommandbuilder (olesub );
Cb1.quoteprefix = "[";
Cb1.quotesuffix = "]";

Test again, pass!