Update help required 1

[maverickmonster]

I have this code to make a new record in my db though vb6 i wish to reuse this code but updating the records fields the same table, the record that is to be updated is the one selected in lstAdmain.

defRsp = lstADmain

Which = "Insert INTO AD VALUES (  '" & lastnum & "','" & ad & "','" & txtBPdmember_key & "','" & txtADcourtesy_title & "','" & txtADaddress1 & "','" & txtADaddress2 & "','" & txtADaddress3 & "','" & txtADaddress4 & "' ,'" & txtADaddress5 & "','" & txtADPostcode & "','" & txtADaddress_date & "' )"
Set RSad = New ADODB.Recordset
RSad.Open Which, cn, adOpenKeyset, adLockOptimistic

 

[Sheco]

So you just need an UPDATE statement instead of an INSERT statement whith a WHERE clause to identify the row?

 

[maverickmonster]

I have got this statement but it does not like this

Which = "Update INTO AD VALUES (  '" & lstADmain & "','" & ad & "','" & txtBPdmember_key & "','" & txtADcourtesy_title & "','" & txtADaddress1 & "','" & txtADaddress2 & "','" & txtADaddress3 & "','" & txtADaddress4 & "' ,'" & txtADaddress5 & "','" & txtADPostcode & "','" & txtADaddress_date & "' ) where lstadmain = [ad].id"

 

[Sheco]

It looks like you are using ad is used as both a variable and a field name. Is that correct?

Also the syntax for your UPDATE statement is incorrect.

 

[jebenson]

UPDATE syntax is different from INSERT syntax. It looks like this:

UPDATE tablename SET field1='value1', field2=value2 WHERE ....

So, you cannot use the insert statement you already have to do an update.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[bjd4jc]

You would not have an INTO clause on an update statement.

It would just be UPDATE AD SET Fieldname='New Value'... WHERE lstadmain=value"


see

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp

 

[Sheco]

I guess instead of just saying that it is wrong that I should have given an example. Here is one of the simplest examples from the SQL-Server docs but if you are not using MS SQL Server then check the help file for your particular database.

UPDATE titles
SET price = price * 1.1
WHERE (pub_id = '0766')

 

[maverickmonster]

Hi thanks for you help so far this is what i have so far but still crashes out

AD is the name of the table and a field is this an issue ?

Does this statement look ok ?

Which = "UPDATE AD "
Which = Which & "SET [id]= '" & lstADmain & "' , [ad] = '" & ad & "',[member_key] = '" & txtBPdmember_key & "', [courtesy_title] =  '" & txtADcourtesy_title & "',[address1] = '" & txtADaddress1 & "',[address2] = '" & txtADaddress2 & "',[address3] = '" & txtADaddress3 & "',[address4] = '" & txtADaddress4 & "',[address5] = '" & txtADaddress5 & "',[postcode] = '" & txtADPostcode & "',[address_date] = '" & txtADaddress_date & "' "
Which = Which & "WHERE [ad].id = '" & lstADmain & "' "


Set RSad = New ADODB.Recordset
' crashed here
RSad.Open Which, cn, adOpenKeyset, adLockOptimistic

I says that there is No Value given for one or more peramiters

Is the which string long enough to hold this ?

 

[bjd4jc]

You don't have a problem with the which string not being long enough.

Usually that error means that you have one of the columns spelled wrong in your SQL Statement.

If you're using Access for a database then do a

debug.print which

right after you're finished creating it. then take the return value of which from the immediate window and create a new query in Access and then paste the value of which into the sql window. Run the query and then it should tell you specifically which field is giving you the error.

 

[Sheco]

How about use Debug.Print or MsgBox to show you the SQL right before you actually use it. Sometimes there is an error and it is hard to see in the code but easy to see if you just look at the string.

Also there is no need to use the recordset for an Update statement because it doesnt return anything. Instead you could just use the .Execute method of the Connection object. In your case it would be:
cn.Execute Which

 

[Sheco]

Another option would be to first do a SELECT statement to get the record that you want into the recordset:
RSad.Open "SELECT * FROM AD WHERE [ad].id = 'foo'", cn, blah blah

Then change the values of fields on the recordset object:
RSad("address1") = txtADaddress1
RSad("address2") = txtADaddress2
RSad("address3") = txtADaddress3
...

Finally call the .Update() method of the recordset object:
RSad.Update



This is a lot more code than you NEED and it will run a tiny bit slower but it might be easier for you to visualize the process and save you time.... time is money.

 

[maverickmonster]

ok getting some where i miss spelt something, the think is saying now that i cannot up date the autonumber id is there and way to get round this

 

[Sheco]

Dude, dont update that, it is your key.

Or if you have your own key just get rid of it.

 

[bjd4jc]

You can just remove the

[id]= '" & lstADmain & "' ,

part from your SQL Statement and it will work fine. It makes no sense to have that in there anyway because you are updating it to the exact same thing you're searching for in your WHERE clause and therefore it is not chaning at all. Why would you want to udpate the field you're keying from?

You can't update an autonumber field anyway.

 

[maverickmonster]

Thanks Sheco,

The code works great , thanks

Another option would be to first do a SELECT statement to get the record that you want into the recordset:
RSad.Open "SELECT * FROM AD WHERE [ad].id = 'foo'", cn, blah blah

Then change the values of fields on the recordset object:
RSad("address1") = txtADaddress1 
RSad("address2") = txtADaddress2
RSad("address3") = txtADaddress3
...

Finally call the .Update() method of the recordset object:
RSad.Update