.NET (Core and Framework)

Re: One button submit that updates my database(insert,delete,..) without using DataAdapter

19-Dec-13 2:02

I updated it . Can you check it Please?

Dave Kreskowiak19-Dec-13 3:12

Dave Kreskowiak

19-Dec-13 3:12

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

Re: One button submit that updates my database(insert,delete,..) without using DataAdapter

Edward Giles19-Dec-13 22:27

Edward Giles

19-Dec-13 22:27

Clean up your code.
Use ElseIf.

Re: One button submit that handles the work with the database

Eddy Vluggen19-Dec-13 8:09

Eddy Vluggen

19-Dec-13 8:09

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 Smile | :)

There's nothing wrong with using a DataAdapter Thumbs Up | :thumbsup:

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: One button submit that handles the work with the database

Member 1038849419-Dec-13 8:16

Re: One button submit that handles the work with the database

19-Dec-13 8:16

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 Deeming19-Dec-13 8:47

Richard Deeming

19-Dec-13 8:47

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

Re: One button submit that handles the work with the database

Member 1038849419-Dec-13 9:38

Re: One button submit that handles the work with the database

19-Dec-13 9:38

That's a big help. Thank you Richard Deeming Smile | :)

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.
Big Grin | :-D

Member 1038849420-Dec-13 7:24

Re: One button submit that handles the work with the database

20-Dec-13 7:24

Hello again. Thanks again Richard Deeming.
I DID IT Big Grin | :-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"
         cmd.Connection = _cn
         cmd.Parameters.AddWithValue("@ID", Me.txtIDD.Text)
         cmd.Parameters.AddWithValue("@Name", Me.txtName.Text)
         cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text)
         cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
         cmd.Parameters.AddWithValue("@serial", Me.txtserial.Text)
         cmd.ExecuteNonQuery()
     Else
         Try
             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 = "INSERT INTO tblCustomer([ID],[Name], [Email], [Address]) VALUES (@ID, @Name, @Email, @Address) select @@IDENTITY;"
             cmd.Connection = _cn
             cmd.Parameters.AddWithValue("@ID", Me.txtCurrent.Text)
             cmd.Parameters.AddWithValue("@Name", Me.txtName.Text)
             cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text)
             cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
             txtserial.Text = cmd.ExecuteScalar.ToString()
             Me._DataAdapter.Fill(Me._DataSet)
             cmdRequery.PerformClick()
             cmdLast.PerformClick()
             'txtAddress.Enabled = False
             'txtEmail.Enabled = False
             'txtName.Enabled = False
         Catch ex As Exception
             MsgBox("Press add before you continue")
         End Try
     End If
 End Sub

Eddy Vluggen20-Dec-13 7:38

Eddy Vluggen

20-Dec-13 7:38

Looks good Thumbs Up | :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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: One button submit that handles the work with the database

Dave Kreskowiak19-Dec-13 9:29

Dave Kreskowiak

19-Dec-13 9:29

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

syntax error while updating a record

Member 1038849418-Dec-13 3:07

18-Dec-13 3:07

Hello. I have this code to update A RECORD IN sql server . But it's giving me an error:
wrong syntax near Where.
Any help Please?

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
       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()
       Dim strsql As String
       strsql = "update tblcustomer set "
       strsql = strsql & " ID = '" & txtCurrent.Text & "', "
       strsql = strsql & " name = '" & txtName.Text & "', "
       strsql = strsql & " email = '" & txtEmail.Text & "', "
       strsql = strsql & " address = '" & txtAddress.Text & "', "
       strsql = strsql & " deptid = " & cbDept.SelectedValue
       strsql = strsql & " where serial = " & Val(txtserial.Text)
       cmd.CommandText = strsql
       cmd.Connection = _cn
       cmd.ExecuteNonQuery()
       cmdRequery.PerformClick()
   End Sub

serial is autoincremented in sql server.

Pete O'Hanlon18-Dec-13 3:39

18-Dec-13 3:39

You really shouldn't build your SQL like that. If I were you, I would consider using parameterized queries so that you can avoid SQL Injection attacks.

Member 1038849418-Dec-13 3:57

18-Dec-13 3:57

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        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],[Name],[Email], [Address], [DeptID]) VALUES (<a href="/Members/id">@ID</a>, <a href="/Members/Name">@Name</a>, <a href="/Members/email">@Email</a>, <a href="/Members/address">@Address</a>, @DeptID)"
        cmd.Connection = _cn
        cmd.Parameters.AddWithValue("<a href="/Members/id">@ID</a>", Me.txtID.Text)
        cmd.Parameters.AddWithValue("<a href="/Members/Name">@Name</a>", Me.txtName.Text)
        cmd.Parameters.AddWithValue("<a href="/Members/email">@Email</a>", Me.txtEmail.Text)
        cmd.Parameters.AddWithValue("<a href="/Members/address">@Address</a>", Me.txtAddress.Text)
        cmd.Parameters.AddWithValue("@DeptID", Me.cbDept.SelectedValue)
        txtserial.Text = cmd.ExecuteScalar()
End Sub

I wrote this one now. But it's giving me error on the executescalar. syntax error near )
Am i missing something?
And if i commented this line of code:

txtserial.Text = cmd.ExecuteScalar()

when i edit the textboxes , everything looks normal. i go next-previous-move last-move first. everything is fine. but not the database.
the edited record stays as it was before editing.
Any idea why?
Thank you Smile | :)

Pete O'Hanlon18-Dec-13 4:00

18-Dec-13 4:00

You shouldn't be executing a scalar - that's expecting a value back. Use ExecuteNonQuery instead.

Member 1038849418-Dec-13 4:03

18-Dec-13 4:03

I tried.
Incorrect syntax near )
but i need the executescalar because i need the first columns value to be in txtserial

Pete O'Hanlon18-Dec-13 4:10

18-Dec-13 4:10

The problem that you have here is that you are performing an update. If you execute the command that you have there, you will update EVERY record because you have no where clause. What are you trying to do here?

Richard Deeming18-Dec-13 4:13

Richard Deeming

18-Dec-13 4:13

You're mixing the syntax for the INSERT[^] and UPDATE[^] statements.

If you want to insert a new record, the query should be:

SQL

INSERT INTO tblcustomer 
(
   [ID],
   [Name],
   [Email],
   [Address],
   [DeptID]
) 
VALUES 
(
   @ID, 
   @Name, 
   @Email, 
   @Address, 
   @DeptID
)

If you want to update an existing record, the query should be:

SQL

UPDATE 
   tblcustomer 
SET 
   ID = @ID,
   Name = @Name,
   Email = @Email,
   Address = @Address,
   DeptID = @DeptID
WHERE
   Serial = @Serial

(You would also need to add the @Serial parameter to the command.)

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

Member 1038849418-Dec-13 4:24

18-Dec-13 4:24

Yes you are right Richard Deeming.
It worked. the databse is updating . and that's the important thing.
Thanks a lot guys.
But once i put this line of code:

txtserial.Text = cmd.ExecuteScalar.ToString()

an eror pops up:
Object reference not set to an instance of an object.
And i need the first columns value to be in the txtserial texbox.
Any advice?

Pete O'Hanlon18-Dec-13 4:38

18-Dec-13 4:38

You're getting a NullReferenceException because you aren't returning anything. You're either passing in the serial number for an update statement (in which case you don't need to get it out), or you should be trying to SELECT SCOPE_IDENTITY after doing your insert to get the value of the record you inserted.

Richard Deeming18-Dec-13 5:02

Richard Deeming

18-Dec-13 5:02

Pete O'Hanlon wrote:
SELECT IDENTITY_SCOPE()

Or SCOPE_IDENTITY() as it's known! Poke tongue | ;-P

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

Pete O'Hanlon18-Dec-13 5:07

18-Dec-13 5:07

That's only in those ol' versions of SQL Server. I'm running SQL Server 2432.

Member 1038849418-Dec-13 21:22

18-Dec-13 21:22

That helped. Thank you