VB.Net and SQL update 1

[Bluejay07]

Hello,

I am facing an unusual situation in which my program executes an update to the SQL database and the database doesn't update properly despite having accurate update statements. I believe this is due to the controls and the data adapter updates.

I have a datagridview which has several columns, two of which are visible. The first visible column is a check box column while the second is a text column.

Say I have four entries displayed, in a manner such as:
checked A
checked B
unchecked C
unchecked D
(where A, B, C, D represent a text value)

If I randomly check/uncheck columns and end with:
unchecked A
checked B
checked C
unchecked D

I then save the data, although the saved data in SQL is not was I was saving.

dgvCategory.ClearSelection()
btnClose.Focus()

For i = 0 To dgvCategory.Rows.Count - 1

   If Not Verify_CategoryUsed(dgvCategory.Rows(i).Cells(2).Value, i + 1) Then
       Category_UpdateCommand(i, m_dsCategory.Tables("Categories").Rows(i).Item("CategoryID"), dgvCategory.Rows(i).Cells(1).Value, dgvCategory.Rows(i).Cells(2).Value)

       m_daCategory.Update(m_dsCategory, "Categories")
   Else
       MessageBox.Show("The category '" & dgvCategory.Rows(i).Cells(2).Value & _
          "' has already been used." & vbNewLine & vbNewLine & _
          "Please enter a different category.", "Invalid Category", MessageBoxButtons.OK, MessageBoxIcon.Warning)

       dgvCategory.Rows(i).Cells(2).Value = String.Empty
       Exit Sub
   End If
Next i

Private Sub Category_UpdateCommand(ByVal pRow As Integer, ByVal pCatID As Integer, ByVal pActive As Boolean, ByVal pCatname As String)
   '// Update a row in the database.

   Dim l_strSQL As String
   Dim UpdateCmd As SqlCommand

   System.Diagnostics.Debug.WriteLine("ID: " & pCatID & ", Active: " & pActive & ", Name: " & pCatname)
   l_strSQL = "UPDATE [Categories] SET [Active] = " & Math.Abs(CInt(pActive)) & ", [CategoryName] = '" & pCatname.Trim & "' " & _
              "WHERE [CategoryID] = " & pCatID

   System.Diagnostics.Debug.WriteLine(l_strSQL)

   UpdateCmd = New SqlCommand(l_strSQL, pihCONN)
   UpdateCmd.CommandType = CommandType.Text

   m_daCategory.UpdateCommand = UpdateCmd
End Sub

The debug sql statements show (which are accurate):
UPDATE [Categories] SET [Active] = 0, [CategoryName] = 'Home' WHERE [CategoryID] = 1
UPDATE [Categories] SET [Active] = 1, [CategoryName] = 'Work' WHERE [CategoryID] = 3
UPDATE [Categories] SET [Active] = 1, [CategoryName] = 'School' WHERE [CategoryID] = 4
UPDATE [Categories] SET [Active] = 0, [CategoryName] = 'Office' WHERE [CategoryID] = 5

When the grid is refreshed after the save, the following is displayed:
unchecked A
checked B
unchecked C
checked D

As I am a VB6 programmer and trying to migrate to vb.net, I find this task difficult to master.

All other aspects of the program that save to the database work perfectly, just not this section.

Could anyone provide assistance as to what may be causing this odd behaviour?

Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[RiverGuy]

When possible, use DataBinding for this. Here is an example of how it works:

1. You have a DataAdapter which contains Select, Insert, Update and Delete .Commands

2. Your DataAdapter uses the .Fill method to fill up a DataTable.

3. Your user makes changes to rows in the DataTable. This could be a DataGridView which is bound to the DataTable.

4. When the user clicks the "Save" button, you call the .Update method of your DataAdapter. Depending on the changes made, the DataAdapter will execute it's proper Insert, Update or Delete command. There is no string concatenation involved--you have columns and parameters mapped to each other.

So for example, consider a table like this:

CREATE TABLE Test1 (
TestID INT IDENTITY NOT NULL PRIMARY KEY,
TestDesc VARCHAR(50))

Now, consider the following code, for a DataTable against that table, a DataGridView to display and edit the data, and your DataAdapter wired up. Try it out. Note that you are not required to use DataAdapters for data access, and even when you do, you can use stored procedures for Insert, Update and Delete statements.

Public Class Form1
    Dim Con As New SqlClient.SqlConnection("Data Source=SomeServer;Initial Catalog=SomeDB;Integrated Security=SSPI;")
    Dim DA As New SqlClient.SqlDataAdapter
    Dim DT As New DataTable

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DT.TableName = "Test1"
        Dim cmdSelect As New SqlClient.SqlCommand
        Dim cmdInsert As New SqlClient.SqlCommand
        Dim cmdUpdate As New SqlClient.SqlCommand
        Dim cmdDelete As New SqlClient.SqlCommand
        With cmdSelect
            .Connection = Con
            .CommandType = CommandType.Text
            .CommandText = "SELECT * FROM Test1"
        End With
        With cmdInsert
            .Connection = Con
            .CommandType = CommandType.Text
            .CommandText = "INSERT INTO Test1 (TestDesc) SELECT @TestDesc"
            .Parameters.Add("@TestDesc", SqlDbType.VarChar, 50)
            .Parameters(0).SourceColumn = "TestDesc"
        End With
        With cmdUpdate
            .Connection = Con
            .CommandType = CommandType.Text
            .CommandText = "UPDATE Test1 SET TestDesc = @TestDesc WHERE TestID = @TestID"
            .Parameters.Add("@TestID", SqlDbType.Int, 4)
            .Parameters(0).SourceColumn = "TestID"
            .Parameters.Add("@TestDesc", SqlDbType.VarChar, 50)
            .Parameters(1).SourceColumn = "TestDesc"
        End With
        With cmdDelete
            .Connection = Con
            .CommandType = CommandType.Text
            .CommandText = "DELETE Test1 WHERE TestID = @TestID"
            .Parameters.Add("@TestID", SqlDbType.Int, 4)
            .Parameters(0).SourceColumn = "TestID"
        End With
        With DA
            .SelectCommand = cmdSelect
            .InsertCommand = cmdInsert
            .UpdateCommand = cmdUpdate
            .DeleteCommand = cmdDelete
            .Fill(DT)
        End With
        DT.PrimaryKey = New DataColumn() {DT.Columns(0)}
        DT.Columns(0).AutoIncrement = True
        Me.DataGridView1.DataSource = DT
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        DA.Update(DT.GetChanges)
        DT.AcceptChanges()
        DA.Fill(DT)
    End Sub
End Class

 

[Bluejay07]

Thanks for the reply RiverGuy.

Your example does work, however, it doesn't for my situation. I obviously do not know enough about VB.net to get this to work. I have been working on this projects for over a year and still can't get it to work (so frustrating). As I am a lonely programmer, this forum is my primary source for aid. You have even tried to help with my initial updates:

http://www.tek-tips.com/viewthread.cfm?qid=1570791&page=5

My datasource is a dataset and certain columns are hidden while others are either a checkbox or text column.

I also have a button for creating a new entry, although at this time I am not allowing any deletions. I do not have any issues with the insert command, just the save.

I don't want to change the entire code unless it's absolutely necessary (or unless the new code is more efficient that my current code) as the majority of the code works based on help and comments provided from this forum. If my situation was different (eg. using the grid just to enter information), your code above would probably be perfect; however, the information obtained from the datagridview is being used to populate a combo box in another section of the program.

I welcome different coding practices as it makes me aware of other possibilities I have not yet been exposed to.

I still do not know how to proceed from here. I can provide any other code if required.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Playing with your code a bit more, I discovered how to hide a column and how to rename a column yet I still haven't figured out how to set a the column's cell to be a checkbox.

If that can be done, perhaps my code could be altered.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[RiverGuy]

Well, when you want to have columns other than just textboxes in a DataGridView, you need to add it manually. Let's say you have three columns: one integer column, one varchar, and one bit. You want the bit column to be a checkbox.

        Dim YourDataGridView As DataGridView
        YourDataGridView.AutoGenerateColumns = False
        YourDataGridView.Columns.Add("Column1", "Column1--Int")
        YourDataGridView.Columns.Add("Column2", "Column2--Varchar")
        YourDataGridView.Columns("Column1").DataPropertyName = "The Name of Your Int Column"
        YourDataGridView.Columns("Column2").DataPropertyName = "The Name of Your Varchar Column"
        Dim colChk As New DataGridViewCheckBoxColumn
        With colChk
            .Name = "Column3"
            .HeaderText = "Column3--Bit"
            .DataPropertyName = "The Name of Your Bit Column"
        End With
        YourDataGridView.Columns.Add(colChk)

 

[Bluejay07]

Thank you RiverGuy.
I will play around with this. So far this seems like it would work so much better than my current code.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[RiverGuy]

So far this seems like it would work so much better than my current code.

Maybe. I didn't understand all of your old code. Either because you didn't provide enough of it, or I didn't examine it close enough. Since you stated you are coming from VB 6, I figured I would provide you with "The .Net Way."

By the way, if you are interested in more ".Net Way" snippets, you might want to replace vbNewLine with Environment.NewLine. I try to stay away from the old school VB constants in .Net.

 

[Bluejay07]

RiverGuy,
I'm still adjusting my code to incorporate your suggestions. I am getting an error during the save, which is directly related to the checkbox column.

With cmdUpdate
   .Connection = pihCONN
   .CommandType = CommandType.Text
   .CommandText = "UPDATE Categories SET Active = @Active, CategoryName = @CatName WHERE CategoryID = @CatID"
   .Parameters.Add("@CatID", SqlDbType.Int, 4)
   .Parameters(0).SourceColumn = "CategoryID"
   .Parameters.Add("@Active", SqlDbType.Bit, 1)
   .Parameters(1).SourceColumn = "Active"
   .Parameters.Add("@CatName", SqlDbType.VarChar, 75)
   .Parameters(2).SourceColumn = "CategoryName"
End With

When a change is made to the checkbox, if it's unchecked then the save gives a null error.

Cannot insert the value NULL into column 'Active', ... column does not allow nulls. UPDATE fails.

How do I save the information as a '0' instead of having the the program recognize it as a null value?

FYI, if the checkbox started off as unchecked and then it was checked, the save performs correctly by saving it as a '1'.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[RiverGuy]

I did not think it would behave that way. But an easy fix would be the following:

   .CommandText = "UPDATE Categories SET Active = [red]ISNULL(@Active, 0)[/red], CategoryName = @CatName WHERE CategoryID = @CatID"

 

[Bluejay07]

Thank you!!!
Your fix suggestion is working. I will continue my modifications and testing, although at the moment, your suggestions seem to work so much better than what I was trying to do.



If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

More problems are happening.

The datagridview is on a form controlled by a tree view. When 'Category' is selected, the form with the grid is displayed and the grid is setup with the appropriate columns, as well as initializing the sqlcommand sections and the data adapter.

If I enter a new row and save it, I am getting a (somewhat) duplicate entry displayed.

Here is something similar to what is displayed:
ID Active Category
...
0 'checked' test
13 'checked' test

The grid obviously updates with the new ID obtained from SQL, however, the newly created line without an ID remains. I've tried clicking another tree node and then back to the category node (which re-initializes the grid) but the row with the zero ID remain. The only way this is fixed is by closing the form and reopening it. I've also tried refreshing the grid and a few other things but nothing is working.

How do I refresh the grid properly so that the data displayed does not show an invalid detail line? I'm assuming this has something to do with the data adapter holding this information yet obtaining the updated database information at the same time.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[RiverGuy]

Does your save routine look like mine?

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        DA.Update(DT.GetChanges)
        DT.AcceptChanges()
        DA.Fill(DT)
    End Sub

 

[Bluejay07]

yes it does, with the exception that I have a try...catch added.

   Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
   '// Save Button.
   Try
      m_daCategory.Update(m_dtCategory.GetChanges)
      m_dtCategory.AcceptChanges()
      m_daCategory.Fill(m_dtCategory)

      MessageBox.Show("Category information has been saved.", "Save Complete", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
   Catch ex As Exception
      MessageBox.Show(ex.Message & Environment.NewLine & Environment.NewLine & "In procedure: 'frmConfig.btnSave_Click'", "PIH Error")
   End Try

Here is my section of code that initializes every else (based on your earlier post and some modifications)

Private Sub Setup_CategoryGrid()

   Dim cmdSelect As New SqlClient.SqlCommand
   Dim cmdInsert As New SqlClient.SqlCommand
   Dim cmdUpdate As New SqlClient.SqlCommand
   Dim cmdDelete As New SqlClient.SqlCommand

   m_dtCategory.TableName = "Categories"

   With dgvCategory
      .ClearSelection()
      .Controls.Clear()
      .Columns.Clear()
      .Refresh()

      .AutoGenerateColumns = False
      .Columns.Add("CategoryID", "CatID")
      .Columns("CategoryID").DataPropertyName = "CategoryID"
     '.Columns("CategoryID").Visible = False

      Dim colChk As New DataGridViewCheckBoxColumn
      With colChk
         .Name = "Active"
         .HeaderText = "Active"
         .DataPropertyName = "Active"
      End With
      .Columns.Add(colChk)

      .Columns.Add("CategoryName", "Category")
      .Columns("CategoryName").DataPropertyName = "CategoryName"

      '// Do Not allow sorting of columns
      For Each column As DataGridViewColumn In dgvCategory.Columns
         column.SortMode = DataGridViewColumnSortMode.NotSortable
      Next

      With cmdSelect
         .Connection = pihCONN
         .CommandType = CommandType.Text
         .CommandText = "SELECT * FROM Categories"
      End With

      With cmdInsert
         .Connection = pihCONN
         .CommandType = CommandType.Text
         .CommandText = "INSERT INTO Categories (Active, CategoryName) SELECT ISNULL(@Active,0), @CatName"
         .Parameters.Add("@Active", SqlDbType.Bit, 1)
         .Parameters(0).SourceColumn = "Active"
         .Parameters.Add("@CatName", SqlDbType.VarChar, 75)
         .Parameters(1).SourceColumn = "CategoryName"
      End With

      With cmdUpdate
         .Connection = pihCONN
         .CommandType = CommandType.Text
         .CommandText = "UPDATE Categories SET Active = ISNULL(@Active,0), CategoryName = @CatName WHERE CategoryID = @CatID"
         .Parameters.Add("@CatID", SqlDbType.Int, 4)
         .Parameters(0).SourceColumn = "CategoryID"
         .Parameters.Add("@Active", SqlDbType.Bit, 1)
         .Parameters(1).SourceColumn = "Active"
         .Parameters.Add("@CatName", SqlDbType.VarChar, 75)
         .Parameters(2).SourceColumn = "CategoryName"
      End With

      With cmdDelete
         .Connection = pihCONN
         .CommandType = CommandType.Text
         .CommandText = "DELETE Categories WHERE CategoryID = @CatID"
         .Parameters.Add("@CatID", SqlDbType.Int, 4)
         .Parameters(0).SourceColumn = "CatergoryID"
      End With

      With m_daCategory
         .SelectCommand = cmdSelect
         .InsertCommand = cmdInsert
         .UpdateCommand = cmdUpdate
         .DeleteCommand = cmdDelete
         .Fill(m_dtCategory)
      End With

      m_dtCategory.PrimaryKey = New DataColumn() {m_dtCategory.Columns(0)}
      m_dtCategory.Columns(0).AutoIncrement = True

      Me.dgvCategory.DataSource = m_dtCategory

      .Focus.Equals(False)
      .ClearSelection()
      .Refresh()
   End With
End Sub

If at first you don't succeed, then sky diving wasn't meant for you!

 

[RiverGuy]

Well, this is one of those special cases as you are using your Identity column as your primary key. The rock-solid solution can be seen at the following link. It's pretty extensive to post here:

http://msdn.microsoft.com/en-us/library/ms971491.aspx

The easier, alternate solution would be like the following:

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        DA.Update(DT.GetChanges)
        DT.Clear()
        DA.Fill(DT)
        Me.DataGridView1.DataSource = DT
    End Sub

 

[Bluejay07]

Thanks again RiverGuy. That seems to work.
Thanks as well for the article. There is a lot on information in there.

If at first you don't succeed, then sky diving wasn't meant for you!