September 28, 2006 at 3:29 pm
Alright i've got this query in vb .net, when i replace my parameters (dateid) with the same thing but in text, it works, but for the life of me, i can't get it to use the parameter properly.. it also works fine using that parameter for datediff in another function.. help?
_strSQL = "select food.food_desc as Food, journal_food_entry.quantity as quantity, journal_food_entry.calories as calories," & _
" journal_food_entry.fat as fat, journal_food_entry.meal_id as mealtime from food, journal_food_entry, journal_entry where " & _
" journal_food_entry.food_id = food.food_id AND journal_food_entry.journal_id = journal_entry.journal_id and " & _
" journal_entry.journal_id = '" & numId & "' and datediff(dd,journal_entry.journal_date,'" & dateid & "') < '1'"
September 28, 2006 at 4:25 pm
Seems ok on a fast read. What error are you getting?
BTW, it would be easier to read if you were using the inner join syntax.
September 29, 2006 at 4:44 am
_strSQL = "select f.food_desc as Food, jfe.quantity, jfe.calories, jfe.fat, jfe.meal_id as mealtime " & _
"from food f " & _
"inner join journal_food_entry jfe on f.food_id = jfe.food_id " & _
"inner join journal_entry je on je.journal_id = jfe.journal_id " & _
"where je.journal_id = @numId and datediff(dd,je.journal_date, @dateid) < 1"
' Assumes that connection is a valid SqlConnection object.
Dim command As SqlCommand = New SqlCommand(_strSQL, connection)
Dim parameter As SqlParameter = command.Parameters.Add( "@numId", SqlDbType.Int)
parameter.Value = _numIdVar
parameter = command.Parameters.Add("@dateid", SqlDbType.DateTime)
parameter.Value = _dateIdVar
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
Do While reader.Read()
'Your processing goes here
Loop
reader.Close()
connection.Close()
September 29, 2006 at 5:15 am
Since you're building the SQL command in code, and it's not a stored procedure, don't use paramaters - put the data content into the command text:
strSQL = "select f.food_desc as Food, jfe.quantity, jfe.calories, jfe.fat, jfe.meal_id as mealtime " & _
"from food f " & _
"inner join journal_food_entry jfe on f.food_id = jfe.food_id " & _
"inner join journal_entry je on je.journal_id = jfe.journal_id " & _
"where je.journal_id = "+cstr(_numIdVar)+" and datediff(dd,je.journal_date, '"+_dateIdVar.toshortdatestring+"') < 1"
September 29, 2006 at 7:31 am
grrrrrrrr
thanks so much for all your help, it turns out my sql was correct, however i did change it to your suggestion jeff. my problem lay in my grid which wasn't refreshing, so it was infact working all along..
cheers
September 29, 2006 at 8:08 am
Using parameters has the advantage of reducing the possibility of SQL injection attacks, as well as making it easier to move your SQL into stored procedures later (always a good thing to do, as a rule; keep your SQL out of your application). Personally I find the SQL easier to read without the concatenations and type conversions needed when you don't use parameters, but it does make the code a little more complex.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply