Link to home
Start Free TrialLog in
Avatar of dearness
dearnessFlag for American Samoa

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))
)

Open in new window

SOLUTION
Avatar of sachitjain
sachitjain
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dearness

ASKER

sachitjain - ramkisan
Thanks for the replies, I am away from the application right now, I will get back to you.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi,

In c# convert the date using Convert.DateTime and passp to SQL.
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.
Ok, proved the function is populating the dataset correctly, other issues to address also.
Thanks for the help