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

[jayeem]

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

[cicatrix]

"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.

[jayeem]

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

[make me rain]

Can you please point out which line of your code u r getting this bug

[jayeem]

this line

nonqueryCommand.Prepare()

[jayeem]

sorry not the one i sent before but this:

nonqueryCommand.ExecuteNonQuery()

[cicatrix]

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?

[make me rain]

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

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

row.Cells(13).Value

check any one

[cicatrix]

He's got errors in his SQL statement, first of all.

[make me rain]

hawk eye cic !!
jay
here why the quotes around the field names & also around parameters

'ItemName',CategoryID,Category,UnitID, Units, 'UnitPrice',Quantity, Amount,'UserName')" & _
" VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID, '@ItemName',@CategoryID,@Category ,@UnitID, @Units, '@UnitPrice',@Quantity,@Amount,'UserName')"

[jayeem]

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

[cicatrix]

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.

[jayeem]

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

[make me rain]

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

[jayeem]

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

[make me rain]

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

[jayeem]

Yes I did what u pointed out.

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

[make me rain]

'Add Parameters to Command Parameters collection
        '     .Parameters.Add("@RequisitionID", SqlDbType.Int)
        '     .Parameters.Add("@Date", SqlDbType.SmallDateTime)
        '     .Parameters.Add("@DepartmentID", SqlDbType.Int)
        '     .Parameters.Add("@Department", SqlDbType.VarChar, 50)
        '     .Parameters.Add("@ItemID", SqlDbType.Int)
        '     .Parameters.Add("@Item_Name", SqlDbType.VarChar, 50)
        '    .Parameters.Add("@CategoryID", SqlDbType.Int)
        '     .Parameters.Add("@Category", SqlDbType.VarChar, 80)
        '     .Parameters.Add("@UnitID", SqlDbType.Int)
        '    .Parameters.Add("@Units", SqlDbType.NVarChar, 20)
        '    .Parameters.Add("@Unit_Price", SqlDbType.Decimal, 9)
        '    .Parameters("@Unit_Price").Precision = 18
        '    .Parameters("@Unit_Price").Scale = 2
        '    .Parameters.Add("@Quantity", SqlDbType.NChar, 10)
        '    .Parameters.Add("@Amount", SqlDbType.Money)
        '    .Parameters.Add("@User_Name", SqlDbType.VarChar, 20)
 
        ' Prepare command for repeated execution
        '    .Prepare()
 
        ' Data to be inserted
        Dim SQL_insert As String = _
        "INSERT INTO Requisitions (Requisitions.RequsitionID,Requisitions.Date,Requisitions.DepartmentID, " _
        & " Requisitions.Department,Requisitions.ItemID,Requisitions.[Item Name],Requisitions.CategoryID, " _
        & " Requisitions.Category,Requisitions.UnitID,Requisitions.Units,Requisitions.[Unit Price], " _
        & " Requisitions.Quantity,Amount,Requisitions.[User Name])" & _
" VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID,@ItemName,@CategoryID,@Category,@UnitID,@Units, " _
& " @UnitPrice,@Quantity,@Amount,@UserName)"
        Dim NonQueryCommand As New OleDbCommand
 
        With NonQueryCommand
            .CommandText = SQL_insert
            .CommandType = CommandType.Text
            .Connection = "Your cONNECTION oBJECT"
        End With
 
        For Each row As DataGridViewRow In RequisitionsDGV.Rows
            If Not row.IsNewRow Then
                With NonQueryCommand
                    .Parameters.AddWithValue.AddWithValue("@RequisitionID", .Value = row.Cells(0).Value)
                    .Parameters.AddWithValue("@Date", row.Cells(1).Value)
                    .Parameters.AddWithValue("@DepartmentID", .Value, row.Cells(2).Value)
                    .Parameters.AddWithValue("@Department", .row.Cells(3).Value)
                    .Parameters.AddWithValue("@ItemID", .row.Cells(4).Value)
                    .Parameters.AddWithValue("@ItemName", .row.Cells(5).Value)
                    .Parameters.AddWithValue("@CategoryID", .row.Cells(6).Value)
                    .Parameters.AddWithValue("@Category", .row.Cells(7).Value)
                    .Parameters.AddWithValue("@UnitID", .row.Cells(8).Value)
                    .Parameters.AddWithValue("@Units", .row.Cells(9).Value)
                    .Parameters.AddWithValue("@UnitPrice", .Value, .row.Cells(10).Value)
                    .Parameters.AddWithValue("@Quantity", .Value, .row.Cells(11).Value)
                    .Parameters.AddWithValue("@Amount", .row.Cells(12).Value)
                    .Parameters.AddWithValue("@UserName", .row.Cells(13).Value) ' see this line
                    .ExecuteNonQuery()
                    .Parameters.Clear()
                End With
            End If
        Next row
 
        NonQueryCommand.Dispose()

[jayeem]

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 ?

[make me rain]

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

.Parameters.AddWithValue.AddWithValue("@RequsitionID", .Value = row.Cells(0).Value)

[jayeem]

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

Conversion failed when converting character string to smalldatetime data type.

[jayeem]

Tanx so much Make me rain

I finally got it Kudos

Best Regards!!