Solved

Retrieve DataGrid data

Posted on 2004-10-08
18
440 Views
Last Modified: 2012-06-22
Hi,

I have import Excel (.xls) data records into DataGrid, eg;

Batch#    Chassis#
1111       ABC123
2222       CDS321              -----------> in 1st DataGrid
3333       DEF456

Let say if I have 2nd DataGrid, if user use the barcode scanner scan the Cin# last 4 digits number are match to the 1111, then the 2nd DataGrid will automatically display the data, eg;

if the user scan this Cin# 'XABNC1111' then the 2nd Datagrid in the 1st row will display this;

   Cin#            Batch#     Chassis#
XABNC1111     1111         ABC123         -----> 2nd DataGrid

My questions is;

1. How do I retrieve the data from 1st DataGrid when the user scan the Cin#? A row of record must immediately display in the 2nd DataGrid when finished scan. How do I write a code? SQL? This is possible? Can sent me a sample code?

2. Bacause the Cin# is XABNC1111 and it need to match the Batch# 1111, only match the last 4 digits, if use sql to call, is possible? how?

Please help.

Cathy









0
Comment
Question by:kaifong78
  • 9
  • 8
18 Comments
 
LVL 2

Expert Comment

by:hb21l6
Comment Utility
what programming language are you using to create this application?

hb



0
 
LVL 11

Accepted Solution

by:
pratap_r earned 500 total points
Comment Utility
ill assume c#.net!?

heres your solution

//DataGrid 1 -- you may have to load it from xl file, i am hardcoding the values
      DataSet ds=new DataSet();
      DataTable dt=ds.Tables.Add();
      dt.Columns.Add("Batch#");
      dt.Columns.Add("Chassis#");
      dt.Rows.Add(new object[]{"1111","ABC123"});
      dt.Rows.Add(new object[]{"2222","CDS321"});
      dt.Rows.Add(new object[]{"3333","DEF456"});
      dataGrid1.DataSource=dt;

//DataGrid2 is bound to an empty dataset now                  
      DataTable dt2=ds.Tables.Add();
      dt2.Columns.Add("Cin#");
      dt2.Columns.Add("Batch#");
      dt2.Columns.Add("Chassis#");
      dataGrid2.DataSource=dt2;

and in your function where you get the cin# put this

      string batchcode="";
      if(cin.Length < 4)return;
      batchcode=cin.Substring(cin.Length-4);
      DataTable dt=(DataTable)dataGrid1.DataSource;
      DataRow []dr=dt.Select("[Batch#]='"+batchcode+"'");
      if(dr.Length==0)return;
      DataTable dt2=(DataTable)dataGrid2.DataSource;
      dt2.Rows.Add(new object[]{cin,batchcode,dr[0]["Chassis#"].ToString()});

your row should have come in the second datagrid :) , if you want the exact same row in the second grid you may use the datarow.Clone method to create a duplicate row.

Hope this Helps!

Enjoy!
Pratap
0
 

Author Comment

by:kaifong78
Comment Utility
Hi,

Sorry I am using VB.netr not C#.net, code are some? Can you sent me the VB.NET code? Another thing is, do you think this is a correct way to add in 2 DataGrid in this project? The important thing is to Import the data from  Excel and Retrieve the data when user scan the barcode.

Thanks
0
 
LVL 11

Expert Comment

by:pratap_r
Comment Utility
no problem.. here you go ..

''initializations
        Dim ds As New DataSet
        Dim dt, dt2 As DataTable
        dt = ds.Tables.Add()
        dt.Columns.Add("Batch#")
        dt.Columns.Add("Chassis#")
        dt.Rows.Add(New Object() {"1111", "ABC123"})
        dt.Rows.Add(New Object() {"2222", "CDS321"})
        dt.Rows.Add(New Object() {"3333", "DEF456"})
        DataGrid1.DataSource = dt
        dt2 = ds.Tables.Add()
        dt2.Columns.Add("Cin#")
        dt2.Columns.Add("Batch#")
        dt2.Columns.Add("Chassis#")
        DataGrid2.DataSource = dt2

''' this is for your cin function
        Dim cin, batchcode As String
        Dim dt, dt2 As DataTable
        Dim dr() As DataRow
        cin = TextBox1.Text
        If (cin.Length < 4) Then Return
        batchcode = cin.Substring(cin.Length - 4)
        dt = DataGrid1.DataSource
        dr = dt.Select("[Batch#]='" + batchcode + "'")
        If (dr.Length = 0) Then Return
        dt2 = DataGrid2.DataSource
        dt2.Rows.Add(New Object() {cin, batchcode, dr(0)("Chassis#").ToString()})

.. as for your question. personally i dont like using datagrids that much right from my vb days. but this sure works for apps that need to be coded soon. adding 2 datagrids wont be a problem. are you expecting changes in your data? i mean is the user going to changing the datagrids? if so then datagrids is your best bet. If you just need it for display then probably you should consider using listview.

as for your fetching data from excel, you could use ado or ole, try using ado that would be simpler.

Enjoy!
Pratap
0
 

Author Comment

by:kaifong78
Comment Utility
hi,

Thanks. I haven't try your code yet, I will try it later.

Actually I am not sure datagrid is suitable in my project or not, first thing is the user will import the Excel data into application, the user just need to import the Excel data and user can view it, user will not do any changes in this data.
what do you think? here I should use datagrid?

After import that user will scan the barcode (barcode display in Text Box), in the same time if the barcode is match with then above data (data from Excel), one row of data will display (maybe in datagrid or listview), the user no need do any changes also, they just need to select one row of data and print it.
How about here? Datagrid or listview?

If the data display in listview, we still can retrieve the data (SQL)?


Hope can hear from you soon. Thanks

   
0
 
LVL 11

Expert Comment

by:pratap_r
Comment Utility
well listview is probably your best bet, its implementation is clean and its been here for a while. now you cant do sql against it . infact you might have to populate the rows yourself instead of just doing a datasource bind and read from it again (in a loop). are you looking at a professional application or just a tool? if its just a tool to aid your work and you need the app up and running quick you can choose datagrid itself.

Enjoy!
Pratap
0
 

Author Comment

by:kaifong78
Comment Utility
Do you mean if I import the Excel data into Listview can't do the sql?  I still can't track / retrieve the data? As I mentioned above import Excel data into listbox -> use barcode scanner to scan the barcode, if barcode number Match the one of Excel data -> one row records will display in datagrid / listview -> print out

I looking at a professional application and also the correct way to display my data in this project.
0
 
LVL 11

Expert Comment

by:pratap_r
Comment Utility
no you cant reterive using a sql but you still can reterive it.. :) but its better if you retain the DataSet so you could use that once the barcode is scanned.. your pseudocode will be like

FetchDataSetWithData
LoadListView(DataSet)
StoreDataSet

...BarCode Scanned...
CheckDataSet(StoredDataSet,scancode)
LoadMatchingRow() .-->probably to another listview or datagrid..

would you prefer a working code??

Pratap
0
 

Author Comment

by:kaifong78
Comment Utility
Hi Pratap,

I think I need your help again.... I have decided use 2 DataGrid in my project. The Reterive part I haven't done it yet, can you help me take a look my code?

Two DataGrid -> DataGrid1 and DataGrid2,
One Buton -> btnLoad (Load Disk Data),
One Text Box -> TextBox1 (scan the barcode).

' Load Excel data into DataGrid1
======================
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 No], [Chassis No] from [sheet1$]", MyConnection)

        Dim dsi As DataSet
            dsi = New System.Data.DataSet

            MyCommand.Fill(dsi)
            MyConnection.Close()

        DataGrid1.DataSource = dsi.Tables(0)

    End Sub


' 3 columns will display Cin Number, Batch Number and Chassis number in DataGrid2
======================================================
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

Qestions
=====================================================
1. Where can I put the Cin no# code?

2. I will use the barcode scanner to scan the barcode into TextBox, and i want it immediately show the result (match) in the DataGrid2 when I finsihed scan ....

       cin = TextBox1.Text
        If (cin.Length < 4) Then Return
        batchcode = cin.Substring(cin.Length - 4)
        dt = DataGrid1.DataSource
        dr = dt.Select("[Batch Number]='" + batchcode + "'")
        If (dr.Length = 0) Then Return
        dt2 = DataGrid2.DataSource
        dt2.Rows.Add(New Object() {cin, batchcode, dr(0)("Chassis Number").ToString()})


3. Last time I mentioned the Batch No. must match the last 4 digits of Cin#, but now  have some changes. My Cin# should be look like this XC9050426249L1JL, the Batch no# must match the 9th - 12th digitd number, not last 4 digtis...how do I write a code?

example:

Batch#    Chassis#
6249       ABC123
6249       CDS321              -----------> in 1st DataGrid
6250       DEF456

 Cin#                        Batch#     Chassis#
XC9050426249L1JL     6249        ABC123         -----> 2nd DataGrid
S40050456250L1KJ     6250         DEF456


1  2  3  4  5  6  7  8   9   10    11  12  13
X  C 9   0  5  0  4  2  '6'  '2'   ' 4'   '9'   L  1  J  L
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 11

Expert Comment

by:pratap_r
Comment Utility
you can put the code in the place where you assign the value to the text box.. just put it in a function

for example if you are saying
textbox1.text = scannedCode;

make it something like
AssignValues(scannedCode);

void AssignValues(dim cin as string)
{
textbox1.text=cin;
        batchcode = cin.Substring(9,4) //9th position and 4 digits long
        dt = DataGrid1.DataSource
        dr = dt.Select("[Batch Number]='" + batchcode + "'")
        If (dr.Length = 0) Then Return
        dt2 = DataGrid2.DataSource
        dt2.Rows.Add(New Object() {cin, batchcode, dr(0)("Chassis Number").ToString()})

}

solves your problem??

:)

Enjoy
Pratap
0
 

Author Comment

by:kaifong78
Comment Utility
hmmm ... can I put as below? Still no idea.... :(

I haven't try to run it yet, I don't have scanner here...I will try to scan the barcode in textbox1 and the records will automatically display in the DataGrid2

Notes: I using vb.net

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        Dim ds As New DataSet
        Dim dt, dt2 As DataTable

        Dim cin, batchcode As String
        Dim dr() As DataRow

        cin = TextBox1.Text
        batchcode = cin.Substring(9, 4)
        dt = DataGrid1.DataSource
        dr = dt.Select("[Batch Number]='" + batchcode + "'")
        If (dr.Length = 0) Then Return
        dt2 = DataGrid2.DataSource
        dt2.Rows.Add(New Object() {cin, batchcode, dr(0)("Chassis Number").ToString()})
0
 

Author Comment

by:kaifong78
Comment Utility
Hi Pratap,

I have add in one more Button OK (Button1), finished scan, click the button, data will display in the DataGrid2, I got an errors.. can you take a look below code?

Error I have recieved is - > Cannot Perform "=" operation on System.Double and System.String

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        Dim dt, dt2 As DataTable

        Dim cin, batchcode As String
        Dim dr() As DataRow

        cin = TextBox1.Text
        If (cin.Length < 4) Then Return
        batchcode = cin.Substring(9, 4) '9 position and 4 digits long
        dt = DataGrid1.DataSource
        dr = dt.Select("[Batch Number]='" + batchcode + "'")
        If (dr.Length = 0) Then Return
        dt2 = DataGrid2.DataSource
        dt2.Rows.Add(New Object() {cin, batchcode, dr(0)("Chassis Number").ToString()})

    End Sub


Thanks.
0
 
LVL 11

Expert Comment

by:pratap_r
Comment Utility
i dont see any double datatypes in the code you have given.. is your cin a double value?
0
 

Author Comment

by:kaifong78
Comment Utility
I have scanned this Cin number in Textbox

             XC9050426249L1JL


Anything wrong?

Btw' can have your email address?
0
 

Author Comment

by:kaifong78
Comment Utility
should I changed Cin to Double?

Dim cin As Double

If changed how about

batchcode = cin.Substring(9, 4) '9 position and 4 digits long

- Substring is not a memeber of Double
0
 
LVL 11

Expert Comment

by:pratap_r
Comment Utility
hmm can you post the entire code.. or send it to me.. ill take a look at it..

my id is pratap_r[AT]hushmail[DOT]com

Pratap
0
 

Author Comment

by:kaifong78
Comment Utility
Hi,

I have send it to your email already pratap_r@hushmail.com, please let me know when you have received it.

Thanks :)
0
 
LVL 11

Expert Comment

by:pratap_r
Comment Utility
try changing the your sql to

        MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
              "select '' & [Batch Number] as [Batch Number], [Chassis Number] from [sheet1$]", MyConnection)


i am preventing the batch number from being treated as double by prefixing it with an empty character sequence-- two apostrophes (') continuously without space in between

that should solve the problem..

pratap
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
A short article about problems I had with the new location API and permissions in Marshmallow
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now