Results 1 to 5 of 5

Thread: Help needed for error "failed to convert parameter value from sting to Datetime"

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    14

    Help needed for error "failed to convert parameter value from sting to Datetime"

    Hi Senior Colleagues,
    I am trying to insert data from datagridview into database but after running the program i received this error message:
    "Failed to convert parameter from string to datetime".

    Below is the written code. I wish people mentor me through to the end.
    Thanks in advance.

    Here is my code.....

    vb.net Code:
    1. Dim dgvconnection As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\Visual Basic Project\Church Database System\WindowsApplication1\cms.mdf;Integrated Security=True")
    2.         Dim dgvcommand As SqlCommand = dgvconnection.CreateCommand()
    3.  
    4.  
    5.         Try
    6.             'open connection
    7.             dgvconnection.Open()
    8.  
    9.             'create Insert statement with named parameters
    10.             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)"
    11.  
    12.             'add parameters to command parameters collection
    13.             dgvcommand.Parameters.Add("@id", SqlDbType.Int, 50)
    14.             dgvcommand.Parameters.Add("@datemembregis", SqlDbType.SmallDateTime)
    15.             dgvcommand.Parameters.Add("@year", SqlDbType.NChar, 10)
    16.             dgvcommand.Parameters.Add("@title", SqlDbType.NChar, 10)
    17.             dgvcommand.Parameters.Add("@fullname", SqlDbType.VarChar, 50)
    18.             dgvcommand.Parameters.Add("@maidname", SqlDbType.VarChar, 50)
    19.             dgvcommand.Parameters.Add("@dob", SqlDbType.SmallDateTime)
    20.             dgvcommand.Parameters.Add("@age", SqlDbType.VarChar, 50)
    21.             dgvcommand.Parameters.Add("@gender", SqlDbType.VarChar, 50)
    22.             dgvcommand.Parameters.Add("@hometown", SqlDbType.VarChar, 50)
    23.             dgvcommand.Parameters.Add("@pob", SqlDbType.VarChar, 50)
    24.             dgvcommand.Parameters.Add("@memstatus", SqlDbType.VarChar, 50)
    25.  
    26.             dgvcommand.Parameters.Add("@memtype", SqlDbType.VarChar, 50)
    27.             dgvcommand.Parameters.Add("@maritalstatus", SqlDbType.VarChar, 50)
    28.             dgvcommand.Parameters.Add("@marriagetype", SqlDbType.VarChar, 50)
    29.             dgvcommand.Parameters.Add("@spousename", SqlDbType.VarChar, 50)
    30.             dgvcommand.Parameters.Add("@spousedeno", SqlDbType.VarChar, 50)
    31.             dgvcommand.Parameters.Add("@dobap", SqlDbType.SmallDateTime)
    32.             dgvcommand.Parameters.Add("@placeofbap", SqlDbType.VarChar, 50)
    33.             dgvcommand.Parameters.Add("@doconf", SqlDbType.SmallDateTime)
    34.             dgvcommand.Parameters.Add("@placeofconf", SqlDbType.VarChar, 50)
    35.             dgvcommand.Parameters.Add("@gengroup", SqlDbType.VarChar, 50)
    36.             dgvcommand.Parameters.Add("@sergroup", SqlDbType.VarChar, 50)
    37.             dgvcommand.Parameters.Add("@othergroup", SqlDbType.VarChar, 50)
    38.             dgvcommand.Parameters.Add("@daygroup", SqlDbType.VarChar, 50)
    39.             dgvcommand.Parameters.Add("@memsince", SqlDbType.VarChar, 50)
    40.             dgvcommand.Parameters.Add("@transfer", SqlDbType.VarChar, 50)
    41.  
    42.             'prepare command for repeated execution
    43.             dgvcommand.Prepare()
    44.  
    45.             'data to be inserted
    46.             For Each row As DataGridViewRow In dgvExcelRegistration.Rows
    47.                 If Not row.IsNewRow Then
    48.  
    49.                     dgvcommand.Parameters("@id").Value = row.Cells("id").Value
    50.                     dgvcommand.Parameters("@datemembregis").SqlValue = row.Cells("DateReg").Value
    51.                     dgvcommand.Parameters("@year").Value = row.Cells("year").Value
    52.                     dgvcommand.Parameters("@title").Value = row.Cells("Title").Value
    53.                     dgvcommand.Parameters("@fullname").Value = row.Cells("FullName").Value
    54.                     dgvcommand.Parameters("@maidname").Value = row.Cells("Maiden").Value
    55.                     dgvcommand.Parameters("@dob").SqlValue = row.Cells("DOB").Value
    56.                     dgvcommand.Parameters("@age").Value = row.Cells("Age").Value
    57.                     dgvcommand.Parameters("@gender").Value = row.Cells("Gender").Value
    58.                     dgvcommand.Parameters("@hometown").Value = row.Cells("HomeTown").Value
    59.                     dgvcommand.Parameters("@pob").Value = row.Cells("POB").Value
    60.                     dgvcommand.Parameters("@memstatus").Value = row.Cells("MemStatus").Value
    61.  
    62.                     dgvcommand.Parameters("@memtype").Value = row.Cells("MemType").Value
    63.                     dgvcommand.Parameters("@maritalstatus").Value = row.Cells("MaritalStatus").Value
    64.                     dgvcommand.Parameters("@marriagetype").Value = row.Cells("MaritalType").Value
    65.                     dgvcommand.Parameters("@spousename").Value = row.Cells("SpouseName").Value
    66.                     dgvcommand.Parameters("@spousedeno").Value = row.Cells("SpouseDeno").Value
    67.                     dgvcommand.Parameters("@dobap").SqlValue = row.Cells("DOBapt").Value
    68.                     dgvcommand.Parameters("@placeofbap").Value = row.Cells("PlaceBaptism").Value
    69.                     dgvcommand.Parameters("@doconf").SqlValue = row.Cells("ConfDate").Value
    70.                     dgvcommand.Parameters("@placeofconf").Value = row.Cells("ConfPlace").Value
    71.                     dgvcommand.Parameters("@gengroup").Value = row.Cells("GenGroup").Value
    72.                     dgvcommand.Parameters("@sergroup").Value = row.Cells("ServiceGrp").Value
    73.                     dgvcommand.Parameters("@othergroup").Value = row.Cells("OtherGrp").Value
    74.                     dgvcommand.Parameters("@daygroup").Value = row.Cells("DayBorn").Value
    75.                     dgvcommand.Parameters("@memsince").Value = row.Cells("MemSince").Value
    76.                     dgvcommand.Parameters("@transfer").Value = row.Cells("TransferFrom").Value
    77.  
    78.                     dgvcommand.ExecuteNonQuery()
    79.                     dgvcommand.Parameters.Clear()
    80.                 End If
    81.             Next
    82.  
    83.         Catch ex As Exception
    84.             MessageBox.Show(ex.Message)
    85.  
    86.         Finally
    87.             dgvconnection.Close()
    88.         End Try
    Last edited by dday9; Jan 19th, 2018 at 02:52 PM. Reason: Added Code Tags

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Help needed for error "failed to convert parameter value from sting to Datetime"

    The error is simple, it cannot convert your string value to a DateTime value in one of the parameters. You need to setup a breakpoint on the first line where you start to set the parameter values (line 49 in your post) and then step through the code comparing if the value returned from row.Cells("...").Value is able to converted to the desired data type and if not then you need to rectify the situation.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Help needed for error "failed to convert parameter value from sting to Datetime"

    You likely aren't getting the exception until you get to the ExecuteNonQuery line, but fortunately, it looks like you only have four DateTime parameters in the query, so looking at each one as DDay described is likely to show you one that doesn't hold quite what you thought it held.
    My usual boring signature: Nothing

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    14

    Re: Help needed for error "failed to convert parameter value from sting to Datetime"

    Thanks Senior Colleagues for your responses.
    But seriously am a bit lost as what to do exactly based on what DDay suggested.
    I would be happy if you aid me through how to resolve this problem of mine. By giving me something that a novice like me can make meaning with.

    Thank you.

  5. #5
    Addicted Member
    Join Date
    Jul 2017
    Location
    Exeter, UK
    Posts
    180

    Re: Help needed for error "failed to convert parameter value from sting to Datetime"

    In order to update a column value in your database you need to ensure the database column datatype matches the datatype you are trying to update. You are sending a 'date' from you DGV at 4 points and most likely these 'dates' are string datatypes with dates manually entered e.g. 01/01/2018. You have set the parameters for these 4 'dates' as SqlDbType.SmallDateTime but that will generate an exception when you attempt the update, if the database columns for these dates are NOT defined as date, and you should then change the column datatype to date, then convert each of the DGV 'date' cell fields, using the Date.TryParseExact method as you assign it to the relevant parameter.

    Here is a link to this method https://msdn.microsoft.com/en-us/lib...110%29.aspx://
    Last edited by bmwpete; Jan 20th, 2018 at 10:28 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width