ADO.Net Help - Search Database and populate a textbox with the result for a particular row.

db India
  • 9 years ago

    Hi there I'am new to VB.net (ADO.Net) and having some trouble with how to populate a textbox from an access database based on a search box.

    What I am trying to achieve is a form with say three textboxes.

    txtSearch    txtPart     txtDescription

    Database has 3 columns Jobnumber  - Partnumber - Description (The Jobnumber is not the ID number)

    The user enters information into the search textbox to look for a job number and as the text is changed the database is searched against the Jobnumber column and returning the associated rows information into the part and description into the other two textboxes. Part & description textboxes will be read only so the user only has one box to fill.

     I would like to do this without displaying a datagridview on the form.

    Does anybody have any ideas or snippets to do the above. I have searched the net but this type of thing is not well documented. I have followed several tutorials but they seem to use the datatable to display the results but this is no good to me.

    Any Help would be greatly appreciated.

  • 9 years ago

    1. Add a class in your project.

    2. add the following code in that class

    imports system.data
    imports system.data.oledb

    public class class1

    private conn as oledbconnection
    private adapt as oledbcommand
    private dset as dataset
    private tabl as datatable
    private sqlstr as string

    public sub new ()
    conn = new oledbconnection("provider= microsoft.jet.oledb.4.0; data source= product.mdb")
    'You have to specify your database name with path instead of product.mdb
    'however if you have stored the database in appilcations bin\debug directory there is no need to specify path
    end sub

    private sub generatesql(field as string, table as string, condition as string )
    sqlstr = "select " & field &" from " & table &" where "& condition
    generaterows()
    end sub

    private sub generaterows()
    adapt = new oloedbdataadapter(sqlstr,conn)
    dset= new dataset
    adapt.fill(dset)
    tabl= new datatable
    tabl= dset.tables(0)
    end sub

    public function getvalue(field as string, table as string, condition as string ) as string
    generatesql(field,table,condition)
    return tabl.rows.item(0).item(0)
    end sub
    end class

    3. open your form and put 3 textboxes and name them accordingly.
    4. write the following code in the lostfocus event of txtsearch textbox.

    dim myobj as new class1
    ' assuming "item" as tablename
    ' use the following format ' for text data type enclose '" & var &"'
    ' for numeric it is " & var &"
    ' and for date #" & var &"#

    txtpart.text = myobj.getvalue("partnumber","item","partnumber ='" & txtsearch.text &"'")
    txtdescription.text = myobj.getvalue("description","item","partnumber ='" & txtsearch.text &"'")

  • 9 years ago

     Thanks for your response I have tried to follow your code but run into a few problems so I have posted the code I have so far and added comments next to the problem points. If you could give us some pointers on what I have done wrong that would be great. I can see your thinking on this but just not sure where its gone wrong.

    I have posted some info about my setup at the bottom if this helps.

    thanks in advance.

     

    Imports System.Data

    Imports System.Data.OleDb

     

    Public Class Class1

    Private conn As OleDbConnection

    Private adapt As OleDbCommand ' Not Sure if this should be OleDbCommand or OleDbDataAdapter? If OleDbCommand then I get the below errors

    'Error 1 Value of type 'System.Data.OleDb.OleDbDataAdapter' cannot be converted to

    'System.Data.OleDb.OleDbCommand'.

    'Error 2 'Fill' is not a member of 'System.Data.OleDb.OleDbCommand'.

    Private dset As DataSet Private tabl As DataTable

    Private sqlstr As String

     

    Public Sub New1() ' Had to change to New1() to stop the below error.

    'Warning 1 'Public Sub New()' in designer-generated type

    'WindowsApplication1.Class1' should call InitializeComponent method.

    conn = New OleDbConnection("DSN = LabelData") ' Using a DSN Source for trial purposes, This will change later.

    End Sub

    Private Sub generatesql(ByVal field As String, ByVal table As String, ByVal condition As String)sqlstr = "select " & field & " from " & table & " where " & condition

    generaterows()

    End Sub

     

    Private Sub generaterows()

    adapt = New OleDbDataAdapter(sqlstr, conn)

    dset = New dataset

    adapt.Fill(dset)

    tabl =
    New datatable

    tabl = dset.tables(0)

    End Sub

    Public Function getvalue(ByVal field As String, ByVal table As String, ByVal condition As String) As String

    generatesql(field, table, condition)

    Return tabl.rows.item(0).item(0)

    End Function

    Private Sub txtSearch_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtSearch.LostFocus

    Dim myobj As New Class1

    txtPart.Text = myobj.getvalue("partnumber", "parts", "partnumber ='" & txtSearch.Text & "'")

    txtDescription.Text = myobj.getvalue("description", "parts", "part ='" & txtSearch.Text & "'")

    End Sub

    End Class

     --------------------- 

    My Database is called LabelData and has one Table which is called Parts. The Parts Table has four Columns

    ID, WorksOrder, Partnumber, Description

    ----------------------

    The form should search the WorksOrder Column for a match and display the part and Description for that number in two text boxes.

    The Form has three text boxes  txtSearch, txtPart, txtDescription.

    ----------------------

    I am not sure is textchanged would be better than lostfocus as I would later like to make txtpart and txtDescription Readonly?

     

     

     

     

     

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Computer Science is no more about computers than astronomy is about telescopes.” - E. W. Dijkstra