One button submit that handles the work with the database

Lost User

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Try
Dim dv As DataView = New DataView(_DataSet.Tables(0))
If ComboBox1.Text = "Deleted" Then
dv.RowStateFilter = DataViewRowState.Deleted
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
ElseIf ComboBox1.Text = "Added" Then
dv.RowStateFilter = DataViewRowState.Added
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
ElseIf ComboBox1.Text = "Unchanged" Then
dv.RowStateFilter = DataViewRowState.Unchanged
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
ElseIf ComboBox1.Text = "None" Then
dv.RowStateFilter = DataViewRowState.None
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
ElseIf ComboBox1.Text = "Current Rows" Then
dv.RowStateFilter = DataViewRowState.CurrentRows
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
ElseIf ComboBox1.Text = "OriginalRows" Then
dv.RowStateFilter = DataViewRowState.OriginalRows
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
ElseIf ComboBox1.Text = "Modified Current" Then
dv.RowStateFilter = DataViewRowState.ModifiedCurrent
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
ElseIf ComboBox1.Text = "Modified Original" Then
dv.RowStateFilter = DataViewRowState.ModifiedOriginal
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
ElseIf ComboBox1.Text = "Modified Current" Then
dv.RowStateFilter = DataViewRowState.ModifiedCurrent
Me.DataGridView2.DataSource = dv
Me.DataGridView2.Refresh()
End If

    Catch ex As Exception
        MsgBox("No record were Modified")
    End Try
End Sub

Dave Kreskowiak

Ugly, ugly, ugly code. Did you try it?? Did it update the database?? Probably not.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

Lost User

I updated it . Can you check it Please?

Dave Kreskowiak

Nothing changed. It's still very ugly and has no code at all for updating the database. If you want to manually rewrite the code that you find in the DataAdapter, that's your choice. I don't see why you're not using a DataAdapter, but then again, I don't what you're SELECT query looks like either. The Refresh method only redraws the grid on screen. It does NOT tell anything to touch the database.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

Lost User

Member 10388494 wrote:

And now i want my dataset to affect the database(sql server)
without using DataAdapter.(It's a method for lazy people)

Only if you consider .NET to be a language for those who are to lazy to write assembler :) There's nothing wrong with using a DataAdapter :thumbsup:

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Lost User

No no no. I didn't mean it in a bad way. I am just trying to find how to write in database without the DataAdapter. Any idea? I am starting to get a bit desperate :/

Richard Deeming

Well, if you want to replicate what the DataAdapter does, you'll have to:

Create a DbCommand and relevant parameters to delete a row;
Call GetChanges(DataRowState.Deleted) on the DataTable;
Iterate through the returned rows, assigning the relevant values to the parameters and executing the command to delete the row from the database;
Create a DbCommand and relevant parameters to update a row;
Call GetChanges(DataRowState.Modified) on the DataTable;
Iterate through the returned rows, assigning the relevant values to the parameters and executing the command to update the row in the database;
Create a DbCommand and relevant parameters to insert a new row;
Call GetChanges(DataRowState.Added) on the DataTable;
Iterate through the returned rows, assigning the relevant values to the parameters and executing the command to insert the row in the database;

If you're dealing with multiple tables, you'll need to ensure that you handle them in the correct order to make sure that you don't try to insert a child row before the related parent row. If you want to add concurrency, you'll need to pass the original values of the relevant columns to your update and delete procedures. Use row[column, DataRowVersion.Original] to read the original value. You'll probably want to wrap everything up in a DbTransaction to make sure the changes are atomic. In short, it's a lot of work that you don't really need to do, as the DbDataAdapter already does it for you.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Dave Kreskowiak

Member 10388494 wrote:

I didn't mean it in a bad way

Yes you did. What you said about "lazy" has no other interpretation.

Member 10388494 wrote:

I am just trying to find how to write in database without the DataAdapter

WHY? What you're doing is exactly what the DataAdapter was designed to do! Ok, fine. While you're sitting there re-inventing the DataAdapter, the rest of us "lazy" people will use the DataAdapter, be done with the code and have moved on to other parts of the project completing them before you do.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

Lost User

That's a big help. Thank you Richard Deeming :) And Dave Kreskowiak, I am sorry if the word "lazy". It was inappropriate. But i am still learning. so I was trying to find a way to do so. Thank you for your time. :-D

Edward Giles

Clean up your code. Use ElseIf.

Lost User

Hello again. Thanks again Richard Deeming. I DID IT :D I found a simpler way to to what i asked before. I realised that there is no need for the datatable. I already have a Dataset. so I can do the following: Here i clear the textboxes in order to insert new data..

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
_isadded = True
txtAddress.Text = ""
txtName.Text = ""
txtEmail.Text = ""
txtserial.Text = ""
End Sub

Here: If _isadded = False: I am updating an existing record. If _isadded = True I am inserting a new record.

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click

    If \_isadded = False Then
        \_DataSet.Tables(0).Rows(txtCurrent.Text - 1).Item("Name") = txtName.Text
        \_DataSet.Tables(0).Rows(txtCurrent.Text - 1).Item("Address") = txtAddress.Text
        \_DataSet.Tables(0).Rows(txtCurrent.Text - 1).Item("Email") = txtEmail.Text
    Else
        txtCount.Text = CStr(CInt(txtCount.Text) + 1)
        txtCurrent.Text = txtCount.Text
        Dim \_dr As DataRow = \_DataSet.Tables(0).NewRow
        \_dr.Item(1) = txtCurrent.Text
        \_dr.Item(2) = txtName.Text
        \_dr.Item(3) = txtEmail.Text
        \_dr.Item(4) = txtAddress.Text
        \_DataSet.Tables(0).Rows.Add(\_dr)
    End If

As for the database..That is what i did:

Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
DataGridView2.Columns.Item(0).Visible = False
If _isdeleted = True Then
Dim cmd As New SqlCommand
If _cn.State = ConnectionState.Closed Then _cn.Open()
cmd.Connection = _cn
cmd.CommandText = "delete from tblcustomer where serial = " & Val(txtserial.Text)
cmd.ExecuteNonQuery()
txtCurrent.Text = (txtCount.Text) - 1
RefreshData(True)
ElseIf _isadded = False Then
Dim strconnection As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=DatabaseConnection;Integrated Security=True;"
Dim _cn As SqlConnection = New SqlConnection(strconnection)
Dim cmd As New SqlCommand
_cn.Open()

        cmd.CommandText = "update tblCustomer set ID=@ID, Name=@Name, Email=@Email, Address=@Address where serial = @serial"

Lost User

Looks good :thumbsup: I would suggest adding a using-clause where you're using the connection, and another one for the command. That way the connection gets closed nicely even if there's an exception or anything else that changes the intended executionpath. You might also want to put that connectionstring in a constant, or in the settings. Enjoy experimenting :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]