Click here to Skip to main content
15,071,262 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

I am running an INSERT SQL statement.
The datetimepicker value in windows form needs to be converted to date type since my SQL table has Date data type.

Have tried using CDATE() upon form load and upon button click but get an error message saying ' Value of DateTimePicker cannot be converted to Date'.

How can I fix this ?

Thanks
Anshika

What I have tried:

I have tried CDATE() in VB, CAST() and COVERT TO in the SQL Statement.
Posted
Updated 19-Oct-20 0:29am
Comments
F-ES Sitecore 19-Oct-20 6:31am
   
If you use parameterised queries to execute your sql and use the ".Value" property of the date picker then ado should do all the work for you. You might have to post the relevant bits of your code when you execute the insert for specific help.

1 solution

Why?
Just use the DateTimePicker.Value property[^] - it's already a DateTime value ready to be passed as a parameter to SQL via your INSERT query:
C#
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("INSERT INTO myTable (myDateColumn, myStringColumn) VALUES (@DT, @ST)", con))
        {
        cmd.Parameters.AddWithValue("@DT", myDateTimePicker.Value);
        cmd.Parameters.AddWithValue("@ST", myTextBox.Text);
        cmd.ExecuteNonQuery();
        }
    }
   
v2
Comments
Member 14969139 20-Oct-20 5:02am
   
Hi I am using VB. Confused about the code above. Listed below is my code.

myCmd.CommandText = "INSERT INTO TASKMANAGER.dbo.CONSULTANT (ID,NAME,RMCVALIDITY) VALUES ('2','" & TextBox1.Text & "','" & Me.DateTimePicker2.Value & "')"

Error message I get is 'System.Data.SqlClient.SqlException: 'Conversion failed when converting date and/or time from character string.'
OriginalGriff 20-Oct-20 5:42am
   
"I am using VB" - brrrr. Is it contagious? You should have said ...
    Using con As SqlConnection = New SqlConnection(strConnect)
        con.Open()

        Using cmd As SqlCommand = New SqlCommand("INSERT INTO myTable (myDateColumn, myStringColumn) VALUES (@DT, @ST)", con)
            cmd.Parameters.AddWithValue("@DT", myDateTimePicker.Value)
            cmd.Parameters.AddWithValue("@ST", myTextBox.Text)
            cmd.ExecuteNonQuery()
        End Using
    End Using
Member 14969139 20-Oct-20 5:22am
   
The complete code for the Button Click is listed below. It errors at ADAPTER.InsertCommand.ExecuteNonQuery()


myConn = New SqlConnection("Data Source = CSA10;Initial Catalog=TaskManager;Integrated Security=SSPI")
myConn.Open()
myCmd = myConn.CreateCommand
myCmd.CommandText = "INSERT INTO TASKMANAGER.dbo.CONSULTANT (ID,NAME,RMCVALIDITY) VALUES ('2','" & TextBox1.Text & "','" & DateTimePicker2.Value.Date & "')"
'myCmd.CommandText = "INSERT INTO TASKMANAGER.dbo.CONSULTANT (ID,NAME,DEPARTMENT,DEPARTMENT2,DESIGNATION,RMCNO,RMCVALIDITY,INDEMNITYVALIDITY,DOB,REMARKS) VALUES ('2','" & TextBox1.Text & "','MEDICAL','" & ComboBox1.Text & "','" & ComboBox3.Text & "','" & TextBox2.Text & "','CAST(" & DateTimePicker2.Value & "AS DATE)','CAST(" & DateTimePicker3.Value & "AS DATE)','CAST(" & DateTimePicker1.Value & "AS DATE)','" & TextBox3.Text & "')"
Dim ADAPTER As New SqlDataAdapter(myCmd)
ADAPTER.InsertCommand = New SqlCommand(myCmd.CommandText, myConn)
ADAPTER.InsertCommand.ExecuteNonQuery()
myCmd.Dispose()
myConn.Close()

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900