locked
This code executes but the record does not change RRS feed

  • Question

  • User1237844216 posted

    Any ideas why this is not updating the record?

            Using Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("cerberusConnectionString").ConnectionString)
                Dim strDocids As String = Request.QueryString("blogno")
                Dim SQL As String = "update [tbl_WIKIblog] set [Title]=@title,[author]=@author,[post]=@post,[published]=@published,bdate=@bdate where blogno=" + strDocids
    
                Dim myCommand As New SqlCommand(SQL, Conn)
                myCommand.Parameters.Add("@published", Data.SqlDbType.Int).Value = CheckBox2.Checked
                myCommand.Parameters.Add("@title", Data.SqlDbType.VarChar).Value = TextBox2.Text
                myCommand.Parameters.Add("@author", Data.SqlDbType.VarChar).Value = DropDownList1.Text
                myCommand.Parameters.Add("@post", Data.SqlDbType.VarChar).Value = FreeTextBox1.Text
                myCommand.Parameters.Add("@bdate", Data.SqlDbType.DateTime).Value = bdate.Text
                Conn.Open()
                myCommand.ExecuteNonQuery()
                Conn.Close()
                Dim nurl As String = "blogview.aspx?blogno=" + strDocids
                Response.Redirect(nurl)
            End Using
    

    Thursday, April 30, 2015 8:11 AM

All replies

  • User1577371250 posted

    Hi,

    Check you are connecting to the right database.

    Thursday, April 30, 2015 8:17 AM
  • User1237844216 posted

    It's the right database.

    Thursday, April 30, 2015 8:18 AM
  • User1577371250 posted

    Check you are getting the blogno.

    Thursday, April 30, 2015 8:35 AM
  • User1237844216 posted

    It's getting the right blogno. The whole code executes fine. It just doesn't change the record.

    Thursday, April 30, 2015 9:19 AM
  • User1237844216 posted

    I've changed the whole code to this...

    Again it runs but still does not make any changed to the database. The stored procedure works if I run it manually though.

            Dim strDocids As String = Trim(Request.QueryString("blogno"))
            Dim con As New SqlConnection
            Dim cmd As New SqlCommand
            Try
                With con
                    .ConnectionString = ConfigurationManager.ConnectionStrings("cerberusConnectionString").ConnectionString
                    .Open()
                End With
                With cmd
                    .Connection = con
                    .CommandText = "UpdateBlog"
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.AddWithValue("@blogno", strDocids)
                    .Parameters.AddWithValue("@published", CheckBox2.Checked)
                    .Parameters.AddWithValue("@title", TextBox2.Text)
                    .Parameters.AddWithValue("@author", DropDownList1.Text)
                    .Parameters.AddWithValue("@post", FreeTextBox1.Text)
                    .Parameters.AddWithValue("@bdate", bdate.Text)
                    .ExecuteNonQuery()
                End With
    
            Catch ex As Exception
    
            Finally
                cmd.Dispose()
                con.Close()
            End Try
    
            Dim nurl As String = "blogview.aspx?blogno=" + strDocids
            Response.Redirect(nurl)

    Friday, May 1, 2015 7:12 AM
  • User-271186128 posted

    Hi acidtrash,

    From your code and description, I create a sample using the following code. It worked well on my side. I suggest you could define an Integer variable and check the SqlCommand.ExecuteNonQuery Method return value. The return value is the number of rows affected by the command.

            Dim constr As String = ConfigurationManager.ConnectionStrings("MyDataControlDBConnectionString").ConnectionString.ToString()
            Dim cmdText As String = "Update Students set ClassId =@classId where StuId=@stuId"
    
            Dim i As Integer
    
            Using con As New SqlConnection(constr)
                Using cmd As New SqlCommand(cmdText, con)
                    cmd.Parameters.AddWithValue("@classId", 15)
                    cmd.Parameters.AddWithValue("@stuId", 1015)
                    con.Open()
                    i = cmd.ExecuteNonQuery() 'Check the return value. If update successfull, the return value is the number of rows affected by the command. 
                    con.Close()
                End Using
            End Using
            GridView1.DataBind()  'Rebind GridView
            Response.Write(i.ToString())

    For more details about SqlCommand.ExecuteNonQuery Method, please see: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

    Best Regards,
    Dillion

    Wednesday, May 6, 2015 6:49 AM
  • User1237844216 posted

    This executes, but again, does not change the record.

    Is there another way to edit data, This way doesn't seem to work whatever I do.

    Tuesday, May 12, 2015 6:52 AM
  • User753101303 posted

    Hi,

    Doing nothing when an exception happens is the last thing you want to do. Prove everypoint. Start by seeing what is the return value, you could also immediately reread the row to see if the update is done, it could be also that there is actually no change because the values are the same, because it is not the same db (show the connection string), if file based the db file could be overwritten when running again etc... that is in short prove every assumption you are making.

    And I would suggest to never change your approach just because something that should work doesn't. You'll learn more by fixing the issue rather than by just doing what you want another way.

    Edit: BTW you don"t have told what is the return value you get from ExecuteNonQuery. Have you checked this as asked by Zhi Lv?

    Tuesday, May 12, 2015 6:54 AM
  • User1237844216 posted

    It says the value is 1, but it doesn't change the record. I've tried three permutations of this kind of code, each time the returned value says 1, but it never changes it.

    Tuesday, May 12, 2015 8:05 AM