VB10 - Search a Access DB with input

[Rawns]

It's been a good few years since I dabbled in VB and I'm very very rusty. What I'm looking to do is search an access database with user input.

 

I have a single table set up in an access database called school_info with the following fields and data:

 

Tres________DfE________Name_________Type

001________1234________School 1________Primary

002________4321________School 2________Secondary

etc

etc

 

The Tres and DfE fields are show in text boxes on the VB form, and the school and Type are show in lables. What I want to do is be able to type in either a Tres or DfE number and have the other lables auto populate with the relevant data thats stored in the database table.

 

Any pointers in a way to do this would be greatly appreciated!

[matt40k]

Funny, I've looked at doing something like this, but I've not got very far. I would recommend using a MSSQL database rather then access for starts.

[SYNACK]

You could do it with a LIKE query

 

Select * from school_info WHERE DfE LIKE "your search" or Tres LIKE "your search"

 

I think this should work but you may need to do it in two querys thanks to Access numerous limitations. I would also recommend MSSQL and there is even an easy upgrade wizard in Access to dump your current design and data on a SQL instance.

[Rawns]

I went with Access purely because I could import all the info straight off of a Excel Spreadsheet, and as it's only every going to be used to look up info, I though it would be simple enough. Seems VB has changed a lot since I last used it. All other aspects of the app are working apart from the ability to search for the info (which is the key feature!)

[mac_shinobi]

Is this a form within access that you are linking to the database records / table using vba or is this literally a seperate VB .NET project that you are linking to an access database ?

[Rawns]

It's a septate VB .NET application that's linked to an Access DB.

[CESIL]

Use the "on change" event (I think it is still called this!).

 

Create the VB code in a module and then use the "on change" for each text box to trigger the updating of the labels.

 

I would need more detail about your project to help much more...

[Rawns]

Right, I'm using Visual Basic 2010 Express and I've created this form:

 

 

I've linked in an Access DB using the "Add new data source" feature:

 

 

I've added each field onto the form and the first record automatically populates. All the other areas (copy, close etc) work a treat but I just cannot seem to put in a Treasury or DfE number and click go to populate the other fields automatically. Someone on the MSDN forums posted this code as a suggestion but it's a bit beyond me:

 

  Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
   Dim SQLs As String
   Dim conditionNum As Integer = 0
   SQLs = "Treasury where 1=1 "
   ' add Tres condition
   If Trim(TreasuryText.Text) <> "" Then
     SQLs = SQLs & " and tres = '" & Trim(TreasuryTxt.Text) & "'"
     conditionNum = conditionNum + 1
   End If
 
   'handle condition
   If conditionNum = 0 Then
     ' do something
     MsgBox("Invalid Treasury Number!")
   Else
     ' DO DB query and populate feild
     Dim command1 As SqlCommand = New SqlCommand(SQLs, conn)
     Dim dt1 As New DataTable()
     Dim da1 As New SqlDataAdapter()

     da1.SelectCommand = command1
     da1.Fill(dt1)
     TreasuryTxt.Text = dt1.Rows(0)(0)
     DfETxt.Text = dt1.Rows(0)(1)
     School.Text = dt1.Rows(0)(2)
     SA_PWD.Text = dt1.Rows(0)(3)
     S2S_PWD.txt = dt1Rows(0)(4)
    End If
 End Sub

 

I've played around but cannot get it to work.

Edited February 22, 2011 by Rawns

[Rawns]

Never mind, got it working! Success!

[mac_shinobi]

any chance you can post back what you did to get it to work.

[Rawns]

Here we go. This is the code linked to the button. (I did not have an open connection to the database!):

 

Private Sub TresSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TresSearch.Click

       Dim SQL As String
       Dim conditionNum As Integer = 0
       Dim conn As New OleDb.OleDbConnection
       'Connect to Access 2007/2010 Database
       conn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;data source=data.accdb;"

       ' SQL Command
       SQL = "select * from data where 1=1 "
       ' add Tres condition
       If Trim(TreasuryTxt.Text) <> "" Then
           SQL = SQL & " and Treasury = '" & Trim(TreasuryTxt.Text) & "'"
           conditionNum = conditionNum + 1
       End If

       'If
       If conditionNum = 0 Then
           ' do something
           SchoolTxt.Text = "Invalid Treasury Number!"
       Else

           ' DO DB query and populate feild
           Dim command1 As OleDb.OleDbCommand = New OleDb.OleDbCommand(SQL, conn)
           Dim dt1 As New DataTable()
           Dim da1 As New OleDb.OleDbDataAdapter()


           da1.SelectCommand = command1
           da1.Fill(dt1)
           DfETxt.Text = dt1.Rows(0)(1)
           SchoolTxt.Text = dt1.Rows(0)(2)
           SA_Pwd.Text = dt1.Rows(0)(3)
           S2S_Pwd.Text = dt1.Rows(0)(4)
       End If
   End Sub

 

One issue I am having is that if you search for a treasury number that does not exist, it errors out!

 

Any suggestions on trapping that error would be appreciated!

[mac_shinobi]

what error do you get because in the code at least in one section it has the error message stating that the number is not valid :

 

If conditionNum = 0 Then
           ' do something
           SchoolTxt.Text = "Invalid Treasury Number!"
       Else

I will install and play about with vb .net 2010 later ( this evening or this weekend ). Any chance of a dummy database so I have the correct fields / records etc , also any chance of doing this in access 2003 as I do not have 2007 or 2010

 

I remember doing something like this at uni and the code I used literally looped through all the ID records to check if the treasury number entered matched any of the ones it looped through, if it did then it would pull out the rest of the information and if not then I could make a label in bold red come up with an error message ie

 

Treasury number does not exist, please re enter the correct number and try again

 

something to that effect anyway - you will have to use the trim function to ensure there are no blank spaces before or after the number entered ie

 

Dim intNumb As Integer

 

intNumb = Trim(txtNumber.Text)

Edited February 23, 2011 by mac_shinobi

[Rawns]

The message "Invalid Treasury Number!" Populates if the Field is blank when you click on "Go"

 

If an unknown treasury number is entered, the app crashes and VB reports "There is no row at position 0".

 

Here is a dummy Access 2003 Database: data.zip

Edited February 23, 2011 by Rawns
Added attachment.

[Rawns]

Never mind! Fixed it again! After some research, I found out about the "Try" function to catch errors. Working code:

 

            Dim command1 As OleDb.OleDbCommand = New OleDb.OleDbCommand(SQL, conn)
           Dim dt1 As New DataTable()
           Dim da1 As New OleDb.OleDbDataAdapter()

           da1.SelectCommand = command1

           Try
               da1.Fill(dt1)
               DfETxt.Text = dt1.Rows(0)(1)
               SchoolTxt.Text = dt1.Rows(0)(2)
               SA_Pwd.Text = dt1.Rows(0)(3)
               S2S_Pwd.Text = dt1.Rows(0)(4)
           Catch ex As IndexOutOfRangeException
               TreasuryTxt.Text = ""
               DfETxt.Text = ""
               SchoolTxt.Text = "Invalid Treasury Number!"
               SA_Pwd.Text = ""
               S2S_Pwd.Text = ""
           End Try

 

So when the SQL command is ran and data is populated, if a treasury number is not there, the error gets caught!