I am working in sql2000 database as backend and Vb6 as frontend. Its for bank scenario I am trying to display the balance corresponding to particular account number.
Do try to help. Following is my code.
*************************************
Private Sub Command1_Click()
Dim con1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim rs1 As New ADODB.Recordset
Dim accessconnect As String

accessconnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=POOJA-D4FD585E2\SQL2000"
con1.ConnectionString = accessconnect
con1.Open
cmd1.ActiveConnection = con1

cmd1.CommandText = "select balance from account where account_number = ' " & (Text1.Text) & " '"
Set rs1 = cmd1.Execute

If (rs1.EOF = Flase) Then
    Text2.Text = rs1("balance")
Else
    Text2.Text = " "
    MsgBox ("Invalid account number")
End If

con1.Close
End Sub
**************************
THANK YOU

It is directly showing me the sgbow of invalid acount_no even if the number is correct

Your spelling in code is incorrect...

If (rs1.EOF = False) Then ''Not Flase...
    Text2.Text = rs1!balance ''Corrected...
Else
    Text2.Text = "" ''Leave no space here or use vbNullString...
    MsgBox ("Invalid account number")
End If

Also change this line...

cmd1.CommandText = "SELECT balance FROM account WHERE account_number = ' " & Text1.Text & "'"

Oh crap, I'll just change the entire sub, easier. :)

Private Sub Command1_Click()
Dim con1 As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim accessconnect As String, strSql as String

Set con1 = New ADODB.Connection
Set rs1 = New ADODB.Recordset

accessconnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=POOJA-D4FD585E2\SQL2000"
con1.ConnectionString = accessconnect
con1.Open
con1.CursorLocation = adUseClient

strSql = "SELECT balance FROM account WHERE account_number = '" & Text1.Text & "'"
rsCheck.Open strSql, con1, adOpenDynamic, adLockPessimistic

If (rs1.EOF = False) Then
    Text2.Text = rs1!balance
Else
    Text2.Text = vbNullString
    MsgBox ("Invalid account number")
End If

rs1.Close
con1.Close
End Sub
If rs1.EOF = False Then
    Text2.Text = rs1!balance
Else
    Text2.Text = ""
    MsgBox ("Invalid account number")
End If

Sir AndreRet,

Your code is showing error that an object is required for the following line:-
**************************
rsCheck.Open strSql, con1, adOpenDynamic, adLockPessimistic
**************************

Also if I correct only the if statement as follows

If rs1.EOF = False Then
Text2.Text = rs1!balance
Else
Text2.Text = ""
MsgBox ("Invalid account number")
End If

It still shows the msg Invalid, even for valid number

The if statement alone will not suffice because there is no record returned at all with no connection which is why I have changed the entire sub.

This question has already been answered. Start a new discussion instead.