Adapter Update...Syntax error

22 Nov 2006 7:45 PM

Arne Beruldsen

I have a simple Access database and I'm using VB.net. I'm getting a syntax
error on the update line. Any idea what needs to be fixed

Dim CX As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
Application.StartupPath & "\zAdmin.mdb;User Id=admin;Password=;"
        Dim PWConn As New OleDbConnection(CX)

        PWConn.Open()
        SQL = "Select * from Admin"
        Dim AdDataSet As New DataSet
        Dim AdAdapter As New OleDbDataAdapter(SQL, PWConn)
        AdAdapter.Fill(AdDataSet, "Admin")
        Dim MyCommandBuilder As New OleDbCommandBuilder(AdAdapter)
        AdAdapter.InsertCommand = MyCommandBuilder.GetInsertCommand
        AdAdapter.UpdateCommand = MyCommandBuilder.GetUpdateCommand
        Dim PwTable As DataTable

        PwTable = AdDataSet.Tables("Admin")
        PwTable.Rows(0)("Password") = "charlie"
        AdAdapter.Update(AdDataSet, "Admin")
        AdDataSet.AcceptChanges()
        PWConn.Close()

Thanks

22 Nov 2006 8:21 PM

Ken Tucker [MVP]

Hi,

Generally that is caused by a keyword being used a column name for your
admin table. Surrounding the column name in [ ] will fix the problem. A
simple fix is to change

Select * from Admin

to

Select [col1], [col2], [col3] from Admin

Ken
-------------------------------------

Show quoteHide quote

"Arne Beruldsen" wrote:

> I have a simple Access database and I'm using VB.net. I'm getting a syntax
> error on the update line. Any idea what needs to be fixed
>
> Dim CX As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
> Application.StartupPath & "\zAdmin.mdb;User Id=admin;Password=;"
>         Dim PWConn As New OleDbConnection(CX)
>
>         PWConn.Open()
>         SQL = "Select * from Admin"
>         Dim AdDataSet As New DataSet
>         Dim AdAdapter As New OleDbDataAdapter(SQL, PWConn)
>         AdAdapter.Fill(AdDataSet, "Admin")
>         Dim MyCommandBuilder As New OleDbCommandBuilder(AdAdapter)
>         AdAdapter.InsertCommand = MyCommandBuilder.GetInsertCommand
>         AdAdapter.UpdateCommand = MyCommandBuilder.GetUpdateCommand
>         Dim PwTable As DataTable
>
>         PwTable = AdDataSet.Tables("Admin")
>         PwTable.Rows(0)("Password") = "charlie"
>         AdAdapter.Update(AdDataSet, "Admin")
>         AdDataSet.AcceptChanges()
>         PWConn.Close()
>
> Thanks

22 Nov 2006 8:29 PM

Arne Beruldsen

That did it ...thanks...

Show quoteHide quote

"Ken Tucker [MVP]" wrote:

> Hi,
>
>      Generally that is caused by a keyword being used a column name for your
> admin table. Surrounding the column name in [ ] will fix the problem. A
> simple fix is to change
>
> Select * from Admin
>
> to
>
> Select [col1], [col2], [col3] from Admin
>
> Ken
> -------------------------------------
>
> "Arne Beruldsen" wrote:
>
> > I have a simple Access database and I'm using VB.net. I'm getting a syntax
> > error on the update line. Any idea what needs to be fixed
> >
> > Dim CX As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
> > Application.StartupPath & "\zAdmin.mdb;User Id=admin;Password=;"
> >         Dim PWConn As New OleDbConnection(CX)
> >
> >         PWConn.Open()
> >         SQL = "Select * from Admin"
> >         Dim AdDataSet As New DataSet
> >         Dim AdAdapter As New OleDbDataAdapter(SQL, PWConn)
> >         AdAdapter.Fill(AdDataSet, "Admin")
> >         Dim MyCommandBuilder As New OleDbCommandBuilder(AdAdapter)
> >         AdAdapter.InsertCommand = MyCommandBuilder.GetInsertCommand
> >         AdAdapter.UpdateCommand = MyCommandBuilder.GetUpdateCommand
> >         Dim PwTable As DataTable
> >
> >         PwTable = AdDataSet.Tables("Admin")
> >         PwTable.Rows(0)("Password") = "charlie"
> >         AdAdapter.Update(AdDataSet, "Admin")
> >         AdDataSet.AcceptChanges()
> >         PWConn.Close()
> >
> > Thanks

23 Nov 2006 5:59 AM

Cor Ligthert [MVP]

Arne,

Why are you trying to screw up your dataadapter with setting an insert
command that is already not used because the dynamicly build one in the
commandbuilder is used?

Cor

Show quoteHide quote

"Arne Beruldsen" <ArneBeruld***@discussions.microsoft.com> schreef in
bericht news:7E1850DF-49C4-4FFC-AF4B-4AE89CF6B6FC@microsoft.com...
>
> That did it ...thanks...
>
> "Ken Tucker [MVP]" wrote:
>
>> Hi,
>>
>>      Generally that is caused by a keyword being used a column name for
>> your
>> admin table. Surrounding the column name in [ ] will fix the problem. A
>> simple fix is to change
>>
>> Select * from Admin
>>
>> to
>>
>> Select [col1], [col2], [col3] from Admin
>>
>> Ken
>> -------------------------------------
>>
>> "Arne Beruldsen" wrote:
>>
>> > I have a simple Access database and I'm using VB.net. I'm getting a
>> > syntax
>> > error on the update line. Any idea what needs to be fixed
>> >
>> > Dim CX As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &
>> > Application.StartupPath & "\zAdmin.mdb;User Id=admin;Password=;"
>> >         Dim PWConn As New OleDbConnection(CX)
>> >
>> >         PWConn.Open()
>> >         SQL = "Select * from Admin"
>> >         Dim AdDataSet As New DataSet
>> >         Dim AdAdapter As New OleDbDataAdapter(SQL, PWConn)
>> >         AdAdapter.Fill(AdDataSet, "Admin")
>> >         Dim MyCommandBuilder As New OleDbCommandBuilder(AdAdapter)
>> >         AdAdapter.InsertCommand = MyCommandBuilder.GetInsertCommand
>> >         AdAdapter.UpdateCommand = MyCommandBuilder.GetUpdateCommand
>> >         Dim PwTable As DataTable
>> >
>> >         PwTable = AdDataSet.Tables("Admin")
>> >         PwTable.Rows(0)("Password") = "charlie"
>> >         AdAdapter.Update(AdDataSet, "Admin")
>> >         AdDataSet.AcceptChanges()
>> >         PWConn.Close()
>> >
>> > Thanks