Displaying only particular Data field from recordset in textboxs

[Poojaaa]

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

[AndreRet]

What error do you get and on which line?

[Poojaaa]

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

[Poojaaa]

msgbox

[AndreRet]

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

[Ajinkya Jagtap]

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

[Poojaaa]

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

[AndreRet]

That is because I have used the incorrect name, my bad. change rsCheck to rs1

[AndreRet]

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.

[Poojaaa]

THANK YOU

[AndreRet]

Only a pleasure. Happy coding :)