sqldataadapter date string

Seven price
Seven price used Ask the Experts™
on
Dim conn As SqlConnection = CreateConnection()
        Dim selectSQL As String = "SELECT  * FROM [dbo].[Events] where EndDate  between EndDate  And @EndDate "
        Dim custDA As New SqlDataAdapter
        Dim ds As New DataSet


        conn.Open()

        Dim selectCMD As SqlCommand = New SqlCommand(selectSQL, conn)
        custDA.SelectCommand = selectCMD

        ' Add parameters and set values.
        selectCMD.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DropDownList1.SelectedValue    
'''  "2/1/2010 12:00:00 AM" '


        Dim custDS As DataSet = New DataSet
        custDA.Fill(custDS, "Events")


        custDA.Fill(ds)

        GridView1.DataSource = ds
        GridView1.DataBind()


String was not recognized as a valid DateTime.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: String was not recognized as a valid DateTime.

Source Error:


Line 183:
Line 184:        Dim custDS As DataSet = New DataSet
Line 185:        custDA.Fill(custDS, "Events")
Line 186:

 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I can't reproduce that behavior over here  apart that I don't get any results back from my test-db.
As long as I use combobox set to dropdownlist style i get a different message using combobox1.selectedvalue (is always null).
if i use combobox1.text instead everyting works fine here.

You have to be very carful with giving datetime-elements like that due to the fact that different cultures have different format-styles. 1/2/2010 can mean 1.feb or 2.jan. - right over here it ist the 2nd of january...
maybe you can upload your project source on that issue.

AshokSr. Software Engineer
Commented:
Try

selectCMD.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = Convert.ToDateTime(DropDownList1........)

HTH
Ashok
Seven priceFull Stack

Author

Commented:
String was not recognized as a valid DateTime
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Seven priceFull Stack

Author

Commented:
>> Pryrates:
The selected dropdownlist1.text - is jan, feb ect...
thats why i use the value.
Seven priceFull Stack

Author

Commented:
Ok i see the problem
the dropdownlist1.selectedvalue is  3/1/2010 12:00:00 AM
but I need the string to appear  2010-01-01 00:00:00.000
is there a format for this.
AshokSr. Software Engineer
Commented:
Try

selectCMD.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DateTime.Parse(DropDownList1........)

HTH
Ashok

AshokSr. Software Engineer
Commented:
You do not have to change format.  You can keep "3/1/2010 12:00:00 AM"  format and use

selectCMD.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = DateTime.Parse(DropDownList1........)

HTH
Ashok
Commented:
ashok111 is right - that should do it - as long as default timeformat setting matches db settings.
otherwise you can use convert in your sqlstatement based on given DateTime-Object input shown by Ashok.

An overview can be found here:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
or
http://www.mssqltips.com/tip.asp?tip=1145
Seven priceFull Stack

Author

Commented:
yks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial