Retrieve DataGrid data

what programming language are you using to create this application?

hb

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

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

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

   

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

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.

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

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

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

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()})

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.

i dont see any double datatypes in the code you have given.. is your cin a double value?

I have scanned this Cin number in Textbox

             XC9050426249L1JL


Anything wrong?

Btw' can have your email address?

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

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

Hi,

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

Thanks :)

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