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