Solved

Update SQL Server from DataSet using VB.NET

Posted on 2004-08-16
8
536 Views
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.

Cheers

PG
0
Comment
Question by:pgilfeather
  • 2
  • 2
  • 2
8 Comments
 
LVL 27

Expert Comment

by:Dabas
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

Dabas
0
 

Author Comment

by:pgilfeather
ID: 11808613
Dabas

This is the dataTable

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

objTable.Columns.Add("QuestionID",System.Type.GetType("System.String"))
objTable.Columns.Add("Question",System.Type.GetType("System.String"))
objTable.Columns.Add("AnswerDescription",System.Type.GetType("System.String"))
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
objTable.Rows.Add(objDataRow)
dgDataCache.DataSource = objTable
dgDataCache.DataBind
Session("CashedDataSet") = objTable
lblDuplicate.Text = "Got that!" & " " & " " & "Now Click" & " " & " " & "'Next Question'" & " " & " " & "to proceed"
ValTitle.Enabled = False
End Sub

Regards

PG
0
 
LVL 19

Expert Comment

by:arif_eqbal
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

        Cn.Open()
        '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)
        DA.Update(DT)
        Cn.Close()

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
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

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

Cheers
0
 
LVL 27

Accepted Solution

by:
Dabas earned 250 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
        sqlcm.ExecuteNonQuery()
    Next

Dabas
0
 
LVL 19

Assisted Solution

by:arif_eqbal
arif_eqbal earned 250 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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

828 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