Update sql from datagrid

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 6185 Views - Last Post: 01 May 2012 - 06:21 AM Rate Topic: -----

#1 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Update sql from datagrid

Posted 28 April 2012 - 08:16 AM

Hello all

how to update the sql database from the gridview without having to add a primary key to the table,
like when the user finishes updating the datagrid it will go delete the sql table and replace it
or read with the adapter and fill the rows i cant find a way for this :( its not making sense to me how
any helo would be greatly appreciated
i wrote a code i am sure its all wrong i am trying to return all rows values from datagrid


        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            For Each column In DataGridView1.Columns
                For j As Integer = 0 To DataGridView1.Columns.Count - 1
                    Dim A As String = DataGridView1.Rows(i).Cells(j).Value
                    j += 1

                    Dim B As String = DataGridView1.Rows(i).Cells(j).Value
                    Dim des As String = "insert into  " & cmbtbl.Text & "(" & A & "," & "" & B & ")"

                    Dim adp As New SqlDataAdapter(des, con)
                    Dim dt As New DataTable
                    adp.Fill(dt)
                Next

            Next
        Next


Thank you

Is This A Good Question/Topic? 0
  • +

Replies To: Update sql from datagrid

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8962
  • View blog
  • Posts: 33,586
  • Joined: 12-June 08

Re: Update sql from datagrid

Posted 28 April 2012 - 09:57 AM

OleDb Basics in VB.Net Rate Topic

A Really Simple Database Create a Database using Access & VB.net Express 2008


Okay a few things - check those tutorials up there.. they go through and do a good job explaining basic interactions with the database.

Quote

how to update the sql database from the gridview without having to add a primary key to the table,

Second, your tables need to have a primary key on them. You can try and write a sql statement to update a table and narrow down it by using multiple columns but there still runs the risk of updating more than one table (that you didn't want to do) without having a unique key. That key allows you to point to one table and say - "you.. there.. just you.. I want ot update your rows or delete *JUST* you!".

Read over those tutorials and you should get a better idea.
Was This Post Helpful? 1
  • +
  • -

#3 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: Update sql from datagrid

Posted 28 April 2012 - 01:05 PM

Thank you for your reply but i made all the application i just need the last thing :( i cant find in any help
when the user writes on tyhe datagrid i update them to the sql database :( thats all missing in my application
i have to add columns delete columns database creation and viewing tables

thank you
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8962
  • View blog
  • Posts: 33,586
  • Joined: 12-June 08

Re: Update sql from datagrid

Posted 28 April 2012 - 01:09 PM

You need an event to trigger.. the use the UPDATE like you would use a DELETE or INSERT query.
Was This Post Helpful? 1
  • +
  • -

#5 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: Update sql from datagrid

Posted 28 April 2012 - 01:30 PM

yes i know the update but the table must have a primary key but can i put a hidden column and set primary in the table? which the user wont see?
Was This Post Helpful? 0
  • +
  • -

#6 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 583
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: Update sql from datagrid

Posted 28 April 2012 - 02:32 PM

There is a lot to digest on this page about Create Table syntax - but it could help!
http://dev.mysql.com...eate-table.html
Was This Post Helpful? 1
  • +
  • -

#7 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: Update sql from datagrid

Posted 29 April 2012 - 03:23 AM

how to change the variables in this query each next A has a dfifferenet value i want it to change too in the query like to build a query
 For i As Integer = 0 To DataGridView1.Rows.Count - 2
            'For Each column In DataGridView1.Rows
            Dim c As Integer = 0
            For c = 0 To DataGridView1.Columns.Count - 1
                Dim A As String = DataGridView1.Rows(i).Cells(c).Value

                MsgBox("" & A & "")


                Dim dt As New DataTable

                Dim sqlad As String = "insert into mano (fname,mname,lname) values ('" & A & "' ,'" & A & "','" & A & "')"
                Dim adp As New SqlDataAdapter(sqlad, con)
                adp.Fill(dt)

            Next

            i += 1


        Next
        'Next


Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update sql from datagrid

Posted 29 April 2012 - 05:42 AM

The first thing I see is that you don't need to loop the columns. You are only specifying 3 in your insert statement so it's not like you're working with a variable amount of unknown columns. Just specify your datagrids column index in the Cells() property and change it for each one in your INSERT statement.

If you want fname from the first column, you would use Datagridview1.Rows(i).Cells(0).Value

This same method would apply to your second column with only a change to the cells index.
Was This Post Helpful? 1
  • +
  • -

#9 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: Update sql from datagrid

Posted 29 April 2012 - 05:59 AM

View PostCharlieMay, on 29 April 2012 - 05:42 AM, said:

The first thing I see is that you don't need to loop the columns. You are only specifying 3 in your insert statement so it's not like you're working with a variable amount of unknown columns. Just specify your datagrids column index in the Cells() property and change it for each one in your INSERT statement.

If you want fname from the first column, you would use Datagridview1.Rows(i).Cells(0).Value

This same method would apply to your second column with only a change to the cells index.

yayy you are the only one who understands my needs and not throw tutorials :D well no my case is lets say i have a table of 5 columns or 6 columns ( so i dont know the number of columns ) i press view table it shows me the table and then i add data to it or edit data so now i want a method like maybe to delete all rows and adds them back in from the datatable or a method like mine to loop through all the columns and rows and adds them to the sql database ( updates the database with the new data )
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Update sql from datagrid

Posted 29 April 2012 - 06:44 AM

If you're dealing with variable column counts then a static SQL statment is never going to work. Your for i loop would be used to move to the next row.

Your for c loop would be to build your statement by concatenating the columns as a string.

This means that you are going to have to build your statement and concatenate the next column with each iteration of c.

I would take a look at understanding and working with the dataset /datatable objects where you can manage this more easily. Concatenating a string based on variable column counts is going to be a headache to manage.

You seem to have a concern about adding a primary key to your table but what is the reason for that concern? As modi123_1 pointed out. It's a sure way to ensure that you are manipulating the correct data. It also allows the command objects a way to manage INSERT, UPDATE and DELETE statements so that they can make your job a lot easier. If your concern is that it appears in the grid, then either set that column to readonly so that the user can't change it, or set its visible property to false so that they don't even know it exists.

And lastly,

Quote

now i want a method like maybe to delete all rows and adds them back in from the datatable or a method like mine to loop through all the columns and rows and adds them to the sql database ( updates the database with the new data )

This is not a good idea, there are many things that can happen between these two tasks which could cause you to lose all your data.
Was This Post Helpful? 1
  • +
  • -

#11 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: Update sql from datagrid

Posted 29 April 2012 - 06:57 AM

that was a good explanation thank you ok,for now i know it wont be visible and i can set as read only
so now my first step is to add a primay key then use the update command using command builder and see where it takes me
Was This Post Helpful? 0
  • +
  • -

#12 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: Update sql from datagrid

Posted 29 April 2012 - 08:13 AM

View PostCharlieMay, on 29 April 2012 - 06:44 AM, said:

If you're dealing with variable column counts then a static SQL statment is never going to work. Your for i loop would be used to move to the next row.

Your for c loop would be to build your statement by concatenating the columns as a string.

This means that you are going to have to build your statement and concatenate the next column with each iteration of c.

I would take a look at understanding and working with the dataset /datatable objects where you can manage this more easily. Concatenating a string based on variable column counts is going to be a headache to manage.

You seem to have a concern about adding a primary key to your table but what is the reason for that concern? As modi123_1 pointed out. It's a sure way to ensure that you are manipulating the correct data. It also allows the command objects a way to manage INSERT, UPDATE and DELETE statements so that they can make your job a lot easier. If your concern is that it appears in the grid, then either set that column to readonly so that the user can't change it, or set its visible property to false so that they don't even know it exists.

And lastly,

Quote

now i want a method like maybe to delete all rows and adds them back in from the datatable or a method like mine to loop through all the columns and rows and adds them to the sql database ( updates the database with the new data )

This is not a good idea, there are many things that can happen between these two tasks which could cause you to lose all your data.



ok i bumped into the first problem what if the user wants to edit an existing table which doesn't have a primary key and we cant add one in code coz the table was set to accept nulls from the first
so thats the first problem
secondly i did another table with a primary key what sql command to use with the adapter to update the database from the datagrid
like update command
Was This Post Helpful? 0
  • +
  • -

#13 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: Update sql from datagrid

Posted 30 April 2012 - 03:00 AM

i am trying other option but its not working for me i dont know where i went wrong if you can check and guide me please
so before there is a button view table that will show the selected table in the datagrid then you fill or edit the info on the table and press fill button this should update the sql command but it doesnt :(

        For row As Integer = 0 To dt.Rows.Count - 1
            Dim flag As Boolean = False
            Dim col As Integer = 0
            While col < dt.Columns.Count And flag = False
                If dt.Rows(row)(col) <> DataGridView1.Item(col, row).Value Then
                    flag = True
                End If
                col += 1
            End While
            If flag = True Then
                Dim qr As String = "UPDATE [" & cmbtbl.Text & "] SET"
                Dim setstr As String = ""
                Dim cndstr As String = ""
                For col = 0 To dt.Columns.Count - 1
                    If setstr <> "" Then
                        setstr += " , "
                        cndstr += " AND "
                    End If
                    setstr += "[" & dt.Columns(col).ColumnName & "] = '" & DataGridView1.Item(col, row).Value & "'"
                    cndstr += "[" & dt.Columns(col).ColumnName & "] = '" & dt.Rows(row)(col) & "'"
                Next
                qr += setstr & " WHERE " & cndstr
                Try
                    Dim cmd As New SqlCommand(qr, con)
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("")
                End Try


            End If
        Next


Was This Post Helpful? 0
  • +
  • -

#14 .nethelp  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 88
  • Joined: 31-March 12

Re: Update sql from datagrid

Posted 30 April 2012 - 12:33 PM

hi now i fixed it while updating all the existing data i cant seem to get new data its not saving if a new table is on the datagrid and has no data in can you help me with it plz i will show u the code
 For row As Integer = 0 To originalDT.Rows.Count - 1
            Dim flag As Boolean = False
            Dim col As Integer = 0
            While (col < originalDT.Columns.Count - 1) And flag = False
                If originalDT.Rows(row)(col) <> DataGridView1.Item(col, row).Value Then
                    flag = True
                End If
                col += 1
            End While
            If flag = True Then
                Dim qr As String = "UPDATE [" & cmbtbl.Text & "] SET"
                Dim setstr As String = ""
                Dim cndstr As String = ""
                For col = 0 To originalDT.Columns.Count - 1
                    If setstr <> "" Then
                        setstr += " , "
                        cndstr += " AND "
                    End If
                    setstr += "[" & originalDT.Columns(col).ColumnName & "] = '" & DataGridView1.Item(col, row).Value & "'"
                    cndstr += "[" & originalDT.Columns(col).ColumnName & "] = '" & originalDT.Rows(row)(col) & "'"
                Next
                qr += setstr & " WHERE " & cndstr
                Try
                    Dim cmd As New SqlCommand(qr, con)
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox("")
                End Try
            Else


            End If
        Next



Was This Post Helpful? 0
  • +
  • -

#15 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 583
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: Update sql from datagrid

Posted 30 April 2012 - 11:20 PM

I don't think that would work considering you supposedly only have an empty table and this will only work if the loop can work.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2