通过datagridview更新内部联接表！ [英] Updating inner joined table through datagridview!

问题描述

大家好，



我一直在忙于搜索互联网以解决我当前的问题，但是，无论我做什么，我似乎无法想出错误！





我有两个表内连接在一个select语句中并绑定到datagridview，因为它们已加入我可以不使用SqlCommandBuilder，这就是我尝试手动更新的原因，但问题是表没有更新，也没有生成错误！



请注意我的代码生成更新的MsgBox！



提前谢谢你，我很感激。



我的代码是如下：

Hello everyone,

I've been really busy searching the internet to solve my current issue, however, no matter what I do I can't seem to figure the error!


I have two tables inner joined in a select statement and bound to datagridview, and since they are joined I can't use SqlCommandBuilder, that's why I tried to update manually but the problem is the table is not updated and it does not generate an error!

Note that My code generate the "Updated" MsgBox!

Thank you in advance, I appreciate it.

my code is as follows:

Imports System.Data
Imports System.Data.SqlClient
Public Class testing
    Dim connectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\New Age\documents\visual studio 2010\Projects\presents2\presents2\AMS.mdf;Integrated Security=True;User Instance=True"
    Dim sql As String = "SELECT [Full Name] as 'Column1',Date as 'Column2', Absentbool as 'Column3',Latebool As 'Column4', Present.ID as 'Column5' From Students INNER JOIN Present ON Students.id = Present.id WHERE Absentbool = 1 ORDER BY [Full Name]"
    Dim sqlupdate As String = "UPDATE Present SET Present.[Absentbool] = @abool WHERE Present.ID = @id"
    Dim connection As New SqlConnection(connectionString)
    Dim dataadapter As New SqlDataAdapter(sql, connection)
    Dim cmdBuilder As New SqlCommandBuilder
    Dim ds As New DataSet()

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            connection.Open()
            dataadapter.Fill(ds, "Present")
            connection.Close()
            'DataGridView1.DataSource =
            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "Present"
        Catch ex As Exception
            MsgBox(Err.Description)
        Finally
            connection.Close()
        End Try
    End Sub

    Private Sub testing_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        DataGridView1.AutoGenerateColumns = False
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        DataGridView1.ReadOnly = False
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim cmd As New SqlCommand
        cmd.Connection = connection
        Try
            connection.Open()
            cmdBuilder = New SqlCommandBuilder(dataadapter)
            dataadapter.UpdateCommand = New SqlCommand(sqlupdate, connection)
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@abool", SqlDbType.Bit))
            dataadapter.UpdateCommand.Parameters("@abool").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@abool").SourceColumn = "Column3"
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@fname", SqlDbType.NVarChar, 50))
            dataadapter.UpdateCommand.Parameters("@fname").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@fname").SourceColumn = "Column1"
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@date", SqlDbType.Date, 8))
            dataadapter.UpdateCommand.Parameters("@date").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@date").SourceColumn = "Column2"
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@lbool", SqlDbType.Time, 8))
            dataadapter.UpdateCommand.Parameters("@lbool").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@lbool").SourceColumn = "Column4"
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@id", SqlDbType.Int))
            dataadapter.UpdateCommand.Parameters("@id").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@id").SourceColumn = "Column5"
            dataadapter.Fill(ds, "Preset")
            'dataadapter.Update(ds, "Present")
            MsgBox("Updated")
        Catch ex As Exception
            MsgBox(Err.Description)
        Finally

            connection.Close()
        End Try
    End Sub
End Class

推荐答案

使用Fill方法使用适配器填充数据集，但您已注释掉Update方法调用。所以你没有执行更新。



还要确保更新语句（变量 sqlupdate 内容）是正确。

You use the Fill method to fill the dataset using the adapter but you have commented out the Update method call. So you're not executing the update.

Also make sure that update statement (variable sqlupdate content) is correct.

Imports System.Data
Imports System.Data.SqlClient
Public Class testing
    Dim connectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\New Age\documents\visual studio 2010\Projects\presents2\presents2\AMS.mdf;Integrated Security=True;User Instance=True"
    Dim sql As String = "SELECT [Full Name] as 'Column1',Date as 'Column2', Absentbool as 'Column3',Latebool As 'Column4', Present.ID as 'Column5' From Students INNER JOIN Present ON Students.id = Present.id WHERE Absentbool = 1 ORDER BY [Full Name]"
    Dim sqlupdate As String = "UPDATE Present SET [Full Name] = @fname, [Date] = @date,[Absentbool] = @abool, [Latebool] = @lbool WHERE Present.ID = @id"

    Dim connection As New SqlConnection(connectionString)
    Dim dataadapter As New SqlDataAdapter(sql, connection)
    Dim cmdBuilder As New SqlCommandBuilder
    Dim ds As New DataSet()

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            connection.Open()
            dataadapter.Fill(ds, "Present")
            connection.Close()
            'DataGridView1.DataSource =
            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "Present"
        Catch ex As Exception
            MsgBox(Err.Description)
        Finally
            connection.Close()
        End Try
    End Sub

    Private Sub testing_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        DataGridView1.AutoGenerateColumns = False
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        DataGridView1.ReadOnly = False
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim cmd As New SqlCommand
        cmd.Connection = connection
        Try
            connection.Open()
            cmdBuilder = New SqlCommandBuilder(dataadapter)
            dataadapter.UpdateCommand = New SqlCommand(sqlupdate, connection)
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@abool", SqlDbType.Bit, 2))
            dataadapter.UpdateCommand.Parameters("@abool").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@abool").SourceColumn = "Column3"
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@fname", SqlDbType.NVarChar, 50))
            dataadapter.UpdateCommand.Parameters("@fname").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@fname").SourceColumn = "Column1"
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@date", SqlDbType.Date, 8))
            dataadapter.UpdateCommand.Parameters("@date").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@date").SourceColumn = "Column2"
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@lbool", SqlDbType.Bit, 2))
            dataadapter.UpdateCommand.Parameters("@lbool").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@lbool").SourceColumn = "Column4"
            dataadapter.UpdateCommand.Parameters.Add(New SqlParameter("@id", SqlDbType.Int))
            dataadapter.UpdateCommand.Parameters("@id").SourceVersion = DataRowVersion.Current
            dataadapter.UpdateCommand.Parameters("@id").SourceColumn = "Column5"
            dataadapter.UpdateCommand.ExecuteNonQuery()
            'dataadapter.Update(ds, "Present")
            MsgBox("updated")
        Catch ex As Exception
            MsgBox(Err.Description)
        Finally

            connection.Close()
        End Try
    End Sub
End Class

谢谢你的回复，



更新'update statement'后，用ExecuteNonQuarry替换fill方法我得到了错误The Parameterized Quarry ....未提供

Thank you for the reply,

After updating the 'update statement', and replacing the fill method with the ExecuteNonQuarry I got the error " The Parameterized Quarry .... were not supplied"

这篇关于通过datagridview更新内部联接表！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！