Searching datas from database after selecting value from COMBO BOX

[Noorul Ariff]

Friends i need code to search the datas...

I'm doing INVENTORY project...

i'm using ACCESS databse with vb.net...

i'm having purchase, sales tables...

THIS IS MY PURCHASE TABLE:

Code Price Category Type Dat SP Brnd
11 222 cde bgf 9/10/2025 356 asd
12 255 MEN Shirt 5/10/2010 355 Vanhesun
55 322 bb bb 9/10/2025 456 asd
66 422 bb bb 9/10/2025 556 fdd

when i select CODE in SALES form, corresponding values have to shown in respective fields...

I.e if i select 55, corresponding values have to be retrieved and showed in respective fields...

Plz help me to finish the project...

Thanks in advance...

[Naveed_786]

firs of all you should add these libraries in your form

Imports System
Imports System.Data
Imports System.Data.OleDb

Then copy and pase this code hope this would help you.

If ComboBox1.Text = "" Then
            MessageBox.Show("Please Select a Search Field From Combobox!", " You Software name ", MessageBoxButtons.OK, MessageBoxIcon.Information)
        ElseIf TextBox5.Text = "" Then
            MessageBox.Show("Please Enter Search Criteria To Search Record!", "You Software name", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else
            Try
                'Set up connection string
                Dim cnString As String

                cnString = ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\DB.mdb;")

                'Create connection
                Dim conn As OleDbConnection = New OleDbConnection(cnString)
                'Try
                ' Open connection
                conn.Open()
                Dim sqlQRY As String = "SELECT * FROM YourTable Name WHERE(" + Me.ComboBox1.SelectedItem + ")LIKE('%" + Me.TextBox1.Text + "%');"

                'create data adapter
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)

                'create dataset
                Dim ds As DataSet = New DataSet

                'fill dataset
                da.Fill(ds, "YourTableName")

                'get data table
                Dim dt As DataTable = ds.Tables("YourTable Name")

                'display data
                Dim row As DataRow

                For Each row In dt.Rows
                    TextBox1.Text = row("C1Name")
                    TextBox2.Text = row("C2Name")
                    TextBox3.Text = row("C3Name")
                    TextBox4.Text = row("C5Name")
                Next row
                If ds.Tables("YourTableName").Rows.Count = 0 Then
                    MessageBox.Show("Sorry No Record Found Please Try Again!", " You Software name ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
                ' Close connection
                conn.Close()
            Catch ex As OleDbException
                MessageBox.Show("Customer Not found" + ex.Message)
            End Try
        End If

I hope this would help pleae mark thrad solved if it help.

[Noorul Ariff]

No it s not working... i'm working this from today morning... but still now it didn't work....

Eror occured in the line,

Dim sqlQRY As String = "SELECT * FROM YourTable Name WHERE(" + Me.ComboBox1.SelectedItem + ")LIKE('%" + Me.TextBox1.Text + "%');"

The Error is:

An unhandled exception of type 'System.InvalidCastException' occured in Microsoft.VisualBasic.Dll

Additional Information: Operator '+' is not defined for string "Select * from TableName Where" and type 'DataRowView'

Plz help me out to solve this

Thanks in advance...

[Naveed_786]

Ok let me ask some questions

Dim sqlQRY As String = "SELECT * FROM YourTable Name WHERE(" + Me.ComboBox1.SelectedItem + ")LIKE('%" + Me.TextBox1.Text + "%');"

1. Just tell me what is your table name?
2. Did you made changes in the code which i have provide you?
3. It sounds me like you didn't modify code just copy and paste.
4. ok just simply copy whole the code and paste it here i will check whats the problem.

[prvnkmr194]

Show your code here with enough detail about your project, so that some help you in better way.

Although I give some idea

You have to write code in combobox text change changed event
your selected statement some thing like this

Dim cmd As New OleDbCommand
cmd.commandtext="select * from <table_name> where <field_name>='"+<combobox_name>.text+"'"
<DataAdapterName>.fill(<datasetName,"<tableName>")

This code fill your dataset from the table by the data which full fill the condition

I hope it will help you, if not free to ask but explain your problem along with your code.

Best Of Luck.

[Noorul Ariff]

Hi NAVEED... 1st of all thanks for ur reply...

Answers for ur question:

1. My table name is PURCHASE...

2. i DONE CHANGES TO MY CODE...

3.i know that, Without changing code, it won't run...

I'm having some doubts in ur code...

1. sql query for searching is
" SELECT * FROM TABLENAME WHERE FIELDNAME =?"

But u not used what field name to be used to search...

2. May i know y u r using me.textbox1.text.

The Code is:

If CmbPrdCde.Text = "" Then
MessageBox.Show("Please Select a Search Field From Combobox!", " You Software name ", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Inventory.mdb;Persist Security Info=False")
'Dim sqlQRY As String = "SELECT * FROM Purchase WHERE Code= (" + Me.CmbPrdCde.SelectedItem + ");"
str = "SELECT * FROM Purchase WHERE(" + Me.CmbPrdCde.SelectedItem + ")LIKE('%" + Me.TxtBrnd.Text + "%');"

'create data adapter
Dim da As OleDbDataAdapter = New OleDbDataAdapter(str, cn)

'create dataset
Dim ds As DataSet = New DataSet

'fill dataset
da.Fill(ds, "Purchase")

'get data table
Dim dt As DataTable = ds.Tables("Purchase")

'display data
Dim row As DataRow

For Each row In dt.Rows
TxtBrnd.Text = row("Brnd")
TxtDesc.Text = row("Type")
TxtSp.Text = row("SP")
Txtprice.Text = row("Price")
Next row
If ds.Tables("Purchase").Rows.Count = 0 Then
MessageBox.Show("Sorry No Record Found Please Try Again!", " You Software name ", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
' Close connection
cn.Close()
Catch ex As OleDbException
MessageBox.Show("Customer Not found" + ex.Message)
End Try
End If

Thanks in advance

[Naveed_786]

Dear,

Here is your problem just correct it i hope it would help you

Dim sqlQRY As String = "SELECT * FROM Purchse WHERE(" + Me.ComboBox1.SelectedItem + ")LIKE('%" + Me.TextBox1.Text + "%');"

Just write this line and follow my instruction

1.You have one combobox its name would be combobox1
2.Just left click on combobox then goto its properties
3.Get item property here you should add Code column or any other column which you want to search.
4.When you select a column from combobox and enter criteria in textbox then click om search button it would populate record against your query hope this would help and would resolve your problem let me know if you have any problem.

[prvnkmr194]

Hello
As I understand your problem, you have a PURCHASES table in which CODE is a column through which you want to retrieve data from table

As you say when you select code corresponding record should be display in respective fields for this

1. From where you going to select CODE?
if is it from combobox then first you have add code column to that combobox then use this code

cmd.commandtext="select * from PURCHASE where CODE="+combobox1.text

if you want to enter code manually in textbox then textbox1.text in stead of combobox1.text
I hope it will be clear for you

[prvnkmr194]

@Naveed_786
1.Why using LIKE statement when Noorul Arif directly selecting the code
2.why you use me.combobox1.selecteditem when we know we the field name in advance CODE
3.Read the rule of DANIWEB how con you give him full code without any work shown by him

[Noorul Ariff]

Dear,

Naveed and Praveen...

Sorry for not replying for one week...

My net was very poor... i could not open DANIWEB... today only it s solved...

1st of all THANKS TO U BOTH...

Naveed, i don't know y u r using textbox1.text and LIKE statement... Still it s not working...

Praveen, i don't understand y u said not to give full code and also WORK referred by u...

it's k... let me explain my prob clearly...

I'm doing INVENTORY project...

i'm having purchase, sales tables...

I'm storing values of a PRODUCT in purchase table. like costprice, code(Product code for every product with which they identify the product), dateofpurchase, sellingprice,description, Brand etc...

I retrieved the CODE FIELD from PURCHASE TABLE and it s shown in COMBOBOX of SALES FORM...

when i select CODE from COMBOBOX in SALES FORM, the corresponding values have to be shown in respected fields(TextBoxes)... i.e sellingprice, description, Brand Etc...

I NEED code for this...

Please HELP me...

Thanks in advance...

[Noorul Ariff]

Dear,

Praveen... Thank you very much...

Your Code worked... Really Really thank you very much...

Naveed thanks for you too for posting reply to me...

Thanks for both of you...

[Balaji_6]

hello navid sir i want to display the textbox tex after selection the combobox value i tried your code but there an error plese help me
i have created the name,city,phone no and i have done to add the all the names in combo box but after selecting the name i want to show in textbox1 and textbox2 value from the city,phoneno so please help me.

[Reverend Jim]

Please start a new thread for your question.