Update SQL database automatically using VB6
 

Can someone please show me a code syntax on how to update an SQL database programmatically ( execute on click of button).

I want to get all records from the "Student" database table where StudentStatus = 'New'' .Find a particular RegDate (2001/02/02) , StudNo ( 200144176) and update it's StudentStatus to "Öld", by clicking a command button ( UpdateDatabase). I'm using SQL2000( Windows Authentication).

and please show me how to connect or retreive that information from the database .

Hope I make sense.

Re: Update SQL database automatically using VB6
 

'This is a quick example of an ADO recordset that does what you want.

'These 2 items would be moved to a global area
Dim gstrStudNo As String
Dim gdatRegDat As Date

'This just fills the 2 items above, you would fill them some other way
gstrStudNo = "111234234"
gdatRegDat = #3/23/2004#

'Create Connection Object to SQL server table
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con.ConnectionString = gstrDSN 'this global string contains the DSN like "DSN=SomeName;"
con.Open
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.ActiveConnection = con
'next line assumes the StudentStatus you want is always 'New' but the other 2 items are variable
rst.Source = "SELECT StudentStatus FROM Students WHERE StudentStatus = 'New' And StudNo = '" & gstrStudNo & "' And RegDate =#" & gdatRegDat & "#"
rst.Open Options:=adCmdText

Do While Not rst.EOF
rstStudentStatus = "Old"
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing

Re: Update SQL database automatically using VB6
 

thank you ,thank you, thank you that's exactly what I needed

thank you so much.