Update SQL Server from DataSet using VB.NET

Posted on 2004-08-16
Medium Priority
Last Modified: 2007-02-13
I have a dataTable that is filled with data programmatically. All I want to do is put the table into a dataset and update the datasource.

I am using VB.NET and SQLServer.

Just want to thank all the experts on this site who have helped me in so many areas! Its much appreciated.


Question by:pgilfeather
  • 2
  • 2
  • 2
LVL 27

Expert Comment

ID: 11808586
Hi pgilfeather:
YOu do not have to use a dataset to update the datasource.
Depending on how you filled the DataTable, this can be done directly as well.

Maybe if you post a snippet of code as to how your dataTable gets created and filled, it will be easier to answer your question


Author Comment

ID: 11808613

This is the dataTable

Function maketable()
objTable= New System.Data.DataTable("AnsweredQuestions")
objTable.Columns("ID").AutoIncrement = True
objTable.Columns("ID").AutoIncrementSeed = 1
objTable.Columns("ID").AutoIncrementStep = 1
objTable.Columns("ID").ReadOnly = True

Session("CashedDataSet") = objTable
End Function

And this is how it gets filled

Sub SubmitAnswer(ByVal sender As System.Object, ByVal e As System.EventArgs)
btnSubmit.Visible = False
If btnFillRad.Visible = True Then
ValTitle.Enabled = False
End If
btnFillRad.Visible = True
objTable = Session("CashedDataSet")
'lblDuplicate.Text = ""
objDataRow = objTable.NewRow
objDataRow("QuestionID") = txtInput.Text
objDataRow("Question") = lblQuestion.Text
objDataRow("AnswerDescription") = RadFSAQuestions.SelectedItem.Text
dgDataCache.DataSource = objTable
Session("CashedDataSet") = objTable
lblDuplicate.Text = "Got that!" & " " & " " & "Now Click" & " " & " " & "'Next Question'" & " " & " " & "to proceed"
ValTitle.Enabled = False
End Sub


LVL 19

Expert Comment

ID: 11808720
Well if you just want to update the data in the datatable you need not put it into a dataset, dataAdapters are overloaded to take Datatable as argument.

        'Create a Connection Object with connection string for your database
        Dim Cn As SqlConnection = New SqlConnection("Your Connection String")
        'Use Command Object so that your Insert/Update Queries are automatically generated
        Dim CD As SqlCommandBuilder

        'Just give some select query so that the DataAdapter knows which table it is dealing with, Also give an Invalid where clause which does not select any record as our aim is to update and not select.
        Dim DA As SqlDataAdapter = New SqlDataAdapter("Select * from Your_Table Where Field1='Invalid text'", Cn)
        CD = New SqlCommandBuilder(DA)

This Code assumes your datatable was built from a single table I mean there was no Join Query. In case its a complex query you will not be able to use CommandBuilder and you'll have to write the Insert/Update/Delete Queries yourself and assign to DataAdapters InsertCommand/UpdateCommand/DeleteCommand properties respectively.

Hope it helps
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.


Author Comment

ID: 11808744
Sorry, I forgot to say, I want to use a custom command object, command builder too slow!!

LVL 27

Accepted Solution

Dabas earned 1000 total points
ID: 11808758
There are several ways to update your dataSource.

For example
    For each objDataRow as DataRow in objTable.Rows
        Dim sqlcm As New SqlClient.SqlCommand
        sqlcm.CommandText = String.Format("Insert into YourTable (QuestionID, Question, AnswerDescription) VALUES ({0}, '{1}', '{2}')", objDataRow.Item("QuestionID"), objDataRow.Item("Question"), objDataRow.Item("AnswerDescription"))
        sqlcm.Connection = YourConnextion

LVL 19

Assisted Solution

arif_eqbal earned 1000 total points
ID: 11808813
Remove the Command Builder lines from the above code and use Command Object as shown..

Dim cmd As SqlCommand
cmd = New SqlCommand("INSERT INTO AnsweredQuestions (ID, Question, ...Further Fields ) VALUES (@ID, @Question, @..Further)", cn)

cmd.Parameters.Add("@ID", SqlDbType.Int, , "ID")

cmd.Parameters.Add("@Question", SqlDbType.NVarChar, 200, "Question")

... Further Column's Parameters ....

da.InsertCommand = cmd

Similarly Create a new cmd for Update query and delete query and assign to DA's UpdateCommand/DeleteCommand properties respectively.

then call DA.Update

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
An ASP.NET Web Form User Control is not newly introduced in ASP.NET. In fact, it was an old technology yet still playing a role to generate web content, especially when we want to use it to have a better and easy way to control part of the web conteā€¦
Six Sigma Control Plans
Introduction to Processes

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question