Solved

Update SQL Server from DataSet using VB.NET

Posted on 2004-08-16
8
542 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Introduction to Processes

632 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