Results 1 to 10 of 10

Thread: VB6 mysql Error(?)

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2010
    Posts
    15

    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:
    1. "search textbox code:"]Private Sub Text1_change()
    2. If Text1.Text <> "" Then Command3.Enabled = True Else Command3.Enabled = False
    3. If rs.State = adStateOpen Then rs.Close
    4. rs.Open "SELECT * FROM LOAN WHERE ACCOUNTNO ='" & Text1.Text & "'", CN, adOpenKeyset, adLockOptimistic
    5.  
    6. 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.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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)

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2010
    Posts
    15

    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:
    1. Private Sub txtAccountNo_Change()
    2. If Len(txtAccountNo) = 0 Then cmdNewPayment.Enabled = False: Exit Sub Else cmdNewPayment.Enabled = True
    3.     If rs.State = adStateOpen Then rs.Close
    4.     rs.Open "SELECT * FROM LOAN WHERE ACCOUNTNO ='" & txtAccountNo.Text & "'", CN, adOpenKeyset, adLockOptimistic
    5.     editdetails
    6. End Sub
    Will get values from a certain account: Code:
    1. Public Sub editdetails()
    2. On Error Resume Next
    3. cboMode_Click
    4. If rs_pay.State = adStateOpen Then rs_pay.Close
    5. rs_loan.Open "SELECT * FROM v_loan WHERE [LOAN.ID]=" & rs("ID"), CN, adOpenStatic, adLockReadOnly
    6. rs_pay.Open "SELECT * FROM LOANPAY WHERE LOANID =" & rs_loan("LOAN.ID"), CN, adOpenStatic, adLockReadOnly
    7. With rs_loan
    8.     txtName.Text = rs_loan("NAME")
    9.     txtDescription.Text = rs_loan("PRODUCT.DESCRIPTION")
    10.     txtCategory.Text = rs_loan("CATEGORY.DESCRIPTION")
    11.     txtAmount.Text = Format(rs_loan("PRODUCT.AMT"), "#,##0.00")
    12.     txtMaker1.Text = rs("MAKER1")
    13.     txtMaker2.Text = rs("MAKER2")
    14.     cboMode.Text = rs_loan("MODE")
    15.     txtDownPayment.Text = Format(rs_loan("DOWNPAY"), "#,##0.00")
    16.     txtDiscount.Text = Format(rs_loan("DISC"), "#,##0.00")
    17.     txtInterest.Text = Format(rs_loan("INT"), "#,##0.00")
    18.     txtAmountPaid.Text = Format(rs_loan("PAID"), "#,##0.00")
    19.     txtBalance.Text = Format(rs_loan("BAL"), "#,##0.00")
    20.     cboTerms.Text = rs_loan("TERMS")
    21. End With
    22.  
    23. rs_loan.Close
    24.  
    25. End Sub

    New MySQL Code:

    Search Textbox Code: Code:
    1. Private Sub text1_change()
    2. If foo = 0 Then Command3.Enabled = True Else Command3.Enabled = False
    3. If rs.State = adStateOpen Then rs.Close
    4. rs.Open "SELECT * FROM LOAN WHERE ACCOUNTNO ='" & Text1.Text & "'", CoNN, adOpenKeyset, adLockOptimistic
    5. getInfo
    6. End Sub

    Get infos from a certain account: Code:
    1. Private Sub getInfo()
    2. On Error Resume Next
    3. If rsPay.State = adStateOpen Then rsPay.Close
    4. rsLoan.Open "SELECT * FROM qloan WHERE [LOAN.ID]=" & rs("ID"), CoNN, adOpenStatic, adLockReadOnly
    5. rsPay.Open "SELECT * FROM LOANPAY WHERE LOANID =" & rsLoan("LOAN.ID"), CoNN, adOpenStatic, adLockReadOnly
    6. With rsLoan
    7.     Text2.Text = rsLoan("NAME")
    8.     Text3.Text = rs("ADDRESS")
    9.     End With
    10. rsLoan.Close
    11. 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

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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)

  5. #5

    Thread Starter
    New Member
    Join Date
    Dec 2010
    Posts
    15

    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

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Dec 2010
    Posts
    15

    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..

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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.

  9. #9

    Thread Starter
    New Member
    Join Date
    Dec 2010
    Posts
    15

    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
    Code:
    [LOAN.ID]
    should be
    Code:
    `LOAN.ID`
    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

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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
  •  



Click Here to Expand Forum to Full Width