CODEWITHSUNDEEP
sqlsql-servervb.net
Yassin Khaloufi
Yassin Khaloufi

Reputation: 3

Get Result (Number) from an sql query

I want to get a simple result from a SQL query to make some calculation

My code :

cn.Open()

cmd.CommandType = CommandType.Text
cmd.CommandText = " SELECT shift1 + shift2 + shift3 as shifts FROM Statistique where nom_produit=  '" & ComboBox3.Text & "' and p_date like '" & Date1.Text & "' "
cmd.Connection = cn

Dim st As Integer = cmd.ExecuteScalar

MsgBox(st, 0 + 32, "Your Value !")
cn.Close()

The value of "shifts" is what I need. Now I get just 0

Just to make you know, the query executed successfully in MS Access 2007

Upvotes: 0

Views: 188

Answers (1)

Steve
Steve

Reputation: 216343

I suggest you to change your query to make use of a parameterized query. Keep in mind that a date is not a string. Passing a string for the where clause often will result in an incorrect statement

cn.Open()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT shift1 + shift2 + shift3 as shifts " & _ 
                  "FROM Statistique where nom_produit=? AND p_date = ?"
cmd.Parameters.AddWithValue("@p1", ComboBox3.Text)
cmd.Parameters.AddWithValue("@p2", Date1.Value)
cmd.Connection = cn
Dim st As Integer = cmd.ExecuteScalar
MsgBox(st, 0 + 32, "Your Value !")
cn.Close()

Of course this assumes that your field p_date is a DateTime value in MS-Access and that you type a date and TIME value that could be correctly mapped to the field in the database.

EDIT If Date1 is a DateTimePicker then just use the Value property.
The key point here is to pass a parameter with type equal to the field to which it will be applied.
In this way you don't need to worry about conversions or how do you quote the value in a string concatenation.

Upvotes: 2

Related Questions