-
Jan 14th, 2011, 02:06 AM
#1
Thread Starter
New Member
VB6 mysql Error(?)
Hi, I'm making a system wherein if you search a client and select it, there is a specific form that will show the client's infos(name, address, age, etc.)
i have a search textbox, where if you input an accountNo, and it matches the one in the database, the controls will return the clients' info.
anyway, here's the error:
vb Code:
"search textbox code:"]Private Sub Text1_change()
If Text1.Text <> "" Then Command3.Enabled = True Else Command3.Enabled = False
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM LOAN WHERE ACCOUNTNO ='" & Text1.Text & "'", CN, adOpenKeyset, adLockOptimistic
End Sub
And I get this error everytime I input accountNo's on my textbox:
Code:
Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Help please, I really need to fix this
Thanks in advance,
Ken.
-
Jan 14th, 2011, 07:05 AM
#2
Re: VB6 mysql Error(?)
What data type is the AccountNo field?
Are you sure that adOpenKeyset and adLockOptimistic are what you want? I recommend checking in the article What do the parameters of the recordset.Open method mean? from our Database Development FAQs/Tutorials (at the top of this forum)
-
Jan 15th, 2011, 12:31 AM
#3
Thread Starter
New Member
Re: VB6 mysql Error(?)
Nah, I got it to work withour error.
Problem is, my old code which is intend to work in Access didn't work now in mySQL. Any ideas?
Old Access code:
Search Textbox Code: Code:
Private Sub txtAccountNo_Change()
If Len(txtAccountNo) = 0 Then cmdNewPayment.Enabled = False: Exit Sub Else cmdNewPayment.Enabled = True
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM LOAN WHERE ACCOUNTNO ='" & txtAccountNo.Text & "'", CN, adOpenKeyset, adLockOptimistic
editdetails
End Sub
Will get values from a certain account: Code:
Public Sub editdetails()
On Error Resume Next
cboMode_Click
If rs_pay.State = adStateOpen Then rs_pay.Close
rs_loan.Open "SELECT * FROM v_loan WHERE [LOAN.ID]=" & rs("ID"), CN, adOpenStatic, adLockReadOnly
rs_pay.Open "SELECT * FROM LOANPAY WHERE LOANID =" & rs_loan("LOAN.ID"), CN, adOpenStatic, adLockReadOnly
With rs_loan
txtName.Text = rs_loan("NAME")
txtDescription.Text = rs_loan("PRODUCT.DESCRIPTION")
txtCategory.Text = rs_loan("CATEGORY.DESCRIPTION")
txtAmount.Text = Format(rs_loan("PRODUCT.AMT"), "#,##0.00")
txtMaker1.Text = rs("MAKER1")
txtMaker2.Text = rs("MAKER2")
cboMode.Text = rs_loan("MODE")
txtDownPayment.Text = Format(rs_loan("DOWNPAY"), "#,##0.00")
txtDiscount.Text = Format(rs_loan("DISC"), "#,##0.00")
txtInterest.Text = Format(rs_loan("INT"), "#,##0.00")
txtAmountPaid.Text = Format(rs_loan("PAID"), "#,##0.00")
txtBalance.Text = Format(rs_loan("BAL"), "#,##0.00")
cboTerms.Text = rs_loan("TERMS")
End With
rs_loan.Close
End Sub
New MySQL Code:
Search Textbox Code: Code:
Private Sub text1_change()
If foo = 0 Then Command3.Enabled = True Else Command3.Enabled = False
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM LOAN WHERE ACCOUNTNO ='" & Text1.Text & "'", CoNN, adOpenKeyset, adLockOptimistic
getInfo
End Sub
Get infos from a certain account: Code:
Private Sub getInfo()
On Error Resume Next
If rsPay.State = adStateOpen Then rsPay.Close
rsLoan.Open "SELECT * FROM qloan WHERE [LOAN.ID]=" & rs("ID"), CoNN, adOpenStatic, adLockReadOnly
rsPay.Open "SELECT * FROM LOANPAY WHERE LOANID =" & rsLoan("LOAN.ID"), CoNN, adOpenStatic, adLockReadOnly
With rsLoan
Text2.Text = rsLoan("NAME")
Text3.Text = rs("ADDRESS")
End With
rsLoan.Close
End Sub
Note: I just changed the variables on my new code, but structure is just the same.
Really need your help please, I'm gonna fail on my class if I didn't make it to work till tomorrow
-
Jan 15th, 2011, 04:59 AM
#4
Re: VB6 mysql Error(?)
I can't tell, because I have no idea what "didn't work now" is supposed to mean in this particular case... you should explain what you can about what the issue is.
Note that despite the first line of your post, there almost certainly is an error... but for some reason you have decided to hide any errors that occur. That doesn't stop the errors from happening, it just stops you from knowing what they are.
For more information, see the article What is wrong with using "On Error Resume Next"? from our Classic VB FAQs (in the FAQ forum)
-
Jan 15th, 2011, 05:19 AM
#5
Thread Starter
New Member
Re: VB6 mysql Error(?)
Thank you so much for replying sir, and sorry for my unclear post.
Here's what the code does:
Form has a textbox wherein if the data matches the one with the database(AccountNo) for example, it will populate textboxes, datepickers with values from the database. So,
Code:
if textboxvalue == recordset value then
text1.text = rs!name
text2.text = rs!address
text3.text = rs!age
and so on..
sorry if my post is unclear..please help me..im gonna really fail if I wont fix this till 12 midnight
-
Jan 15th, 2011, 05:27 AM
#6
Re: VB6 mysql Error(?)
Your explanation only tells us what is supposed to happen, not what is happening instead.
If you don't tell us what is wrong, there is almost nothing we can do to help you fix it.
You also haven't mentioned if you are still using On Error Resume Next
-
Jan 15th, 2011, 05:31 AM
#7
Thread Starter
New Member
Re: VB6 mysql Error(?)
Yes, that's what is supposed to happen.
I used 'on error resume next' because on mysql code, the line
Code:
rsLoan.Open "SELECT * FROM qloan WHERE [LOAN.ID]=" & rs("ID"), CoNN, adOpenStatic, adLockReadOnly
gives error '3021' saying that there is no record on the database.
When I tried that code on access backend, it works even without on error resume next. And by the way, that v_loan is a query on access while on mysql qLoan is a table..
-
Jan 15th, 2011, 06:52 AM
#8
Re: VB6 mysql Error(?)
You should never use On Error Resume Next in the way you have, all that does is make things worse.
As I mentioned in post #4 (and linked to more detail, which you really need to read), it does not get rid of the problem(s) at all, and it will not magically make the code work - all it does is hide the explanation of the problem(s) from you.
Now we know what the problem is, we can start solving it - as we should have been able to do just after post #3.
It seems that rs is a recordset, and for whatever reason it does not have any records in it.
Based on the code in text1_change in post #3, the most likely reason is that the current value of Text1.Text is not equal to any of the values in the ACCOUNTNO field. As the code runs every time the value of Text1.Text changes, that is not very surprising.
What you should do is check whether rs contains any records at the start of getInfo, and if it doesn't then do something appropriate (such as clearing the textboxes) instead of keeping going into a guaranteed error. eg:
Code:
Private Sub getInfo()
'make sure rs has data, if not clear the textboxes and exit
If rs.BOF And rs.EOF Then
Text2.Text = ""
Text3.Text = ""
Exit Sub
End If
'load the data related to the current rs record
If rsPay.State = adStateOpen Then rsPay.Close
rsLoan.Open "SELECT * FROM qloan WHERE [LOAN.ID]=" & rs("ID"), CoNN, adOpenStatic, adLockReadOnly
...
You should also do a similar check after opening rsLoan , because it is possible (but presumably less likely) that a similar issue will occur there too.
-
Jan 15th, 2011, 07:29 AM
#9
Thread Starter
New Member
Re: VB6 mysql Error(?)
on resume next works fine, it shows that my error is in this line:
Code:
rs_loan.Open "SELECT * FROM v_loan WHERE [LOAN.ID]=" & rs("ID"), CN, adOpenStatic, adLockReadOnly
should be
and it works like charm.
Thank you for your kind response
Last edited by si_the_geek; Jan 15th, 2011 at 07:40 AM.
Reason: fixed typo in tags
-
Jan 15th, 2011, 07:40 AM
#10
Re: VB6 mysql Error(?)
On Error Resume Next does not work fine, unless you particularly want to make your own life harder for no reason. There are times/places when it can be useful, but not in the way that you used it. The way you used it is purely creating bugs in your code intentionally - it does not give any benefit.
While it turns out that you had an extra issue to deal with (which you solved well), you should still be checking whether a recordset has records before you try to use them (for rs, and rs_loan etc).
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|