Saving Back

Hi

I need help in Saving multiple records on single click of button from datagridview to dataset and then to database. I am using VB.Net, SQL Server 2000 -- i am not using in line quries only store procedures.

Sample Code which is not working properly is

OptionExplicitOn

Imports System.Data

Imports System.Data.SqlClient

PublicClass Form1

Inherits System.Windows.Forms.Form

PublicConst ConnectionStringAsString = _

"Integrated Security=sspi;Initial Catalog=pubs;Data Source=Shahji;"

ProtectedConst GetAllAuthorsSqlStringAsString ="Select au_id, au_lname, au_fname, phone, address, city,state, zip, contract from authors order by au_lname, au_fname"

PrivateSub Form1_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

RefreshData()

EndSub

PrivateSub mnuRefresh_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles mnuRefresh.Click

RefreshData()

EndSub

PublicSub RefreshData()

Dim connectionAsNew SqlConnection(ConnectionString)

connection.Open()

Dim adapterAsNew SqlDataAdapter(GetAllAuthorsSqlString, connection)

Dim datasetAsNew DataSet

adapter.Fill(dataset)

adapter.Dispose()

connection.Close()

Dim tableAs DataTable = dataset.Tables(0)

AddHandler table.ColumnChanged,New DataColumnChangeEventHandler(AddressOf ColumnChanged)

datagridAuthors.DataSource = table

EndSub

ProtectedSub ColumnChanged(ByVal senderAsObject,ByVal eAs DataColumnChangeEventArgs)

mnuSaveChanges.Enabled =True

EndSub

PrivateSub mnuSaveChanges_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles mnuSaveChanges.Click

SaveChanges()

EndSub

PublicSub SaveChanges()

Dim TableAs DataTable =CType(datagridAuthors.DataSource, DataTable)

Dim ChangedRowsAsNew ArrayList

Dim rowAs DataRow

ForEach rowIn Table.Rows

If row.RowState <> DataRowState.UnchangedThen

EndIf

Dim connectionAsNew SqlConnection(ConnectionString)

connection.Open()

Dim adapterAsNew SqlDataAdapter(GetAllAuthorsSqlString, connection)

Dim builderAsNew SqlCommandBuilder(adapter)

Dim rows()As DataRow =CType(ChangedRows.ToArray(GetType(DataRow)), DataRow())

adapter.Update(rows)

adapter.Dispose()

connection.Close()

mnuSaveChanges.Enabled =False

Next

If ChangedRows.Count = 0Then

Return

EndIf

EndSub

EndClass

Please Help.

Regards

Mirza Ashraf

[8987 byte] By [MirzaAshraf] at [2007-12-28]