VB code to search record

Question

I have 2 tables in a database. Database CUSTOMER.MDB, Table1 fields are - CustomerID, Name, Address. Table2 fields are CustomerID, Itemname, Amount. I have created a form where I am using Table2 for Dataentry. I want to write code on CustomerID Lostfocus that 'When I enter CustomerID in the form it should check the CustomerID in Table1 and if exists it should print the NAME from Table1 in a Textbox. If not found it should give a msg.'

Both the tables are linked on CustomerID.

Pls help. What should be the code?

Answer 1

Prasad,

Sounds to me like you need to populate a DatSet with two DataTables then create a DataRelation between them.  This will give you access to both parent and child rows of each one.  MSDN Library has a wealth of information:

http://msdn.microsoft.com/en-us/library/system.data.datarelation.aspx

---

jfc

Answer 2

Hello I did something like that with SqCommand, u just have to change to oledbcommand, click here please:
Go to this address :http://gallery.expression.microsoft.com/en-us/Malange

---

Don't judge me, just Upgrade me. Thanks!

Answer 3

Hi Prasad,

Add to james's reply, you can use DataRow.GetChildRows with DataRelation to achieve it.
You can first create a DataRelation between the two table.
Then locate the row in parent table. You can traverse table or use DataView.RowFilter for locating.
At last, use DataRow.GetChildRows to get the related child row and show the field you want.


Best Regards
Yichun Feng

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Prasad,

Sorry I've your question understand wrong
Therefore here you complete code

        Using conn As New OleDb.OleDbConnection("connectionString")
            Using cmd As New OleDb.OleDbCommand("Select name from MyTable where ID = @ID", conn)
                cmd.Parameters.Clear()
                cmd.Parameters.Add(New OleDb.OleDbParameter("@ID", TheTextBox.Text))
                Dim name = cmd.ExecuteScalar
                If Not name Is DBNull.Value Then
                    myOtherTextbox.Text = name
                    Else
                    MessageBox.Show("Does not exist")
                End If
            End Using
        End Using

---

Success
Cor

Answer 5

There is a good video on filtering the Dataset at
http://windowsclient.net/learn/video.aspx?v=5548

After a brief review of our previous Client Server application, this example demonstrates Filtering the Dataset on the client side using both a Textbox and by adding a read-only Dataset/TableAdapters to the Webservice that return the first letters of all the Northwind companies as the basis for a filtering toolbar.  This applies to Client Server and SmartClient applications.

Author: Pat Tormey