sql update problem

[1loudsvt]

using the code below when i click the update button the page reloads but the text box displays the original value and not the updated one. i get no errors at all. i tried removing the variable from the update statement and adding a hard coded value and it will then update the database to that hard coded value. really confused and frustrated. thanks for the help.

Imports System.Data.OleDb
Partial Class CP
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim strSQL As String

        strSQL = "SELECT * from FrontPage WHERE Headline = 'a'"

        Dim conConnection As New OleDbConnection

        conConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=xxxxxx;Initial Catalog=xxxxxxx;User ID=xxxxxxxxx;Password=xxxxxxxx;"
        conConnection.Open()

        Dim cmdcommand As New OleDbCommand(strSQL, conConnection)
        Dim rdrReader As OleDbDataReader
        rdrReader = cmdcommand.ExecuteReader

        If rdrReader.Read Then
            txtFP.Text = rdrReader("body").ToString
        End If
        conConnection.Close()
        cmdcommand.Dispose()
        rdrReader.Close()

    End Sub


    Protected Sub btnUpdt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdt.Click
        Dim strSQL As String
        Dim strFP As String = txtFP.Text.ToString

        strSQL = "UPDATE FrontPage SET body = 0000000000"
        '" + strFP + "'"

        Dim conConnection As New OleDbConnection

        conConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=xxxxx;Initial Catalog=xxxxxxx;User ID=xxxxxxxx;Password=xxxxxxxxx;"
        conConnection.Open()

        Dim cmdCommand As New OleDbCommand(strSQL, conConnection)
        cmdCommand.ExecuteNonQuery()
        conConnection.Close()
        cmdCommand.Dispose()
    End Sub
End Class

 

[Menthos]

Hi,

You don't mention whether your update is actually getting set in the DB... is it?

Also your SQL update string is using +'s when you should be concatenating with &'s in VB - +'s would be used in a straight SQL query, not a string.

 

[1loudsvt]

thank you very much for the reply

no the update is not getting set in the db. i have always used +'s but i tried with &'s and i get the same results.

i have also tried swapping these two lines

conConnection.Close()
cmdcommand.Dispose()

s the command should be disposed before closing the connection but that did not work either. I also triple checked the permissions of the DB. I even gave the user full rights.

any other ideas?

 

[1loudsvt]

ok just made a discovery. if i comment out all of the page load code the update works just as it should. as soon as i uncomment the page load code the update no longer works so it must be an issue there but I do not see anything wrong.

 

[Menthos]

A couple of things to try:

Pop a Try block around your update code (which is always good practice) - something along the lines of :

Protected Sub btnUpdt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdt.Click
     Dim strSQL As String
     Dim strFP As String = txtFP.Text.ToString

     strSQL = "UPDATE FrontPage SET body = 0000000000"
     '" + strFP + "'"

     Dim conConnection As New OleDbConnection

     conConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=xxxxx;Initial Catalog=xxxxxxx;User ID=xxxxxxxx;Password=xxxxxxxxx;"
     Dim cmdCommand As New OleDbCommand(strSQL, conConnection)

     Try        
             conConnection.Open()        
             Dim iResult as integer = cmdCommand.ExecuteNonQuery()
     Finally
             conConnection.Close()
             cmdCommand.Dispose()
     End Try
End Sub

... you'll see I've popped your close and dispose into the 'Finally' part of the Try block - this should make sure your connection is closed in the case of an exception. You can of course add a Catch in there too to handle any exception.

- ExecuteNonQuery() handily sends back the number of rows effected by your query, so tying it to an integer (as shown) will allow you to, in this case, drop a breakpoint in and see what that value gets set to.

A quick question - if you manually add something to the db, does your page load pull that info in initially?

 

[1loudsvt]

tried the above iresult=1 but still does not update in the db

here is the latest version of the code

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim strSQL As String

        strSQL = "SELECT * from FrontPage WHERE Headline = 'a'"

        Dim conConnection As New OleDbConnection

        conConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=xxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxxx;"
        conConnection.Open()

        Dim cmdcommand As New OleDbCommand(strSQL, conConnection)
        Dim rdrReader As OleDbDataReader
        rdrReader = cmdcommand.ExecuteReader

        If rdrReader.Read Then
            txtFP.Text = rdrReader("body").ToString
        End If

        rdrReader.Close()
        cmdcommand.Dispose()
        conConnection.Close()

    End Sub


    Protected Sub btnUpdt_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdt.Click
        Dim strSQL As String
        Dim strFP As String = txtFP.Text.ToString

        strSQL = "UPDATE FrontPage SET body = '" & strFP & "'"

        Dim conConnection As New OleDbConnection

        conConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx;"

        Dim cmdCommand As New OleDbCommand(strSQL, conConnection)
        Dim iResult As Integer
        Try
            conConnection.Open()
            iResult = cmdCommand.ExecuteNonQuery()
        Finally
            cmdCommand.Dispose()
            conConnection.Close()
        End Try

        lbl1.Text = iResult.ToString

    End Sub

thanks for all the help

 

[Menthos]

Ok, so you made the code changes - what did iResult return when you stepped through the code?

 

[1loudsvt]

it returned a 1

 

[1loudsvt]

bump...still having this issue can anyone help?

 

[Menthos]

Sorry for the lack of reply - a lot on at the moment.

If you're getting a '1' returned, then your db update is succeeding - have you added breakpoints in your code and stepped through the execution at runtime? add a break after the db update and take a look at your table contents - it should show the updated fields.

Just out of interest, where are you setting headline to 'a'? since that's what your SELECT statement is after, yet you don't set it when you run your UPDATE.

 

[1loudsvt]

the code throws no exceptions. currently there is only one entry in this table the key being Headline which is 'a' and body which is what will be changed.

 

[1loudsvt]

here is another way i have tried with the same results

Imports System.Data.OleDb
Imports System.Data
Partial Class cp2
    Inherits System.Web.UI.Page
    Dim ds As New DataSet
    Dim conConnection As New OleDbConnection
    Dim strSQL As String
    Dim cmdcommand As New OleDbCommand(strSQL, conConnection)
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        conConnection.ConnectionString = "Provider=SQLOLEDB;Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx;"
        conConnection.Open()


        strSQL = "SELECT * from FrontPage WHERE Headline = 'abcd'"

        cmdcommand.CommandText = strSQL
        cmdcommand.ExecuteNonQuery()

        Dim oledbAdapter As OleDbDataAdapter


        oledbAdapter = New OleDbDataAdapter(strSQL, conConnection)
        oledbAdapter.Fill(ds)
        
       
        txtFP.Text = ds.Tables(0).Rows(0).Item(1)



    End Sub
    Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        ds.Tables(0).Rows(0).Item(1) = txtFP.Text
        strSQL = "UPDATE FrontPage SET body = '" & ds.Tables(0).Rows(0).Item(1) & "'"

        cmdcommand.CommandText = strSQL
        cmdcommand.ExecuteNonQuery()

        ds.Dispose()
        cmdCommand.Dispose()
        conConnection.Close()
    End Sub

    
End Class

 

[Menthos]

Well your second attempt won't work since you're using ExecuteNonQuery() which won't return what you're after.

So, going back to my suggestion on breakpoints and checking your database - You shouldn't be getting any exceptions, and that's not the point of adding the breakpoints and stepping through the code - you need to see what your code is doing at a given point in time and also to see how (or if) it's changing your database.