Vb6 sql command

[ferretmaster]

Hey,
I am creating a vb6 program that updates a field in a access database.

Public Sub addNewSeat(ByVal aseat As String)
    Dim db As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    Dim query As String
     db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\CNA\Card Problemvc\test.mdb;"
        db.Open
[B]         query = "Update seat Set (seat) = ('" & aseat& "') where (number) = 4[/B]
      '  query = "Insert into seat (seat) Values ('" & aseat & "');"
        rec.Open query, db
End Sub

I'm having trouble getting the SQL update command to work on the field number 4. I can get insert to work but cant get a field to update. Any help?

Thanks.

 

[pinkaardvark]

Using a recordset is unnecessary overhead if your not fetching anything use commands instead.

Dim cmd As ADODB.Command
Dim cn As ADODB.Connection

'open connection here, as discussed in other articles

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.Execute "update customers set country='USA' where customerID=301"

 

[Ladforce]

you need to move the & sign away from the string as it is invalid syntax otherwise. ignore if thats not a straight copy and paste from your actual code though in fact there are a few syntax errors.

 

[ferretmaster]

you need to move the & sign away from the string as it is invalid syntax otherwise. ignore if thats not a straight copy and paste from your actual code though in fact there are a few syntax errors.

query = "Update seat Set seat = ('" & aseat & "') where number=4"

I get the error "not a valid bookmark" when i use this sql statement. why is this?

 

[pinkaardvark]

query = "Update seat Set seat = ('" & aseat & "') where number=4"

I get the error "not a valid bookmark" when i use this sql statement. why is this?

Well it should be update [TableName] Set [ColumnName] =

So is your table and column called seat? if it is you'll need to use Set Seat.seat =

also the brackets aren't needed

 

[ferretmaster]

Well it should be update [TableName] Set [ColumnName] =

So is your table and column called seat? if it is you'll need to use Set Seat.seat =

also the brackets aren't needed

Yes, the table and column are called seat.

I am still getting the error not a valid bookmark.

No worry's mate iv got it! That bookmark error was something to do with the database. Thanks for the help.