Results 1 to 23 of 23

Thread: Failed to convert parameter value from a String to a Int32.

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Failed to convert parameter value from a String to a Int32.

    Hi Guys,

    I am new to Vb.net please help me sort out this problem. Formatexceptionunhandled:

    i am trying to save data from unbound datagridview to sql database.

    Failed to convert parameter value from a String to a Int32. The following is my code:

    Private Sub btnPlace_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPlace.Click
    ' Modify the following code to correctly connect to your SQL Server.
    Dim Connection As New SqlConnection("Server=.\SQLEXPRESS;Initial Catalog=Store Management System;Integrated Security=SSPI ")

    'Create Command object
    Dim nonqueryCommand As SqlCommand = Connection.CreateCommand()




    Try
    ' Open Connection
    Connection.Open()
    MsgBox("Connection Opened")

    ' Create INSERT statement with named parameters
    nonqueryCommand.CommandText = _
    "INSERT REQUISITIONS (RequsitionID,Date,DepartmentID,Department,ItemID, Item Name,CategoryID,Category,UnitID, Units, Unit Price,Quantity, Amount,User Name)" & _
    " VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID, @Item Name,@CategoryID,@Category ,@UnitID, @Units, @Unit Price,@Quantity,@Amount,User Name)"

    'Add Parameters to Command Parameters collection
    nonqueryCommand.Parameters.Add("@RequisitionID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Date", SqlDbType.SmallDateTime)
    nonqueryCommand.Parameters.Add("@DepartmentID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Department", SqlDbType.VarChar, 50)
    nonqueryCommand.Parameters.Add("@ItemID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Item_Name", SqlDbType.VarChar, 50)
    nonqueryCommand.Parameters.Add("@CategoryID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Category", SqlDbType.VarChar, 80)
    nonqueryCommand.Parameters.Add("@UnitID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Units", SqlDbType.NVarChar, 20)
    nonqueryCommand.Parameters.Add("@Unit_Price", SqlDbType.Decimal, 9)
    nonqueryCommand.Parameters("@Unit_Price").Precision = 18
    nonqueryCommand.Parameters("@Unit_Price").Scale = 2
    nonqueryCommand.Parameters.Add("@Quantity", SqlDbType.NChar, 10)
    nonqueryCommand.Parameters.Add("@Amount", SqlDbType.Money)
    nonqueryCommand.Parameters.Add("@User_Name", SqlDbType.VarChar, 20)

    ' Prepare command for repeated execution
    nonqueryCommand.Prepare()

    ' Data to be inserted

    For Each row As DataGridViewRow In RequisitionsDGV.Rows

    If Not row.IsNewRow Then
    nonqueryCommand.Parameters("@RequisitionID").Value = row.Cells(0).Value.ToString
    nonqueryCommand.Parameters("@Date").Value = row.Cells(1).Value.ToString
    nonqueryCommand.Parameters("@DepartmentID").Value = row.Cells(2).Value.ToString
    nonqueryCommand.Parameters("@Department").Value = row.Cells(3).Value.ToString
    nonqueryCommand.Parameters("@ItemID").Value = row.Cells(4).Value.ToString
    nonqueryCommand.Parameters("@Item_Name").Value = row.Cells(5).Value.ToString
    nonqueryCommand.Parameters("@CategoryID").Value = row.Cells(6).Value.ToString
    nonqueryCommand.Parameters("@Category").Value = row.Cells(7).Value.ToString
    nonqueryCommand.Parameters("@UnitID").Value = row.Cells(8).Value.ToString
    nonqueryCommand.Parameters("@Units").Value = row.Cells(9).Value.ToString
    nonqueryCommand.Parameters("@Unit_Price").Value = row.Cells(10).Value.ToString
    nonqueryCommand.Parameters("@Quantity").Value = row.Cells(11).Value.ToString
    nonqueryCommand.Parameters("@Amount").Value = row.Cells(12).Value.ToString
    nonqueryCommand.Parameters("@User_Name").Value = row.Cells(13).Value.ToString
    nonqueryCommand.ExecuteNonQuery()

    End If
    Next row



    Catch ex As SqlException
    ' Display error
    MsgBox("Error: " & ex.ToString())
    Finally
    ' Close Connection
    Connection.Close()
    MsgBox("Connection Closed")



    End Try
    End Sub

  2. #2
    PowerPoster cicatrix's Avatar
    Join Date
    Dec 2009
    Location
    Moscow, Russia
    Posts
    3,654

    Re: Failed to convert parameter value from a String to a Int32.

    Quote Originally Posted by jayeem View Post
    Code:
    "INSERT REQUISITIONS (RequsitionID,Date,DepartmentID,Department,ItemID, Item Name,
    CategoryID,Category,UnitID, Units, Unit Price, Quantity, Amount,User Name)" & _
    " VALUES(@RequsitionID,@Date,@DepartmentID,@Department,
    @ItemID, @Item Name,@CategoryID,@Category ,@UnitID, @Units, @Unit Price,
    @Quantity,@Amount,User Name)"
    I think that's the whitespaces in the field names that are causing your troubles. I generally avoid using whitespaces in my databases. Where you list the field names - put every field name, containing a white space in single quote and of course - don't use names like 'Unit Price' for named parameters - use UnitPrice instead.

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    Thanks Citrix

    I tried changing my table and code bu still get the same error 'failed to convert parameter value from string to int32".

    My new code is as follows:

    Private Sub btnPlace_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPlace.Click
    ' Modify the following code to correctly connect to your SQL Server.
    Dim Connection As New SqlConnection("Server=.\SQLEXPRESS;Initial Catalog=Store Management System;Integrated Security=SSPI ")

    'Create Command object
    Dim nonqueryCommand As SqlCommand = Connection.CreateCommand()




    Try
    ' Open Connection
    Connection.Open()
    MsgBox("Connection Opened")

    ' Create INSERT statement with named parameters
    nonqueryCommand.CommandText = _
    "INSERT INTO Requisitions (RequsitionID,Date,DepartmentID,Department,ItemID, 'ItemName',CategoryID,Category,UnitID, Units, 'UnitPrice',Quantity, Amount,'UserName')" & _
    " VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID, '@ItemName',@CategoryID,@Category ,@UnitID, @Units, '@UnitPrice',@Quantity,@Amount,'UserName')"

    'Add Parameters to Command Parameters collection
    nonqueryCommand.Parameters.Add("@RequisitionID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Date", SqlDbType.SmallDateTime)
    nonqueryCommand.Parameters.Add("@DepartmentID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Department", SqlDbType.VarChar, 50)
    nonqueryCommand.Parameters.Add("@ItemID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@ItemName", SqlDbType.VarChar, 50)
    nonqueryCommand.Parameters.Add("@CategoryID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Category", SqlDbType.VarChar, 80)
    nonqueryCommand.Parameters.Add("@UnitID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Units", SqlDbType.NVarChar, 20)
    nonqueryCommand.Parameters.Add("@UnitPrice", SqlDbType.Decimal, 9)
    nonqueryCommand.Parameters("@UnitPrice").Precision = 18
    nonqueryCommand.Parameters("@UnitPrice").Scale = 2
    nonqueryCommand.Parameters.Add("@Quantity", SqlDbType.NChar, 10)
    nonqueryCommand.Parameters.Add("@Amount", SqlDbType.Money)
    nonqueryCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 20)

    ' Prepare command for repeated execution
    nonqueryCommand.Prepare()

    ' Data to be inserted

    For Each row As DataGridViewRow In RequisitionsDGV.Rows

    If Not row.IsNewRow Then
    nonqueryCommand.Parameters("@RequisitionID").Value = row.Cells(0).Value.ToString
    nonqueryCommand.Parameters("@Date").Value = row.Cells(1).Value.ToString
    nonqueryCommand.Parameters("@DepartmentID").Value = row.Cells(2).Value.ToString
    nonqueryCommand.Parameters("@Department").Value = row.Cells(3).Value.ToString
    nonqueryCommand.Parameters("@ItemID").Value = row.Cells(4).Value.ToString
    nonqueryCommand.Parameters("@ItemName").Value = row.Cells(5).Value.ToString
    nonqueryCommand.Parameters("@CategoryID").Value = row.Cells(6).Value.ToString
    nonqueryCommand.Parameters("@Category").Value = row.Cells(7).Value.ToString
    nonqueryCommand.Parameters("@UnitID").Value = row.Cells(8).Value.ToString
    nonqueryCommand.Parameters("@Units").Value = row.Cells(9).Value.ToString
    nonqueryCommand.Parameters("@UnitPrice").Value = row.Cells(10).Value.ToString
    nonqueryCommand.Parameters("@Quantity").Value = row.Cells(11).Value.ToString
    nonqueryCommand.Parameters("@Amount").Value = row.Cells(12).Value.ToString
    nonqueryCommand.Parameters("@UserName").Value = row.Cells(13).Value.ToString
    nonqueryCommand.ExecuteNonQuery()

    End If
    Next row



    Catch ex As SqlException
    ' Display error
    MsgBox("Error: " & ex.ToString())
    Finally
    ' Close Connection
    Connection.Close()
    MsgBox("Connection Closed")



    End Try
    End Sub

  4. #4
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Failed to convert parameter value from a String to a Int32.

    Can you please point out which line of your code u r getting this bug
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    this line

    nonqueryCommand.Prepare()

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    sorry not the one i sent before but this:

    nonqueryCommand.ExecuteNonQuery()

  7. #7
    PowerPoster cicatrix's Avatar
    Join Date
    Dec 2009
    Location
    Moscow, Russia
    Posts
    3,654

    Re: Failed to convert parameter value from a String to a Int32.

    Don't use single quotes on the parameters, only on the fields whose names consist more than of two words.
    Did you actually change the field names in your DB table?

  8. #8
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Failed to convert parameter value from a String to a Int32.

    i don't think the error may be there, if so then
    is the datagridview is bound to data ?
    (1) u r trying to insert a string to a numeric field
    i suspect this line or lines
    (2) check this
    vb Code:
    1. row.Cells(0).Value.ToString
    when you are converting the value of a cell to tostring then there must be some value
    whether all the cells you are using are having values ?
    try using
    vb Code:
    1. row.Cells(13).Value

    check any one
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  9. #9

  10. #10
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Failed to convert parameter value from a String to a Int32.

    hawk eye cic !!
    jay
    here why the quotes around the field names & also around parameters
    vb Code:
    1. 'ItemName',CategoryID,Category,UnitID, Units, 'UnitPrice',Quantity, Amount,'UserName')" & _
    2. " VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID, '@ItemName',@CategoryID,@Category ,@UnitID, @Units, '@UnitPrice',@Quantity,@Amount,'UserName')"
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  11. #11

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    cicatrix
    i forgot to remove the single quotes, m have also changed my database tables.le'me try again

    make me rain
    The datagridview is not bound to data " i fill it programmatically"

    will try your idea as well thanks

  12. #12
    PowerPoster cicatrix's Avatar
    Join Date
    Dec 2009
    Location
    Moscow, Russia
    Posts
    3,654

    Re: Failed to convert parameter value from a String to a Int32.

    You can leave the names in your database, still it's confusing that's why I try to avoid naming fields like 'Unit Price' - note that you should surround such names in single quotes if you use them. It'll be easier and less confusing if you name it like UnitPrice (w/o whitespace).
    As for the parameter names - I don't even know if multi-word parameters are supported, so you should use @UnitPrice even if the name of your database field is 'Unit Price'.
    And of course the names of the fields in your SQL statements must exactly match the names of the fields in the database table.

  13. #13

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    Guys'

    even after trying out your options i still get the same error at the line- nonqueryCommand.ExecuteNonQuery()

    Pls review my current code and point out any mistakes.

    Private Sub btnPlace_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPlace.Click
    ' Modify the following code to correctly connect to your SQL Server.
    Dim Connection As New SqlConnection("Server=.\SQLEXPRESS;Initial Catalog=Store Management System;Integrated Security=SSPI ")

    'Create Command object
    Dim nonqueryCommand As SqlCommand = Connection.CreateCommand()




    Try
    ' Open Connection
    Connection.Open()
    MsgBox("Connection Opened")

    ' Create INSERT statement with named parameters
    nonqueryCommand.CommandText = _
    "INSERT INTO Requisitions (RequsitionID,Date,DepartmentID,Department,ItemID,'Item Name',CategoryID,Category,UnitID,Units,'Unit Price',Quantity,Amount,'User Name')" & _
    " VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID,@Item Name,@CategoryID,@Category,@UnitID,@Units,@Unit Price,@Quantity,@Amount,@User Name)"

    'Add Parameters to Command Parameters collection
    nonqueryCommand.Parameters.Add("@RequisitionID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Date", SqlDbType.SmallDateTime)
    nonqueryCommand.Parameters.Add("@DepartmentID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Department", SqlDbType.VarChar, 50)
    nonqueryCommand.Parameters.Add("@ItemID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Item_Name", SqlDbType.VarChar, 50)
    nonqueryCommand.Parameters.Add("@CategoryID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Category", SqlDbType.VarChar, 80)
    nonqueryCommand.Parameters.Add("@UnitID", SqlDbType.Int)
    nonqueryCommand.Parameters.Add("@Units", SqlDbType.NVarChar, 20)
    nonqueryCommand.Parameters.Add("@Unit_Price", SqlDbType.Decimal, 9)
    nonqueryCommand.Parameters("@Unit_Price").Precision = 18
    nonqueryCommand.Parameters("@Unit_Price").Scale = 2
    nonqueryCommand.Parameters.Add("@Quantity", SqlDbType.NChar, 10)
    nonqueryCommand.Parameters.Add("@Amount", SqlDbType.Money)
    nonqueryCommand.Parameters.Add("@User_Name", SqlDbType.VarChar, 20)

    ' Prepare command for repeated execution
    nonqueryCommand.Prepare()

    ' Data to be inserted

    For Each row As DataGridViewRow In RequisitionsDGV.Rows

    If Not row.IsNewRow Then
    nonqueryCommand.Parameters("@RequisitionID").Value = row.Cells(0).Value
    nonqueryCommand.Parameters("@Date").Value = row.Cells(1).Value
    nonqueryCommand.Parameters("@DepartmentID").Value = row.Cells(2).Value
    nonqueryCommand.Parameters("@Department").Value = row.Cells(3).Value
    nonqueryCommand.Parameters("@ItemID").Value = row.Cells(4).Value
    nonqueryCommand.Parameters("@Item_Name").Value = row.Cells(5).Value
    nonqueryCommand.Parameters("@CategoryID").Value = row.Cells(6).Value
    nonqueryCommand.Parameters("@Category").Value = row.Cells(7).Value
    nonqueryCommand.Parameters("@UnitID").Value = row.Cells(8).Value
    nonqueryCommand.Parameters("@Units").Value = row.Cells(9).Value
    nonqueryCommand.Parameters("@Unit_Price").Value = row.Cells(10).Value
    nonqueryCommand.Parameters("@Quantity").Value = row.Cells(11).Value
    nonqueryCommand.Parameters("@Amount").Value = row.Cells(12).Value
    nonqueryCommand.Parameters("@User_Name").Value = row.Cells(13).Value
    nonqueryCommand.ExecuteNonQuery()

    End If
    Next row



    Catch ex As SqlException
    ' Display error
    MsgBox("Error: " & ex.ToString())
    Finally
    ' Close Connection
    Connection.Close()
    MsgBox("Connection Closed")



    End Try
    End Sub

  14. #14
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Failed to convert parameter value from a String to a Int32.

    still i could see quotes around the fields (please refer point number (2))
    nonqueryCommand.CommandText = _
    "INSERT INTO Requisitions (RequsitionID,Date,DepartmentID,Department,ItemID,'Item Name',CategoryID,Category,UnitID,Units,'Unit Price',Quantity,Amount,'User Name')" & _
    " VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID,@Item Name,@CategoryID,@Category,@UnitID,@Units,@Unit Price,@Quantity,@Amount,@User Name)"
    (2) as far as i know in access when there is a empty space in the field name the field name was
    surrounded with [] brackets, i never user SQL server
    (3) break at this line nonqueryCommand.Parameters("@RequisitionID").Value = row.Cells(0).Value
    and check what are the values line by line
    (4) try to run your nonqueryCommand.CommandText SQL statement with in your SQL server query
    window and find out what exception it is throwing
    (5) for the time being remove the try catch block
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  15. #15

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    Make me Rain,

    Tried all that bt still getting "format exception was unhandled" . Failed to convert parameter value to a int32.

    Can u show me how i can convert the datagrid cell values from string to int32 like how to convert RequisitionID to int32

  16. #16
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Failed to convert parameter value from a String to a Int32.

    please note shall i assume you did all i above mentioned ?
    still u r getting error on the line
    nonqueryCommand.ExecuteNonQuery()
    is this is a oledb exception
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  17. #17

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    Yes I did what u pointed out.

    Thinks it is something to do with convertsion of datatype at runtime

  18. #18
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Failed to convert parameter value from a String to a Int32.

    vb Code:
    1. 'Add Parameters to Command Parameters collection
    2.         '     .Parameters.Add("@RequisitionID", SqlDbType.Int)
    3.         '     .Parameters.Add("@Date", SqlDbType.SmallDateTime)
    4.         '     .Parameters.Add("@DepartmentID", SqlDbType.Int)
    5.         '     .Parameters.Add("@Department", SqlDbType.VarChar, 50)
    6.         '     .Parameters.Add("@ItemID", SqlDbType.Int)
    7.         '     .Parameters.Add("@Item_Name", SqlDbType.VarChar, 50)
    8.         '    .Parameters.Add("@CategoryID", SqlDbType.Int)
    9.         '     .Parameters.Add("@Category", SqlDbType.VarChar, 80)
    10.         '     .Parameters.Add("@UnitID", SqlDbType.Int)
    11.         '    .Parameters.Add("@Units", SqlDbType.NVarChar, 20)
    12.         '    .Parameters.Add("@Unit_Price", SqlDbType.Decimal, 9)
    13.         '    .Parameters("@Unit_Price").Precision = 18
    14.         '    .Parameters("@Unit_Price").Scale = 2
    15.         '    .Parameters.Add("@Quantity", SqlDbType.NChar, 10)
    16.         '    .Parameters.Add("@Amount", SqlDbType.Money)
    17.         '    .Parameters.Add("@User_Name", SqlDbType.VarChar, 20)
    18.  
    19.         ' Prepare command for repeated execution
    20.         '    .Prepare()
    21.  
    22.         ' Data to be inserted
    23.         Dim SQL_insert As String = _
    24.         "INSERT INTO Requisitions (Requisitions.RequsitionID,Requisitions.Date,Requisitions.DepartmentID, " _
    25.         & " Requisitions.Department,Requisitions.ItemID,Requisitions.[Item Name],Requisitions.CategoryID, " _
    26.         & " Requisitions.Category,Requisitions.UnitID,Requisitions.Units,Requisitions.[Unit Price], " _
    27.         & " Requisitions.Quantity,Amount,Requisitions.[User Name])" & _
    28. " VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID,@ItemName,@CategoryID,@Category,@UnitID,@Units, " _
    29. & " @UnitPrice,@Quantity,@Amount,@UserName)"
    30.         Dim NonQueryCommand As New OleDbCommand
    31.  
    32.         With NonQueryCommand
    33.             .CommandText = SQL_insert
    34.             .CommandType = CommandType.Text
    35.             .Connection = "Your cONNECTION oBJECT"
    36.         End With
    37.  
    38.         For Each row As DataGridViewRow In RequisitionsDGV.Rows
    39.             If Not row.IsNewRow Then
    40.                 With NonQueryCommand
    41.                     .Parameters.AddWithValue.AddWithValue("@RequisitionID", .Value = row.Cells(0).Value)
    42.                     .Parameters.AddWithValue("@Date", row.Cells(1).Value)
    43.                     .Parameters.AddWithValue("@DepartmentID", .Value, row.Cells(2).Value)
    44.                     .Parameters.AddWithValue("@Department", .row.Cells(3).Value)
    45.                     .Parameters.AddWithValue("@ItemID", .row.Cells(4).Value)
    46.                     .Parameters.AddWithValue("@ItemName", .row.Cells(5).Value)
    47.                     .Parameters.AddWithValue("@CategoryID", .row.Cells(6).Value)
    48.                     .Parameters.AddWithValue("@Category", .row.Cells(7).Value)
    49.                     .Parameters.AddWithValue("@UnitID", .row.Cells(8).Value)
    50.                     .Parameters.AddWithValue("@Units", .row.Cells(9).Value)
    51.                     .Parameters.AddWithValue("@UnitPrice", .Value, .row.Cells(10).Value)
    52.                     .Parameters.AddWithValue("@Quantity", .Value, .row.Cells(11).Value)
    53.                     .Parameters.AddWithValue("@Amount", .row.Cells(12).Value)
    54.                     .Parameters.AddWithValue("@UserName", .row.Cells(13).Value) ' see this line
    55.                     .ExecuteNonQuery()
    56.                     .Parameters.Clear()
    57.                 End With
    58.             End If
    59.         Next row
    60.  
    61.         NonQueryCommand.Dispose()
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  19. #19

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    Make me rain,

    U have almost made my day.


    I have used your code but i get an error that i must declare scalar variable ' requisitionID' How do i get to fix this one ?

  20. #20
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: Failed to convert parameter value from a String to a Int32.

    VALUES(@RequsitionID Vs .Parameters.AddWithValue.AddWithValue("@RequisitionID"
    see these 2 red words in u r statement both should be same
    accordingly change them as @RequsitionID
    vb Code:
    1. .Parameters.AddWithValue.AddWithValue("@RequsitionID", .Value = row.Cells(0).Value)
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  21. #21

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    thanks alot almost there.. now i get the following error

    Conversion failed when converting character string to smalldatetime data type.

  22. #22

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    13

    Re: Failed to convert parameter value from a String to a Int32.

    Tanx so much Make me rain

    I finally got it Kudos

    Best Regards!!

  23. #23
    New Member
    Join Date
    Jan 2018
    Posts
    14

    Re: Failed to convert parameter value from a String to a Int32.

    Make me rain and Jayeem,
    I am facing a problem with a code i copied from Jayeem...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,sergro up,othergroup,daygroup,memsince,transfer) values(@id,@datemembregis,@year,@title,@fullname,@maidname,@dob,@Age,@gender,@hometown,@pob,@MemStat us,@MemType,@maritalstatus,@marriagetype,@SpouseName,@SpouseDeno,@dobap,@placeofbap,@doconf,@placeof conf,@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

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