locked
ADO retrieving value RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    how do i compare the value from the user input with the value in the database and then select another value from that same row and print out

    For example

    StudentID           name

    1                          John

    2                          Matt

    3                          Victor

    now the user put in 2 so i should go into database get the student id and then compare with user input. if its the same print out Matt, so here is the code for that but because i'm working with vb2005 so im not really sure what to do. Could you guys help me out. Thank you.

    Dim OpenConnection As New ADODB.Connection

    Dim records As New ADODB.Recordset

    Dim RecordData As ADODB.Recordset

    Dim strRecordData As String

    OpenConnection = New ADODB.Connection

    OpenConnection .Provider = "Microsoft.Jet.OLEDB.4.0"

    OpenConnection .ConnectionString = "C:\Student.mdb"

    OpenConnection .Open()

    RecordData = New ADODB.Recordset

    RecordData .Open("StudentTable", OpenConnection, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockPessimistic)

    strRecordData = "Select studentid, name =" & student_id

    strRecordData .source = strRecordData (.source is not available in the drop down selection for vb2005)

     then the last thing i want to do is to say this

    take taht student id from database then print out name from the table that match with the student id

    so "output = name get from table"?

     

     

    Wednesday, March 22, 2006 4:37 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim stid As String
    Dim lc As Integer

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Student.mdb;"
    cmd.ActiveConnection = cn

    stid = InputBox("Enter Student ID")

    If Trim(stid) <> "" Then
       
        'Method 1 - Open a recordset based on a table
        rs.Open "studenttable", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        Do While Not rs.EOF
            If rs.Fields(0).Value = CInt(stid) Then
                MsgBox "Student ID : " & rs.Fields(0).Value & "  Name : " & rs.Fields(1).Value
               Exit Do
            End If
            lc = lc + 1
            rs.MoveNext
        Loop
        If lc = rs.RecordCount Then MsgBox "Match not found"
        Set rs = Nothing
       
        'Method 2 - Open a recordset based on a command object
        cmd.CommandType = adCmdText
        cmd.CommandText = "select * from studenttable where StudentID = " & CInt(stid)
        Set rs = cmd.Execute
        While Not rs.EOF
            MsgBox "Student ID : " & rs.Fields(0).Value & "  Name : " & rs.Fields(1).Value
            rs.MoveNext
        Wend
        Set rs = Nothing


        'Method 3 - Open based on SQL statement
        rs.Open "select * from studenttable where StudentID = " & CInt(stid), cn, , , adCmdText
        While Not rs.EOF
            MsgBox "Student ID : " & rs.Fields(0).Value & "  Name : " & rs.Fields(1).Value
            rs.MoveNext
        Wend
    End If

    Set rs = Nothing
    Set cmd = Nothing
    cn.Close
    ================================================================================================

    You can also refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjodbrec.asp for more information about Recordset.

    Hope this helps

    Saturday, March 25, 2006 3:35 AM