Need some help to figure out why record isnt updating

  • Thread starter Thread starter Showjumper
  • Start date Start date
S

Showjumper

I am working on content management system that allows the text in articles
to be updated. On the main page of the cms (default.aspx) the article titles
are shown with an edit hyperlink that sends the user to the edit page which
has a textbox and an update linkbutton. All of my data access logic is a
separate class file and i then call the appropriate routine. However the
text in the textbox doesnt update. It only updates when a text value is
hardcoded into the the sql statement. Maybe one of you can figure out what
the heck is going on. Thanks in advance...Here is the code:

'this sub populates the textbox
Public Sub ShowArticleText(ByVal ArticleID As String, ByRef ArticleText As
String)
Dim cmd As New OleDbCommand("Select * From Articles Where ArticleID =
@ArticleID", RDConn)
cmd.Parameters.Add(New OleDbParameter("@ArticleID", cmd))
cmd.Parameters("@ArticleID").Value = ArticleID
da.SelectCommand = cmd
da.Fill(ds, "Articles")
ArticleText = ds.Tables(0).Rows(0).Item("ArticleText").ToString
End Sub

' this is the sub to update the text in the texbox
Public Function UpdateComment(ByVal ArticleText As String, ByVal articleid
As String) As OleDbCommand
Dim cmd As New OleDbCommand("UPDATE Articles SET ArticleText = @ArticleText
WHERE ArticleID =@ArticleID", RDConn)
cmd.Parameters.Add("@ArticleText", cmd).Value = ArticleText
cmd.Parameters.Add("@ArticleID", cmd).Value = articleid
da.UpdateCommand = cmd
da.Fill(ds, "Articles")
End Function

And in the page w/ the textbox
Dim Dal As New DataLayer.AccessDataLayer
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dal.ShowArticleText(Request.QueryString("ArticleID"), TextBox1.Text)
End Sub
Sub BindData()
Dal.ShowArticleText(Request.QueryString("ArticleID"), TextBox1.Text)
End Sub
Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles LinkButton1.Click
If IsValid Then
Dal.UpdateComment(TextBox1.Text, Request.QueryString("articleid"))
End If
BindData()
End Sub
 
Hi,

Your logic is wrong.
Fill method is for retrieving data from database.
Instead of Fill method use cmd.ExecuteNonQuery() and get rid of dataadapter.
This is the simplies solution for your problem.

If you want do it with dataadapters and datatables you'll have to code quite
a bit more.
 
I tried executenonquery to begin with but that doesnt work either. The text
is only updated when i hardcode a value for the ArticleText parameter, i.e
If IsValid Then
Dal.UpdateComment("Some Text", Request.QueryString("articleid"))
End If
BindData()

Here is the rewritten sub:
Public Function UpdateComment(ByVal ArticleText As String, ByVal articleid
As String) As OleDbCommand
Dim cmd As New OleDbCommand("UPDATE Articles SET ArticleText = @ArticleText
WHERE ArticleID =@ArticleID", RDConn)
cmd.Parameters.Add("@ArticleText", cmd).Value = ArticleText
cmd.Parameters.Add("@ArticleID", cmd).Value = articleid
ExecuteCommand(cmd)
End Function

Where ExecuteCommand is
Private Sub ExecuteCommand(ByVal cmd As OleDbCommand)
cmd.Connection = RDConn
Try
RDConn.Open()
cmd.ExecuteNonQuery()
Finally
RDConn.Close()
End Try
End Sub
 
Hi,

Second parameter (cmd) in call to Parameters.Add is wrong.
Try this:
cmd.Parameters.Add("@ArticleText", ArticleText)
cmd.Parameters.Add("@ArticleID", articleid)
 
Hello

Try something like this:

Public Function UpdateComment(ByVal ArticleText As String, ByVal articleid
As String) As Integer

Dim MyCN As New OleDb.OleDbConnection("your connection string")

Dim MyCMD As New OleDb.OleDbCommand("UPDATE Articles SET ArticleText =
@ArticleText WHERE ArticleID =@ArticleID", MyCN)

Dim MyParam As OleDb.OleDbParameter

Try

With MyCMD

MyParam = New OleDb.OleDbParameter

With MyParam

..ParameterName = "@ArticleText"

..OleDbType = OleDb.OleDbType.VarChar

..Direction = ParameterDirection.Input

..Value = ArticleText

..Size = 50 'add actual table field size here

End With

..Parameters.Add(MyParam)

MyParam = New OleDb.OleDbParameter

With MyParam

..ParameterName = "@ArticleID"

..OleDbType = OleDb.OleDbType.Integer

..Direction = ParameterDirection.Input

..Value = articleid

End With

..Parameters.Add(MyParam)

MyCN.Open()

Return MyCMD.ExecuteNonQuery()

End With

Catch ex As Exception

'do whatever error handling you need here

Finally

If MyCN.State = ConnectionState.Open Then

MyCN.Close()

End If

MyCN = Nothing

MyCMD = Nothing

End Try

End Function




--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx
 
Still doesnt update.
Miha Markic said:
Hi,

Second parameter (cmd) in call to Parameters.Add is wrong.
Try this:
cmd.Parameters.Add("@ArticleText", ArticleText)
cmd.Parameters.Add("@ArticleID", articleid)
Click to expand...
 
And your apporach didnt succeed either. I have never encountered such a
situation where the data refused to update. Is there a fault in the way i
cam calling my function in the actual code behind of the update page?
Dim Dal As New DataLayer.AccessDataLayer

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

'Put user code to initialize the page here

Dal.ShowArticleText(Request.QueryString("ArticleID"), TextBox1.Text)

End Sub

Sub BindData()

Dal.ShowArticleText(Request.QueryString("ArticleID"), TextBox1.Text)

End Sub

Private Sub LinkButton1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles LinkButton1.Click

If IsValid Then

Dal.UpdateComment(TextBox1.Text, Request.QueryString("articleid"))

End If

BindData()

End Sub
 
The ID is a AutoNumber while the ArticleText is a Memo field. Is this due to
the fact the the field is a memo field?
 
Showjumper said:
The ID is a AutoNumber while the ArticleText is a Memo field. Is this due to
the fact the the field is a memo field?
Click to expand...

Are you talking about Access database?
Then no, it doesn't matter.
Are you sure that there is an article with ArticleId passed?
What does ExecuteNonQuery return?
 
OK, lets step back and look at the situation

Ahh....this is a MS-Access database.


Change update statement to read ("UPDATE Articles SET ArticleText =? WHERE
ArticleID =?")

Should work then.


--
Ibrahim (I have learned to loath Access) Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx
 
Oh, and just as a test, use the function I sent you and pass it an actual
string rather than a variable...just to test:

MyResult = UpdateComment("Just Testing, 1)
 
The update would work fine when passed an actual string. when using the
variable would it not update. Nor do using the ? for the parameters - i had
tried that early on and it didnt work. I am at loss here.
 
It seems that the text in the textbox isnt be passed into the sql statement
and thus into in the database since an actual string works rather than the
variable.
 
OK...we have narrowed down the problem

Your textbox appears to not be providing the data

Let me ask you a question, it the Textbox you are referring to a part of a
code generated array of textboxes?


--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx
 
I used to request.form to get the text from the textbox and the update
works. For some reason, it doesnt work with textbox1.text however which i
would think it should.
 
If ArticleID in the database is AutoNumber then you should be setting the
value to an Integer not to a string as shown in your routine. The second
parameter would be
cmd.Parameters.Add("@ArticleID", OleDbType.Integer).Value =
Convert.ToInt32(articleid)

Ron Allen
 
Back
Top