1 Replies - 240 Views - Last Post: 14 February 2013 - 03:23 PM Rate Topic: -----

#1 silverxtreme  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 14-February 13

Searching a database with a ComboBox (VB 2008)

Posted 14 February 2013 - 11:22 AM

Public Class Form1

    Private Sub btnsear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsear.Click
        If (txtname.Text = "") Then
            MsgBox("Invalid Search")
        Else
            Try
                Dim newsql As String
                newsql = "select * from Animals where AnimalName like " & "'%" & txtname.Text & "%'"
                'MsgBox("select * from Animals where AnimalName like " & "'" & txtname.Text & "'")
                'msgbox(newsql)
                Dim con As New OleDb.OleDbConnection
                Dim da As New OleDb.OleDbDataAdapter

                ' dim ds as NewDataTable
                Dim dt As New DataTable("Animals")
                ' uses the 2010 compatible connection string
                con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = h:\Animals.accdb"
                con.Open()

                da = New OleDb.OleDbDataAdapter(newsql, con)
                da.Fill(dt)

                Form2.Show()

                'show name in unbound text box
                Form2.nametxt.Text = dt.Rows(0).Item(1)
                Form2.latintxt.Text = dt.Rows(0).Item(2)
                Form2.locationtxt.Text = dt.Rows(0).Item(3)
                Form2.heighttxt.Text = dt.Rows(0).Item(4)
                Form2.weighttxt.Text = dt.Rows(0).Item(5)
                Form2.diettxt.Text = dt.Rows(0).Item(6)
                Form2.statustxt.Text = dt.Rows(0).Item(7)
                Form2.lifetxt.Text = dt.Rows(0).Item(9)
                Form2.breedtxt.Text = dt.Rows(0).Item(10)
                Form2.lengthtxt.Text = dt.Rows(0).Item(11)
                Form2.txtimage.Text = dt.Rows(0).Item(12)
                Form2.socialchk.Checked = dt.Rows(0).Item(8)

                If dt.Rows(0).Item(8) = True Then
                    Form2.socialchk.Checked = True
                Else
                    Form2.socialchk.Checked = False
                End If

            Catch
                MsgBox("Item Not Found")
                'con.close()
            End Try

        End If

        If (txtopt.Text = "'") Then
            Try
                Dim newsql As String
                newsql = "select * from Animals where AnimalName like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where LatinName like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where Location like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where AverageHeight like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where AverageWeight like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where DietaryNeeds like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where ConservationStatus like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where AverageLifeSpan like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where BreedingSeason like " & "'%" & txtopt.Text & "%'"
                newsql = "select * from Animals where AverageLength like " & "'%" & txtopt.Text & "%'"

            Catch
            End Try
        End If

    End Sub






Hi, for my college project I am designing a program that can read a Microsoft Access database about animals and display the data. I have managed to program it so that I can search by name and it will display the results, but I need to be able to search other fields such as LatinName or AverageWeight and then display any results that have a match into a combo box e.g. if I were to type in 50kg and there are two records with 50kg, it would display them both and then allow me to select which one I wanted.

Any help or advice would be much appreciated, fell free to ask if you need more information.

Is This A Good Question/Topic? 0
  • +

Replies To: Searching a database with a ComboBox (VB 2008)

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5436
  • View blog
  • Posts: 11,662
  • Joined: 02-June 10

Re: Searching a database with a ComboBox (VB 2008)

Posted 14 February 2013 - 03:23 PM

1 - Don't do this:
09 newsql = "select * from Animals where AnimalName like " & "'%" & txtname.Text & "%'"
Read this:
Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.


2 - You have shown that you know how to get this info from AnimalName. What were your results when you did exactly the same thing but used AnimalWeight or whatever that column is?

3 - Don't use GUI controls like they were variables.
nametxt.Text is just plain bad practice
Use properties (link in my signature block)

4 - Don't do this horrible tight binding directly to another form's GUI controls.
Form2.nametxt.Text is beyond 'bad'.

Q: How do I get Form/class 'A' to make a change or talk to Form/class 'B'
Spoiler


Lines 56 to 65. Read them again.
You keep replacing your value of newsql over and over.
If you did this

x = 5
x = 10
x = 15
x = 20


What would the value of x be?

Q:... how to do x,y,z with a database {probably for the first time}...
A: Read this tutorial
Entire section of tutorials
Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.
Using SqlDependency to monitor SQL database changes
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1