Search Access 2002 Database with VB Application

why are you HARD-CODING the productID to search for?

 Dim cmd As New OleDbCommand("SELECT * FROM PRODUCTS WHERE PROD_ID = '917369'", cn)

I would think you would use the value from the TextBox:

 Dim cmd As New OleDbCommand("SELECT * FROM PRODUCTS WHERE PROD_ID = '" & hounddog & "'", cn)

also, is the Pod_id a TEXT field in the database?  It appears to possibly be a NUMBER?

where are you trying to change the Row Number?  I see nothing here that would affect any row number.  It is not at all clear what purpose the 'row number' is supposed to serve.

AW

AW,
Well, to be perfectly honest, I didn't know that I could or how to insert a value into my SQL statement.  I thought the only values in a SQL statement could only be database terms or values.  You noticed the "hounddog" variable, I just put that variable in a little while ago, as an experiment but I never got to the point of knowing how to use it.  You've helped me already.  As you've probably guessed, I'm pretty new at VB.

Yes, you are correct, I want to use the value that will be entered in the textbox.

Yes, the PROD_ID field is a field name in the database.  '917369' is the primarykey value for a specific record.  Even though it is and will always be a number value I assigned it's data type to be a "Text" value in Access DB design view.  I find it easier to work with text values than numbers.  Access seems to have it's quirks or uniqueness.  If I were more familiar with working with different data types, I may have used the "Number" data type.  

I created my form using the Data Bound form wizard in VB.Net.  When it built my form, the controls were automatically created.  In the middle of the record navigation buttons there is a little view (or text box) and as I navigate through the records it permits me to see what probably is an index value for the record of data that I'm seeing in my text boxes.  For instance, I have 61 records in my test database, the record with the primarykey value (PROD_ID) is like record 52 of 61.  

When I launch my form, the formLoad event handler automatically fills the dataset.  To search for a specific record, I clear all the values from my textboxes using a clear event handler.  Next, what I want to do is enter a value in the txtPrdSrchNum txtbox, click the search event handler button, locate the record in the database (while counting the records and displaying the record count) and advancing to the "actual" record.  If my application advances to the correct row of data, I can update or delete, etc.  My program will let me make updates, but not to the correct row, e.g.  I clear my text boxes, execute my search event handler, the ExecuteReader reads the data and I append the values to my text boxes however, if I change a value, the values are reflected in the first record in my database, not the record associated with the 917369 PROD_ID value.  Therefore, when I launch my search event handler, the first thing my program needs to do is advance to the proper row first, then do the rest.

P.S.  The row number counter is not necessarily required.  It's just a visual indication which provides feedback to the enduser that they have reached a certain record.  As I stated above, VB.Net created the counter for me when I created the form using the Data Bound Form wizard.

Thanks for your interest in helping me and I look forward to hearing back from you!!

AW,
The view that displays the record count is actually a label (lblNavLocation).
BF

Well in ADO:

Private Sub cmdFindRecord_Click()

Dim objConn as ADODB.Connection
Dim objRec as ADODB.Recordset

Set objConn = New ADODB.Connection
Set objRec = New ADODB.Recordset

objConn.ConnectionString =  "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=A:\CCntry.mdb;"
'Assuming that is a correct connection string.

objConn.Open

objRec.Open "SELECT RecordID, First_Name, Last_Name FROM tblMaster WHERE RecordID = " & txtSearchID.Text, objConn

txtRecordID.Text = objRec!RecordID
txtFirstName.Text = objRec!First_Name
txtLastName.Text = objRec!Last_Name

objRec.Close

If objRec.State <> adStateClosed then
    objRec.Close
End If

Set objRec = nothing

If objConn.State <> adStateClosed Then
   objConn.Close
End If

Set objConn = Nothing

End Sub
--------------------

If you were searching for a String in the database then the SQL string would be a lil Different

objRec.Open "SELECT RecordID, First_Name, Last_Name FROM tblMaster WHERE RecordID = '" & txtSearchID.Text & "'", objConn

Need to enclose the value entered into the SQL string in Single Quotes.

-----

Hope this helps some.

OOps slight error, you wouldnt use objRec.Close twice, just once in the If Statment

Drifter,
Thanks for offering to help.  I tried your solution but ran into a too many arguments error following the SQL statement when the objRec value is assigned to the form textBox name.  I don't understand why, any help?  See code below.
BF


Private Sub btnCmdSrch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCmdSrch.Click
        Dim objConn As ADODB.Connection
        Dim objRec As ADODB.Recordset

        objConn = New ADODB.Connection()
        objRec = New ADODB.Recordset()

        objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=A:\CCntry.mdb;"
        'Assuming that is a correct connection string.
        objConn.Open()
        Dim hounddog As String
        Dim adStateClosed As Integer
        hounddog = txtPrdSrchNam.Text
        objRec.Open("SELECT RecordID, PROD_ID, PROD_NAM, PROD_DESC, CAT_ID, SUPP_ID, PRICE, STK_LOC, STK_LOC_EXT, JR, POP, BOXES, QTY, ITEM_COST, TOT_COST_2002 FROM PRODUCTS WHERE PROD_ID = ' " & hounddog & "'", objConn)

   >>    lblNavLocation.Text = objRec!RecordID
   >>    editPROD_ID.Text = objRec!PROD_ID


        objRec.Close()

        If objRec.State <> adStateClosed Then
            objRec.Close()
        End If

        objRec = Nothing

        If objConn.State <> adStateClosed Then
            objConn.Close()
        End If

        objConn = Nothing

    End Sub

try this:

    objRec.Open("SELECT RecordID, PROD_ID, PROD_NAM, PROD_DESC, CAT_ID, SUPP_ID, PRICE, STK_LOC, STK_LOC_EXT, JR, POP, BOXES, QTY, ITEM_COST, TOT_COST_2002 FROM PRODUCTS WHERE PROD_ID = ' " & hounddog & "'", objConn)
if no objRec.EOF then
       lblNavLocation.Text = objRec!RecordID
       editPROD_ID.Text = objRec!PROD_ID
else
    MsgBox "There are no records for ID= " & houndog, vbOkOnly
end if

also what is the field RecordID, is that a Field n you table?  If it is NOT, then having it identified in yur SQL will generate an error.  You can only select fields that are either DEFINED in the table, or are 'calculated' in the SQL.  You cannot create fields out of nothing.

AW

AW,
I do not have a RecordID field in my database.  I didn't think I needed one because I was using the PROD_ID field as my primarykey; I thought that was all I would need for sorting, filtering, referential integrity, etc.

Right now my reader is working fine, since you clued me in about being able to insert a variable in my SQL statement. So I can enter a PROD_ID value in my search textBox, click the search event handler button, and read the values back into the text boxes on my form.  Works great.  However, my intent for this search button was to use the PROD_ID to find or advance to the row, for the purposes of updating, verifying values, deleting, etc.  When the data is read back into my form, I can make changes and save them, but the changes are effected upon the first record in the database, not the actual record that I searched for.  I'm wondering if I should be using some other OleDbCommand object or approaching this some other way.  Would it be best to add a RecordID to my database?

Thanks Again for your help,
BF

AW,
I tried your code and got the same error message as I did when I tried Drifter's code.  Here's the error message I get:  "Too many arguments to 'Public Overridable Overloads ReadOnly Default Property Fields() As ADODB.Fields."  I do not know what that means, do you?

I went ahead and added a RecordID filed to my DB and I'm experimenting with it now.

Thanks Much,
BF

the error, as I indicated, is because you re using a NON-EXISTANT field in th SQL.  change it to:

 objRec.Open("SELECT  PROD_ID, PROD_NAM, PROD_DESC, CAT_ID, SUPP_ID, PRICE, STK_LOC, STK_LOC_EXT, JR, POP, BOXES, QTY, ITEM_COST, TOT_COST_2002 FROM PRODUCTS WHERE PROD_ID = ' " & hounddog & "'", objConn)
if no objRec.EOF then
      ' lblNavLocation.Text = objRec!RecordID ' this field does not exist, so you can't use it
       editPROD_ID.Text = objRec!PROD_ID
else
    MsgBox "There are no records for ID= " & houndog, vbOkOnly
end if


how are you saving the changes back to th database?

in code, or are you using BOUND controls?

AW

AW,
Here's the code for the search event handler, I'm still getting the same error.  The Field Name RecordID is out of the SQL statement.  The only error I get is the Too Many arguments for these two lines:   editPROD_ID.Text = objRec!PROD_ID
                          editPROD_NAM.Text = objRec!PROD_NAM

I get the GREEN squiggly line under objRec!PROD_ID, etc.

Private Sub btnCmdSrch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCmdSrch.Click
        Dim objConn As ADODB.Connection
        Dim objRec As ADODB.Recordset

        objConn = New ADODB.Connection()
        objRec = New ADODB.Recordset()

        objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=A:\CCntry.mdb;"
        'Assuming that is a correct connection string.
        objConn.Open()
        Dim hounddog As String

        hounddog = txtPrdSrchNam.Text
        objRec.Open("SELECT  PROD_ID, PROD_NAM, PROD_DESC, CAT_ID, SUPP_ID, PRICE, STK_LOC, STK_LOC_EXT, JR, POP, BOXES, QTY, ITEM_COST, TOT_COST_2002 FROM PRODUCTS WHERE PROD_ID = ' " & hounddog & "'", objConn)
        If Not objRec.EOF Then
            ' lblNavLocation.Text = objRec!RecordID ' this field does not exist, so you can't use it
            editPROD_ID.Text = objRec!PROD_ID
            editPROD_NAM.Text = objRec!PROD_NAM
        Else
            MsgBox("There are no records for ID= " & hounddog, vbOKOnly)
        End If


    End Sub

Here's my code for updating the database, I'm using bound controls.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Try
            'Attempt to update the datasource.
            Me.UpdateDataSet()
        Catch eUpdate As System.Exception
            'Add your error handling code here.
            'Display error message, if any.
            System.Windows.Forms.MessageBox.Show(eUpdate.Message)
        End Try
        Me.objDsProducts_PositionChanged()

    End Sub

I appreciate your help and look forward to hearing from you again soon.

BF

thank you, and glad to be of assistance.

AW