Syntax Error

  • Thread starter Thread starter Michael Hart
  • Start date Start date
M

Michael Hart

I am a newcommer to ADO.NET an just experimenting. I am having a lot of
difficulty.

I cannot find a syntax error that causes an exception when trying to update
an access 2000 table from a dataset.

The queries are built using the command builder so I presume they are right
and its a reserved word issue. But I am unable to find it.

Any help is appreciated.

Michael

Here is the error:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at ADB_MS.ADB_CC_Browser.btnUpdateHandler_Click(Object sender, EventArgs
e) in

Here is the OLEDBAdapter UpdateCommand command string

UPDATE ADB_CC_Table SET URNumber = ? , Sex = ? , DOB = ? , AreaOfResidence =
? , PostCode = ? , Telephone = ? , InterpreterNeed = ? , Language = ? ,
Compensable = ? , CareAvail = ? , AdmDate = ? , ServDate = ? ,
InterventionTime = ? , EstCost = ? , ClientType = ? , Provider1 = ? ,
Provider2 = ? , Provider3 = ? , Student = ? , Party = ? , GroupRecord = ? ,
PatCount = ? , NAHCC_Clas = ? , RefSrc = ? , Place = ? , Indicator = ? ,
Notes = ? , Department = ? , HospDept = ? , DRG = ? , Diag1 = ? , Diag2 = ?
, Diag3 = ? , Proc1 = ? , Proc2 = ? , Proc3 = ? , EntryDate = ? , LoginID =
? WHERE ( (RowID = ?) AND ((? IS NULL AND URNumber IS NULL) OR (URNumber =
?)) AND ((? IS NULL AND Sex IS NULL) OR (Sex = ?)) AND ((? IS NULL AND DOB
IS NULL) OR (DOB = ?)) AND ((? IS NULL AND AreaOfResidence IS NULL) OR
(AreaOfResidence = ?)) AND ((? IS NULL AND PostCode IS NULL) OR (PostCode =
?)) AND ((? IS NULL AND Telephone IS NULL) OR (Telephone = ?)) AND ((? IS
NULL AND InterpreterNeed IS NULL) OR (InterpreterNeed = ?)) AND ((? IS NULL
AND Language IS NULL) OR (Language = ?)) AND ((? IS NULL AND Compensable IS
NULL) OR (Compensable = ?)) AND ((? IS NULL AND CareAvail IS NULL) OR
(CareAvail = ?)) AND ((? IS NULL AND AdmDate IS NULL) OR (AdmDate = ?)) AND
((? IS NULL AND ServDate IS NULL) OR (ServDate = ?)) AND ((? IS NULL AND
InterventionTime IS NULL) OR (InterventionTime = ?)) AND ((? IS NULL AND
EstCost IS NULL) OR (EstCost = ?)) AND ((? IS NULL AND ClientType IS NULL)
OR (ClientType = ?)) AND ((? IS NULL AND Provider1 IS NULL) OR (Provider1 =
?)) AND ((? IS NULL AND Provider2 IS NULL) OR (Provider2 = ?)) AND ((? IS
NULL AND Provider3 IS NULL) OR (Provider3 = ?)) AND ((? IS NULL AND Student
IS NULL) OR (Student = ?)) AND ((? IS NULL AND Party IS NULL) OR (Party =
?)) AND ((? IS NULL AND GroupRecord IS NULL) OR (GroupRecord = ?)) AND ((?
IS NULL AND PatCount IS NULL) OR (PatCount = ?)) AND ((? IS NULL AND
NAHCC_Clas IS NULL) OR (NAHCC_Clas = ?)) AND ((? IS NULL AND RefSrc IS NULL)
OR (RefSrc = ?)) AND ((? IS NULL AND Place IS NULL) OR (Place = ?)) AND ((?
IS NULL AND Indicator IS NULL) OR (Indicator = ?)) AND ((? IS NULL AND
Department IS NULL) OR (Department = ?)) AND ((? IS NULL AND HospDept IS
NULL) OR (HospDept = ?)) AND ((? IS NULL AND DRG IS NULL) OR (DRG = ?)) AND
((? IS NULL AND Diag1 IS NULL) OR (Diag1 = ?)) AND ((? IS NULL AND Diag2 IS
NULL) OR (Diag2 = ?)) AND ((? IS NULL AND Diag3 IS NULL) OR (Diag3 = ?)) AND
((? IS NULL AND Proc1 IS NULL) OR (Proc1 = ?)) AND ((? IS NULL AND Proc2 IS
NULL) OR (Proc2 = ?)) AND ((? IS NULL AND Proc3 IS NULL) OR (Proc3 = ?)) AND
((? IS NULL AND EntryDate IS NULL) OR (EntryDate = ?)) AND ((? IS NULL AND
LoginID IS NULL) OR (LoginID = ?)) )
 
Hi Michael,

I think Language is a reserved word. But anyway if you do not know which
field name causes this problem, then you could wrap all the fields into
square brackets. You could do this setting QuotePrefix and QuoteSuffix
properties of the CommandBuilder to the "[" and "]" characters respectively.
It should fix issue with the reserved words

MyCommandBuilder.QuotePrefix = "["
MyCommandBuilder.QuoteSuffix = "]"

--
Val Mazur
Microsoft MVP


Michael Hart said:
I am a newcommer to ADO.NET an just experimenting. I am having a lot of
difficulty.

I cannot find a syntax error that causes an exception when trying to
update
an access 2000 table from a dataset.

The queries are built using the command builder so I presume they are
right
and its a reserved word issue. But I am unable to find it.

Any help is appreciated.

Michael

Here is the error:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at ADB_MS.ADB_CC_Browser.btnUpdateHandler_Click(Object sender, EventArgs
e) in

Here is the OLEDBAdapter UpdateCommand command string

UPDATE ADB_CC_Table SET URNumber = ? , Sex = ? , DOB = ? , AreaOfResidence
=
? , PostCode = ? , Telephone = ? , InterpreterNeed = ? , Language = ? ,
Compensable = ? , CareAvail = ? , AdmDate = ? , ServDate = ? ,
InterventionTime = ? , EstCost = ? , ClientType = ? , Provider1 = ? ,
Provider2 = ? , Provider3 = ? , Student = ? , Party = ? , GroupRecord = ?
,
PatCount = ? , NAHCC_Clas = ? , RefSrc = ? , Place = ? , Indicator = ? ,
Notes = ? , Department = ? , HospDept = ? , DRG = ? , Diag1 = ? , Diag2 =
?
, Diag3 = ? , Proc1 = ? , Proc2 = ? , Proc3 = ? , EntryDate = ? , LoginID
=
? WHERE ( (RowID = ?) AND ((? IS NULL AND URNumber IS NULL) OR (URNumber =
?)) AND ((? IS NULL AND Sex IS NULL) OR (Sex = ?)) AND ((? IS NULL AND DOB
IS NULL) OR (DOB = ?)) AND ((? IS NULL AND AreaOfResidence IS NULL) OR
(AreaOfResidence = ?)) AND ((? IS NULL AND PostCode IS NULL) OR (PostCode
=
?)) AND ((? IS NULL AND Telephone IS NULL) OR (Telephone = ?)) AND ((? IS
NULL AND InterpreterNeed IS NULL) OR (InterpreterNeed = ?)) AND ((? IS
NULL
AND Language IS NULL) OR (Language = ?)) AND ((? IS NULL AND Compensable
IS
NULL) OR (Compensable = ?)) AND ((? IS NULL AND CareAvail IS NULL) OR
(CareAvail = ?)) AND ((? IS NULL AND AdmDate IS NULL) OR (AdmDate = ?))
AND
((? IS NULL AND ServDate IS NULL) OR (ServDate = ?)) AND ((? IS NULL AND
InterventionTime IS NULL) OR (InterventionTime = ?)) AND ((? IS NULL AND
EstCost IS NULL) OR (EstCost = ?)) AND ((? IS NULL AND ClientType IS NULL)
OR (ClientType = ?)) AND ((? IS NULL AND Provider1 IS NULL) OR (Provider1
=
?)) AND ((? IS NULL AND Provider2 IS NULL) OR (Provider2 = ?)) AND ((? IS
NULL AND Provider3 IS NULL) OR (Provider3 = ?)) AND ((? IS NULL AND
Student
IS NULL) OR (Student = ?)) AND ((? IS NULL AND Party IS NULL) OR (Party =
?)) AND ((? IS NULL AND GroupRecord IS NULL) OR (GroupRecord = ?)) AND ((?
IS NULL AND PatCount IS NULL) OR (PatCount = ?)) AND ((? IS NULL AND
NAHCC_Clas IS NULL) OR (NAHCC_Clas = ?)) AND ((? IS NULL AND RefSrc IS
NULL)
OR (RefSrc = ?)) AND ((? IS NULL AND Place IS NULL) OR (Place = ?)) AND
((?
IS NULL AND Indicator IS NULL) OR (Indicator = ?)) AND ((? IS NULL AND
Department IS NULL) OR (Department = ?)) AND ((? IS NULL AND HospDept IS
NULL) OR (HospDept = ?)) AND ((? IS NULL AND DRG IS NULL) OR (DRG = ?))
AND
((? IS NULL AND Diag1 IS NULL) OR (Diag1 = ?)) AND ((? IS NULL AND Diag2
IS
NULL) OR (Diag2 = ?)) AND ((? IS NULL AND Diag3 IS NULL) OR (Diag3 = ?))
AND
((? IS NULL AND Proc1 IS NULL) OR (Proc1 = ?)) AND ((? IS NULL AND Proc2
IS
NULL) OR (Proc2 = ?)) AND ((? IS NULL AND Proc3 IS NULL) OR (Proc3 = ?))
AND
((? IS NULL AND EntryDate IS NULL) OR (EntryDate = ?)) AND ((? IS NULL AND
LoginID IS NULL) OR (LoginID = ?)) )
Click to expand...
 
Back
Top