Range Query ~ VB

Question

Trying to do a OleDbCommand to find a row in an excel sheet. Each row has a low number and a high number in different cells. No rows have overlapping numbers. I am not getting any results nor errors. Though I know I am a row's range. 205221 is the sheet name. StartSerial and StopSerial are column headers. Do I need to use the column letter instead?

***Edit: Sample data:

Job	Descript	StartSerial	StopSerial
55555	Job1	44334	44897
55556	Job2	44898	45001
55557	Job3	45002	46001

comm = New OleDbCommand("SELECT * from [205221$] WHERE (StartSerial) <= " & serial & " AND (StopSerial) >= " & serial, conn)
Try
    dr = comm.ExecuteReader
Catch ex As Exception
    MsgBox(ex)
End Try
While dr.Read
    Dim jobNum = dr("Job").ToString
    Dim jobName = dr("Descript").ToString
    TextBox1.AppendText(jobNum & " " & jobName)
End While

Your query doesn't make sense. Replace `serial` with `1` and you get `WHERE (StartSerial) >= 1 and (StopSerial) <=1`. This is the equivalent of saying where StartSerial and StopSerial equal 1. — David, Jun 02 '21 at 18:19
@Jimi Yes those are from the first row/header. I have those two names in parens. — stefan_aus_hannover, Jun 02 '21 at 18:19
I suppose I mispoke, but I still don't think it will give you the answer you want. Could you give us an update with some sample data with what you'd expect to be returned? — David, Jun 02 '21 at 18:22
@steveo314 To check if the conditions are too restrictive, you could remove them, just to make sure that there isn't something else preventing the code from returning results, then add one restriction back to make sure, and so on. — Andrew Morton, Jun 02 '21 at 18:35
I would try a couple of things. Remove the parenthesis from the column names, cast the columns to an `INT`, and use a parameterized query. I'll provide my answer below. — David, Jun 02 '21 at 18:39
@steveo314 ...and then you will notice that the <= and >= are the wrong way round. — Andrew Morton, Jun 02 '21 at 18:40
@steveo314 Now that you have edited the question, does the code shown there work? — Andrew Morton, Jun 03 '21 at 06:57
@AndrewMorton I created an answer with code that works with what I was wanting to attain. Thank you for helping. — stefan_aus_hannover, Jun 03 '21 at 14:44

score 1 · Answer 1 · answered Jun 03 '21 at 14:41

I updated from what @David and @AndrewMorton both supplied. Thank you for the help.

Using cmd = New OleDbCommand("SELECT * from [205221$] WHERE [StartSerial] <= ? AND [StopSerial] >= ?", conn)
   cmd.Parameters.Add("?", OleDbType.Integer).Value = serial
   cmd.Parameters.Add("?", OleDbType.Integer).Value = serial
   Dim reader = cmd.ExecuteReader()
   While reader.Read()
       Dim jobNum = reader("Job").ToString
       Dim jobName = reader("Descript").ToString
       TextBox1.AppendText(jobNum & " " & jobName)
   End While
End Using

score 0 · Answer 2 · answered Jun 02 '21 at 18:44

0

Try to do the following:

Remove the parenthesis from the column names. If you want to escape the column names, use brackets.
Try to cast the columns in your WHERE clause
Use a parameterized query

Take a look at this example:

Using cmd = New OleDbCommand("SELECT * from [205221$] WHERE CAST([StartSerial] AS INT) >= @0 AND CAST([StopSerial] AS INT) <= @0", conn)
    cmd.Parameters.Add("@0", OleDbType.Integer).Value = serial

    Dim reader = cmd.ExecuteReader()
    While reader.Read()
        ' do something
    End While
End Using

If serial would equal 44898 then it would return the second record in your example dataset.

answered Jun 02 '21 at 18:44

David

5,877
3
23
40

@David ExecuteReader line gives 'System.Data.OleDb.OleDbException: 'IErrorInfo.GetDescription failed with E_FAIL(0x80004005).'' – stefan_aus_hannover Jun 02 '21 at 19:03
Let startSerial = 1000, stopSerial= 2000, serial = 1500. Then startSerial >= serial is false, and stopSerial <= serial is false. – Andrew Morton Jun 02 '21 at 19:06
Corrected comparison and still get on this error on the ExecuteReader line 'System.Data.OleDb.OleDbException: 'IErrorInfo.GetDescription failed with E_FAIL(0x80004005).'' – stefan_aus_hannover Jun 02 '21 at 19:10
1

@steveo314 Please try the correct comparisons in your original code. – Andrew Morton Jun 02 '21 at 19:11
@David I don't think that parameter names can start with a digit. A-Z and a-z will work as the first character of a parameter name. – Andrew Morton Jun 02 '21 at 19:13

Range Query ~ VB

2 Answers2