none
SQL parameter update not updating changed null/blank values

    Question

  • I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application.  It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db.  I am new to this, and I'm sure it is something simple.  Here is some sample code.

    SqlConnection1.Open()

    strSQLu = "UPDATE table1 " _
      & "SET Field1Tag = @Field1Tag, Field2Tag = @Field2Tag " _
      & "WHERE (Field1Tag = @Field1Tag) "

    cmdCategoriesUpdate.CommandText = strSQLu

    With cmdCategoriesUpdate
                .Parameters("@Field1Tag").Value = txtFld1.Text
                .Parameters("@Field2Tag").Value = txtFld2.Text
    End With
    cmdCategoriesUpdate.ExecuteNonQuery()
    SqlConnection1.Close()

    Tuesday, October 24, 2006 1:15 PM

Answers

  • Thanks for the help.

    Solved.  I traced it to the textbox not updating... then realized the "If Not Page.IsPostBack Then" was missing from the "Page_Load"... so it was reinitializing the data each time.  It works fine now.

    Wednesday, October 25, 2006 1:09 PM

All replies

  • First, a minor issue not directly related to your question...your query string uses Field1Tag in both the where clause and the set clause. You probably only want it in the where clause since the set will change it to a similar or identical value (depending on your database's dictionary rules -- e.g. case insensitive, case sensitive).

    Seeing your table definition would help pinpoint this issue, but my first suggestion is to verify that the empty textbox does NOT evaluate to null (in a C# desktop application, the value is an empty string...but I can't easily verify in a VB web app). If the Field2Tag column is specified to not take NULL values, this would obviously present a problem.

    To do this, look at the return value from SqlCommand.ExecuteNonQuery. If the value is zero, you can use the SqlError class to determine what happened. For an example of this, check out: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlerror.aspx

    For further tips and suggestions for debugging the problem in managed code, the folks monitoring the ADO.NET forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1) can indubitably provide some excellent advice.

    Tuesday, October 24, 2006 5:47 PM
  • Thanks for the help.

    Solved.  I traced it to the textbox not updating... then realized the "If Not Page.IsPostBack Then" was missing from the "Page_Load"... so it was reinitializing the data each time.  It works fine now.

    Wednesday, October 25, 2006 1:09 PM