A solution to the syntax error of ASP.net Insert into statement

ASP tutorial solution to syntax errors for. NET INSERT INTO statement

Problem Description:

I write data to access data in OLE DB, and the sample source 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 compiles with no problems, but when it runs, it reports a run-time error: "Syntax error in INSERT INTO statement."

With OLEDBAdapter, I did not specify an INSERT statement, but instead used OleDbCommandBuilder to generate the INSERT statement. After a careful thought, why does this mistake occur? 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, I wrote an Insert SQL, confirmed my conclusion is correct, number is a reserved word of the system, how to modify it?

Generally speaking, the simplest way is to change this field name, instead of the name of the system reserved words, but the structure of the library is provided by customers, not allowed to modify, only think of other ways. Taking into account previous experience, when operating Access,sql server, if the table field contains the system's reserved words, we can in the field plus the upper bracket, such as INSERT into tblmultitable (Prserv,[number)) VALUES (...) It's OK. But from the above code we see that there is no place where we can specify INSERT statements. I think OleDbCommandBuilder should have automatically generated the INSERT statement based on the SELECT statement that adapter uses, so just add square brackets to the fields in the SELECT statement, so I made the following modifications:

String sql= "Select Prserv,[number],priorref,grantor,grantee from Multitable";

After the modification, after the test, still produces the previous "INSERT INTO statement syntax error"; Where is the problem? I think it should be on the Oledbcommanbuilder, in general, only need to use the Oledbcommanbuilder class on this:

OleDbDataAdapter olesub=new OleDbDataAdapter (SQL,OLECN);
OleDbCommandBuilder cb1=new OleDbCommandBuilder (olesub);

Open MSDN to see the Oledbcommanbuilder class members. Found two key properties: Quoteprefix,quotesuffix; Consider that OLE DB can access a lot of data types, so the key field prefix, suffix processing method is certainly not the same, such as access to Excel to indicate should be written [sheet1$ Way], so providing such a way is quite flexible. Next I modify the code to assign values to these two properties:

OleDbDataAdapter olesub=new OleDbDataAdapter (SQL,OLECN);
OleDbCommandBuilder cb1=new OleDbCommandBuilder (olesub);
Cb1.quoteprefix= "[";
Cb1.quotesuffix= "]";