[RESOLVED] Update single row

[aikidokid]

I am trying to update a single row in an Access DB, with values from an array of 19 text boxes.

Table name is tbl_Membership
MembID is the row to be updated

Is this Update statement along the right lines?

Set cn = New ADODB.Connection
cn.ConnectionString = strConnection & App.Path & "\Membership.mdb"
cn.Open
    
strSQL = "UPDATE tblMembership Where MembID = " & lngSelection & _
            "Set Firstname = " & txtMembership(0).Text 
            "Set Middle_Name = " & txtMembership(1).Text
            'next 17 values inserted here
 
cn.Execute strSQL

[Hack]

If the membId is unique, you will only affect one row.

Also, you need to use your connection object to execute it.

cn.Execute strSQL

[aikidokid]

If the membId is unique, you will only affect one row.

Also, you need to use your connection object to execute it.

cn.Execute strSQL

I edited that post straight away, so you must have been quick posting

Yes MembID is the primary key in the database, so does that mean I need to use a different method to identify the row to be updated?
If so, what is the best way to do this?

As for the SQL statement, is that the correct structure?

[GaryMazzone]

An Update statement should look like this:

Update tableName Set
ColumnName1 = Value1,
ColumnName2 = Value2,
.......
Where PrimaryKey (or some other field) = Value

Yous should look like this:

"UPDATE tblMembership Set "
"Firstname = " & txtMembership(0).Text & ',"
"Middle_Name = " & txtMembership(1).Text & "',"
....next values
"Where Where MembID = " & lngSelection

[salvelinus]

so does that mean I need to use a different method to identify the row to be updated?

No, you should be fine there

As for the SQL statement, is that the correct structure?

No. I'll guess that you have some kind of control array, else the index numbers on txtMembership don't make sense.
Anyway, you need to surround text values with single quotes in Access. If the SQL is actually on separate lines, you need more & _'s. You also need spaces, commas, etc, along with only one SET statement.

strSQL = "UPDATE tblMembership  " & _
            "SET Firstname = '" & txtMembership(0).Text & "', " & _
            "Middle_Name = '" & txtMembership(1).Text & "', " & _
            'next 17 values inserted here
            "WHERE MembID = " & lngSelection

[Hack]

An Update statement should look like this:

Update tableName Set
ColumnName1 = Value1,
ColumnName2 = Value2,
.......
Where PrimaryKey (or some other field) = Value

Yous should look like this:

"UPDATE tblMembership Set "
"Firstname = " & txtMembership(0).Text & ',"
"Middle_Name = " & txtMembership(1).Text & "',"
....next values
"Where Where MembID = " & lngSelection

I completely missed the fact that he had the WHERE before the SET.

[aikidokid]

Thanks everbody for the help

I am still getting the error:

Syntax error in UPDATE statement

Set cn = New ADODB.Connection
    cn.ConnectionString = strConnection & App.Path & "\Membership.mdb"
    cn.Open
    
    strSQL = "UPDATE tblMembership SET " & _
        "Firstname = '" & txtMembership(0).Text & "', " & _
        "Middle_Name = '" & txtMembership(1).Text & "', " & _
        "Surname = '" & txtMembership(2).Text & "', " & _
        "Street = '" & txtMembership(3).Text & "', " & _
        "Town = '" & txtMembership(4).Text & "', " & _
        "City = '" & txtMembership(5).Text & "', " & _
        "County = '" & txtMembership(6).Text & "', " & _
        "Post_Code = '" & txtMembership(7).Text & "', " & _
        "Home_Tel = '" & txtMembership(8).Text & "', " & _
        "Mobile_Tel = '" & txtMembership(9).Text & "', " & _
        "Email = '" & txtMembership(10).Text & "', " & _
        "DOB = '" & txtMembership(11).Text & "', " & _
        "Date_Joined = '" & txtMembership(12).Text & "', " & _
        "Insurance_Due = '" & txtMembership(13).Text & "', " & _
        "Grade = '" & txtMembership(14).Text & "', " & _
        "Last_Graded = '" & txtMembership(15).Text & "', " & _
        "Membership_Number = '" & txtMembership(16).Text & "', " & _
        "Occupation = '" & txtMembership(17).Text & "', " & _
        "Notes = '" & txtMembership(18).Text & "', " & _
        "Where MembID = " & lngSelection
              
    cn.Execute strSQL
    cn.Close
    Set cn = Nothing

Do I need to add any properties after the strSQL part of the execute line, for example:

cn.Execute strSQL , , adcmdText

[Hack]

There should not be a comma after the last field update.

"Notes = '" & txtMembership(18).Text & "', " &
'should be
"Notes = '" & txtMembership(18).Text & "' "

[aikidokid]

Thanks Hack.
I have changed this but now I am getting the error:

No value given for one or more required parameters

I can't see what is missing!

[GaryMazzone]

That sounds like one of the field names is wrong in the update statement.

[aikidokid]

That sounds like one of the field names is wrong in the update statement.

That was it thanks, I had MembID instead of Memb_ID.