syntax error in INSERT INTO statement

  • Thread starter Thread starter William
  • Start date Start date
W

William

i am using a dataset and a DataAdapter to update a table with the
following schema:
ResourceID
ProjectID
LastName
FirstName
Year
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct

(ResourceID, ProjectID) is the primary key for the above "Forecast"
schema.

i got the following error message:
syntax error in INSERT INTO statement

my code:
private System.Windows.Forms.Button btnProjectView;
private System.Data.OleDb.OleDbConnection dcBUResourcesTrackingOriginal;
private System.Data.OleDb.OleDbConnection dcBUResourcesTracking;
private DataGridTableStyle dgrdTableStyle = new DataGridTableStyle();


private void btnGetData_Click(object sender, System.EventArgs e)
{
daForecast.Fill( dsResourceUsage );
}


private void btnUpdateData_Click(object sender, System.EventArgs e)
{
try
{
daForecast.Update( dsResourceUsage );
}
catch (Exception ex)
{
Console.WriteLine( ex.Message );

Trace.WriteLine( ex.Source );
Trace.WriteLine( ex.StackTrace );
}
}

private void btnProjectView_Click(object sender, System.EventArgs e)
{
frmProjectView projectView = new frmProjectView();
projectView.ShowDialog();
}


code for populating the dsResourceUsage dataset:

SELECT ResourceID, ProjectID, LastName, FirstName, Year, Nov, Dec,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct
FROM Forecast


I have tried setting
InsertCommand, UpdateCommand and DeleteCommand properties in the
DataAdapter object as follows:

INSERT INTO Forecast
(ResourceID, ProjectID, LastName, FirstName, Year,
Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)




UPDATE Forecast
SET ResourceID = ?, ProjectID = ?, LastName = ?, FirstName =
?, Year = ?, Nov = ?, Dec = ?, Jan = ?, Feb = ?, Mar = ?, Apr = ?, May =
?, Jun = ?, Jul = ?,
Aug = ?, Sep = ?, Oct = ?
WHERE (ProjectID = ?) AND (ResourceID = ?) AND (Apr = ? OR
? IS NULL AND Apr IS NULL) AND (Aug = ? OR
? IS NULL AND Aug IS NULL) AND (Dec = ? OR
? IS NULL AND Dec IS NULL) AND (Feb = ? OR
? IS NULL AND Feb IS NULL) AND ([First Name] = ? OR
? IS NULL AND [First Name] IS NULL) AND (Jan = ? OR
? IS NULL AND Jan IS NULL) AND (Jul = ? OR
? IS NULL AND Jul IS NULL) AND (Jun = ? OR
? IS NULL AND Jun IS NULL) AND ([Last Name] = ? OR
? IS NULL AND [Last Name] IS NULL) AND (Mar = ? OR
? IS NULL AND Mar IS NULL) AND (May = ? OR
? IS NULL AND May IS NULL) AND (Nov = ? OR
? IS NULL AND Nov IS NULL) AND (Oct = ? OR
? IS NULL AND Oct IS NULL) AND (Sep = ? OR
? IS NULL AND Sep IS NULL) AND (Year = ? OR
? IS NULL AND Year IS NULL)



DELETE FROM Forecast
WHERE (ProjectID = ?) AND (ResourceID = ?) AND (Apr = ? OR
? IS NULL AND Apr IS NULL) AND (Aug = ? OR
? IS NULL AND Aug IS NULL) AND (Dec = ? OR
? IS NULL AND Dec IS NULL) AND (Feb = ? OR
? IS NULL AND Feb IS NULL) AND (Jan = ? OR
? IS NULL AND Jan IS NULL) AND (Jul = ? OR
? IS NULL AND Jul IS NULL) AND (Jun = ? OR
? IS NULL AND Jun IS NULL) AND ([Last Name] = ? OR
? IS NULL AND [Last Name] IS NULL) AND (Mar = ? OR
? IS NULL AND Mar IS NULL) AND (May = ? OR
? IS NULL AND May IS NULL) AND (Nov = ? OR
? IS NULL AND Nov IS NULL) AND (Oct = ? OR
? IS NULL AND Oct IS NULL) AND (Sep = ? OR
? IS NULL AND Sep IS NULL) AND (Year = ? OR
? IS NULL AND Year IS NULL) AND (FirstName = ? OR
? IS NULL AND FirstName IS NULL)


Any suggestions/fix with regards to the "syntax error in INSERT INTO
statement" (occured when I call the btnUpdateData_Click() method) is
greatly appreciated.

William
 
My first guess is that this is Access and you are using Year as a columname
which is also a Reserved Word

http://www.knowdotnet.com/articles/reservedwords.html
http://support.microsoft.com/kb/q109312/

Your best bet is to get rid of it as a colum name although you can use
[Year] which will fix the problem. The only downside is that in the near
future, you or anothe developer will likely forget this again and you'll
spend tim over and over debugging it. You may have anothe syntax error but
address this first and if it doesn't fix it, let me know and we'll proceed
from there.

Cheers,

Bill


--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
William said:
i am using a dataset and a DataAdapter to update a table with the
following schema:
ResourceID
ProjectID
LastName
FirstName
Year
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct

(ResourceID, ProjectID) is the primary key for the above "Forecast"
schema.

i got the following error message:
syntax error in INSERT INTO statement

my code:
private System.Windows.Forms.Button btnProjectView;
private System.Data.OleDb.OleDbConnection dcBUResourcesTrackingOriginal;
private System.Data.OleDb.OleDbConnection dcBUResourcesTracking;
private DataGridTableStyle dgrdTableStyle = new DataGridTableStyle();


private void btnGetData_Click(object sender, System.EventArgs e)
{
daForecast.Fill( dsResourceUsage );
}


private void btnUpdateData_Click(object sender, System.EventArgs e)
{
try
{
daForecast.Update( dsResourceUsage );
}
catch (Exception ex)
{
Console.WriteLine( ex.Message );

Trace.WriteLine( ex.Source );
Trace.WriteLine( ex.StackTrace );
}
}

private void btnProjectView_Click(object sender, System.EventArgs e)
{
frmProjectView projectView = new frmProjectView();
projectView.ShowDialog();
}


code for populating the dsResourceUsage dataset:

SELECT ResourceID, ProjectID, LastName, FirstName, Year, Nov, Dec,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct
FROM Forecast


I have tried setting
InsertCommand, UpdateCommand and DeleteCommand properties in the
DataAdapter object as follows:

INSERT INTO Forecast
(ResourceID, ProjectID, LastName, FirstName, Year,
Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)




UPDATE Forecast
SET ResourceID = ?, ProjectID = ?, LastName = ?, FirstName =
?, Year = ?, Nov = ?, Dec = ?, Jan = ?, Feb = ?, Mar = ?, Apr = ?, May =
?, Jun = ?, Jul = ?,
Aug = ?, Sep = ?, Oct = ?
WHERE (ProjectID = ?) AND (ResourceID = ?) AND (Apr = ? OR
? IS NULL AND Apr IS NULL) AND (Aug = ? OR
? IS NULL AND Aug IS NULL) AND (Dec = ? OR
? IS NULL AND Dec IS NULL) AND (Feb = ? OR
? IS NULL AND Feb IS NULL) AND ([First Name] = ? OR
? IS NULL AND [First Name] IS NULL) AND (Jan = ? OR
? IS NULL AND Jan IS NULL) AND (Jul = ? OR
? IS NULL AND Jul IS NULL) AND (Jun = ? OR
? IS NULL AND Jun IS NULL) AND ([Last Name] = ? OR
? IS NULL AND [Last Name] IS NULL) AND (Mar = ? OR
? IS NULL AND Mar IS NULL) AND (May = ? OR
? IS NULL AND May IS NULL) AND (Nov = ? OR
? IS NULL AND Nov IS NULL) AND (Oct = ? OR
? IS NULL AND Oct IS NULL) AND (Sep = ? OR
? IS NULL AND Sep IS NULL) AND (Year = ? OR
? IS NULL AND Year IS NULL)



DELETE FROM Forecast
WHERE (ProjectID = ?) AND (ResourceID = ?) AND (Apr = ? OR
? IS NULL AND Apr IS NULL) AND (Aug = ? OR
? IS NULL AND Aug IS NULL) AND (Dec = ? OR
? IS NULL AND Dec IS NULL) AND (Feb = ? OR
? IS NULL AND Feb IS NULL) AND (Jan = ? OR
? IS NULL AND Jan IS NULL) AND (Jul = ? OR
? IS NULL AND Jul IS NULL) AND (Jun = ? OR
? IS NULL AND Jun IS NULL) AND ([Last Name] = ? OR
? IS NULL AND [Last Name] IS NULL) AND (Mar = ? OR
? IS NULL AND Mar IS NULL) AND (May = ? OR
? IS NULL AND May IS NULL) AND (Nov = ? OR
? IS NULL AND Nov IS NULL) AND (Oct = ? OR
? IS NULL AND Oct IS NULL) AND (Sep = ? OR
? IS NULL AND Sep IS NULL) AND (Year = ? OR
? IS NULL AND Year IS NULL) AND (FirstName = ? OR
? IS NULL AND FirstName IS NULL)


Any suggestions/fix with regards to the "syntax error in INSERT INTO
statement" (occured when I call the btnUpdateData_Click() method) is
greatly appreciated.

William
Click to expand...
 
William, what do you need this for? It seems irrelevant. On the other hand
(I am not sure though) how many parameters do you pass to the update and
delete query?

Peter


AND (Apr = ? OR
? IS NULL AND Apr IS NULL) AND (Aug = ? OR
? IS NULL AND Aug IS NULL) AND (Dec = ? OR
? IS NULL AND Dec IS NULL) AND (Feb = ? OR
? IS NULL AND Feb IS NULL) AND ([First Name] = ? OR
? IS NULL AND [First Name] IS NULL) AND (Jan = ? OR
? IS NULL AND Jan IS NULL) AND (Jul = ? OR
? IS NULL AND Jul IS NULL) AND (Jun = ? OR
? IS NULL AND Jun IS NULL) AND ([Last Name] = ? OR
? IS NULL AND [Last Name] IS NULL) AND (Mar = ? OR
? IS NULL AND Mar IS NULL) AND (May = ? OR
? IS NULL AND May IS NULL) AND (Nov = ? OR
? IS NULL AND Nov IS NULL) AND (Oct = ? OR
? IS NULL AND Oct IS NULL) AND (Sep = ? OR
? IS NULL AND Sep IS NULL) AND (Year = ? OR
? IS NULL AND Year IS NULL)
 
I have changed "Year" to "FiscalYear", and I still get the "syntax error
in INSERT INTO statement" error message.

My new schema:
My INSERT INTO SQL statement is as follows:
INSERT INTO Forecast (ProjectName, LastName, FirstName, FiscalYear, Nov,
Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

The PK for the Forecast schema is ResourceID, which is an autonumber
field. I am using MS Access.


In addition, I would also appreciate your suggestions on the following:

#2) How do I sum by row?
e.g. I have
{ Name, Jan, Feb, Mar, Apr }
Joe, 1.0, 0.25, 0, 0.5

and I want a total for Joe.

#3) I want to extract the entries in one column to become the items in a
combobox. e.g in the above schema, I have the following project names:

ProjectA
ProjectC
ProjectB
ProjectD

How do I make these project names the name of a combobox with a query?



Thanks. any help is greatly appreciated.

William


My first guess is that this is Access and you are using Year as a columname
which is also a Reserved Word

http://www.knowdotnet.com/articles/reservedwords.html
http://support.microsoft.com/kb/q109312/

Your best bet is to get rid of it as a colum name although you can use
[Year] which will fix the problem. The only downside is that in the near
future, you or anothe developer will likely forget this again and you'll
spend tim over and over debugging it. You may have anothe syntax error but
address this first and if it doesn't fix it, let me know and we'll proceed
from there.

Cheers,

Bill


--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
William said:
i am using a dataset and a DataAdapter to update a table with the
following schema:
ResourceID
ProjectID
LastName
FirstName
Year
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct

(ResourceID, ProjectID) is the primary key for the above "Forecast"
schema.

i got the following error message:
syntax error in INSERT INTO statement

my code:
private System.Windows.Forms.Button btnProjectView;
private System.Data.OleDb.OleDbConnection dcBUResourcesTrackingOriginal;
private System.Data.OleDb.OleDbConnection dcBUResourcesTracking;
private DataGridTableStyle dgrdTableStyle = new DataGridTableStyle();


private void btnGetData_Click(object sender, System.EventArgs e)
{
daForecast.Fill( dsResourceUsage );
}


private void btnUpdateData_Click(object sender, System.EventArgs e)
{
try
{
daForecast.Update( dsResourceUsage );
}
catch (Exception ex)
{
Console.WriteLine( ex.Message );

Trace.WriteLine( ex.Source );
Trace.WriteLine( ex.StackTrace );
}
}

private void btnProjectView_Click(object sender, System.EventArgs e)
{
frmProjectView projectView = new frmProjectView();
projectView.ShowDialog();
}


code for populating the dsResourceUsage dataset:

SELECT ResourceID, ProjectID, LastName, FirstName, Year, Nov, Dec,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct
FROM Forecast


I have tried setting
InsertCommand, UpdateCommand and DeleteCommand properties in the
DataAdapter object as follows:

INSERT INTO Forecast
(ResourceID, ProjectID, LastName, FirstName, Year,
Nov, Dec, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)




UPDATE Forecast
SET ResourceID = ?, ProjectID = ?, LastName = ?, FirstName =
?, Year = ?, Nov = ?, Dec = ?, Jan = ?, Feb = ?, Mar = ?, Apr = ?, May =
?, Jun = ?, Jul = ?,
Aug = ?, Sep = ?, Oct = ?
WHERE (ProjectID = ?) AND (ResourceID = ?) AND (Apr = ? OR
? IS NULL AND Apr IS NULL) AND (Aug = ? OR
? IS NULL AND Aug IS NULL) AND (Dec = ? OR
? IS NULL AND Dec IS NULL) AND (Feb = ? OR
? IS NULL AND Feb IS NULL) AND ([First Name] = ? OR
? IS NULL AND [First Name] IS NULL) AND (Jan = ? OR
? IS NULL AND Jan IS NULL) AND (Jul = ? OR
? IS NULL AND Jul IS NULL) AND (Jun = ? OR
? IS NULL AND Jun IS NULL) AND ([Last Name] = ? OR
? IS NULL AND [Last Name] IS NULL) AND (Mar = ? OR
? IS NULL AND Mar IS NULL) AND (May = ? OR
? IS NULL AND May IS NULL) AND (Nov = ? OR
? IS NULL AND Nov IS NULL) AND (Oct = ? OR
? IS NULL AND Oct IS NULL) AND (Sep = ? OR
? IS NULL AND Sep IS NULL) AND (Year = ? OR
? IS NULL AND Year IS NULL)



DELETE FROM Forecast
WHERE (ProjectID = ?) AND (ResourceID = ?) AND (Apr = ? OR
? IS NULL AND Apr IS NULL) AND (Aug = ? OR
? IS NULL AND Aug IS NULL) AND (Dec = ? OR
? IS NULL AND Dec IS NULL) AND (Feb = ? OR
? IS NULL AND Feb IS NULL) AND (Jan = ? OR
? IS NULL AND Jan IS NULL) AND (Jul = ? OR
? IS NULL AND Jul IS NULL) AND (Jun = ? OR
? IS NULL AND Jun IS NULL) AND ([Last Name] = ? OR
? IS NULL AND [Last Name] IS NULL) AND (Mar = ? OR
? IS NULL AND Mar IS NULL) AND (May = ? OR
? IS NULL AND May IS NULL) AND (Nov = ? OR
? IS NULL AND Nov IS NULL) AND (Oct = ? OR
? IS NULL AND Oct IS NULL) AND (Sep = ? OR
? IS NULL AND Sep IS NULL) AND (Year = ? OR
? IS NULL AND Year IS NULL) AND (FirstName = ? OR
? IS NULL AND FirstName IS NULL)


Any suggestions/fix with regards to the "syntax error in INSERT INTO
statement" (occured when I call the btnUpdateData_Click() method) is
greatly appreciated.

William
Click to expand...
Click to expand...
 
Back
Top