Can't carry a Value into @name

[chuckh70]

I am doing some time conversions, and need to search my database based on these. As a test I put them at the start of the page. I can do response.write(unxStart.tostring)
just before my sql statement which is in my getTicket Sub, and it gives me what I need.

But if I put @unxStart into the sql statement it errors out with @unxStart must be declared?

<script language="VB" runat="server">
	
Dim MyConnection As SqlConnection
Dim nmStart As DateTime = DateTime.Today 
Dim nmTomorrow As DateTime = nmStart.AddDays(1) 
Dim dtStart As New DateTime(1970, 1, 1)


Dim difStart As TimeSpan = nmStart.Subtract(dtStart)
Dim difTomorrow As TimeSpan = nmTomorrow.Subtract(dtStart)

Dim unxStart As Double = System.Math.Floor(difStart.TotalSeconds) 
Dim unxTomorrow As Double = System.Math.Floor(difTomorrow.TotalSeconds)

Sub GetTickets_Click(Sender As Object, E As EventArgs) 
   
If MyList.SelectedItem.Value="day" Then

Dim SelectCmd As String = "select count(*) as 'tcktOpen' from HPD_HelpDesk where status < 4 and created_time >= @unxStart" 

Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
			
MyCommand = New SqlDataAdapter(SelectCmd, MyConnection)

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Group", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters("@Group").Value = MySelect.Value

DS = new DataSet()
MyCommand.Fill(DS, "Tickets")

MyDataGrid.DataSource= DS.Tables("Tickets").DefaultView
MyDataGrid.DataBind()

End Sub

 

[chiph]

The @ symbol is used for variables within SQL. Like most languages, SQL requires that you define your variables before using them, as well as assigning them a datatype:

DECLARE @unxStart datetime;

You would then assign a value to that variable with a SELECT statement:

SELECT @unxStart = '2004-12-01T13:43:32';
..or
SELECT @unxStart = ?;

The second option is for when you're using ADO.NET Parameter objects.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[chuckh70]

Yeah I forgot to add the paramater. Probably not the best way of doing, but it works.

Thanks for the help.

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@unxStart", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters("@unxStart").Value = unxStart