0

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

3
Contributors
10
Replies
12
Views
4 Years
Discussion Span
Last Post by AndreRet
0

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

0

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
0
If rs1.EOF = False Then
    Text2.Text = rs1!balance
Else
    Text2.Text = ""
    MsgBox ("Invalid account number")
End If
0

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

0

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.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.