从datagridview更新SQL。 [英] Updating SQL from a datagridview.

问题描述

我有以下代码从表中提取信息。

代码中的所有内容都有效，但我的保存（更新）子。

我试过移动我的连接私有和公共声明，但我无法正确使用语法。

我想要做的就是允许用户对datagridview进行更改并将其更新到表中。

问题在于我的Button2_Click子。

I have the following code to pull information from a Table.
Everything in the code works, but my save(update) sub.
I have tried moving my connections to Private and Public declarations, but I just cannot get the syntax right.
All I want to do is allow a user to make changes to the datagridview and update it to the table.
The problem is in my Button2_Click sub.

Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Windows.Forms.DataGridView
Imports System.Windows.Forms.CheckBox
Imports System.Windows.Forms.DateTimePicker

Public Class Form6


    Private sqlCon1 As New SqlConnection("Data Source=SERVER1\DEV01;database=Production;uid=sa;pwdpasswordhere")
    Private sql As String

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

        Dim CheckBox1 As New CheckBox
        Dim CheckBox2 As New CheckBox
        Dim CheckBox3 As New CheckBox
        Dim DateTimePicker1 As New DateTimePicker

    End Sub


    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click


        Dim sql1 As String
        Dim sql2 As String
        Dim sql3 As String
        Dim datepicked As Date
        datepicked = DateTimePicker1.Text
        'Checkbox Check Loop
        If CheckBox1.CheckState = False And CheckBox2.CheckState = False And CheckBox3.CheckState = False Then
            MessageBox.Show("Please select a Shift")
            Exit Sub
        End If
        If CheckBox1.Checked = True And CheckBox2.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If
        If CheckBox2.Checked = True And CheckBox3.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If
        If CheckBox1.Checked = True And CheckBox3.Checked = True Then
            MessageBox.Show("Please select a One Shift Only")
            Exit Sub
        End If

        'Selection of Shift 
        If CheckBox1.Checked = True Then
            sql1 = "SELECT * FROM PickProd WHERE SHIFT = 1"
        ElseIf CheckBox2.Checked = True Then
            sql1 = "SELECT * FROM PickProd WHERE SHIFT = 2"
        ElseIf CheckBox3.Checked = True Then
            sql1 = "SELECT * FROM PickProd WHERE SHIFT = 3"
        End If

        'Date selection string
        sql2 = " and DATE = "
        sql3 = "'"
        Sql = sql1 + sql2 + sql3 + datepicked + sql3

        'proves/shows concat
        MessageBox.Show(Sql)

        'SQL Data connection

        Dim daSWC1 As New SqlDataAdapter(Sql, sqlCon1)
        Dim SQLcmdBuilder1 As New SqlCommandBuilder(daSWC1)
        Dim ds1 As New DataSet

        daSWC1.Fill(ds1, "MACHINENUMBER")
        DataGridView1.DataSource = ds1.Tables(0)
        DataGridView1.AllowUserToAddRows = False


    End Sub


    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

        Dim daSWC2 As New SqlDataAdapter
        Dim ds2 As New DataSet

        'PROBLEM IS HERE
        DataGridView1.DataSource = ds2.Tables(0)
        daSWC2.Update(ds2.Tables(0))
        
        'clears data from memory
        MessageBox.Show("Picks have been Updated")
        DataGridView1.DataSource = Nothing
        DataGridView1.Rows.Clear()
        DataGridView1.Columns.Clear()

    End Sub

End Class

推荐答案

您收到该错误是因为您要为数据网格分配一个空数据集。与你想要完成的事情相反。此外，我进一步阅读了你想要做的事情。它不像只是编写代码来更新数据集那么容易，它更容易参与。



查看这篇文章，它是在C＃但你会得到想法

http://csharp.net-informations.com/dataadapter/ updatecommand-sqlserver.htm [ ^ ]



您的代码不会自动知道您的数据集引用的数据库中的哪个表，并自动知道如何更新。基本上为了使这项工作，dataprovider上的update方法会查找您声明的命令，并且您仍然最终编写update sql。我建议可能创建类来模拟数据库中的内容，在接收数据时创建类，然后将列表对象中的这些类分配给数据网格作为源，然后您可以为该类编写更新方法。 user通过datagrid更改其属性。换句话说，假设你有一个装满汽车的数据库...

You are getting that error because you are assigning an empty dataset to your datagrid. The opposite of what you are trying to accomplish. Furthermore, I read further on what you are trying to do. Its not as easy as just writing code saying to update the dataset, it is more involved.

Check out this article, it is in C# though but you'll get the idea
http://csharp.net-informations.com/dataadapter/updatecommand-sqlserver.htm[^]

Your code isn't going to automatically know which table in the database your dataset refers to and automatically know how to update. Basically to make this work, that update method on the dataprovider looks for a command you declare and you still end up writing update sql. I would recommend maybe creating classes to model what you have in your database, create the classes as you receive data and then assign those classes in a list object to your datagrid as its source, then you can write an update method for that class after the user changes its properties via the datagrid. In other words, lets say you have a database full of cars...

'Your car class
Public Class Car
Public Property RowID as string
Public Property Make as string
Public Property Color as string

Public Sub Update()
'sql to update the car table with the new values
end sub
End Class

你会写代码因此，对于数据集中的每一行，您制作汽车并将其放入列表中，然后将数据网格的数据源设置为该列表。用户进行更改后，您将浏览列表并为每辆车调用更新方法。这可能需要考虑很多，但是根据您目前设置代码的方式，手动更新数据库并不容易。



基本上不幸的是，你做的事情并不容易。您将需要编写更新sql。通常我使用数据实体框架来做这样的事情，但上面的方法是我做的一种方式。

You would write code so for every row in the dataset you make a car and put it in a list, then you set the datasource of your datagrid to that list. After the user makes changes, you go through your list and call the update method for each car. This might be a lot to take in, but with the way you currently have your code set up its not going to be easy to update the database by hand.

Basically what your doing isn't easy unfortunately. You are going to need to write update sql. Typically I use the data entity framework for things like this, but the above way is a way I'd do it.

你在做什么.... DataGridView1.DataSource = ds2 .Tables（0）。您正在将datagridview分配给空数据集。我对此有点模糊。试试这个，让我知道它是否有效。

Where you are doing this....DataGridView1.DataSource = ds2.Tables(0). You are assigning your datagridview to an empty dataset. I'm a little fuzzy on this. Try this instead and let me know if it works.

daSWC2.Update(DataGridView1.DataSource)

好的，这给了我一个需要更新命令错误，我修复了添加SQL命令生成器，但是...

Ok, that gave me a "Requires UpdateCommand" error, which I fixed adding a SQL Command Builder, but...

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

    Dim daSWC2 As New SqlDataAdapter
    Dim SQLcmdBuilder2 As New SqlCommandBuilder(daSWC2)
    Dim ds2 As New DataSet
    DataGridView1.DataSource = ds2.Tables(0)
    daSWC2.Update(DataGridView1.DataSource)
    MessageBox.Show("Picks have been Updated")
    DataGridView1.DataSource = Nothing
    DataGridView1.Rows.Clear()
    DataGridView1.Columns.Clear()

End Sub

现在我得到一个IndexOutOfRangeException在更新行上找不到表（0）。



我想我需要某种类型的数组。

Now I get an IndexOutOfRangeException "Cannot find table (0)'on the Update Line.

I am thinking I need an Array of some sort.

这篇关于从datagridview更新SQL。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！