add new records to database

[Palmtree]

I have a datagrid filled with dataset. Now I add several new records from the grid and want to save them by clicking a "Save" button. How can I know which record is new, and how to save several new records at same time, in an other words, how to nevigate the grid? Thanks

[CyberHawke]

Check out this thread for the answer to your question,
http://www.vbforums.com/showthread.p...hreadid=291167

[Winanjaya]

Private Sub Save()
SQLConnection1.Open()

Dim UpdatedRows As System.Data.DataSet
Dim InsertedRows As System.Data.DataSet
Dim DeletedRows As System.Data.DataSet

'these three are Data Tables that hold any changes that have been made to the dataset
'since the last update.

UpdatedRows = Me.Dataset1.GetChanges(DataRowState.Modified)
InsertedRows = Me.Dataset1.GetChanges(DataRowState.Added)
DeletedRows = Me.Dataset1.GetChanges(DataRowState.Deleted)

Try
'For each of these, we have to make sure that the Data Tables contain
'any records, otherwise, we will get an error.

If Not UpdatedRows Is Nothing Then
Me.SQLDataAdapter1.Update(UpdatedRows)
If Not InsertedRows Is Nothing Then
Me.SQLDataAdapter1.Update(InsertedRows)
If Not DeletedRows Is Nothing Then
Me.SQLDataAdapter1.Update(DeletedRows)
Me.Dataset1.AcceptChanges()
Catch eUpdate As System.Exception
Message("Err !!!, " & eUpdate.Message)
End Try
SQLConnection1.Close()

End Sub

Good luck

Winanjaya

[CyberHawke]

Dim UpdatedRows As System.Data.DataSet
Dim InsertedRows As System.Data.DataSet
Dim DeletedRows As System.Data.DataSet

'these three are Data Tables that hold any changes that have been made to the dataset
'since the last update.

I'm sorry, but DataSets and DataTables are not anywhere near the same thing. A "DataSet" is a container for one or more DataTables, DataRelations and other assorted objects.

As for even considering the concept of mutiple DataTables for managing your inserts, updates and deletes, where on earth would you get the idea for this

If you have a table that contains data from a datasource, the correct methodology for managing data in that table and returning the results to the datasource are by associating the correct command objects with your DataSet.

Dim cnLocal As SqlConnection = New SqlConnection("")
        Dim dtLocal As DataTable
        Dim daLocal As SqlDataAdapter = New SqlDataAdapter
        Dim cmdSelect As SqlCommand = New SqlCommand("SELECT CategoryID, CategoryName, Description, Picture FROM Categories", cnLocal)
        Dim cmdInsert As SqlCommand = New SqlCommand("INSERT INTO Categories(CategoryName, Description, Picture) VALUES (@CategoryName, @Description, @Picture); SELECT CategoryID, CategoryName, Description, Picture FROM Categories WHERE (CategoryID = @@IDENTITY", cnLocal)
        cmdInsert.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryName", System.Data.SqlDbType.NVarChar, 15, "CategoryName"))
        cmdInsert.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.NVarChar, 1073741823, "Description"))
        cmdInsert.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Picture", System.Data.SqlDbType.VarBinary, 2147483647, "Picture"))
        Dim cmdUpdate As SqlCommand = New SqlCommand("UPDATE Categories SET CategoryName = @CategoryName, Description = @Description, Picture = @Picture WHERE (CategoryID = @Original_CategoryID) AND (CategoryName = @Original_CategoryName); SELECT CategoryID, CategoryName, Description, Picture FROM Categories WHERE (CategoryID = @CategoryID)", cnLocal)
        cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryName", System.Data.SqlDbType.NVarChar, 15, "CategoryName"))
        cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Description", System.Data.SqlDbType.NVarChar, 1073741823, "Description"))
        cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Picture", System.Data.SqlDbType.VarBinary, 2147483647, "Picture"))
        cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CategoryID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, 0, 0, "CategoryID", System.Data.DataRowVersion.Original, Nothing))
        cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CategoryName", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, False, 0, 0, "CategoryName", System.Data.DataRowVersion.Original, Nothing))
        cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryID", System.Data.SqlDbType.Int, 4, "CategoryID"))
        Dim cmdDelete As SqlCommand = New SqlCommand("DELETE FROM Categories WHERE (CategoryID = @Original_CategoryID) AND (CategoryNam = @Original_CategoryName)", cnLocal)
        cmdDelete.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CategoryID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, 0, 0, "CategoryID", System.Data.DataRowVersion.Original, Nothing))
        cmdDelete.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CategoryName", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, False, 0, 0, "CategoryName", System.Data.DataRowVersion.Original, Nothing))
        daLocal.SelectCommand = cmdSelect
        daLocal.InsertCommand = cmdInsert
        daLocal.UpdateCommand = cmdUpdate
        daLocal.DeleteCommand = cmdDelete
        daLocal.Fill(dtLocal)

After you have made changes such as Inserts Updates or Deletes to your datatable, call the daLocal.Update method to return your changes to the database.

This is the methodolgy which Microsoft teaches you to manage data with ADO.NET

My suggestion on the code in the previous post is to learn ADO before you post something that could be much more harmful than helpful...

Regards

[Palmtree]

thank you, CyberHawke.

Actually i shoud thank that previous poster too, sometimes correcting mistake can help us understand things better.

[RichardAtherton]

I must be the laziest programmer about, I don't even bother with any of the above, I simple use:

Dim OledbStr As String = "Select * from FixturesandFittings where (Proprty = " & CStr(FindResult.Item) & ") and Unit = " & CStr(FindResult.Unit)
Dim cmd6 As OleDbCommand = New OleDbCommand(OledbStr, Cn6)
da6.SelectCommand = cmd6
Cn6.Open()
' Fill the Dataset with selected Fixtures and Fittings values
ds6.Clear()
da6.Fill(ds6, "FixturesandFittings")
Cn6.Close()

that data is then bound to the datagrid as in:

DataGrid2.SetDataBinding(ds6, "FixturesandFittings")

then when they hit the save button:

Dim Cb6 As OleDbCommandBuilder = New OleDbCommandBuilder(da6)


da6.Update(ds6, "FixturesandFittings")



Note:

da6 is a predefined datadaptor
ds6 a predefined dataset

cn6 a predfined connection, which gets opened and closed appropiately


amd yes this is working code

[Venkrishna]

Hawke,

If you happen to read this thread again, can you please post your C# code that can insert / edit / delete records into datagrid. I have 3 buttons to add, save and delete records.

[mendhak]

C# to VB.NET and vice versa here

http://www.developerfusion.com/utili...sharptovb.aspx

[skv_noida]

Winanjaya is on right way!!!!!!!

[jmcilhinney]

I must be the laziest programmer about, I don't even bother with any of the above, I simple use:

Dim OledbStr As String = "Select * from FixturesandFittings where (Proprty = " & CStr(FindResult.Item) & ") and Unit = " & CStr(FindResult.Unit)
Dim cmd6 As OleDbCommand = New OleDbCommand(OledbStr, Cn6)
da6.SelectCommand = cmd6
Cn6.Open()
' Fill the Dataset with selected Fixtures and Fittings values
ds6.Clear()
da6.Fill(ds6, "FixturesandFittings")
Cn6.Close()

that data is then bound to the datagrid as in:

DataGrid2.SetDataBinding(ds6, "FixturesandFittings")

then when they hit the save button:

Dim Cb6 As OleDbCommandBuilder = New OleDbCommandBuilder(da6)


da6.Update(ds6, "FixturesandFittings")



Note:

da6 is a predefined datadaptor
ds6 a predefined dataset

cn6 a predfined connection, which gets opened and closed appropiately


amd yes this is working code

CommandBuilders have three major flaws: They cannot be used where the result set is the result of a join. As far as I've been able to discern, there is no way to wrap the commands the build into a transaction. They will attempt to update all fields included in the corresponding select statement. They are fine for the simple case where these three factors are not an issue. I do recall reading, however, that Microsoft do not recommend CommandBuilders. Not that we all must do what Microsoft says, of course. I think that a lot of people are unaware of the fact that a DataAdapter can generate your delete, insert and update commands for you as actual, editable objects, complete with parameters and all the trimmings without "resorting" to CommandBuilders. When you add a DataAdapter to a form the wizard can create your extra commands for you. This wizard can also be invoked using the "Configure Data Adapter..." function at the bottom of the Properties window.