Need some help to figure out why record isnt updating

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
 
M

Miha Markic

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.
 
S

Showjumper

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
 
M

Miha Markic

Hi,

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

IbrahimMalluf

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
 
S

Showjumper

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...
 
S

Showjumper

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
 
S

Showjumper

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?
 
M

Miha Markic

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?
 
I

IbrahimMalluf

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
 
I

IbrahimMalluf

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)
 
S

Showjumper

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.
 
S

Showjumper

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.
 
I

IbrahimMalluf

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
 
S

Showjumper

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.
 
R

Ron Allen

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't update field in datagrid 3
Using CommandBuilder to update an Access table from a DataGridView 2
Trouble with Dataset 3
Connection property has not been initialized 1
Luddites ! ; forward into the past ! (ADO/DAO) 5
cmd.Parameters.Add(new OleDbParameter NOT TAKING ??? 3
SqlDataReader.Read() with image data type size 65,535 2
vb.net query 3

Top