Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update SQL Server from DataSet using VB.NET

Posted on 2004-08-16
8
Medium Priority
?
548 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 your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 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
        sqlcm.ExecuteNonQuery()
    Next

Dabas
0
 
LVL 19

Assisted Solution

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

Featured Post

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Introduction to Processes

715 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