CODEWITHSUNDEEP
sqlvb.netms-access
ken
ken

Reputation: 399

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

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

Upvotes: 0

Views: 2866

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415620

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)

Upvotes: 1

Related Questions