sainavya1215
asked on
updating db from unboundGrid
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.OL EDB.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
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.OL
";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
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.
ASKER
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
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for that
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?