How to insert records from DataGridView to SQL Server Database 2000

[tracy]

Hi everyone,

This is Tracy here.
I'm having a form created from Visual Studio 2005 and it contains a button named as Add and a DataGridview. If the records are successfully added, it will be inserted into the SQL server database.

But my program can't work and I don't know where it had gone wrong.

Here listed my codes when the user click on the "Add" button for the DataGridView:

'Add Button For DataGrid
Dim sqlcom As SqlCommand = New SqlCommand()
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter
Dim myDataSet AsNew DataSet()
Dim mySqlcb As SqlCommandBuilder
mySqlcb = New SqlCommandBuilder(mySqlDataAdapter)
mySqlDataAdapter.InsertCommand = mySqlcb.GetInsertCommand
mySqlDataAdapter.InsertCommand.CommandText = "INSERT INTO TBReqDG(ReqID,Quantity,Description,UnitPrice,TotalPrice) VALUES(@ReqID,@Quantity,@Description,@UnitPrice,@TotalPrice)"
Dim para As SqlParameter 
'para = sqlcom.Parameters.Add("@OrgReqID", SqlDbType.Int)
'para.SourceColumn = "ReqID"
'para.SourceVersion = DataRowVersion.Original
para = sqlcom.Parameters.Add("@ReqID", SqlDbType.Int, 4)
para.SourceColumn = "ReqID"
para.SourceVersion = DataRowVersion.Current
para = sqlcom.Parameters.Add("@Quantity", SqlDbType.NVarChar, 50)
para.SourceColumn = "Quantity"
para.SourceVersion = DataRowVersion.Current
para = sqlcom.Parameters.Add("@Description", SqlDbType.NVarChar, 50)
para.SourceColumn = "Description"
para.SourceVersion = DataRowVersion.Current
para = sqlcom.Parameters.Add("@UnitP", SqlDbType.NVarChar, 50)
para.SourceColumn = "UnitPrice"
para.SourceVersion = DataRowVersion.Current
para = sqlcom.Parameters.Add("@TotalP", SqlDbType.NVarChar, 50)
para.SourceColumn = "TotalPrice"
para.SourceVersion = DataRowVersion.Current
'mySqlDataAdapter.InsertCommand = ""
sqlcon.Open()
mySqlDataAdapter.InsertCommand = New SqlCommand("INSERT INTO TBReqDG(ReqID,Quantity,Description,UnitPrice,TotalPrice) VALUES(@ReqID,@Quantity,@Description,@UnitPrice,@TotalPrice)")
sqlcon.Close()

Hope to receive reply soon as it's urgent.

 

[vis781]

Ok, here is your code again with all the bits you don't need highlighted in red....

'Add Button For DataGrid
Dim sqlcom As SqlCommand = New SqlCommand()
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter
'Reason, you should be using the same dataadapter that you filled the datatable with in the first place
Dim myDataSet AsNew DataSet()
' Does Nothing Here
Dim mySqlcb As SqlCommandBuilder
mySqlcb = New SqlCommandBuilder(mySqlDataAdapter)
mySqlDataAdapter.InsertCommand = mySqlcb.GetInsertCommand
mySqlDataAdapter.InsertCommand.CommandText = "INSERT INTO TBReqDG(ReqID,Quantity,Description,UnitPrice,TotalPrice) VALUES(@ReqID,@Quantity,@Description,@UnitPrice,@TotalPrice)"
'The command builder object has already created the insert command for you. There is no need to create a new one.
Dim para As SqlParameter
'para = sqlcom.Parameters.Add("@OrgReqID", SqlDbType.Int)
'para.SourceColumn = "ReqID"
'para.SourceVersion = DataRowVersion.Original
para = sqlcom.Parameters.Add("@ReqID", SqlDbType.Int, 4)
para.SourceColumn = "ReqID"
para.SourceVersion = DataRowVersion.Current
para = sqlcom.Parameters.Add("@Quantity", SqlDbType.NVarChar, 50)
para.SourceColumn = "Quantity"
para.SourceVersion = DataRowVersion.Current
para = sqlcom.Parameters.Add("@Description", SqlDbType.NVarChar, 50)
para.SourceColumn = "Description"
para.SourceVersion = DataRowVersion.Current
para = sqlcom.Parameters.Add("@UnitP", SqlDbType.NVarChar, 50)
para.SourceColumn = "UnitPrice"
para.SourceVersion = DataRowVersion.Current
para = sqlcom.Parameters.Add("@TotalP", SqlDbType.NVarChar, 50)
para.SourceColumn = "TotalPrice"
para.SourceVersion = DataRowVersion.Current
'mySqlDataAdapter.InsertCommand = ""
'Same reason as above.
sqlcon.Open()
mySqlDataAdapter.InsertCommand = New SqlCommand("INSERT INTO TBReqDG(ReqID,Quantity,Description,UnitPrice,TotalPrice) VALUES(@ReqID,@Quantity,@Description,@UnitPrice,@TotalPrice)")
'Doesn't do anything relavent here
sqlcon.Close()

Ok, so the commandbuilder creates an insert command for you when you call it's GetInsertCommand method. All you need to do then is call the update method of the dataadapter.

DataAdapter.Update(TableName)

Thats it. Also you don't need to explicitly open and close the connection as the dataadapter will do this for you. But my burning question here is have you already filled a datatable with information from your database or are you just trying to insert records into your database without retrieving any information first?

 

[tracy]

Pls reply through here if you know the answer and thanks !!

Hi vis781 and everyone,

I am trying to insert records into my SQL Server database without retrieving any information first.

I made use of the codings as shown below:

'Add Button For DataGridView
Dim sqlcom As SqlCommand = New SqlCommand()
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter
Dim myDataSet AsNew DataSet()
Dim mySqlcb As SqlCommandBuilder
mySqlcb = New SqlCommandBuilder(mySqlDataAdapter)
mySqlDataAdapter.InsertCommand = mySqlcb.GetInsertCommand
mySqlDataAdapter.Update("TBReqDG") 'ERROR COMES FROM HERE
'Open connection, execute the commands
sqlcon.Open()
'Close Connection
sqlcon.Close()


But there is still an error as shown below:

Error 1 Overload resolution failed because no accessible 'Update' can be called with these arguments:
'Public Function Update(dataTable As System.Data.DataTable) As Integer': Value of type 'String' cannot be converted to 'System.Data.DataTable'.
'Public Function Update(dataRows() As System.Data.DataRow) As Integer': Value of type 'String' cannot be converted to '1-dimensional array of System.Data.DataRow'.
'Public Overrides Function Update(dataSet As System.Data.DataSet) As Integer': Value of type 'String' cannot be converted to 'System.Data.DataSet'. C:\Documents and Settings\Administrator\Desktop\CCC\OfasERP_New PartII\OfasERP\frmRequisitionVoucher.vb 246 9 OfasERP


Hope to receive reply soon as it's urgent & thanks

 

[vis781]

It is saying that you should be passing the name of your Datatable/Dataset to the dataadapters update method. The first argument should be the name of your datatable, then if you want to go that way, the second argument should be the name of your database table in a string format.

DataAdapter.Update(Datatable, "SourceTable")

 

[tracy]

Hi vis 781 & everyone

I am trying to insert records from DataGridView </SPAN>into my SQL Server database without retrieving any information and I do not require to retrieve records from database. Initally, the database in SQL Server is empty. For this program, I made use of the SqlDataAdapter, DataSet and BindingSource.


This is my latest codings as listed below:

Dim sqlcom As SqlCommand = New SqlCommand()
Dim da As New SqlDataAdapter("SELECT * FROM TBReqDG", sqlcon)
Dim myDataSet As New DataSet()
Dim cb As New SqlCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand()
da.Update(myDataSet, "TBReqDG") 'ERROR COMES FROM HERE
'Open connection, execute the commands
sqlcon.Open()
sqlcom.Connection = sqlcon
'Close Connection
sqlcon.Close()


As my program runs, it show this error à Update unable to find TableMapping['TBReqDG'] or DataTable 'TBReqDG'.
But I'm using a dataset and not a datatable.


Hope to receive reply soon as it’s urgent & thanks.

Regards
Tracy

 

[tracy]

Hi vis781 & everyone,


Just want to clarify if I add records from a DataGridView in Visual Studio
2005 to SQL Server database ?

Hope to receive reply soon as it's urgent

Rgds
Tracy

 

[vis781]

You are using a datatable, you should look up the differences between a dataset and a datatable. Just pass the datatable in the update method...

DataAdapter.Update(mydataset.tables(0))

Or the name of your datatable

 

[tracy]

Hi vis781 & everyone

Thanks vis781 for the reply and hope that you can guide me along if I
make any mistake for the coding

This is my latest coding as shown below:

[FONT=Courier New]      [COLOR=green]'Declare SQL Command[/COLOR][/FONT]
[FONT=Courier New]      [COLOR=blue]Dim[/COLOR] inscmd [COLOR=blue]As[/COLOR] SqlCommand = [COLOR=blue]New[/COLOR] SqlCommand()[/FONT]
[FONT=Courier New]      [COLOR=green]'Open connection, execute the commands[/COLOR][/FONT]
[FONT=Courier New]      sqlcon.Open()[/FONT]
[FONT=Courier New]      inscmd.Connection = sqlcon[/FONT]
[FONT=Courier New]      [COLOR=green]'Declare SQL DataAdapter for "TBReqDG" table[/COLOR][/FONT]
[FONT=Courier New]      [COLOR=blue]Dim[/COLOR] sqlda [COLOR=blue]As[/COLOR] [COLOR=blue]New[/COLOR] SqlDataAdapter([COLOR=maroon]"SELECT * FROM TBReqDG"[/COLOR], sqlcon)[/FONT]
[FONT=Courier New]      [COLOR=green]'Declare New DataSet[/COLOR][/FONT]
[FONT=Courier New]      [COLOR=blue]Dim[/COLOR] myDataSet [COLOR=blue]As[/COLOR] [COLOR=blue]New[/COLOR] DataSet()[/FONT]
[FONT=Courier New]      [COLOR=green]'Fill DataSet[/COLOR][/FONT]
[FONT=Courier New]      sqlda.Fill(myDataSet, [COLOR=maroon]"TBReqDG"[/COLOR])[/FONT]
[FONT=Courier New]      [COLOR=green]'Bind datagridview with DataSet[/COLOR][/FONT]
[FONT=Courier New]      grdRequest.DataSource = myDataSet.Tables([COLOR=maroon]"TBReqDG"[/COLOR])[/FONT]
[COLOR=green][FONT=Courier New]'Bulid Insert Commands[/FONT][/COLOR]
[FONT=Courier New]      inscmd.Parameters.Add([COLOR=maroon]"@ReqID"[/COLOR], SqlDbType.Int, 4, [COLOR=maroon]"ReqID"[/COLOR])[/FONT]
[FONT=Courier New]      inscmd.Parameters.Add([COLOR=maroon]"@Quantity"[/COLOR], SqlDbType.NVarChar, 50, [COLOR=maroon]"Quantity"[/COLOR])[/FONT]
[FONT=Courier New]      inscmd.Parameters.Add([COLOR=maroon]"Description"[/COLOR], SqlDbType.NVarChar, 50, [COLOR=maroon]"Description"[/COLOR])[/FONT]
[FONT=Courier New]      inscmd.Parameters.Add([COLOR=maroon]"@UnitPrice"[/COLOR], SqlDbType.NVarChar, 50, [COLOR=maroon]"UnitPrice"[/COLOR])[/FONT]
[FONT=Courier New]      inscmd.Parameters.Add([COLOR=maroon]"@TotalPrice"[/COLOR], SqlDbType.NVarChar, 50, [COLOR=maroon]"TotalPrice"[/COLOR])[/FONT]
[FONT=Courier New]      sqlda.InsertCommand = inscmd[/FONT]
[FONT=Courier New]      inscmd.CommandText = [COLOR=maroon]"INSERT INTO TBReqDG(ReqID,Quantity,Description,UnitPrice,TotalPrice) VALUES(@ReqID,@Quantity,@Description,@UnitPrice,@TotalPrice)"[/COLOR][/FONT]
 
[FONT=Courier New]      inscmd.ExecuteNonQuery()[COLOR=green]'ERROR COMES FROM HERE[/COLOR][/FONT]
 
[FONT=Courier New]      [COLOR=green]'Update SQL DataAdapter with DataSet[/COLOR][/FONT]
[FONT=Courier New]      sqlda.Update(myDataSet.Tables([COLOR=maroon]"TBReqDG"[/COLOR]))[/FONT]
[FONT=Courier New]      [COLOR=green]'Close Connection[/COLOR][/FONT]
[FONT=Courier New]      sqlcon.Close()[/FONT]

As my program runs, it show this error
--> Prepared statement '(@ReqID int,@Quantity nvarchar(50),@Description nvarchar(50),@Un' expects parameter @ReqID, which was not supplied.

Hope to receive reply soon as it’s urgent & thanks.

Rgds
Tracy

 

[capella07]

Apparently Tracy's problem was resolved, as there haven't been any posts here since Oct. 26th.

Much of what Tracy posted here is what I am dealing with. My code looks very much like the examples posted. I've encountered all of the errors mentioned here, but still can't get my data written to the database table. I posted this stuff over at VBForums.com, but no one's answered yet and I've been working on this for two days now!

Below is the code I've got so far.

Dim GetAtmtTable As New SqlCommand("Select * from dbo.emptyemailwithattachments", sqlConn)
Dim EtavDataAdapter As New SqlDataAdapter(GetAtmtTable)
 
EtavDataAdapter.SelectCommand.Connection = sqlConn
Dim EtavDataSet As New DataSet
EtavDataAdapter.Fill(EtavDataSet, "emptyemailwithattachments")
'DataGridView1.DataSource = EtavDataSet
'DataGridView1.DataMember = "emailwithattachments"
'The connection works fine, as the above binding populates the DataGridView
 
Dim mailSubj As String
mailSubj = mailItem.Subject.ToString
 
Dim mailDateReceived As String
mailDateReceived = mailItem.ReceivedTime.ToString
 
Dim mailAtmtName As String
mailAtmtName = mailItem.Attachments.Item(atmtCounter).FileName
 
Dim dateAppended As String
dateAppended = DateTime.Now.ToString
 
Dim row As DataRow = EtavDataSet.Tables("emptyemailwithattachments").NewRow
 
row.Item("Subject") = mailSubj
row.Item("DateReceived") = mailDateReceived
row.Item("FileName") = mailAtmtName
row.Item("DateAppended") = dateAppended
 
EtavDataSet.Tables("emptyemailwithattachments").Rows.Add(row)
 
' Create the InsertCommand.
Dim insertIntoAtmtTable As New SqlCommand("INSERT INTO emptyemailwithattachments" & _
"(Subject, DateReceived, FileName, DateAppended)" & _
"VALUES (@mailSubj, @mailDateReceived, @mailAtmtName, @dateAppended)", sqlConn)
 
'Using the SQL statement's variable names
'insertIntoAtmtTable.Parameters.Add(mailSubj, SqlDbType.NVarChar, 100, "Subject")
'insertIntoAtmtTable.Parameters.Add(mailDateReceived, SqlDbType.NVarChar, 20, "DateReceived")
'insertIntoAtmtTable.Parameters.Add(mailAtmtName, SqlDbType.NVarChar, 100, "FileName")
'insertIntoAtmtTable.Parameters.Add(dateAppended, SqlDbType.NVarChar, 20, "DateAppended")
 
'Error generated by above code:
'"SqlException was unhandled. Line 1: Incorrect syntax near '<'. Must declare the variable '@mailSubj'."
 
'Using the datbase table's column names
insertIntoAtmtTable.Parameters.Add("mailSubj", SqlDbType.NVarChar, 100, "Subject")
insertIntoAtmtTable.Parameters.Add("mailDateReceived", SqlDbType.NVarChar, 20, "DateReceived")
insertIntoAtmtTable.Parameters.Add("mailAtmtName", SqlDbType.NVarChar, 100, "FileName")
insertIntoAtmtTable.Parameters.Add("dateAppended", SqlDbType.DateTime, 20, "DateAppended")
 
'Error generated by above code:
'"SqlException was unhandled. Invalid object name 'emptyemailwithattachments'."
 
EtavDataAdapter.InsertCommand = insertIntoAtmtTable
 
EtavDataAdapter.Update(EtavDataSet, "emptyemailwithattachments")
'Both of the errors are on the above line

So, that's where I'm at. I would greatly appreciate some help here. Two days on this is waaaaay too long!

Thanks

 

[vis781]

insertIntoAtmtTable.Parameters.Add("mailSubj", SqlDbType.NVarChar, 100, "Subject")
insertIntoAtmtTable.Parameters.Add("mailDateReceived", SqlDbType.NVarChar, 20, "DateReceived")
insertIntoAtmtTable.Parameters.Add("mailAtmtName", SqlDbType.NVarChar, 100, "FileName")
insertIntoAtmtTable.Parameters.Add("dateAppended", SqlDbType.DateTime, 20, "DateAppended")

This should look more like....

insertIntoAtmtTable.Parameters.Add("@mailSubj", SqlDbType.NVarChar, 100, "Subject")
insertIntoAtmtTable.Parameters.Add("@mailDateReceived", SqlDbType.NVarChar, 20, "DateReceived")
insertIntoAtmtTable.Parameters.Add("@mailAtmtName", SqlDbType.NVarChar, 100, "FileName")
insertIntoAtmtTable.Parameters.Add("@dateAppended", SqlDbType.DateTime, 20, "DateAppended")

But remember the first argument is the parameter name, the last argument has got to be the original name of the column in the database.

EtavDataAdapter.Update(EtavDataSet, "emptyemailwithattachments")

Secondly on the above line i find it's always just easier to pass the datatable you are using.

EtavDataAdapter.Update(EtavDataSet.Tables(0))

Or whatever the index you datatable is located at. If it's the only table in the dataset then the above code should work just fine. Also stick a try/ catch block around your update line and display the error message. it will be much more descriptive than that unhandled exception one

 

[capella07]

[Resolved & Lessons learned]

Okay, after a lot of more searching, trial and error, a good night's sleep, and some more trial and error this morning, I finally got it working
properly!

Below is the final, working code.

For the benefit of others, I'm going to include some explainations. I went through three days of searching, posting questions, and trying
code before I finally got this right. Hopefully I can help prevent someone else from doing the same...

The biggest things that were tripping me up were the correct syntax in the INSERT statement, and the Tables() syntax.

I was using the table name (emptyemailwithattachments) in the EtavDataSet.Tables lines. I found that just using a "0" in "EtavDataSet.Tables(0)"
rather than the table name, as I've seen it done in the forums is sufficient, since the table name has already been "found" in the select statement.

This is a Windows form, not aspx (which I usually work on), so it's a whole different ballgame in some respects. In the INSERT statement I had to
tell the statement to "escape" from SQL, read the VB variable in the code, and then "re-enter" the statement.

For example, in [' " & mailSubj & "
',] the first ' (single quote) is the beginning of the value in the sql statement. The first " (double quote) escapes from the SQL syntax. The first
& (ampersand) is VB code that concatenates whatever follows. Then follows the mailSubj variable that was declared and set earlier in the code. Then we concatenate (&)
again, "re-enter" the SQL statement (") and signify the end of the first value ('). Finally, of course, is the comma which separates the values in the SQL statement.

'Connection string defined earlier in code
sqlConn.Open()
 
Dim GetAtmtTable As New SqlCommand("Select * from dbo.emptyemailwithattachments", sqlConn)
Dim EtavDataAdapter As New SqlDataAdapter(GetAtmtTable)
EtavDataAdapter.SelectCommand.Connection = sqlConn
 
Dim EtavDataSet As New DataSet
EtavDataAdapter.Fill(EtavDataSet)
 
Dim mailSubj As String
mailSubj = MailItem.Subject.ToString
 
Dim mailDateReceived As String
mailDateReceived = MailItem.ReceivedTime.ToString
 
'The attachment counter is for coding outside of this code that allows for multiple attachments in the email by using a loop
Dim mailAtmtName As String
mailAtmtName = MailItem.Attachments.Item(atmtCounter).FileName
 
Dim mailMsgBody As String
mailMsgBody = mailitem.Body.ToString
 
Dim dateAppended As String
dateAppended = DateTime.Now.ToString
 
Dim row As DataRow = EtavDataSet.Tables(0).NewRow
row.Item("Subject") = mailSubj
row.Item("DateReceived") = mailDateReceived
row.Item("FileName") = mailAtmtName
row.Item("MessageBody") = mailMsgBody
row.Item("DateAppended") = dateAppended
EtavDataSet.Tables(0).Rows.Add(row)
 
'The next two lines replace any single quotes in the subject and body with spaces
'As I said above, single quotes mark the beginning or end of a value in SQL, so they have to be removed
mailSubj = mailSubj.Replace("'", " ")
mailMsgBody = mailMsgBody.Replace("'", " ")
 
'The notorious INSERT statement that caused me so much grief!
Dim insertIntoAtmtTable As String = "INSERT INTO yorkovichm.emptyemailwithattachments(Subject, DateReceived, FileName, MessageBody, DateAppended) 
VALUES ('" & mailSubj & "','" & mailDateReceived & "','" & mailAtmtName & "', '" & mailMsgBody & "', '" & dateAppended & " ')"
 
EtavDataAdapter.InsertCommand = New SqlCommand(insertIntoAtmtTable, sqlConn)
EtavDataAdapter.Update(EtavDataSet.Tables(0))
 
sqlConn.Close()

Several people on three different forum sites were very patient with me and helped out a lot. Thanks a million!

Code on...

 

[pri1493]

I need help with the insert statement

can u please tell me what does the " MailItem " stand for in your code??

 

[JohnH]

MailItem Object (Outlook)