dearness
asked on
Conversion failed when converting datetime from character string when calling SQL function in ASP.net
I am trying to get my first SQL function working from ASP.net . In the ASP.net application the user clicks the calendar control and I want to fill a dataset with the SQL function using the date selected. I keep getting the error described in the title when the dataset is to be filled. With a msgbox I can see the date being passed to the funcition in the format mm\dd\yyyy. Please help.
Protected Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged
Dim StartDate As Date
StartDate = Calendar1.SelectedDate.Date
Dim cnstr2 As String = "Data Source=ELECTRICAL-PRO;Initial Catalog=Power;Integrated Security=True"
Dim sqlcon As New SqlClient.SqlConnection(cnstr2)
sqlcon.Open()
Dim str As String = "SELECT * FROM udfDailyKwHrUsage('@" & StartDate & "')"
Dim cmd As New SqlClient.SqlCommand(str, sqlcon)
Dim ds As New DataSet()
Dim da As New SqlClient.SqlDataAdapter(cmd)
da.Fill(ds) 'Conversion failed when converting datetime from character string
End Sub
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[udfDailyKwHrUsage]
(
-- Add the parameters for the function here
@StartDate datetime
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT tblPOWER.P_DATE, tblPOWER.P_KW, tblMETER.M_DESCRIPTION FROM tblPOWER INNER JOIN tblMETER ON tblPOWER.M_NUMBER = tblMETER.M_NUMBER WHERE (tblPOWER.P_DATE > @StartDate) AND (tblPOWER.P_DATE < dateadd(day,1,@StartDate))
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi,
In c# convert the date using Convert.DateTime and passp to SQL.
In c# convert the date using Convert.DateTime and passp to SQL.
ASKER
Sorry for the slow response, back to the application now, 6:30am here.
All suggestions got rid of the error, but I dont know if the dataset is being populated yet, gridview remains blank. This is my first SQL function and new to ASP.net VB so I dont know if its right.
All suggestions got rid of the error, but I dont know if the dataset is being populated yet, gridview remains blank. This is my first SQL function and new to ASP.net VB so I dont know if its right.
ASKER
Ok, proved the function is populating the dataset correctly, other issues to address also.
Thanks for the help
Thanks for the help
ASKER
Thanks for the replies, I am away from the application right now, I will get back to you.