-
Apr 15th, 2012, 01:57 AM
#1
Thread Starter
New Member
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
-
Apr 15th, 2012, 05:41 AM
#2
Re: Failed to convert parameter value from a String to a Int32.
Originally Posted by jayeem
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.
-
Apr 15th, 2012, 06:42 AM
#3
Thread Starter
New Member
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
-
Apr 15th, 2012, 07:55 AM
#4
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
-
Apr 15th, 2012, 08:00 AM
#5
Thread Starter
New Member
Re: Failed to convert parameter value from a String to a Int32.
this line
nonqueryCommand.Prepare()
-
Apr 15th, 2012, 08:01 AM
#6
Thread Starter
New Member
Re: Failed to convert parameter value from a String to a Int32.
sorry not the one i sent before but this:
nonqueryCommand.ExecuteNonQuery()
-
Apr 15th, 2012, 08:10 AM
#7
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?
-
Apr 15th, 2012, 08:16 AM
#8
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:
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
check any one
-
Apr 15th, 2012, 08:21 AM
#9
Re: Failed to convert parameter value from a String to a Int32.
He's got errors in his SQL statement, first of all.
-
Apr 15th, 2012, 08:27 AM
#10
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:
'ItemName',CategoryID,Category,UnitID, Units, 'UnitPrice',Quantity, Amount,'UserName')" & _ " VALUES(@RequsitionID,@Date,@DepartmentID,@Department,@ItemID, '@ItemName',@CategoryID,@Category ,@UnitID, @Units, '@UnitPrice',@Quantity,@Amount,'UserName')"
-
Apr 15th, 2012, 08:30 AM
#11
Thread Starter
New Member
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
-
Apr 15th, 2012, 08:38 AM
#12
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.
-
Apr 15th, 2012, 09:30 AM
#13
Thread Starter
New Member
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
-
Apr 15th, 2012, 09:55 AM
#14
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
-
Apr 15th, 2012, 10:25 AM
#15
Thread Starter
New Member
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
-
Apr 15th, 2012, 10:45 AM
#16
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
-
Apr 15th, 2012, 10:58 AM
#17
Thread Starter
New Member
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
-
Apr 15th, 2012, 11:02 AM
#18
Re: Failed to convert parameter value from a String to a Int32.
vb Code:
'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()
-
Apr 15th, 2012, 11:56 AM
#19
Thread Starter
New Member
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 ?
-
Apr 15th, 2012, 12:03 PM
#20
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:
.Parameters.AddWithValue.AddWithValue("@RequsitionID", .Value = row.Cells(0).Value)
-
Apr 15th, 2012, 02:44 PM
#21
Thread Starter
New Member
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.
-
Apr 15th, 2012, 05:18 PM
#22
Thread Starter
New Member
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!!
-
Jan 20th, 2018, 05:38 AM
#23
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|