We help IT Professionals succeed at work.

updating db from unboundGrid

sainavya1215
sainavya1215 asked
on
222 Views
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
Comment
Watch Question

Commented:
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
             grid[r,c]
      }
}

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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Hi sainavya1215
The previous piece of code we filled the datagrid. Now use this to update the database

IMPORTANT:
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)
  Try
            da.Update(ds, "Articles")
        Catch
            '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.

Author

Commented:
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
 
begin
         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
             begin
                      return -1000
             end
                      else
                              begin
                                       return -2000
                             end
end
                                 
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')"
cmd.ExceuteNonQuery()
next


here is an example how ExecuteNonQuery works

        Dim cmd As OleDbCommand = New OleDbCommand()

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


Author

Commented:
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?
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for that
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

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

OR

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.