Question How do I link the date with the form lable as parameter

[zakaryahelal]

I have querie that work fine, but I can't use text fields as
parameters.
what I need is how to link the date of sql statement to (labelDate)
but when I start debugging the project, give me this error:
Incorrect syntax near ')'.

here is my code

Dim BuySQL As String = "WITH Complete AS ( SELECT BuySell, Quantity * Price AS Value,  ExecuteDateTime, MarketID FROM dbo.CompletedOrdersView GROUP BY BuySell, Quantity, Price, ExecuteDateTime, MarketID HAVING  CONVERT(varchar(10), ExecuteDateTime, 101) =  " & LabelDate.Text & ")  SELECT SUM (Value) FROM Complete GROUP BY BuySell HAVING BuySell = -1"
        Dim Buyda As New System.Data.SqlClient.SqlDataAdapter(BuySQL, conn)
        Dim Buyds As New DataSet
        Buyds.Clear()
        Buyda.Fill(Buyds, "BuySQL")
        If Buyds.Tables(0).Rows.Count > 0 Then
            Me.TxtComptBuy.Text = Format(Buyds.Tables(0).Rows(0).Item(0), "n")
        Else : Me.TxtComptBuy.Text = "0"
        End If

is there any thing wrong in code

 

[newguy]

Try wrapping the label.text with single quotes: like

( SELECT BuySell, Quantity * Price AS Value,  ExecuteDateTime, MarketID FROM dbo.CompletedOrdersView GROUP BY BuySell, Quantity, Price, ExecuteDateTime, MarketID HAVING  CONVERT(varchar(10), ExecuteDateTime, 101) =  '" & LabelDate.Text & "')  SELECT SUM (Value) FROM Complete GROUP BY BuySell HAVING BuySell = -1"

 

[JohnH]

Try using Parameters for the SelectCommand command object. See for example jmcilhinneys code example in post 5 here: http://www.vbdotnetforums.com/winfo...ror-converting-datetime-character-string.html

 

[cjard]

Red the PQ link in ym signature
THen also read the DW2 link, section "Creating a Form To Search Data"

Finally, remember that dates are implemented as fractional numbers, so a date of "01-jan-1901 midnight" is 0, but "01-jan-1901 mid day NOON" is 0.5

And an = op will never assert that 0 = 0.5, so if you want records from a certain day either use a RANGE, or floor the date to a day. In oracle that would be :

WHERE trunc(record_date) = trunc(sysdate) 'today's records
WHERE record_date BETWEEN trunc(sysdate) AND sysdate 'today's records