Trouble updating my database

[1loudsvt]

Been stuck on this for a while now, can create tables add to them, delete items from them but cannot get an update to stick to save my life. The code compiles and runs with no errors but the update never happens in the database. Here is my code in vb.net and i have tried numerous methods please let me know what i am missing thanks


the page loads text from a field in a table, allows the user to edit it then click an update button to write the changes back to the database.


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
If Request.Cookies("LoggedIn") Is Nothing Then
pnlCP.Visible = False
lblError.Visible = True
ElseIf Request.Cookies("LoggedIn").Value = 0 Then
pnlCP.Visible = False
lblError.Visible = True
Else
pnlCP.Visible = True
lblError.Visible = False
End If

Dim strSQL2 As String

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

Dim conConnection2 As New OleDbConnection

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

Dim cmdcommand2 As New OleDbCommand(strSQL2, conConnection2)
Dim rdrReader2 As OleDbDataReader
rdrReader2 = cmdcommand2.ExecuteReader

If rdrReader2.Read Then
txtFP.Text = rdrReader2("body").ToString
End If
rdrReader2.Close()
cmdcommand2.Dispose()
conConnection2.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

strSQL = "UPDATE FrontPage SET body = '" + strFP + "' where Headline = 'a'"

Dim conConnection As New OleDbConnection

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

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

[1loudsvt]

here is another method i have tried with no luck

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

[Tasslehoff]

Try adding a breakpoint in your application and step through it to see what the value of strSQL is and see if that is a valid SQL statement. Also, test your query in SSMS to make sure it works.

[1loudsvt]

thanks for all the help i changed the page load code to this....

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Request.Cookies("LoggedIn") Is Nothing Then
pnlCP.Visible = False
lblError.Visible = True
ElseIf Request.Cookies("LoggedIn").Value = 0 Then
pnlCP.Visible = False
lblError.Visible = True
Else
pnlCP.Visible = True
lblError.Visible = False
End If


If Page.IsPostBack Then

Else
Dim conConnection2 As New OleDbConnection

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

Dim cmdcommand2 As New OleDbCommand()
cmdcommand2.Connection = conConnection2
cmdcommand2.CommandText = "SELECT * from FrontPage WHERE Headline = 'abcd'"
Dim rdrReader2 As OleDbDataReader
rdrReader2 = cmdcommand2.ExecuteReader

If rdrReader2.Read Then
txtFP.Text = rdrReader2("body").ToString
End If
rdrReader2.Close()
cmdcommand2.Dispose()
conConnection2.Close()
End If


End Sub

and now everything works :-)

[mjp]

Nice, glad you got it going.