The code has an error "Failed to convert parameter string to datetime". I'm using DateTimePicker. Please help me.

Question

0.00/5 (No votes)

See more:

VB

Public Sub SaveButton()
        Dim MyDataTable As New DataTable
        Dim strQuery As String = "INSERT INTO tbl_HD_TransactionUpdate(DateAssigned, TimeAssigned, EstCompletionTime, EstManHours, EscalationType, DutyTimeDepart, DutyTimeArrive, DutyTimeStart, DutyTimeEnd, Remarks) values(@DateAssigned, @TimeAssigned, @EstCompletionTime, @EstManHours, @EscalationType, @DutyTimeDepart, @DutyTimeArrive, @DutyTimeStart, @DutyTimeEnd, @Remarks)"
        Dim MySqlCommand As SqlCommand = New SqlCommand(strQuery)

        Call Conn()

        MySqlCommand.Parameters.Add("@DateAssigned", SqlDbType.DateTime).Value = DateText.Text
        MySqlCommand.Parameters.Add("@TimeAssigned", SqlDbType.DateTime).Value = TimeText.Text
        MySqlCommand.Parameters.Add("@EstCompletionTime", SqlDbType.DateTime).Value = DateTimePicker1.Value
        MySqlCommand.Parameters.Add("@EstManHours", SqlDbType.DateTime).Value = EstMantext.Text
        MySqlCommand.Parameters.Add("@EscalationType", SqlDbType.VarChar).Value = EscalationText.Text
        MySqlCommand.Parameters.Add("@DutyTimeDepart", SqlDbType.DateTime).Value = DateTimePicker3.Value
        MySqlCommand.Parameters.Add("@DutyTimeArrive", SqlDbType.DateTime).Value = DateTimePicker4.Value
        MySqlCommand.Parameters.Add("@DutyTimeStart", SqlDbType.DateTime).Value = DateTimePicker5.Value
        MySqlCommand.Parameters.Add("@DutyTimeEnd", SqlDbType.DateTime).Value = DateTimePicker6.Value
        MySqlCommand.Parameters.Add("@Remarks", SqlDbType.VarChar).Value = RemarksTextBox.Text

       


        MySqlCommand.CommandType = CommandType.Text
        MySqlCommand.Connection = MySqlConnection

        Dim MySqlDataAdapter As SqlDataAdapter = New SqlDataAdapter
        MySqlDataAdapter.SelectCommand = MySqlCommand



        Try
            MySqlConnection.Open()
            MySqlDataAdapter.SelectCommand = MySqlCommand
            MySqlDataAdapter.Fill(MyDataTable)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            MySqlConnection.Close()
            MySqlConnection.Dispose()
            MySqlDataAdapter.Dispose()
        End Try
    End Sub

Posted 20-Sep-12 17:11pm

Janna Dela Cruz

Updated 20-Sep-12 17:14pm

Kenneth Haugland

v2

Add a Solution

3 solutions

Solution 1

If you are going to pass a String as DateTime you'll have to do something like this:

VB

Dim dat as new DateTime.Now

and sending the data to the database could be done like this:

VB

MySqlCommand.Parameters.Add("@DateAssigned", SqlDbType.DateTime).Value = dat.ToString("MM/dd/yyyy")

Posted 20-Sep-12 17:18pm

Kenneth Haugland

Comments

Janna Dela Cruz 20-Sep-12 23:24pm

Mr. Haugland,
Thanks for the answer but the error is still there. I think it's because of the DateTimePicker in the @EstCompetionTime but i don't now how to fix it.

Kenneth Haugland 20-Sep-12 23:26pm

Check that the value is not Nothing, and call .Value.ToString("MM/dd/yyyy") there as well.

Janna Dela Cruz 20-Sep-12 23:28pm

Actually sir, what i need is the time.

Kenneth Haugland 20-Sep-12 23:32pm

ToString("MM/dd/yyyy HH:mm:ss")? I dont know if that will work as I dont have a database ready to go. When you just send MM/dd/yyyy it just gave me a 12:00:00 stamp I think.

Janna Dela Cruz 20-Sep-12 23:35pm

I still got the same error. Anyway thanks.

Rockstar_ 21-Sep-12 0:33am

I think u may be getting error at EstManHours.Text,convert it to datetime

Solution 2

VB

MySqlCommand.Parameters.Add("@DateAssigned", SqlDbType.DateTime).Value = dat.ToString("yyyy/MM/dd")

pass date value to database in format yyyy/MM/dd
Happy Coding!
:)

Posted 20-Sep-12 18:45pm

Aarti Meswania

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

BheemG · Accepted Answer · 2012-09-20T19:07:00

Solution 3

Hi

You can create a new datetime variable and pass the value like this

Dim datevalue As DateTime = New DateTime(DateTimePicker1.Value.Year, DateTimePicker1.Value.Month, DateTimePicker1.Value.Day, DateTimePicker1.Value.Hour, DateTimePicker1.Value.Minute, DateTimePicker1.Value.Second)

Now pass the datevalue to the sql.

Happy Coding....

Posted 20-Sep-12 19:07pm

BheemG

Comments

Janna Dela Cruz 21-Sep-12 1:50am

BheemG thanks for the code. It answered my question. :)

BheemG 21-Sep-12 1:52am

Happy coding