kaifong78
asked on
Display records in my 2nd Datagrid (vb.net)
Hi, I trying to explain what I have done and my problems...
In Design Form, I have 2 DataGrid (DataGrid1 & DataGrid2), one TextBox1, one btnLoad buton & one btnOK.
I have import Data from Excel (.xls) into DataGrid1, please take a look my code;
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
Dim MyCommand As System.Data.OleDb.OleDbDat aAdapter
Dim MyConnection As System.Data.OleDb.OleDbCon nection
Dim NewRecord As Boolean = False
MyConnection = New System.Data.OleDb.OleDbCon nection( _
"provider=Microsoft.Jet.OL EDB.4.0; " & _
"data source=c:\data.xls; " & _
"Extended Properties=""Excel 8.0;IMEX=1;ReadOnly:=False ;UpdateLin ks:=0""")
MyCommand = New System.Data.OleDb.OleDbDat aAdapter( _
"select [Batch Number], [Chassis Number] from [sheet1$]", MyConnection)
Dim dsi As DataSet
dsi = New System.Data.DataSet
MyCommand.Fill(dsi)
MyConnection.Close()
DataGrid1.DataSource = dsi.Tables(0)
End Sub
DataGrid1 will display the data as below;
Batch Number Chassis Number
6249 0123456ABCDEF123456
6249 ABC1234AJF12567PIHN
6250 0876HGIYFNB12347809
6250 012365POMNB1245987
Below is my DataGrid2 code;
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim ds As New DataSet
Dim dt, dt2 As DataTable
dt2 = ds.Tables.Add()
dt2.Columns.Add("Cin Number")
dt2.Columns.Add("Batch Number")
dt2.Columns.Add("Chassis Number")
DataGrid2.DataSource = dt2
End Sub
So in DataGrid2, it look like this (no records inside, just have 3 columns here);
Cin Number Batch Number Chassis Number
When I finished Load the data from Excel into the DataGrid1 & in DataGrid2 we can see the 3 columns there, Cin, Batch & Chassis Number without record.
After that I will use the Barcode Scanner to scan the Cin Number(from barcode label) into the TextBox1 (Cin # will display in the Textbox1), if the the 9th - 12th of the Cin Number are MATCH the Batch Number, one row of records will display in DataGrid2.
Example if this Cin number is XC9050426249L1JL, the 9th - 12th number is 6249 so it is MATCH to 6249 Batch Number, in the DataGrid2 the result will be like this
Result in DataGrid2 when finished scan;
Cin Number Batch Number Chassis Number
XC9050426249L1JL 6249 0123456ABCDEF123456
My Questions is..
How do I write a code retrieved the records, when I finished scan the Cin# in Textbox1 the record will automatically/immediately display in the DataGrid2?
In DataGrid2 should have a below record..
Cin Number Batch Number Chassis Number
XC9050426249L1JL 6249 0123456ABCDEF123456
If can please sent me codes...
Hope can understand what I am saying...
In Design Form, I have 2 DataGrid (DataGrid1 & DataGrid2), one TextBox1, one btnLoad buton & one btnOK.
I have import Data from Excel (.xls) into DataGrid1, please take a look my code;
Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
Dim MyCommand As System.Data.OleDb.OleDbDat
Dim MyConnection As System.Data.OleDb.OleDbCon
Dim NewRecord As Boolean = False
MyConnection = New System.Data.OleDb.OleDbCon
"provider=Microsoft.Jet.OL
"data source=c:\data.xls; " & _
"Extended Properties=""Excel 8.0;IMEX=1;ReadOnly:=False
MyCommand = New System.Data.OleDb.OleDbDat
"select [Batch Number], [Chassis Number] from [sheet1$]", MyConnection)
Dim dsi As DataSet
dsi = New System.Data.DataSet
MyCommand.Fill(dsi)
MyConnection.Close()
DataGrid1.DataSource = dsi.Tables(0)
End Sub
DataGrid1 will display the data as below;
Batch Number Chassis Number
6249 0123456ABCDEF123456
6249 ABC1234AJF12567PIHN
6250 0876HGIYFNB12347809
6250 012365POMNB1245987
Below is my DataGrid2 code;
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim ds As New DataSet
Dim dt, dt2 As DataTable
dt2 = ds.Tables.Add()
dt2.Columns.Add("Cin Number")
dt2.Columns.Add("Batch Number")
dt2.Columns.Add("Chassis Number")
DataGrid2.DataSource = dt2
End Sub
So in DataGrid2, it look like this (no records inside, just have 3 columns here);
Cin Number Batch Number Chassis Number
When I finished Load the data from Excel into the DataGrid1 & in DataGrid2 we can see the 3 columns there, Cin, Batch & Chassis Number without record.
After that I will use the Barcode Scanner to scan the Cin Number(from barcode label) into the TextBox1 (Cin # will display in the Textbox1), if the the 9th - 12th of the Cin Number are MATCH the Batch Number, one row of records will display in DataGrid2.
Example if this Cin number is XC9050426249L1JL, the 9th - 12th number is 6249 so it is MATCH to 6249 Batch Number, in the DataGrid2 the result will be like this
Result in DataGrid2 when finished scan;
Cin Number Batch Number Chassis Number
XC9050426249L1JL 6249 0123456ABCDEF123456
My Questions is..
How do I write a code retrieved the records, when I finished scan the Cin# in Textbox1 the record will automatically/immediately display in the DataGrid2?
In DataGrid2 should have a below record..
Cin Number Batch Number Chassis Number
XC9050426249L1JL 6249 0123456ABCDEF123456
If can please sent me codes...
Hope can understand what I am saying...
I'm assuming batchnumber is the primary key.
do something like this in the textchanged event of textbox1 (cin)
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Dim dr As DataRow = ds.Tables(0).Rows.Find(Tex tBox1.Text .Substring (8, 4))
If Not IsNothing(dr) Then
Dim dr2 As DataRow = ds.Tables(1).NewRow
dr2.Item("Cin Number") = TextBox1.Text
dr2.Item("Batch Number") = dr.Item("batchnumber")
dr2.Item("Chassis Number") = dr.Item("chassisnumber")
ds.Tables(1).Rows.Add(dr2)
else
msgbox("batchnumber not found")
End If
End Sub
do something like this in the textchanged event of textbox1 (cin)
Private Sub TextBox1_TextChanged(ByVal
Dim dr As DataRow = ds.Tables(0).Rows.Find(Tex
If Not IsNothing(dr) Then
Dim dr2 As DataRow = ds.Tables(1).NewRow
dr2.Item("Cin Number") = TextBox1.Text
dr2.Item("Batch Number") = dr.Item("batchnumber")
dr2.Item("Chassis Number") = dr.Item("chassisnumber")
ds.Tables(1).Rows.Add(dr2)
else
msgbox("batchnumber not found")
End If
End Sub
ASKER
Answer your question...
One batch number can have many Chassis number...
eg.
6249 0123456ABCDEF123456
6249 ABC1234AJF12567PIHN
6250 6250 0876HGIYFNB12347809
this 6249 batch number have 2 Chassis number and 6250 have only one chassis number...
if I scan this Cin number (in testbox) XC9050426249L1JL , 9th - 12th number are match to batch number (6249), the in the DataGrid2 it will display
XC9050426249L1JL 6249 0123456ABCDEF123456
6249 ABC1234AJF12567PIHN
the user will choose this XC9050426249L1JL 6249 0123456ABCDEF123456 from the DataGrid2 and print it out, next time he will choose XC9050426249L1J 6249 ABC1234AJF12567PIHN to print out from DataGrid2 again.
So I don't need the primary key rite? user just import the Batch and Chassis number from Excel, if the Cin# number match with the Batch number, records will display in the DataGrid2.
Do you get what I mean? :)
One batch number can have many Chassis number...
eg.
6249 0123456ABCDEF123456
6249 ABC1234AJF12567PIHN
6250 6250 0876HGIYFNB12347809
this 6249 batch number have 2 Chassis number and 6250 have only one chassis number...
if I scan this Cin number (in testbox) XC9050426249L1JL , 9th - 12th number are match to batch number (6249), the in the DataGrid2 it will display
XC9050426249L1JL 6249 0123456ABCDEF123456
6249 ABC1234AJF12567PIHN
the user will choose this XC9050426249L1JL 6249 0123456ABCDEF123456 from the DataGrid2 and print it out, next time he will choose XC9050426249L1J 6249 ABC1234AJF12567PIHN to print out from DataGrid2 again.
So I don't need the primary key rite? user just import the Batch and Chassis number from Excel, if the Cin# number match with the Batch number, records will display in the DataGrid2.
Do you get what I mean? :)
Yes I think I do, then this should work
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
Dim dv As DataView = ds.Tables(0).DefaultView
dv.Sort = "batchnumber"
Dim drs() As DataRowView
drs = dv.FindRows(TextBox1.Text. Substring( 8, 4))
For Each dr As DataRowView In drs
Dim dr2 As DataRow = ds.Tables(1).NewRow
dr2.Item("Cin Number") = TextBox1.Text
dr2.Item("Batch Number") = dr.Item("batchnumber")
dr2.Item("Chassis Number") = dr.Item("chassisnumber")
ds.Tables(1).Rows.Add(dr2)
Next
End Sub
Private Sub TextBox1_TextChanged(ByVal
Dim dv As DataView = ds.Tables(0).DefaultView
dv.Sort = "batchnumber"
Dim drs() As DataRowView
drs = dv.FindRows(TextBox1.Text.
For Each dr As DataRowView In drs
Dim dr2 As DataRow = ds.Tables(1).NewRow
dr2.Item("Cin Number") = TextBox1.Text
dr2.Item("Batch Number") = dr.Item("batchnumber")
dr2.Item("Chassis Number") = dr.Item("chassisnumber")
ds.Tables(1).Rows.Add(dr2)
Next
End Sub
ASKER
Hi,
I have tried your code...but I found 2 errors here....
Frist is when I scan this Cin # in Textbox1 XC9050426249L1JL, it display X in TextBox, always display the 1st character...
After that this error message again...
Dim dv As DataView = ds.Tables(0).DefaultView ---> An unhandled exception of type "System.IndexOutOfRange Exception; occurred in System.data.dll.
Additional Information: Cannot find table 0
I have tried your code...but I found 2 errors here....
Frist is when I scan this Cin # in Textbox1 XC9050426249L1JL, it display X in TextBox, always display the 1st character...
After that this error message again...
Dim dv As DataView = ds.Tables(0).DefaultView ---> An unhandled exception of type "System.IndexOutOfRange Exception; occurred in System.data.dll.
Additional Information: Cannot find table 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
a question
6249 0123456ABCDEF123456
6249 ABC1234AJF12567PIHN
6250 0876HGIYFNB12347809
6250 012365POMNB1245987
the batchnumber doesn't seem to be unique how will you choose between them
if this is just a typo, then the next question would be is batchnumber the primary key?