Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

Update SQL Server from DataSet using VB.NET

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
pgilfeather
Asked:
pgilfeather
  • 2
  • 2
  • 2
2 Solutions
 
DabasCommented:
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
 
pgilfeatherAuthor Commented:
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
 
arif_eqbalCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Cheers
0
 
DabasCommented:
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
 
arif_eqbalCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now