Why does my update query doesnt work ?

Question

0.00/5 (No votes)

See more:

VB.NET

Private Sub updateww()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Administrator\Documents\Visual Studio 2010\Projects\St. Peter Academy\St. Peter Academy\App_Data\St.mdf;Integrated Security=True;User Instance=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "update " & dbname.Text & " set percentage='" & edwwp.Text & "', description='" & edwwd.Text & "' where componentid=1 "
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub

    Private Sub updatept()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Administrator\Documents\Visual Studio 2010\Projects\St. Peter Academy\St. Peter Academy\App_Data\St.mdf;Integrated Security=True;User Instance=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "update " & dbname.Text & " set percentage= '" & edptp.Text & "', description='" & edptd.Text & "' where componentid=2 "
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub
    Private Sub updateqa()
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Administrator\Documents\Visual Studio 2010\Projects\St. Peter Academy\St. Peter Academy\App_Data\St.mdf;Integrated Security=True;User Instance=True"
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "update " & dbname.Text & " set percentage='" & edqap.Text & "', description='" & edqad.Text & "' where componentid=3 "
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub

What I have tried:

my update isnt working . it does not show any errors but not updating

Posted 23-Dec-16 18:50pm

Member 12919944

Updated 23-Dec-16 23:22pm

Add a Solution

Comments

Dave Kreskowiak 24-Dec-16 1:16am

Without knowing the values that you're plugging into the query it's impossible to tell you.

I can say that you're using string concatenation to build the queries which opens your database to SQL Injection attacks and the complete destruction of your database.

Google for "SQL Injection attack" to find out why what you're doing is so bad.

Then Google for "VB.NET parameterized queries" to find out how to fix it AND make your code easier to debug.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-12-23T20:17:00

So many things to fix here....

The big one is: don't do it like that. Never concatenate strings to form an SQL command - it leaves you wide open to SQL Injection attacks where your users can damage or destroy your database just by going in textbox. Always use parameterized queries instead.

Secondly, do not hard code connection strings - always store them in a config file or similar so you don't have to change the program between development and release.

Thirdly, don't routinely attach databases - let SQL handle them itself instead - attaching is an Express version only and is a special developer mode that is much slower than SQL management.

Fourthly, SqlConnection and SqlCommand objects are scarce resources and should be Disposed when you are finished with them.

Now for the problem you have noticed ... We can't tell! It could be you data in the textboxes is causing a problem, it could be that there are no errors which match your condition. So start be fixing the other stuff throughout your application first, and if the problem is still happening after that, check your DB and make sure that the data is where you think it is, and what you think it is.

But if you don't fix the other stuff first, your DB will get destroyed - your best mate will do it just to see the look on your face...

Wendelius · Answer 2 · 2016-12-23T23:22:00

As said there are a lot of problems in the code
- concantenation of values leaves you open to SQL injection
- concantenation of values introduces conversion problems
- you don't use using blocks so Dispose may be omitted even if present
- you don't have any error handling
- connection string is statically embedded into a method
- not necessarily a problem but if these methods are used in a loop or with other DML statements then you're missing transactions and so on...

I suggest going through Properly executing database operations[^]

Why does my update query doesnt work ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0