UPDATE query with datatable

  • Thread starter Thread starter VB Programmer
  • Start date Start date
Hi VB,

I assume that you are one of the few who understand your message.
Is it possible to update a table in a db with a local datatable variable?
Click to expand...
Or do you mean to use an SQLstring with Insert and a
xxxcommand.ExecuteNonQuery?

I hope this helps?

Cor
 
ADO.NET wouldn't be of much use if it couldn't ;-). Like Cor mentions, you
can use the command object's Executexxx series of methods if you are looking
to update just one value based on an individual field in a datatable - or
you can use the DataAdapter's Update method provided you have a valid
insert/Update/Delete statement.

To this end though, if you are just using the datatable to obtain a value
and once you have it, use it to update the db - the process is virtually
identical to using a local variable or hard coded value.

Let me know a little more about the update scenario and I can probably be of
a little more help.

Cheers,

Bill

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
 
Thanks. Here's the situation:
I have a list box that whose contents are based on a database table. (So,
when the user is working with the list box it is "disconnected" from the
db.) Once items have been added and deleted to this db table the user can
hit 'Save'. The 'Save' button basically makes sure that each item in the
list box get written to the database. Some items may have been deleted,
some added. I was just wondering how I should update the db with the data
found in this list box.

Thanks.
 
Hi VB,

I think that in this sample is enough to get an idea how to do it.
When you use SQL change OleDB.Oledb for SQLclient.SQL

I hope this helps?

Cor

\\\
Public Class Form4
Inherits System.Windows.Forms.Form
Dim ds As New DataSet
Dim dv As New DataView
Private cma As CurrencyManager
Private da As OleDb.OleDbDataAdapter
Private conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb;User Id=admin;Password=;")
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim fi As New IO.FileInfo("c:\db1.mdb")
FillDatasetAndBindings()
End Sub
Private Sub FillDatasetAndBindings()
ds.Clear()
Dim sqlString As String = "Select * from countries"
da = New OleDb.OleDbDataAdapter(sqlString, conn)
da.Fill(ds)
Dim dt As DataTable = ds.Tables(0)
dv = New DataView(dt)
dv.Sort = "Id"
cma = DirectCast(BindingContext(dv), CurrencyManager)
Dim cmb As New OleDb.OleDbCommandBuilder(da)
TextBox1.DataBindings.Clear()
TextBox2.DataBindings.Clear()
Dim mybinding As Binding = New Binding("text", dv, "Id")
TextBox1.DataBindings.Add(mybinding)
TextBox2.DataBindings.Add("text", dv, "Name")
ListBox1.DataSource = dv
ListBox1.DisplayMember = "Id"
ComboBox1.DataSource = dv
ComboBox1.DisplayMember = "Id"
If ds.Tables(0).Rows.Count = 0 Then
cma.AddNew()
TextBox1.Focus()
End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
cma.AddNew()
TextBox1.Focus()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
dv(cma.Position).Delete()
End Sub
Private Sub Button3_Click_1(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click
cma.EndCurrentEdit()
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button4.Click
cma.EndCurrentEdit()
If ds.HasChanges Then
Try
da.Update(ds)
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
End If
End Sub
///
 
Back
Top