Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

syntax error with my UPDATE statement ?

Status
Not open for further replies.
c0deM0nK424

c0deM0nK424

Programmer
Oct 28, 2007
126
GB
First off, to give u an idea as to what my implementation looks like, view this screenshot link below.


I'm bamboozled as to why updates arent performed. Insert and Delete work fine.

The whole thing is implemented using the repeater control, and i've cleverly assigned command name buttons to appear as well. If I'm not mistaken, once upon a 'few moments in 2008' I 'did something' which was making UPDATE work fine !

THEN thinking i'd resolved the issue, i tried to get ahead of myself and started playing around with the code by adding checkboxes int the repeater (dont ask why I do strange things sometimes...) and when i tried to undo one too many changes, i was back to square one. No update happening. :|

WHAT have i done/not done ?

The actual sub routine thats fired when the update command button is clicked is as follows:

<script runat="server">

Sub Update_Record(ByVal Src As Object, ByVal Args As RepeaterCommandEventArgs)
' subroutine that is called by onItemCommand, i.e when the submit buttons are clicked.

Dim Stock_ID As TextBox = Args.Item.FindControl("Stock_ID")
Dim Stock_Name As TextBox = Args.Item.FindControl("Stock_Name")
Dim Quantity As TextBox = Args.Item.FindControl("Quantity")
Dim Price As TextBox = Args.Item.FindControl("Price")


If Args.CommandName = "Insert" Then
' if the button happened to be insert
' then
Dim myquery As String = "INSERT INTO Stock_details (Stock_Name , Quantity, Price) VALUES ('" & Stock_Name.Text & "', '" & Quantity.Text & "', '" & Price.Text & "')"
' assign the following insert query to 'myquery


StockInfo.InsertCommand = myquery
StockInfo.Insert()

Response.Redirect("Default.aspx")
' End If
ElseIf Args.CommandName = "Update" Then
' If Args.CommandName = "Update" Then



' Dim myquery As String = "UPDATE Stock_details SET " & _
' "Stock_Name = '" & Stock_Name.Text & "', " & _
' "Quantity = '" & Quantity.Text & "', " & _
' "Price = '" & Price.Text & "', " & _
' "WHERE Stock_ID = '" & Stock_ID.Text & "'"
Dim myquery As String = "UPDATE Stock_details SET & Stock_Name = '" & Stock_Name.Text & "', & Quantity = '" & Quantity.Text & "', & Price = '" & Price.Text & "', WHERE Stock_ID = '" & Stock_ID.Text & "'"

StockInfo.UpdateCommand = myquery
StockInfo.Update()

Response.Redirect("Default.aspx")
' End If
ElseIf Args.CommandName = "Delete" Then


' If Args.CommandName = "Delete" Then

Dim myquery As String = "DELETE FROM Stock_details WHERE Stock_ID = " & Stock_ID.Text

StockInfo.DeleteCommand = myquery
StockInfo.Delete()

Response.Redirect("Default.aspx")
End If



End Sub
</script>


I commented out some of the end if's and replaced them with else if's, both do the job but it doesnt change anything, update button causes an error heh.

does the update query look okay to you guys ??
 
Sort by date Sort by votes
Upvote 0 Downvote
ca8msm, the comma that appears after '" & Price.Text & " was ommited.

I still get an error statement. As for the sql injection attack(s), i'll look into that sometime.
 
Upvote 0 Downvote
What do you mean it was omitted? Do you mean it is not part of the code that is causing the problem? If so, please paste the real code that you are using as it makes it harder if we have to guess what your code is actually doing.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers
 
Upvote 0 Downvote
There is an extra comma before the WHERE clause that will make the statement invalid. "

I made an alteration on my own code, the code pasted above contains the UPDATE query that you pointed out as being problematic by mentioning the 'extra comma'.

By omitting that coma, nothing was resolved. That is all I meant.
 
Upvote 0 Downvote
OK, so what happens when you debug and run the sql code directly against the database? What does the executed statement look like? Are any rows updated?

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers
 
Upvote 0 Downvote
Hi,
You could add an Label control and set its text to myquery to see what is actually being sent to the database.

It is how I debug concatenated strings.



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
Turkbear said:
Hi,
You could add an Label control and set its text to myquery to see what is actually being sent to the database.

It is how I debug concatenated strings.
Click to expand...
Rather than having to change your code, you can just use a breakpoint and either:

1. Hover your mouse over the "myquery" string and it will show you the contents.
2. Use the immediate window and type "? myquery" (without the quotes" to see the contents.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers
 
Upvote 0 Downvote
Unfortunately no rows are updated, I get the syntax error in UPDATE statement.

I'm completely stumped mate. I thought maybe it was to do with the single quotes surrounding the quantity and price textbox fields (since these are not string literal values, but integer values), but i still cant update.

Something, somewhere in my update statement looms an obvious error heh. But what and where is it ? :|





 
Upvote 0 Downvote
Upvote 0 Downvote
Upvote 0 Downvote
notice in the original screenshots row 12 (Stock_ID 12), quanity holds value of 111.

myquery showed you that I attempted to change it to 122.

heh
 
Upvote 0 Downvote
HEEEEEEEY ! :p

EVERYONE, ive solved the issue.

i think it was an incorrect data type mismatch/blah blah blah, i 'think' so anyway.

i decided to give this a try, even though I HAD once made this code work using my own convention of apostrophes etc.


well it works wonderfuly well now.

Dim myquery As String = "UPDATE Stock_details SET Stock_Name = '" + Stock_Name.Text + "', Quantity = " + Quantity.Text + ", Price = " + Price.Text + " WHERE Stock_ID = " + Stock_ID.Text + "" does the trick it seems.

^^
 
Upvote 0 Downvote
YOu should never write inline code to access the db. As Mark said there is a good chance of sql injection. You should use stored procedures with parameters.
 
Upvote 0 Downvote
I know that jbenson001, this was 'play about code' - i intend to change the entire thing, infact im doing so right now by creating stored procedures with sql server heh.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

G12Consult
  • Locked
  • Question
Help with Json
Replies
2
Views
397
gmmastros
gmmastros
JohnB33
  • Locked
  • Question
int type causes error
Replies
0
Views
330
JohnB33
JohnB33
weberm
  • Locked
  • Question
How do I make a page with multiple AJAX custom controls?
Replies
2
Views
379
weberm
weberm
WIREMESH
  • Locked
  • Question
ASP.NET 2.0 Webform compilation error
Replies
1
Views
140
jbenson001
jbenson001
baran121
  • Locked
  • Question
Sending data with bluetooth on webproject
Replies
1
Views
135
jbenson001
jbenson001

Part and Inventory Search

Sponsor

Back
Top