• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

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

0
dearness
Asked:
dearness
3 Solutions
 
sachitjainCommented:
Within your function definition, why don't you typecast datetime to varchar only in desired format before fetching

something like this convert(Varchar(20), tblPOWER.P_DATE, 102). For appropriate syle code (102, 103...) you could refer following msdn link:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
Ramkisan JagtapLead DeveloperCommented:
Dim str As String = "SELECT * FROM udfDailyKwHrUsage('@" & StartDate & "')"

Why are you using @ in the Query?
you can try something as

Dim str As String = "SELECT * FROM udfDailyKwHrUsage(CONVERT ( DateTime,'" & StartDate & "'))";
0
 
dearnessAuthor Commented:
sachitjain - ramkisan
Thanks for the replies, I am away from the application right now, I will get back to you.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
deightonCommented:
try a SQL parameter

at the moment you are sending @10/10/2011 to the function (for example)

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)

        cmd.Parameters.Add(New SqlParameter("@StartDate", DbType.DateTime) With {.Value = StartDate})


        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

Open in new window

0
 
kovilpattiBaluCommented:
hi,

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now