SQL Update Statement Appears Successful But Does Nothing

The code listed below executes, appears to be successful, and will even return a number of rows affected, but will not actually update the information in the database.
Sub SaveChanges(ByVal Sender As Object, ByVal E As EventArgs)
        Dim constr As String = "Data Source=MSSQL-01;Initial Catalog=IDX_Download;User ID=sa;Password=3Ou$iedl"
        Dim connection As New System.Data.SqlClient.SqlConnection(constr)
        Dim cmdText As String = "UPDATE [Files] SET [DisplayName] = '" & txtDisplayName.Text & "', [FileName] = '" & txtFileName.Text & "', [Server] = '" & txtServer.Text & "', [FileSize] = '" & txtFileSize.Text & "', [DownloadCount] = '" & txtHits.Text & "' WHERE [ID] = '" & Request.QueryString("ID") & "'"
        Dim command As New System.Data.SqlClient.SqlCommand(cmdText, connection)
        
        connection.Open()
        command.ExecuteNonQuery()
        connection.Close()
        
        Response.Redirect("Default.aspx")
    End Sub

Open in new window

LVL 6
page1985Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
Oops never mind I did not scroll.

Try posting the contents of cmdText, I suspect that Request.QueryString("ID") is null.  Also, what is the data type for ID
0
 
Anthony PerkinsCommented:
It looks like you are missing a single quote.
This:
 Dim cmdText As String = "UPDATE [Files] SET [DisplayName] = '" & txtDisplayName.Text & "', [FileName] = '"
Should be:
 Dim cmdText As String = "UPDATE [Files] SET [DisplayName] = '" & txtDisplayName.Text & "', [FileName] = ''"
0
 
page1985Author Commented:
Request.QueryString("ID") is a 4 digit value passed by another page.  In this test instance, it's value is 1000.
the ID column is an identity/primary key with a value type of INT.
Currently, the following IDs exist in the database 1000 through 1005.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
can you provide your data before UPDATE and after UPDATE? also the parameters you passed to UPDATE statement.
0
 
page1985Author Commented:
Please clarify.
The code above is the entire subroutine.  
0
 
Anthony PerkinsCommented:
Again, I need you to post the contents of cmdText after the line:
Dim cmdText As String =  ...
0
 
Anthony PerkinsCommented:
Also, if the ID is an integer then there is no need to enclose it in single quotes as in:
WHERE [ID] = " & Request.QueryString("ID")
0
 
page1985Author Commented:
I've tried it with and without the apostraphe.  And the cmdText string is up there.  Scroll right.
0
 
Anthony PerkinsCommented:
>>I've tried it with and without the apostraphe.<<
I realize that is not the issue.  I was just pointing it out.

>>And the cmdText string is up there.  Scroll right.<<
No, it is not.  The code is there, I am asking that you look at the actual value of cmdText after asigning it.  If it is not obvious from that, than post it here and we can help.  I suspect I know what is the problem, but I would like you to see it for yourself.
0
 
page1985Author Commented:
Ok.  Here is the value of the string if I use ID=1001

UPDATE [Files]
SET [DisplayName] = 'Fedora 10 64-bit DVD',
    [FileName] = 'Fedora-10-x86_64-DVD.iso',
    [Server] = 'file1.sadc.biz',
    [FileSize] = '4172283904',
    [DownloadCount] = '1'
WHERE [ID] = '1001'

Open in new window

0
 
page1985Author Commented:
Additionally, I have a status field that tells me how many rows were updated.  When I submit this command, I get 1 row updated from the page, but the SQL Server doesn't show any changes and, obviously, the web app doesn't see the changes since they aren't actually committed.
0
 
page1985Author Commented:
You're welcome to take a look at the sandbox:
 
http://files.infinityenginegames.com/Downloads/Webtrends/Edit.aspx?ID=1001
There's no login.
0
 
SharathData EngineerCommented:
Are you able to update it directly in the database? Update the record directly in the database and check the result. My assumption is you are unable to send the changes to database and update.

SELECT * FROM Files WHERE ID  = 1001

Open in new window

0
 
page1985Author Commented:
Well, actually, the text boxes on the Edit page are filled using the SELECT statement you posted above.  Then, after the SqlDataReader has been given the Reader.Read(), the boxes are populated with box.Text = Reader.Item(Sql_Column_Name)
0
 
page1985Author Commented:
Point value has been increased.
0
 
Anthony PerkinsCommented:
If you are not getting any error and still not updating the database, then the only explanation is that you are updting a different table or a different database or a different server or any combination of the above.  There is no other explanation.

When you get this resolved, don't forget to lose the single quotes around the ID.  At least that way no one will waste your time pointing it out to you. :)
0
 
page1985Author Commented:
1) It's not hitting another server because there is only one server.
2) It's not hitting another database because the connection string specifically points to the WebStats database.
3) There is only 1 table in the database, so another table isn't an option either.
0
 
Anthony PerkinsCommented:
Then I am afraid I have no idea.
0
 
Anthony PerkinsCommented:
Did you even find a solution?
0
 
page1985Author Commented:
I did a while back.  I don't remember exactly what it was, but I believe it had something to do with the coding of the application.  Don't remember beyond just that I was extremely frustrated with myself over how simple it was.
0
 
Anthony PerkinsCommented:
Next time, help enhance the knowledgebase by posting your solution, regardless of how embarassing it turned out to be.
0
 
page1985Author Commented:
You're habitually condescending aren't you.
I didn't post the solution because I didn't think about it when I arrived at it and I forgot the details that would make posting it worth anything now.
0
 
Anthony PerkinsCommented:
>>I didn't post the solution because I didn't think about it when I arrived at it and I forgot the details that would make posting it worth anything now.<<
I understand, that is why I am asking you to remember next time so that we can all benfit from your experience.
0
 
page1985Author Commented:
Next time, give a little more thought to how you word things if you don't intend to come across as belittling someone.  Examples:
I realize that is not the issue.  I was just pointing it out.
If it's not an issue, why does it need to be pointed out?
Did you even find a solution?
Yes.  I'm not stupid.  Thanks for the vote of confidence.
When you get this resolved, don't forget to lose the single quotes around the ID.  At least that way no one will waste your time pointing it out to you.
Again, if it's not an issue, why is it a big deal.  No one else wasted time on it.
Again, I need you to post the contents of cmdText after the line:
Dim cmdText As String =  ...
The only thing I can say to this is, if I needed to ask you to clarify what you were asking, condescendingly restating the exact same request won't get a different response.
And my personal favorite from this thread:
I suspect I know what is the problem, but I would like you to see it for yourself.
If you know what the problem is, why not just come out and say it instead of treating me like I'm ignorant.  You don't need to teach me a lesson.  If you don't want to help, that's fine, don't reply to the question.
0
 
Anthony PerkinsCommented:
Fair enough.  Now let me give you my viewpoint.  You asked a question, we volunteered our time to attempt to help you and contrary to EE Guidelines you abandoned it. In future, if you want to participate on this site, then abide by the rules.

Got it?
0
 
page1985Author Commented:
All I got is an objection to the way you've handled yourself through the entirety of this question.  I, too, volunteer to answer questions on this forum, and I, too, strive to provide quality answers.
Either be nice to the people you deal with and treat them like intelligent human beings, or just leave them alone.
Once again, I ask that a moderator please close this thread.  There is no intelligent value left in the discussion going on here from either side of the fence.
0
 
Anthony PerkinsCommented:
I have never in any way treated you with anything but the utmost respect.  The fact that you have interpreted it that way is your choice and only yours.

But let us assume for a minute that I had treated you like an idiot.  Pray tell what has that got to do with this community and the EE Guidelines that you subscribe to every time you sign on.  Again, you asked a question, members (other then myself) volunteered their valuable time and against the EE Guidelines you abandoned the question for over 3 months and then proceeded to state you wanted to close the question without providing a solution (again against EE Guidelines).  My question to you is why are you doing this?  Do you not care for this community and feel you want to "punish" it?

If you feel my comments were in any way inappropriate at any time, it is as simple as posting a message in CS and reporting that fact.  Why abandon the question for three months and then close it without a solution?

Or even better still, I live in Texas, too, just pick up the phone (I am in the phone book) and tell me what a jerk I am and then get on with your life supporting this community.

I am sorry, but I fail to understand any logic in your thought process whatsoever and trust me I have tried very hard through out this thread.

I wish you the very best of luck.
0
 
page1985Author Commented:
It has nothing to do with the community or the guidelines.  It has to do with your conduct.  I didn't abandon the question.  I found a solution on my own because I didn't find one here.  I'm sorry, but my job is more important than stroking your ego.  If you want point credit, provide a solution.  If you're stumped, that's fine.  Don't get all upset that the question was closed after no one provided a solution.  I didn't "abandon" the question.  In fact, I tried to further encourage answers from more experts by increasing its point value.  The fact that this question did not get resolved on EE has nothing to do with me, you, or any other expert who did or didn't participate in it.
To those experts who provided honest input, thank you for your help.  This includes you.  You did give some decent input.  I'm not questioning anyone's help.  What I'm complaining about is that you seem to think that the EE guidelines are relevant to your conduct and they're not.  Frankly, what it comes down to is we're having a discussion about how I felt about the way you treated me and you felt that bringing up me failing a guideline by not posting my solution was a cardinal sin that outweighed your conduct.  It's not even related.  Fine, I didn't post a solution, slap my wrist and send me to the corner.  Duly noted, but irrelevant.
0
 
page1985Author Commented:
Let's just get over this and move on.  It's argued and done now.  I'll forgive and forget if you'll forgive and forget.
0
 
page1985Author Commented:
Provided good assistance, but not a solution.
0
 
Anthony PerkinsCommented:
I am afraid you have missed my point entirely.  I guess I am not surprised, it is just a further example of how you simply do not care for this community by blatantaly ignoring the guidelines and everything it stands for (and yes, any question without activity for 21 days is considered abandoned, whether you like it or not) and not prepared to bring up "my conduct" whatever that means, through the appropriate channels and at the appropriate time, rather then pollute this thread with irrelevant comments.

Again I wish you the best of luck and I trust I can be there for you when you need help in your next question.
0
 
page1985Author Commented:
Please do NOT respond to my questions in the future.
0
 
Anthony PerkinsCommented:
I am afraid you will have to fogive me, but I don't pay attention to the author.  You may have to remind me when you see me next time attempting to help you.
0
All Courses

From novice to tech pro — start learning today.