通过datagridview更新内部联接表！

本文介绍了通过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"