replacing an old record with new data

[Niamh02]

Is there a replace keyword in vb that I can use to update the fields in a record of a database connected to via a microsoft ADO control. I do a search in the code to check if the person the user is entering data about is already there using the findfirst keyword and a unique key. I want to then replace this record with the new if its already there or even simply delete the old record and add the new! Can anyone help me?

 

[veneficuss]

Do this using an SQL UPDATE statement. Some people use the recordset.update method, but I find that most people want their database programmers to be very adept in SQL.

The SQL UPDATE Statement

UPDATE <vendorname> SET <field1name> = newvalue, <field2name> = newvalue2 WHERE <primarykey> = <old primarykeyvalue>


Here is a detailed example for VB :


example :
THe database is set like this (in SQL 2000)
Database = vendors ; DSN = mydsn ; UID = sa ; PASSWORD = mypass

the table tabvendors looks like this :

Field Name Data type
field1 nvchar(30)
field2 integer *PRIMARY KEY
field3 nvchar(40)


Ok, now you gave the user a form to fill out to update a record in the database. The first thing you do is store the primary key(s) in a temporary variable(s). You need this to tell SQL 2000 which file to update in case the person actually changed the primary key info. The form you gave the user has 3 text areas, each corresponding to a field in the DB record named text1,text2,text3. Then you added a button to submit the changes called submit.

in the submit_click() sub :

sub submit_click()
Dim tempprimary As integer
Dim sqlstatement As string
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

'first get the current values of the record you want to update - say where primary key equals 3
sql = &quot;SELECT * FROM tab vendor WHERE (field1 = 3 );&quot;
Set conn = New ADODB.Connection
conn.ConnectionString &quot;DSN=mydsn;UID=sa;PWD=mypass;DATABASE=vendors;&quot;
conn.Open
Set rs = conn.Execute(sql, , adCmdText)

'save the old primary key
tmpprimary = rs(1)

' update the record with the users entries
sql = &quot;UPDATE tabvendor SET field1 = '&quot; & text1.Text & _
&quot;', field2 = &quot; & text2.Text & &quot;, field3 = '&quot; & _
text3.Text & &quot;' WHERE (field2 = &quot; & tmpprimary & &quot;&quot;
Set rs = conn.Execute(sql, , adCmdText)

end sub

That should be in. be sure to use error handling in case the user changed field2, the primary key, to an already existing primary key. Such an action generates error number -2147217900.

let me know if u need further help veneficuss@aol.com