Syntax Error in INSERT INTO statement

[99pshop]

Help this is not new and I know the problem but cant fix it. I am rewriting a program in vb.net and the original Access database has a field name call 'Day' (Reserved Word)

I want to keep the name as I cannot change the data structure not practical(too many copies about)

I am using dataset and databinded controls. The following is snippets from the source code.

Dim stSQL As String = "Select ID,[Day],PilotID Where PilotID=" & PilotID
 
        da = New OleDbDataAdapter(stSQL, pConnection)
        ds.Clear()
 
        Try
            da.Fill(ds, "tblLogbook")
        Catch ex As Exception
 
        End Try

My controls are binded as such:

txtPilotID.DataBindings.Add("Value", dbLogbook.ds, "tblLogbook.PilotID")
        txtDay.DataBindings.Add("Value", dbLogbook.ds, "tblLogbook.Day")

my insertcommand is as follows

cmdInsert.Connection = FTLDb ' FTLDb is a declared connection object
        cmdInsert.CommandText = "Insert Into tblLogbook (PIlotID,[Day]) Values (@PIlotID,@Day)"
 
        cmdInsert.Parameters.Add(New OleDbParameter("@PIlotID", "PIlotID"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Day", "[Day]"))
        da.InsertCommand = cmdInsert

When I call

da.Update(ds, "tblLogbook")

I get the error message 'Syntax Error in INSERT INTO statement'
I know the problem is Day because if I change the name in the database to something else, it works. I am looking for a resolution without changing the datastructure. I know I have to use square brackets but where?

[dee-u]

Try this... You missed a space...

cmdInsert.CommandText = "Insert Into tblLogbook (PIlotID,[Day]) Values (@PIlotID,@Day)"

[99pshop]

Thats not the problem. I do have a space in my code. The problem lies in the reserve keyword 'Day'

[kleinma]

I don't use params often (and I never use databindings).. but are you sure you are doing it correctly?

cmdInsert.Parameters.Add(New OleDbParameter("@PIlotID", "PIlotID"))
cmdInsert.Parameters.Add(New OleDbParameter("@Day", "[Day]"))

aren't the params being passed to this new constructor a Name and Value?

If that is the case, then it would loot to me as though you are passing 2 strings
shouldn't the second param be the ACTUAL value, not a string of the field name?

like
cmdInsert.Parameters.Add(New OleDbParameter("@PIlotID", 12345))

or am I missing something in the databinging mix?

[99pshop]

This is a full listing of my insert statement, I have tried the abriviated version as above too. I just call the sub which is part of a class.

Private Sub lInsertCommand()
        cmdInsert.Connection = FTLDb
        cmdInsert.CommandText = "Insert Into tblLogbook (PilotID,acDate,Copilot,Capacity,TakeOff,Land,[Day],Night,Instrument,Route,Reg,DutyID,Duty,AcType, " & _
         "Remark,DutyStart,EndDuty,RestStart,Landings,RestEnd) Values (@PilotID,@acDate,@Copilot,@Capacity,@TakeOff,@Land,@Day,@NIght,@Instrument,@Route,@Reg,@DutyID,@Duty,@AcType, " & _
         "@Remark,@DutyStart,@EndDuty,@RestStart,@Landings,@RestEnd)"
 
        cmdInsert.Parameters.Add(New OleDbParameter("@PilotID", "PilotID"))
        cmdInsert.Parameters.Add(New OleDbParameter("@acDate", "acDate"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Copilot", "Copilot"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Capacity", "Capacity"))
        cmdInsert.Parameters.Add(New OleDbParameter("@TakeOff", "TakeOff"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Land", "Land"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Day", "[Day]"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Night", "Night"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Instrument", "Instrument"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Route", "Route"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Reg", "Reg"))
        cmdInsert.Parameters.Add(New OleDbParameter("@DutyID", "DutyID"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Duty", "Duty"))
        cmdInsert.Parameters.Add(New OleDbParameter("@AcType", "AcType"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Remark", "Remark"))
        cmdInsert.Parameters.Add(New OleDbParameter("@DutyStart", "DutyStart"))
        cmdInsert.Parameters.Add(New OleDbParameter("@EndDuty", "EndDuty"))
        cmdInsert.Parameters.Add(New OleDbParameter("@RestStart", "RestStart"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Landings", "Landings"))
        cmdInsert.Parameters.Add(New OleDbParameter("@RestEnd", "RestEnd"))
        cmdInsert.Parameters.Add(New OleDbParameter("@MultiEngine", "MultiEngine"))
        cmdInsert.Parameters.Add(New OleDbParameter("@Export", "Export"))
        da.InsertCommand = cmdInsert
    End Sub

[99pshop]

The first is the param name and the second is the object name as string.

If I take out the entry with the 'Day' value, it works fine. So there lies my problem.

[kleinma]

did you try to remove the brackets for just the parameter setting?

cmdInsert.Parameters.Add(New OleDbParameter("@PIlotID", "PIlotID"))
cmdInsert.Parameters.Add(New OleDbParameter("@Day", "Day"))

Im just guessing right now, like I said I don't use this stuff very often

[99pshop]

Yes, tried. I think I will recode and just add the values normally. databinding has a lot of funnies I think. I'm not very used to it either.

Cheer

[99pshop]

Okay got it now. have removed the brackets from the source column since its specified in the insert startment.