Update SQL database using VB code.

[nosipho]

Hello everyone,

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.

[hariharanmca]

Hello everyone,

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.

You can Get it by qrys.....

[nosipho]

You can Get it by qrys.....

Yes, but how do I do that. I want to design program that is going to update the database for me and change the Status from new to old. I need to know what I should write on the Update command button, can you please show me the code. I'm using SQL2005 and vb6

[willakawill]

Hi. You will need a connection object and a sql update statement.

"UPDATE [Student] SET [StudentStatus] = 'Old' WHERE (([StudentStatus] = 'New') AND (RegDate = 2001/02/02') AND (StudNo = 200144176))"

[Killer42]

Hi. You will need a connection object and a sql update statement.

"UPDATE [Student] SET [StudentStatus] = 'Old'
  WHERE (([StudentStatus] = 'New')
  AND [B](RegDate = 2001/02/02[U]'[/U])[/B]
  AND (StudNo = 200144176))"

I know this is a convoluted area, but wouldn't one need hashes around the date? Or is that Access only, or what? Hm... guess I'd better go re-read NeoPa's mini-tutorial.

Oops! Just noticed, you've got a spurious (well, at least unmatched) single-quote character after your date. Now underlined

[willakawill]

I know this is a convoluted area, but wouldn't one need hashes around the date? Or is that Access only, or what? Hm... guess I'd better go re-read NeoPa's mini-tutorial.

Oops! Just noticed, you've got a spurious (well, at least unmatched) single-quote character after your date. Now underlined

There you go. Yes the # is for Access only

[nosipho]

There you go. Yes the # is for Access only

Thanks for you responses guys but the problem is, What I need is the code to execute that statement in the database e.g

Option Explicit

Dim conn as ADODB.Connection
Dim rs AS ADODB.Recordset

Private sub Form_load()
Set conn = new ADODB.Connection
Set rs = New ADODB.Recordset

conn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog= TEST ;Data Source=NOCOMP" 


Private Sub cmdUpdate _Click)

( Then what?) I do'nt know. I've never connected vb using a code I use a data environment.
I need help connecting vb to the database & Update command

I know I'm asking a lot but help me out guys.

[willakawill]

You don't need a recordset for this sql statement

Dim stSQL As String

stSQL = "UPDATE [Student] SET [StudentStatus] = 'Old' WHERE " _
& "(([StudentStatus] = 'New') AND (RegDate = '" & putdatehere & "') " _
& "AND (StudNo = " & putstudentnohere & "))"

conn.Execute stSQL

[nosipho]

You don't need a recordset for this sql statement

Dim stSQL As String

stSQL = "UPDATE [Student] SET [StudentStatus] = 'Old' WHERE " _
& "(([StudentStatus] = 'New') AND (RegDate = '" & putdatehere & "') " _
& "AND (StudNo = " & putstudentnohere & "))"

conn.Execute stSQL

Ok, I don't need a recordset but what do I need? I have no I idea how / where to write that in my programm. Do I write it under cmdUpdate. I need the whole code to this pleease

[willakawill]

Ok, I don't need a recordset but what do I need? I have no I idea how / where to write that in my programm. Do I write it under cmdUpdate. I need the whole code to this pleease

All the code is here. Yes you should put it in the cmdUpdate onclick event

[nosipho]

All the code is here. Yes you should put it in the cmdUpdate onclick event

Thank you so much for your responses. My program is running so smooth because of you.

Thanks