CODEWITHSUNDEEP
vb.netexcel
Silvestre
Silvestre

Reputation: 59

Excel to VB: Can't read the zero behind

I'm doing a connection with excel and I have a problem when I try to use an ID that have 0 behind...

I'm using a ListBox and add the IDs from the excel's worksheet as items. IDs have 9 numbers, like "123456789" or "098765430". So that I remove the last 4 characters to search the IDs with the same 5 numbers and add in another ListBox. It works fine, except with the codes with 0 (zero) behind.

Dim ConnectionString As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\Tabela_Precos.xlsx; Extended Properties=Excel 12.0;")
        ConnectionString.Open()

        Dim ds As New DataSet
        Dim dt As New DataTable
        ds.Tables.Add(dt)
        Dim da

For i = 0 To Form1.ListBox1.Items.Count - 1

        Dim str As String = Compras.ListBox1.Items(i).ToString
        Dim prod As String = str.Remove(str.Length - 4)
        da = New OleDbDataAdapter("SELECT * FROM [Sheet1$] WHERE ID like '%" & prod & "%'", ConnectionString)

        ListBox1.Items.Add(dt.Rows(i).Item(0))
Next

Upvotes: 0

Views: 106

Answers (1)

TnTinMn
TnTinMn

Reputation: 11801

Your Excel file has the ID column entered as integer values, but is formatted for left-zero padding to present as a nine character field. Your Excel db connection is reading the values as numbers (type Double, even-though they are integers). Your original select statement is implicitly convert ID to a string for the Like comparison; however, this conversion does not now you want left-zero padding. To use this type of comparison, you need to format ID yourself.

Select * From [sheet1$] Where (Format([ID], ""000000000"") Like '" & prod & "%')"

As you have indicated in the comments above, this works. However, it is not the most efficient in terms of speed. Since ID is numeric, it should be faster to do a numeric comparison. You have already defined a String variable named prod and the following solution uses that variable to prepare a numeric value for use in constructing an alternate select based on your criteria.

Dim prodNum As Int32 = Int32.Parse(prod) * 10000I

Then the Select statement would become:

"Select * From [sheet1$] Where ((([ID]\10000) * 10000)=" & prodNum.ToString & ")"

These examples use a concatenated select statement, and ideally you would not do it this way, but rather use a parameterized statement with replacement values. I'll leave that exercise up to you to perform.

Upvotes: 1

Related Questions