asp.net update record problem

[fozail]

Hey guys, I'm having a problem updating a record.I'm using the code below:

Sub UpdateMember(Sender as Object, e as EventArgs)

cmd.CommandText="Update tbl_members Set title=@title Where member_id='" & intMemberId & "'"
cmd.Parameters.Add("@title",txtTitle.Text)
cmd.Connection = conn
conn.open()
cmd.ExecuteNonQuery()
conn.close()
response.Redirect("members_profile.aspx?view=")

End Sub

The above code works fine if I don't use parameter e.g if I use Set title='some text'

Can someone please tell me what am I doing wrong.

Thanks

[RobDavid]

Hey guys, I'm having a problem updating a record.I'm using the code below:

Sub UpdateMember(Sender as Object, e as EventArgs)

cmd.CommandText="Update tbl_members Set title=@title Where member_id='" & intMemberId & "'"
cmd.Parameters.Add("@title",txtTitle.Text)
cmd.Connection = conn
conn.open()
cmd.ExecuteNonQuery()
conn.close()
response.Redirect("members_profile.aspx?view=")

End Sub

The above code works fine if I don't use parameter e.g if I use Set title='some text'

Can someone please tell me what am I doing wrong.

Thanks

Well...you should be using sqlparameters for ALL of your parameters.....
But what you have looks fine to me....I like using this syntax better?

sqlCmd.Parameters.Add(New SqlParameter("@title", SqlDbType.VarChar))
sqlCmd.Parameters("@title").Value = txtTitle.text

[Ribeyed]

Hi,
your trying to add parameters to your querystring.
Maybe wrong but i think you only need to supply parameters if your running a stored procedure.
as you said it works when you add your value to your querystring, that is the correct way if your not using stored procedures.

[fozail]

Well guys! thank for the replies.First thing I found is that I'm using quotes around intmemberId variable that is wrong as that variable is integer and I should not be using quotes.But the problem is that I still cannot get the record to update.For now Im trying to update just one field but if this works I will add code to update all the fields.And Ribeyed I have heard ppl saying that before that you should use parameters only with stored procedures but I have done this before and I know it works but just don't know what am I doing wrong this time.

I can totally update the record using your syntax but I want to know WHAT IF someone does not want to use stored procedures so then how would they go about updating records ?

[Ribeyed]

Hi,
cmd.CommandText="Update tbl_members Set title=@title Where member_id='" & intMemberId & "'"
cmd.Parameters.Add(New SQLParameter("@title", SQLDbType.Char, 50))
cmd.Aprameters("@title").Value = "The Title"

give that a try let me know how you get on.
Think all that is missing is the size of the Char in the Parameter.

[drallab]

Try this:


Sub UpdateMember(Sender as Object, e as EventArgs)
Dim _error as boolean = true
Dim conn As New SqlConnection(connectionString)
Dim comm As New SqlCommand("Update tbl_members Set title=@title Where member_id= @member_id", conn)
comm.Parameters.AddWithValue("@member_id", intMemberId)
comm.Parameters.AddWithValue("@title", txtTitle.Text.Trim)
Try
conn.Open()
comm.ExecuteNonQuery()
Catch ex As Exception
response.write(ex.ToString) 'displays SQL or .NET error on page if error occurs
_error = false
Finally
conn.Close()
conn.Dispose()
comm.Dispose()
End Try

If _error = true then
response.Redirect("members_profile.aspx?view=")
End If
End Sub


I don't like using inline SQL statements; I use stored procedures. SQL statements are really meant to run on the server because SQL statements are ran faster in SQL than in the code.

Also, the use of the try/catch/finally block ensures that if an error occured, it will close all connections so that none all left open.

We all have encountered the common "Object reference not set to an instance of an object" error crap. The try block helps to eliminate that generic error and displays the actual SQL or .NET error when one generates by the exception by the response.write(ex.ToString) statement.

The _error boolean variable is used for the redirect. It is set to true at first. If an error occurs, the try block catches it and set the _error to false. Once the SQL part is finished, the _error is checked for its value. And if true, it redirects; if false, it will show the exception at which the SQL had generated on the page.

No object reference generic error will ever show its ugly head again.

One last thing on the redirect, the url has "?view=". In some instances which I have occured, they are rare, yet they have popped up a few days ago, having view with no value will cause an error. 99% of the time it will not error out without a value passed in the url. Normally if the url parameter isn't being set to anything, it should be left off. If the parameter is being used on the redirect page itself checking to see if a value is present or not, this code will work:
If not Request.Params("view") is nothing then
'code will run when a view parameter exists
else
'code will run when it doesn't exist
End If

I hope this helps. :)