ADO/ADO.NET - Problem in SqlDataAdapter Update Method With Inner Join Query... (Windows Application)

Asked By Eswaran Radhakrishnan on 15-Jul-09 02:16 AM
Hi all,
I have filled a dataset using sqlDataAdapter. This SqlDataAdapter fetching the rows from two tables using Inner join. After I displayed the rows in DataGrid, I need to update if the user modify the any one or more than one row in DataGrid. I used "Update method of SqlDataAdapter". But, I could not update. The update should be happend when the user click the button.

Here is my code..

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

Public Class Form1

    Dim strConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=studentMDB.mdf;Integrated Security=True;User Instance=True"
    Dim con As SqlConnection
    Dim ds As New DataSet
    Dim da1 As SqlDataAdapter
    Dim sqlCmdBuilder As SqlCommandBuilder

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

        con = New SqlConnection(strConnectionString)
        con.Open()

        Dim sbSql As New StringBuilder
        sbSql.Append("SELECT tblStudentMaster.studentName, ")
        sbSql.Append("tblMarkMaster.tamilMark, tblMarkMaster.englishMark, tblMarkMaster.mathsMark, tblMarkMaster.scienceMark, tblMarkMaster.socialMark ")
        sbSql.Append("FROM tblMarkMaster INNER JOIN tblStudentMaster ON tblMarkMaster.studentId = tblStudentMaster.studentId")
        Dim strSql_3 As String
        strSql_3 = sbSql.ToString()
        cmd.CommandText = strSql_3
        da1.Fill(ds, "Combined")
sqlCmdBuilder = New SqlCommandBuilder(da1)
        DataGridView1.DataSource = ds.Tables("Combined")
        con.Close()

    End Sub

    Private Sub buttonUpdateTheChangesOnGridView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttonUpdateTheChangesOnGridView.Click
        If MessageBox.Show("Update?", "Update Confirmation", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
            Dim ds1 As DataSet = ds.GetChanges()
            If Not ds1 Is Nothing Then
                da1.Update(ds.Tables(0))
            End If
        End If
    End Sub

End Class

I am getting runtime error name called "Dynamic sql Query Generation is not supported multiple table updates".

How to do that?

Thanks
R.Eswaran.


RE - Ravenet Rasaiyah replied to Eswaran Radhakrishnan on 15-Jul-09 02:53 AM

Yes

you are trying to update , there is no support in .net in direct way. you need do like this way

http://social.msdn.microsoft.com/forums/en-US/winformsdesigner/thread/a4facb47-6e82-49dd-9ebf-b44fd9ca7c3f/

thank you
http://www.codegain.com