Question dataset update problem using data adapter

[divjoy]

Hi, Im using vb.net 2010 express, ADO and sql server 2008 express.

I have a simple table that I create a dataset from, consisting of 2 small tables and then when I edit TblClient data and try to update that table back to the sql server database I get an error.
The dataset is called 'ds' tables are TblClient (PK ID, FName, Surname) with only 1 record in it and TblOtherServices (ID, ClientID, OtherService, SDate) which is in a datagridview

see ,y code below Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click '*****************
'BEGIN SEND CHANGES TO SQL SERVER


Dim objCommandBuilder As New SqlCommandBuilder(da)


'Assign new Values to row items
ds.Tables("TblClient").Rows(0).Item("ID") = Convert.ToInt32(ID.Text)
ds.Tables("TblClient").Rows(0).Item("FName") = FName.Text
ds.Tables("TblClient").Rows(0).Item("Surname") = Surname.Text


da.Update(ds.Tables("TblClients"))
MsgBox("SQL Server updated successfully" & Chr(13) & "Check Server explorer to see changes")


' END SEND CHANGES TO SQL SERVER


End Sub

The error stated column Client ID not found in TblClient see attached.
I have tried deleting the 2nd table from the dataset and then updating the dataset but I get the same result!

Any suggestions....

 

[jmcilhinney]

Are you populating that DataTable using the same data adapter in the first place? Can you show us where you create the adapter?

 

[divjoy]

Hi, Here is my form code...

Imports System.Data
Imports System.Data.SqlClient

Public Class Form6
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim ds As New DataSet
    Dim conn As New SqlClient.SqlConnection(connStr)
    Dim bs1, bs2 As New BindingSource
    


    Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        'Load Supporting Tables County, Office, Service, Gender etc...
        Try
          Me.WindowState = FormWindowState.Maximized


            'Populate Dataset with supporting tables first !
            cmd = New SqlCommand("SELECT * FROM TblCounty", conn)
            da = New SqlDataAdapter(cmd)
            conn.Open()
            da.Fill(ds, "TblCounty")
            Me.cbCounty.DataSource = ds.Tables("TblCounty")
            Me.cbCounty.DisplayMember = "County"
            Me.cbCounty.ValueMember = "ID"
            Me.cbCounty.SelectedIndex = 0
            conn.Close()
            cmd = New SqlCommand("SELECT * FROM TblArea", conn)
            da = New SqlDataAdapter(cmd)
            da.Fill(ds, "TblArea")
            conn.Open()
            Me.cbArea.DataSource = ds.Tables("TblArea")
            Me.cbArea.DisplayMember = "Area"
            Me.cbArea.ValueMember = "ID"
            Me.cbArea.SelectedIndex = 0
            conn.Close()
            cmd = New SqlCommand("SELECT * FROM TblGender", conn)
            da = New SqlDataAdapter(cmd)
            da.Fill(ds, "TblGender")
            conn.Open()
            cbCounty.Enabled = True
            Me.cbGender.DataSource = ds.Tables("TblGender")
            Me.cbGender.DisplayMember = "Gender"
            Me.cbGender.ValueMember = "ID"
            Me.cbGender.SelectedIndex = 0
            conn.Close()
            Dim x As Integer
            'Get Client Data
            '   MsgBox(ClientID)
            cmd = New SqlCommand("SELECT * FROM TblClients WHERE ID=" & ClientID.ToString, conn)
            da = New SqlDataAdapter(cmd)
            da.Fill(ds, "TblClient")
            conn.Open()
            conn.Close()
           x = ds.Tables("TblClient").Rows.Count
           If x > 0 Then 'has records Then
                ID.Text = ds.Tables("TblClient").Rows(0).Item("ID")
                FName.Text = ds.Tables("TblClient").Rows(0).Item(3)
                Surname.Text = ds.Tables("TblClient").Rows(0).Item(4)
                
                
                Me.cbArea.SelectedValue = ds.Tables("TblClient").Rows(0).Item("AreaID")
                
            End If




            'Get Clients Other Services.
            cmd = New SqlCommand("SELECT * FROM TblOService", conn)
            da = New SqlDataAdapter(cmd)
            da.Fill(ds, "TblOService")
            conn.Open()
            conn.Close()
            Me.cbOService.DataSource = ds.Tables("TblOService")
            Me.cbOService.DisplayMember = "OService"
            Me.cbOService.ValueMember = "ID"
            Dim bs1 As New BindingSource
            cmd = New SqlCommand("SELECT * FROM TblClientOtherService WHERE ClientID=" & ClientID.ToString, conn)
            da = New SqlDataAdapter(cmd)
            da.Fill(ds, "TblClientOtherService")
            conn.Open()
            conn.Close()
            'Check Data Collected
            '   x = ds.Tables("TblClientOtherService").Rows.Count
            '   MsgBox("No of Other Services " & x)
            bs1.DataSource = ds.Tables("TblClientOtherService")
            Me.BindingNavigator1.BindingSource = bs1
            Me.DataGridView1.DataSource = Me.BindingNavigator1.BindingSource
            Me.DataGridView1.Columns.Remove("ID")
            Me.DataGridView1.Columns.Remove("ClientID")
          
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error!")
        End Try
    End Sub

 

[divjoy]

I think I have it working now...The codes still a bit ropey, but will post it when I have a chance to clean it up.
Regards