Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 30th, 2005, 01:55 AM
Registered User
 
Join Date: Nov 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using .Find with ADO

hi..

I want to find a record in a recordset and after that, I want to retrieve data from the previous record of the found record. But when I reached to the .find sTmp, I got this error

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. 3001

here is the code I used..


    cnn.CursorLocation = 3
    cnn.Open "Provider=SQLOLEDB.1;**************"
    sTmp = Trim(frmCustomer.txtCustomerCode.Text)

    sql = "SELECT * FROM Customer Where CustomerCode='" & sTmp & "'"
    rstCust.Open sql, cnn, adOpenStatic, adLockOptimistic

    If rstCust.RecordCount <> 0 Then
        rstCust1.Open "Customer", cnn, adOpenStatic, adLockOptimistic
        rstCust1.Find sTmp
        rstCust1.MovePrevious
    End if


The way of using .Find is ok with DAO. But I can't use it here. Is there any wrong with my code or whatelse I need to do.. Is there other way to point out the required record and go to the previous and next record of it.

Tks alot for any help and reply..

Reply With Quote
  #2 (permalink)  
Old January 6th, 2006, 07:32 AM
Registered User
 
Join Date: Nov 2005
Location: , , India.
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi phi
There is a problem in your query. Your query is retrieving only
one record. So moveprevious method is raising an erro.

Try this one

sql = "SELECT * FROM Customer"
rstCust.Open sql, cnn, adOpenStatic, adLockOptimistic

do while not rsCust.EOF
    if rsCust!CustomerCode= '" & sTmp & "'"
        exit DO
    end if
rsCust.moveNext
loop
rsCust.movePrevious

Reply With Quote
  #3 (permalink)  
Old January 16th, 2006, 07:11 AM
Friend of Wrox
Points: 464, Level: 7
Points: 464, Level: 7 Points: 464, Level: 7 Points: 464, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2005
Location: Colombo, , Sri Lanka.
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to nalla Send a message via Yahoo to nalla
Default



Hi phi kyo,

you don't need to select and then use find method to locate a record in the database or loop through the whole recordset. There are more efficient methods in ADO. Write a sql statement directly to locate the record in database. include the input customer code in the WHERE clause.


Private Sub Command1_Click()

Dim dbConnection As New ADODB.Connection
With dbConnection
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=SQLOLEDB.1;************"
    .Open
End With

Dim rsCustomer As New ADODB.Recordset
With rsCustomer
    .Open "SELECT * FROM Customers WHERE CustomerCode='" & txtCustomerCode.Text & "'", dbConnection, adOpenForwardOnly, adLockReadOnly
End With

If rsCustomer.EOF = False Then
    ' Record found
    MsgBox "Record found", vbInformation
Else
    ' Record not found
    MsgBox "Record not found", vbCritical
End If

rsCustomer.Close
Set rsCustomer = Nothing

dbConnection.Close
Set dbConnection = Nothing

End Sub

Please let us no if you need more help.
   nalla




nalaka hewage
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Find Method of ADO martinaccess Access 3 November 21st, 2007 07:35 AM
Find and noMatch ADO knowledge76 Access VBA 1 May 9th, 2005 03:01 PM
can't find sample code r professional ADO.net prog calvinpost ADO.NET 2 January 27th, 2004 04:01 AM
ADO Find using wildcard on an integer dfbosse VB How-To 2 June 26th, 2003 02:14 AM
ADO could ADO counot find the specified provider. Rob Collie Classic ASP Databases 2 June 9th, 2003 04:12 AM



All times are GMT -4. The time now is 05:04 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.