sql select statement in vb returning value when its supposed to be null

Go To StackoverFlow.com

0

My code seems to behave a way I don't understand. Sometimes it returns null as expected and sometimes it returns a value when its supposed to be null. How can someone explain this? Here is the code that selects from access and displays the result in a messagebox:

cmd5.Connection = cnn

cmd5.CommandText = "SELECT receipt_num " & _
         "FROM brought_coffee, farmer where crop_year= " & yr & " and " & _
         "brought_date=#" & dtt & "# and farmer_centre='" & ctr & _
         "' and farmer.farmer_num=brought_coffee.farmer_num"

myData5 = cmd5.ExecuteReader

While myData5.Read()
   chkdb = myData5(0).ToString
End While

MsgBox("the check" & chkdb)
myData5.Close()
2012-04-03 20:06
by ken
Where do you expect a Nothing value? If you refer to chkdb, could it be that it is set by a former call - Nico Schertler 2012-04-03 20:11
Nico+1. It looks like it returns a null if it finds a row, and receipt_num is null. If it doesn't find a row, myData5.Read() will be false and chkdb will have a prior value - SeaDrive 2012-04-03 20:15
This code will be vulnerable to sql injection attack - Joel Coehoorn 2012-04-03 20:22
Yes chkdb is the value from database. I expect it to be null when the condition crop_year= " & yr & " and " & _ "brought_date=#" & dtt & "# and farmer_centre='" & ctr & "' and farmer.farmer_num=brought_coffee.farmer_num" is not met. Let me be abit clear. My program has three comboboxes whose values are assigned to dtt, ctr, and yr. The weired part is when I run the program and the first time the values of dtt,ctr and yr meet the condition for returning null, null is displayed in message box. But if the values are changed such that chkdb is not null, it thereafter returns a valu - ken 2012-04-03 20:27


1

The question was already answered in the comments, but I'll repeat it here to match our Q&A format:

It looks like it returns a null if it finds a row, and receipt_num is null. If it doesn't find a row, myData5.Read() will be false and chkdb will have a prior value.

Therefore, to actually solve the problem you can reset the variable before running the query every time or you can use the Count() aggregate function to show the number of rows returned, and look for zero instead of null.

I also want to address a very serious security issue in your code. You should not use string concatenation to substitute query values into your sql string like that. You want something more like this instead:

Using cnn As New OleDbConnection("connection string here"), _
      cmd5 As New OleDbCommand("", cnn)

    cmd5.CommandText = _
         "SELECT receipt_num " & _
         "FROM brought_coffee, farmer " & _
         "WHERE crop_year= ? " & _
             "and brought_date= ? and farmer_centre= ? " & _
             "and farmer.farmer_num=brought_coffee.farmer_num"

    cmd5.Parameters.Add("crop_year", OleDbType.Integer).Value = yr
    cmd5.Parameters.Add("brougt_date", OleDbType.Date).Value = dtt
    cmd5.Parameters.Add("farmer_centre", OleDbType.VarChar).Value = ctr

    cnn.Open()        
    chkdb = Cstr(cmd5.ExecuteScalar())
End Using

MsgBox("the check" & chkdb)
2012-04-03 20:34
by Joel Coehoorn
You must be right on this It looks like it returns a null if it finds a row, and receipt_num is null. If it doesn't find a row, myData5.Read() will be false and chkdb will have a prior value but how do I get over that? Thanks for the security stuff, am going to make adjustments but want this to work firs - ken 2012-04-03 20:37
Reset chkdb before running the query, or run the query in such a way that chkdb is guaranteed to be assigned. You have other flaws in that code as well. Take a good look at the code in my answer, as this is a much better pattern: new connection each time, Using blocks, query parameters, and more, are all important - Joel Coehoorn 2012-04-03 20:39
Thank you. Let me give it a tr - ken 2012-04-03 20:43
Final note: I'm worried that MsgBox() call at the end may blow up on you. It would be a good idea to add a String.IsNullOrEmpty() check in there somewhere first - Joel Coehoorn 2012-04-03 20:45