We help IT Professionals succeed at work.

updating db from unboundGrid

sainavya1215 asked
Last Modified: 2011-06-28
I have an unbound Grid which displays   columns  and their values as

serial number      end serial number
1                              4
7                              9

I have a table existing in the database where i have 3 columns  namely :     ID,SerialNumber,Status
The table has already all the serial numbers mentioned above with status as NULL

I would want to read the ranges from the Grid and update the table setting the column status to "P" Or whatever  for range of serialNumbers listed in the unbound grid
Watch Question

One way to do this is to build a sql statement while looping the rows and then execute it. Something like this:

StringBuilder sSQL = new StringBuilder();

for (int r = 0; r < grid.VisibleRowCount; r ++)
    for (int c = 0; c < grid.VisibleColumnCount; c++)
             // do something with the contents of each cell

The solution also depends on if you are using tablestyles or not, and if you are using the regular datagrid or a third party datagrid. You aren't binding the grid to a complex datasource?
Top Expert 2004
This one is on us!
(Get your first solution completely free - no credit card required)
Hi sainavya1215
The previous piece of code we filled the datagrid. Now use this to update the database

There must be key in the table or the following error will be generated
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not
return any key column information.

Imports System.Data.OleDb
Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
#End Region

    Private Const DBNAME = "c:\testdb\testdb.mdb"
    Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBNAME & _
                          ";Persist Security Info=False"
    Private cn As New OleDbConnection(sConn)
    Private ds As New DataSet()
    Private da As New OleDbDataAdapter("select * from Articles", sConn)

'make all the changes in the grid and then click button. That will update DB
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cb As New OleDbCommandBuilder(da)
            da.Update(ds, "Articles")
            'trying to insert duplicate key
            MessageBox.Show(Err.Number & " " & Err.Description)
        End Try
    End Sub
End Class

I forgot to mention. If you will use this code, don't use ds.AcceptChanges in the previous snippet when you fill the datagrid. Otherwise the ds will not see changes to your dg.


serial number      end serial number
1                              4
7                              9

What this means is  a range of missing serial numbers  ie 1,2,3,4  are missing like wise
                                                                                    7,8,9 are missing

i already have a table known as ARTICLES (all the above serials are already present in the table) I just need to read the range from DataGrid and Update the table article (Again all the serialNumbers are present in the table articles ...Only Update is required to set the status to "M" pls check down
Article table consists of    Fields  :  article_ID, SerialNumber ,Status
                                                           1          1                 Null
                                                           2           2                null
                                                           3           3                null
                                                           same way till
                                                           9           9                null

So in our present situation 1-4 serialnumber status  should be updated by "M" and 7-9 serialnumbers status should be updated with "M"
 5,6 are left as null   so i have already one stored proc which loops starting from 1st serial number to the end range and updates
I want to use this stored proc and Update the database

here it is

As per my understanding we have to read the firstrange=end range from grid keep on updating using
this stored proc

my stored procedure is like this

create procedure sp_ListmissingArticles
@serial_Number1 int,
@serial_number2 int
         while @serial_number1 <= @serial_number2
                update Table Set  status = 'Missing' where serial_number = @serial_number1  
             @serial_number1 = @Serial_number1 + 1
           end  -- while
 if @@error!=0
                      return -1000
                                       return -2000
if you have the table like
serial number      end serial number
1                              4
7                              9

I would go with a loop that will execute (ExecuteNonQuery) several SQL's

for i = 1 to 4
sSQL = "insert into Articles values(" & i & ", 'MIssing Item')"

here is an example how ExecuteNonQuery works

        Dim cmd As OleDbCommand = New OleDbCommand()

        cmd.Connection = con
        cmd.CommandText = sSQL
        Catch e As Exception
            MsgBox(Err.Number & " " & Err.Description)
            Exit Sub
        End Try


Yes i understood what u said but

for i = 1 to 4 (U have to read from the datagrid thats 1 and end seerial is 4 right) how would u read from the grid was my question?
This one is on us!
(Get your first solution completely free - no credit card required)


Thanks for that

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.