1 Replies - 128 Views - Last Post: 20 January 2018 - 05:13 AM Rate Topic: -----

#1 kremilk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 20-January 18

vb.net 2013 error - "Failed to convert parameter from string to a

Posted 20 January 2018 - 04:39 AM

Hi Seniors,

I am facing a problem with my code...each time i run the code i get the error "failed to convert parameter from string to a datetime".
Below is my code...I urgently need your help. Thanks a lot.

Dim dgvconnection As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\Visual Basic Project\Church Database System\WindowsApplication1\cms.mdf;Integrated Security=True")
        Dim dgvcommand As SqlCommand = dgvconnection.CreateCommand()


        Try
            'open connection
            dgvconnection.Open()

            'create Insert statement with named parameters
            dgvcommand.CommandText = "Insert into members(id,datemembregis,year,Title,FullName,MaidName,DOB,Age,Gender,HomeTown,POB,MemStatus,MemType,MaritalStatus,MarriageType,SpouseName,SpouseDeno,DOBap,PlaceofBap,DOConf,PlaceofConf,gengroup,sergroup,othergroup,daygroup,memsince,transfer) values(@id,@datemembregis,@year,@title,@fullname,@maidname,@dob,@Age,@gender,@hometown,@pob,@MemStatus,@MemType,@maritalstatus,@marriagetype,@SpouseName,@SpouseDeno,@dobap,@placeofbap,@doconf,@placeofconf,@gengroup,@sergroup,@othergroup,@daygroup,@memsince,@transfer)"

            'add parameters to command parameters collection
            dgvcommand.Parameters.Add("@id", SqlDbType.Int, 50)
            dgvcommand.Parameters.Add("@datemembregis", SqlDbType.SmallDateTime)
            dgvcommand.Parameters.Add("@year", SqlDbType.NChar, 10)
            dgvcommand.Parameters.Add("@title", SqlDbType.NChar, 10)
            dgvcommand.Parameters.Add("@fullname", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@maidname", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@dob", SqlDbType.SmallDateTime)
            dgvcommand.Parameters.Add("@age", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@gender", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@hometown", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@pob", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@memstatus", SqlDbType.VarChar, 50)

            dgvcommand.Parameters.Add("@memtype", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@maritalstatus", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@marriagetype", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@spousename", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@spousedeno", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@dobap", SqlDbType.SmallDateTime)
            dgvcommand.Parameters.Add("@placeofbap", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@doconf", SqlDbType.SmallDateTime)
            dgvcommand.Parameters.Add("@placeofconf", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@gengroup", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@sergroup", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@othergroup", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@daygroup", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@memsince", SqlDbType.VarChar, 50)
            dgvcommand.Parameters.Add("@transfer", SqlDbType.VarChar, 50)

            'prepare command for repeated execution
            dgvcommand.Prepare()

            'data to be inserted
            For Each row As DataGridViewRow In dgvExcelRegistration.Rows
                If Not row.IsNewRow Then

                    dgvcommand.Parameters("@id").Value = row.Cells("id").Value
                    dgvcommand.Parameters("@datemembregis").SqlValue = row.Cells("DateReg").Value
                    dgvcommand.Parameters("@year").Value = row.Cells("year").Value
                    dgvcommand.Parameters("@title").Value = row.Cells("Title").Value
                    dgvcommand.Parameters("@fullname").Value = row.Cells("FullName").Value
                    dgvcommand.Parameters("@maidname").Value = row.Cells("Maiden").Value
                    dgvcommand.Parameters("@dob").SqlValue = row.Cells("DOB").Value
                    dgvcommand.Parameters("@age").Value = row.Cells("Age").Value
                    dgvcommand.Parameters("@gender").Value = row.Cells("Gender").Value
                    dgvcommand.Parameters("@hometown").Value = row.Cells("HomeTown").Value
                    dgvcommand.Parameters("@pob").Value = row.Cells("POB").Value
                    dgvcommand.Parameters("@memstatus").Value = row.Cells("MemStatus").Value

                    dgvcommand.Parameters("@memtype").Value = row.Cells("MemType").Value
                    dgvcommand.Parameters("@maritalstatus").Value = row.Cells("MaritalStatus").Value
                    dgvcommand.Parameters("@marriagetype").Value = row.Cells("MaritalType").Value
                    dgvcommand.Parameters("@spousename").Value = row.Cells("SpouseName").Value
                    dgvcommand.Parameters("@spousedeno").Value = row.Cells("SpouseDeno").Value
                    dgvcommand.Parameters("@dobap").SqlValue = row.Cells("DOBapt").Value
                    dgvcommand.Parameters("@placeofbap").Value = row.Cells("PlaceBaptism").Value
                    dgvcommand.Parameters("@doconf").SqlValue = row.Cells("ConfDate").Value
                    dgvcommand.Parameters("@placeofconf").Value = row.Cells("ConfPlace").Value
                    dgvcommand.Parameters("@gengroup").Value = row.Cells("GenGroup").Value
                    dgvcommand.Parameters("@sergroup").Value = row.Cells("ServiceGrp").Value
                    dgvcommand.Parameters("@othergroup").Value = row.Cells("OtherGrp").Value
                    dgvcommand.Parameters("@daygroup").Value = row.Cells("DayBorn").Value
                    dgvcommand.Parameters("@memsince").Value = row.Cells("MemSince").Value
                    dgvcommand.Parameters("@transfer").Value = row.Cells("TransferFrom").Value

                    dgvcommand.ExecuteNonQuery()
                    dgvcommand.Parameters.Clear()
                End If
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            dgvconnection.Close()
        End Try

This post has been edited by IronRazer: 20 January 2018 - 04:49 AM
Reason for edit:: Fixed Code Tags - Please use them when posing code.


Is This A Good Question/Topic? 0
  • +

Replies To: vb.net 2013 error - "Failed to convert parameter from string to a

#2 IronRazer  Icon User is offline

  • Custom Control Freak
  • member icon

Reputation: 1470
  • View blog
  • Posts: 3,725
  • Joined: 01-February 13

Re: vb.net 2013 error - "Failed to convert parameter from string to a

Posted 20 January 2018 - 05:13 AM

Well, i can see where you are trying to insert a date into the database but, you have not said which line gives the exception. I can only guess, if you actually have the column(s) in the Database set to a Date type, then it appears you are trying to pass a String to it instead of a Date type.

This line in your code shows one of the spots. Is the 'dob' column actually set to be a Date type in the Database?
dgvcommand.Parameters.Add("@dob", SqlDbType.SmallDateTime)

And here is the line that assigns the 'date' to the parameter. Is your "DOB" column set to be Date type or a String type.
dgvcommand.Parameters("@dob").SqlValue = row.Cells("DOB").Value


I would say that you have a String in the DataGridView 'DOB' cell and you are trying to pass that String to the "dob" database column which is expecting you to pass a Date type to it. You could try either, converting the String from the cell to a Date type and assign the Date type to the 'dob' parameter value, or change the type to a String type in the Database.

You can use the DateTime.TryParseExact Method to try converting the String to a Date. There are examples at that link as well as information on using the method.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1