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

• • Reply

  12 years ago

  by biswajit_k_roy

  Biswajit Roy

  , India

  Joined 13 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.

  Report abuse
• • Reply

  12 years ago

  by satyasahu

  satya narayana sahu

  , India

  Joined 13 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 &"'")
  

  Report abuse
• • Reply

  12 years ago

  by biswajit_k_roy

  Biswajit Roy

  , India

  Joined 13 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?

   

   

   

   

   

  Report abuse