Solved

Search Access 2002 Database with VB Application

Posted on 2003-11-21
13
707 Views
Last Modified: 2008-02-01
I'm looking for  some assistance to read a record from an Access 2002 database from a Vb form.  I want to be able to enter a primarykey value in a textbox on a VB form, click a search button and invoke a click event handler that will: (1) find the record; (2)update a row counter to the row where the record is located, return the record associated with the primarykey and populate the textboxes on the form with the field values from the row.  My code below will find the record and populate the text boxes on the form but it doesn't count the row and update the row counter on my form.  Also, when I find the record and read the data back into my form, I can't update any of the values because I'm not actually updating the correct record.  My code only lets me read the record field values into blank text boxes.  Please let me know if you need any additional info & thanks.  See my code below.

Private Sub btnCmdSrch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCmdSrch.Click
        Dim cn As New OleDbConnection()
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=A:\CCntry.mdb;"
        cn.Open()
        Dim hounddog As String
        hounddog = txtPrdSrchNum.Text
        Dim cmd As New OleDbCommand("SELECT * FROM PRODUCTS WHERE PROD_ID = '917369'", cn)

        Dim dr As OleDbDataReader = cmd.ExecuteReader()

        Do While dr.Read()
            editPROD_ID.AppendText(dr(0).ToString)
            editPROD_NAM.AppendText(dr(2).ToString)
            editPROD_DESC.AppendText(dr(3).ToString)
            editSTK_LOC.AppendText(dr(4).ToString)
            editSTK_LOC_EXT.AppendText(dr(5).ToString)
            editCAT_ID.AppendText(dr(1).ToString)
            editSUPP_ID.AppendText(dr(6).ToString)
            editJR.AppendText(dr(7).ToString)
            editPOP.AppendText(dr(8).ToString)
            editBOXES.AppendText(dr(9).ToString)
            editQTY.AppendText(dr(10).ToString)
            editPRICE.AppendText(dr(11).ToString)
            editITEM_COST.AppendText(dr(12).ToString)
            editTOT_COST_2002.AppendText(dr(13).ToString)
        Loop

        dr.Close()
        cn.Close()
    End Sub
0
Comment
Question by:bfrancisco
  • 6
  • 5
  • 2
13 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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


0
 

Author Comment

by:bfrancisco
Comment Utility
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!!
0
 

Author Comment

by:bfrancisco
Comment Utility
AW,
The view that displays the record count is actually a label (lblNavLocation).
BF
0
 
LVL 2

Expert Comment

by:Drifter88zxtW
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Drifter88zxtW
Comment Utility
OOps slight error, you wouldnt use objRec.Close twice, just once in the If Statment
0
 

Author Comment

by:bfrancisco
Comment Utility
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
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
0
 

Author Comment

by:bfrancisco
Comment Utility
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
0
 

Author Comment

by:bfrancisco
Comment Utility
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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
0
 

Author Comment

by:bfrancisco
Comment Utility
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
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
Comment Utility
this is one thing that I just noticed:

       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)


should be:

       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)

the extra space before the string houndog might cause the value to be ignored.


I do not understand why you would get 'Too many arguments' as the erro message.  Bu try making this chane, as well:

change:

            editPROD_ID.Text = objRec!PROD_ID
            editPROD_NAM.Text = objRec!PROD_NAM

to:

            editPROD_ID.Text = objRec.Fields("PROD_ID").Value
            editPROD_NAM.Text = objRec.Fields("PROD_NAM").Value

as for the other problem, I NEVER EVER use Bound controls for any purpose whatsoever, and thus always update the database using the Recordset.Update method.  So I can't offer much in the way of assistance there.

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
thank you, and glad to be of assistance.

AW
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now