Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

How to 'seek' in a database using ado?

skihardskihard Posts: 37Member
I just had the user input some info with this command:
adoProject.Recordset.Fields("SSN").Value = Trim(InputBox("Enter SSN:"))

Now the info is in: textSSN.text

I would like to do a search of the table to see if the SSN is in the database or not. I'm guessing I would do a 'Seek' type command, but I'm not sure how to set it up. Here is what I'm guessing it should be close to:

If adoProject.Recordset.Seek(textSSN.text) Then
Do whatever...

Can anyone tell me if this command would do what I want it to, and if so, how to set it up correctly?
Thanks!

Comments

  • doofusboydoofusboy Posts: 256Member
    : I just had the user input some info with this command:
    : adoProject.Recordset.Fields("SSN").Value = Trim(InputBox("Enter SSN:"))
    :
    : Now the info is in: textSSN.text
    :
    : I would like to do a search of the table to see if the SSN is in the database or not. I'm guessing I would do a 'Seek' type command, but I'm not sure how to set it up. Here is what I'm guessing it should be close to:
    :
    : If adoProject.Recordset.Seek(textSSN.text) Then
    : Do whatever...
    :
    : Can anyone tell me if this command would do what I want it to, and if so, how to set it up correctly?
    : Thanks!
    :
    To 'Seek' (find) a record in a database table, you need to use a SQL SELECT statement. Something like the following:

    Dim strSQL As String

    strSQL = "SELECT * FROM you_table_name WHERE SSN = " & textSSN.text

    This says to select all the records in the database table that have an entry in the "SSN" column that is the exact same as what was entered in your textSSN textbox.

    You then need to use your ADO connection object to Execute the SQL statement and assign the recordset returned to your recordset object with something like this:

    Set recSet = conDB.Execute(strSQL)

    This assumes you have previously declared the connection and recordset objects previously with something like this:
    Public conDB As ADODB.Connection
    Public recSet As ADODB.Recordset

    AND have initialized the connection object with something like this:
    Set conDB = New ADODB.Connection

  • skihardskihard Posts: 37Member
    [b][red]This message was edited by skihard at 2003-6-15 17:20:26[/red][/b][hr]
    I would be embarrassed to admit how long I have been trying to accomplish this one task, but I am still having no luck at all. So, in hope of holding off complete insanity a bit longer here is all my code for your review:

    Private Sub cmdAdd_Click()
    On Error GoTo Errorhandler
    adoProject.Recordset.AddNew
    adoProject.Recordset.Fields("LName").Value = Trim(InputBox("Enter last name:"))
    adoProject.Recordset.Fields("FName").Value = Trim(InputBox("Enter first name:"))
    adoProject.Recordset.Fields("MINT").Value = Trim(InputBox("Enter middle initial:"))
    adoProject.Recordset.Fields("SSN").Value = Trim(InputBox("Enter SSN:"))

    adoProject.Recordset.Fields("BDate").Value = Trim(InputBox("Enter birthdate (XX/XX/XX):"))
    adoProject.Recordset.Fields("SEX").Value = Trim(InputBox("Enter sex (M/F):"))
    adoProject.Recordset.Fields("ADDRESS").Value = Trim(InputBox("Enter address (Street/City/State):"))
    adoProject.Recordset.Fields("SALARY").Value = Trim(InputBox("Enter salary (Yearly):"))
    adoProject.Recordset.Fields("SUPERSSN").Value = Trim(InputBox("Enter supervisor's SSN:"))
    adoProject.Recordset.Fields("DNO").Value = Trim(InputBox("Enter department number:"))
    adoProject.Recordset.Update
    MsgBox "Record Added", , "Message"

    Errorhandler:
    MsgBox "Error, try and enter again!", , "Message"
    adoProject.Recordset.CancelUpdate

    End Sub


    Private Sub cmdDelete_Click()
    Confirm = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
    If Confirm = vbYes Then
    adoProject.Recordset.Delete
    MsgBox "Record Deleted!", , "Message"
    Else
    MsgBox "Record Not Deleted!", , "Message"
    End If
    End Sub

    Private Sub cmdExit_Click()
    End
    End Sub

    Private Sub cmdNext_Click()
    If Not adoProject.Recordset.EOF Then
    adoProject.Recordset.MoveNext
    If adoProject.Recordset.EOF Then
    adoProject.Recordset.MovePrevious
    End If
    End If
    End Sub

    Private Sub cmdPrev_Click()
    If Not adoProject.Recordset.BOF Then
    adoProject.Recordset.MovePrevious
    If adoProject.Recordset.BOF Then
    adoProject.Recordset.MoveNext
    End If
    End If
    End Sub

    Private Sub cmdSave_Click()
    adoProject.Recordset.Fields("LName") = TextLName.Text
    adoProject.Recordset.Update
    End Sub

    In the cmdAdd area, I am still trying to find some code that will look at the database and see if the SSN just added is there or not.
    The database is called: projectdatabase
    And the current table is called: Employee

    Let me know if any more info is needed.
    Any more help here would be appreciate beyond belief!



    : : I just had the user input some info with this command:
    : : adoProject.Recordset.Fields("SSN").Value = Trim(InputBox("Enter SSN:"))
    : :
    : : Now the info is in: textSSN.text
    : :
    : : I would like to do a search of the table to see if the SSN is in the database or not. I'm guessing I would do a 'Seek' type command, but I'm not sure how to set it up. Here is what I'm guessing it should be close to:
    : :
    : : If adoProject.Recordset.Seek(textSSN.text) Then
    : : Do whatever...
    : :
    : : Can anyone tell me if this command would do what I want it to, and if so, how to set it up correctly?
    : : Thanks!
    : :
    : To 'Seek' (find) a record in a database table, you need to use a SQL SELECT statement. Something like the following:
    :
    : Dim strSQL As String
    :
    : strSQL = "SELECT * FROM you_table_name WHERE SSN = " & textSSN.text
    :
    : This says to select all the records in the database table that have an entry in the "SSN" column that is the exact same as what was entered in your textSSN textbox.
    :
    : You then need to use your ADO connection object to Execute the SQL statement and assign the recordset returned to your recordset object with something like this:
    :
    : Set recSet = conDB.Execute(strSQL)
    :
    : This assumes you have previously declared the connection and recordset objects previously with something like this:
    : Public conDB As ADODB.Connection
    : Public recSet As ADODB.Recordset
    :
    : AND have initialized the connection object with something like this:
    : Set conDB = New ADODB.Connection
    :
    :



Sign In or Register to comment.