how to update entry in mysql table from asp.net?

Question

In mysql workbench, I can type

UPDATE contact_log
SET note = 'test1'
WHERE customer = 'customer'

and it will update the customer's note. WHen i try this in asp.net, it has no effect.

    Try
        conn.Open()
        cmd.Connection = conn
    Catch ex As Exception

    End Try
    cmd.CommandText = "UPDATE contact_log " +
                "SET note = '" & TextBox2.Text & "'" +
                "WHERE customer = '" & Request.QueryString("ID") & "'"
    reader = cmd.ExecuteReader()
    conn.Close()
    conn.Dispose()

Some facts are that the connection string is correct, I can use select and bring back data with no problem, and the request.querystring("ID") brings back the customer name.

Is there a better way to update a mysql table from asp.net, or a way that actually works?

score 4 · Accepted Answer · edited May 23 '17 at 11:56

4

Many problems in your code.

Do not use string concatenation to build sql commands, but parameterized query
Do not catch exceptions and swallow them
Use the appropriate using statement to close and dispose the connection
Of course an INSERT/UPDATE/DELETE statement requires ExecuteNonQuery

To summarize I would change your code to this

Dim cmdText = "UPDATE contact_log SET note = @note WHERE customer = @cust"
Using conn = new MySqlConnection(connString)
Using cmd = new MySqlCommand(cmdText, conn)
    conn.Open()
    cmd.Parameters.AddWithValue("@note",TextBox2.Text)
    cmd.Parameters.AddWithValue("@cust",Request.QueryString("ID"))
    Dim rowsAffected = cmd.ExecuteNonQuery()
End Using
End Using

Parameterized query are very important because you avoid Sql Injections and parsing problems with string containing quotes (You will get a syntax error if the TextBox2 contains a text with a single quote)

The Using Statement will ensure that youR connection is properly closed and disposed also in case of exceptions and you avoid dangerous memory leaks and get lower usage of system resources

The exception is better handled on a upper level of your code where you could show a message to your user or write in an error log. Catching an exception and doing nothing is very bad because you will never learn what is the reason of failure in your code.

edited May 23 '17 at 11:56

Community

1
1

answered Jul 15 '13 at 19:35

Steve

213,761
22
232
286

Thanks Steve, hadn't come across this yet. I'll try this out in a second... Edit: This did not update the table either... – Feign Jul 15 '13 at 19:43
The code should work if the conditions are met. Some suggestions: Check if the connection string is correct, check if the ID is valid (there is a customer with the passed ID), check the value of rowsAffected (should be 1 if ID=one valid customer) – Steve Jul 15 '13 at 19:48
When I output rowsAffected, I get 1... But nothing is changing in my table. I am certain that the connection string is correct, and select * from contact_log where customer = 'the same id in the querystring' brings back the customer I want. – Feign Jul 15 '13 at 19:52
If rowsAffected is 1 then the query executed correctly. I have to ask, is it possible that you are looking to a different copy of your database? – Steve Jul 15 '13 at 19:58
I only have my localhost database, is there a correct way to refresh tables maybe? – Feign Jul 15 '13 at 20:06
Another possibility for this behavior is related to an open transaction not correctly committed. – Steve Jul 15 '13 at 20:10
I decided to try this in another application, and it worked... Thanks for your help Steve! Now to find what's causing the problem in my original application... – Feign Jul 15 '13 at 20:28

score 1 · Answer 2 · answered Jul 15 '13 at 19:27

1

You're using the wrong command... You're WRITING to the database, not reading from it - You need to change from using a reader to an execution command....

Try this:

cmd.CommandText = "UPDATE contact_log " +
            "SET note = '" & TextBox2.Text & "'" +
            "WHERE customer = '" & Request.QueryString("ID") & "'"
cmd.ExecuteNonQuery()
conn.Close()
conn.Dispose()

answered Jul 15 '13 at 19:27

John Bustos

19,036
17
89
151

Thanks for the response, but this didn't work for me... The table shows no changes after I try to refresh it. – Feign Jul 15 '13 at 19:32
I have no idea why this wouldn't work for you... I've done this hundreds of times in the past... Are you sure all else is correct?? ... In debug mode, after your CommandText line, right before running the `ExecuteNonQuery` line, does the `cmd` object look correct? If you were to copy / paste the commandtext to MySQL Workbench would it work? Is the connection ok??? – John Bustos Jul 15 '13 at 19:36
Yeah, this works perfectly fine in workbench, and as for the connection, I've been connecting this way for other queries, and it's all been working fine. But, I've only been using select queries. – Feign Jul 15 '13 at 19:46
Does the connection / user you're using have read/write permission?? I just can't think as to why this would not work otherwise.... – John Bustos Jul 15 '13 at 20:00
Yes, all permissions. I also have no clue why this won't work haha – Feign Jul 15 '13 at 20:10
John, you are right, this does work. I tried this is a new application and it worked. I guess something is up with my conn... THanks for the help :) – Feign Jul 15 '13 at 20:28

how to update entry in mysql table from asp.net?

2 Answers2