Solved

Display records in my 2nd Datagrid (vb.net)

Posted on 2004-10-13
8
263 Views
Last Modified: 2010-04-23
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.OleDbDataAdapter
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim NewRecord As Boolean = False

        MyConnection = New System.Data.OleDb.OleDbConnection( _
                      "provider=Microsoft.Jet.OLEDB.4.0; " & _
                      "data source=c:\data.xls; " & _
                      "Extended Properties=""Excel 8.0;IMEX=1;ReadOnly:=False;UpdateLinks:=0""")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
              "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...
0
Comment
Question by:kaifong78
  • 4
  • 2
8 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12296573
Hi Kaifong78,

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?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12296767
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(TextBox1.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
0
 

Author Comment

by:kaifong78
ID: 12297452
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? :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12297546
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
0
 

Author Comment

by:kaifong78
ID: 12304683
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

0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 125 total points
ID: 12306132
Well, the second error is because, I think in your case it would be probably dsi instead of ds and dsi should be declared public in the form not local in your btnLoad_Click event.

The first error is probably because your scanning puts the code in one by one, meaning it will fire the textchange event for every character. What you could do about that is probably testing for the correct lenght of your scanned string, something like
(I hope the cin code is always the same lenght)

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
 If TextBox1.Text.Length = 16 Then
        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 if
    End Sub
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with consolidating excel files using VB.net 2 35
.NET 2008 VB and C# 6 46
Visual Studios 1 87
Help with error in Query 2 36
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question