Resolved Update method

[Socarsky]

I tried to modify field of FirstName's column with new value, and its successfully done.
But if I click on another cell or use arrow keys up or down then modified data does not update and no exception thrown so this is my first question. Second one is that how can I write codes more useful because as much as it looks in my code that I need to write the same thing for the other columns when possible modifying needs to update. It must be another wise way to update data into the database from a DataGridView.
I mean below:

Dim sqlQuery As String = "UPDATE tblContacts SET FirstName = '" &  db_firstname & "' WHERE CONTACTID = " & db_contactID & ""

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim MyIndex As Integer
        Dim db_contactID As Integer
        Dim db_firstname As String
        Dim db_lastname As String
        Dim db_email As String
        Dim db_phonenumber As String
        Dim db_tarih As Date
        Dim conn As New SqlConnection("Data Source=PC-N39\sqlexpress;Initial Catalog=ADO_PRACTICE;Persist Security Info=True;User ID=sa;Password=sas")
        conn.Open()
        MyIndex = DataGridView1.CurrentRow.Index
        db_contactID = CInt(DataGridView1.Item(0, MyIndex).Value)
        db_firstname = CStr(DataGridView1.Item(1, MyIndex).Value)
        db_lastname = CStr(DataGridView1.Item(2, MyIndex).Value)
        db_email = CStr(DataGridView1.Item(3, MyIndex).Value)
        db_phonenumber = CStr(DataGridView1.Item(4, MyIndex).Value)
        db_tarih = CDate(DataGridView1.Item(5, MyIndex).Value)
        Dim sqlQuery As String = "UPDATE tblContacts SET FirstName = '" & db_firstname & "' WHERE CONTACTID = " & db_contactID & ""
        Dim sqlCommand As New SqlCommand
        With sqlCommand
            .CommandText = sqlQuery
            .Connection = conn
            .ExecuteNonQuery()
        End With
        conn.Close()
    End Sub

 

[IanRyder]

Hi,

It seems you have misunderstood how easy it can be to read a table from your DataBase, Display and Modify the information in a DataGridView and then persist those changes back to your Database. Add A DataGridView and a Button to a new form and have a look at this code example:-

Imports System.Data.SqlClient
 
Public Class Form1
  Dim DT As New DataTable
  Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
  Dim daEmployees As New SqlDataAdapter("Select * From Employees", sqlConn)
  Dim EmployeeCB As New SqlCommandBuilder(daEmployees)
 
  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    daEmployees.Fill(DT)
    DataGridView1.DataSource = DT
  End Sub
 
  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    daEmployees.Update(DT)
  End Sub
End Class

In this code, I declare at the class level, a DataTable, a ConnectionString, a DataAdapter and a CommandBuilder.

I then initialise the DataAdapter with a Select Query to return all the records from an Employees table. I also then initialise the CommendBuilder with the Employee DataAdapter. What this then does is to automatically create the Inset / Update and Delete Action queries for the Employee DataAdapter. Notice, I have basically done very little coding so far.

Then, when the Form Loads I populate the DataTable using the Employee DataAdapter's Fill method and set the DataSource of the DataGridView to the DataTable.

Now, in the DataGridView, you can Modify existing rows, Insert new records and Delete records as you need. These changes are then reflected back in the DataTable which is the DataSource of the DataGridView.

Finally, when you click on the button it calls the Employee DataAdapter's Update method which takes a data source as an argument and then persists all the changes in that data source back to the Database.

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

I already told you what to do in another thread earlier today: don't use ExecuteNonQuery to save data from a DataGridView. Use a DataTable and a data adapter. If you need any data from the database initially, call Fill on the adapter to populate the DataTable. Bind the DataTable to a BindingSource. Bind the BindingSource to the grid. When you want to save, call EndEdit on the BindingSource to make sure all changes have been committed and then call Update on the adapter. Fortunately, you'll have no choice but to use parameters in the UPDATE command as well.

Retrieving and Saving Data in Databases

 

[Socarsky]

Thanks guys I will do your suggestions tomorrow. And share my situation. I know that my code is worst.

 

[Socarsky]

@jmcilhinney,
Unfortunately I could not achieved to apply your method to mine even I spent my time for one hour What's wrong I don't know.

Here your code:

Private Sub InitialiseDataAdapter()
        Dim delete As New SqlCommand("DELETE FROM tblContacts where ContactID = @ID", Me.cs)
        Dim insert As New SqlCommand("INSERT INTO tblContacts (FirstName, LastName, Email, PhoneNumber, Tarih) VALUES (@Name, @LName, @Email, @Phone, @Tar)", Me.cs)
        Dim update As New SqlCommand("UPDATE tblContacts Set FirstName = @Name, LastName = @LName, Email = @Email, PhoneNumber = @Phone, Tarih = @Tar", Me.cs)

        delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ContactID")

        insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "FirstName")
        insert.Parameters.Add("@LName", SqlDbType.VarChar, 100, "LastName")
        insert.Parameters.Add("@Email", SqlDbType.VarChar, 100, "Email")
        insert.Parameters.Add("@Phone", SqlDbType.VarChar, 100, "PhoneNumber")
        insert.Parameters.Add("@Tar", SqlDbType.SmallDateTime, 18, "Tarih")

        update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "FirstName")
        update.Parameters.Add("@LName", SqlDbType.VarChar, 100, "LastName")
        update.Parameters.Add("@Email", SqlDbType.VarChar, 100, "Email")
        update.Parameters.Add("@Phone", SqlDbType.VarChar, 100, "PhoneNumber")
        update.Parameters.Add("@Tar", SqlDbType.SmallDateTime, 18, "Tarih")

        Me.da.DeleteCommand = delete
        Me.da.InsertCommand = insert
        Me.da.UpdateCommand = update

        Me.da.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub

I put above sub in btnUpdate event and SaveData too, but fail

 

[jmcilhinney]

That is only part of the code I provided. As the name suggests, that code initialises the data adapter. It doesn't retrieve data from the database and it doesn't save data to the database. If that's all the code you've got relating to the data access then it won't do anything useful. That initialisation code should be executed once at the start. If you want to retrieve data then you call Fill on the data adapter and if you want to save data then you call Update on the data adapter, both after the one and only initialisation, exactly as I do in the example provided.

 

[Socarsky]

Hi,

It seems you have misunderstood how easy it can be to read a table from your DataBase,
Display and Modify the information in a DataGridView and then persist those changes back to your Database.
Add A DataGridView and a Button to a new form and have a look at this code example...
Hope that helps.
Cheers,
Ian

Ian, you rock thanks a lot! What a easy way to handle that. I am happy now. Thanks:suspicion:

 

[Socarsky]

@IanRyder,
Your code needs to refresh the data on DataGridView's cells I think, because It occurs an error like "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
I analyze that if I add a new row data into Database with clicking the update button and then trying to change something any of that row's cell then clicking the update button that error occurs.

 Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim x As Integer
        x = SqlDA.Update(TheDataTable)
End Sub

 

[IanRyder]

Hi,

As concurrency implies, you get this error message when the Data in the Backend Database does not match the current information in your DataGridView when you try to update the DataAdapter. This can be due to two things:-

1) The more common answer is that another user has changed the data in the Database since you loaded your DataGridView and you can therefore use the Concurrency error to decide what to do with the changes that you are making.

2) A more subtle answer to this is that you have an Auto Incrementing Primary Key in your Database which automatically adds a new auto incremented value when each new record you add in the DataGridView is saved to the Database. However, this value is NOT then replicated back in the DataGridView so when you try and update a change on a new record you get a concurrency violation since the source data and the DataGridView data do not match.

Based on the fact that you have a ContactID in your table, I am guessing that point number 2 is causing you your issue. To get round this all you need to do is map the Schema of the DataTable in the Database to the DataTable in VS. i.e:-

daEmployees.FillSchema(DT, SchemaType.Mapped)
daEmployees.Fill(DT)

You do this just before you call the fill command and what you will then see is that every time you add a new row the ContactID field will be populated correctly as per the structure of the DataTable in your Database. You can now modify and save an added record immediately after it has been inserted into the backend database.

Hope that helps.

Cheers,

Ian

 

[Socarsky]

Hi,
As concurrency implies, you get this error message when the Data in the Backend Database does not match the current information in your DataGridView when you try to update the DataAdapter. This can be due to two things:-
1) The more common answer is that another user has changed the data in the Database since you loaded your DataGridView and you can therefore use the Concurrency error to decide what to do with the changes that you are making.
2) A more subtle answer to this is that you have an Auto Incrementing Primary Key in your Database which automatically adds a new auto incremented value when each new record you add in the DataGridView is saved to the Database. However, this value is NOT then replicated back in the DataGridView so when you try and update a change on a new record you get a concurrency violation since the source data and the DataGridView data do not match.

Based on the fact that you have a ContactID in your table, I am guessing that point number 2 is causing you your issue. To get round this all you need to do is map the Schema of the DataTable in the Database to the DataTable in VS. i.e:-

daEmployees.FillSchema(DT, SchemaType.Mapped)
daEmployees.Fill(DT)

You do this just before you call the fill command and what you will then see is that every time you add a new row the ContactID field will be populated correctly as per the structure of the DataTable in your Database. You can now modify and save an added record immediately after it has been inserted into the backend database.

Hope that helps.
Cheers,
Ian

Hi Ian,
Appreciate for your support and you are right about the SqlAdapter as (Select * from tblContacts) if I use that then I live problems, I don't know that How can I handle this without SqlAdapter query? There is another method to make it without fill a table's data into a DataGridView at the starting of its. But I dont't know how to control that too. Because DataTable SqlAdapter and SqlAdapter needs a query to retrieve data from a table to DataGridView. I just copied a main project on my laptop for testing but when I remove the SQLAdapter query then VB.Net showed me 5 errors related with that line of code. My intention is not to retrieve data from a table at the beginning of program and enter some row to update and see the circumstances. Now I am just testing environments for getting experience about Sql and DataGridView.
So, I tried your suggestion about after the btnUpdate all data retrieve into the DataGridView one more time it means if the program retrieved 5 rows data and I add one row that totally 6 and press the btnUpdate the program retrieve again all data from DB to the DataGridView then whole rows count will be 11.

Thanks for helping Ian

 

[Socarsky]

This topic paralel with this: http://www.vbdotnetforums.com/sql-s...atecommand-affected-0-expected-1-records.html end it's just totally solved below.

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim x As Integer
        x = SqlDA.Update(TheDataTable)
        TheDataTable.Clear()
        SqlDA.Fill(TheDataTable)
        MessageBox.Show(CStr(x) & " record(s) updated")
        Dim getCounts As Integer
        getCounts = TheDataTable.Rows.Count
        MessageBox.Show(CStr(getCounts) & " That is count of row(s) in DataGridView")
End Sub