Updating SQL from a datagridview.

Question

0.00/5 (No votes)

See more:

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.

VB

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

Posted 18-Feb-14 13:20pm

moordoom

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jacob Himes · Answer 1 · 2014-02-19T03:42:00

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...

VB

'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

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.

Jacob Himes · Answer 2 · 2014-02-18T13:46:00

Solution 1

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.

VB

daSWC2.Update(DataGridView1.DataSource)

Posted 18-Feb-14 13:46pm

Jacob Himes

Comments

moordoom 19-Feb-14 8:52am

Did not work, see my new code in update.

moordoom · Answer 3 · 2014-02-19T02:25:00

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

VB

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

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

I am thinking I need an Array of some sort.

Updating SQL from a datagridview.

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0