ASP.NET - VB.NET - Updating MS_Access table

Question

I'm trying to update a record from an Ms-Access table with VB.NET and ASP.NET. I'm getting 2 errors:

On the web page that's opened I'm getting Thread was being aborted
Web Developer 2010 gives me an error says there's an error in the UPDATE statement

This is the code so far:

Imports System.Data.OleDb


Partial Class ChangePassword
    Inherits System.Web.UI.Page

    Protected Sub btnChange_Click(sender As Object, e As System.EventArgs) Handles btnChange.Click

        Dim tUserID As String = Session("UserID")

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Brian\Documents\Visual Studio 2010\WebSites\WebSite3\db.mdb;")

        conn.Open()

        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [User] where UserID=?", conn)

        Dim cmd2 = New OleDbCommand("UPDATE USER SET [Password] = '" + txtConfPass.Text + "' where UserID = '" + tUserID + "'", conn)

        cmd.Parameters.AddWithValue("@UserID", tUserID)

        Dim read As OleDbDataReader = cmd.ExecuteReader()
        Dim read2 As OleDbDataReader = cmd2.ExecuteReader()


        lblUser.Text = tUserID.ToString
        lblUser.Visible = True

        If read.HasRows Then
            While read.Read()

                If txtOldPass.Text = read.Item("Password").ToString Then

                    cmd2.ExecuteNonQuery()

                    lblPass.Visible = True


                End If
            End While

        Else
            lblPass.Text = "Invalid Password."
            lblPass.Visible = True

        End If

        conn.Close()

        lblPass.Text = tUserID.ToString
        lblPass.Visible = True

Any help would be appreciated.

Thanks !

Well, there are some problems here. Your password setting SQL is subject to SQL injection attacks, and this is all very old-school. Have you considered using SQL Server Express for this? It's really much better suited. — Robert Harvey
– Robert Harvey, Commented Mar 28, 2012 at 16:54

Steve · Accepted Answer · 2012-03-28 17:53:00Z

1

First, your cmd2 fails because USER is a reserved word. Enclose in square brackets as you already do in the first OleDbCommand.
Second, to execute a statement like UPDATE, INSERT, DELETE you call cmd2.ExecuteNonQuery not ExecuteReader. Don't really needed that call after the first for cmd.
Third, in the first OleDbCommand (cmd) you use a parameter for UserID, why in the second one you revert to string concatenation for user and password? This opens the door to any kind of Sql Injection Attack.
Fourth, the Using statement assure that every Disposable object used in your code will be CLOSED thus freeing the memory used by this commands ALSO IN CASE OF EXCEPTIONS. An example of Using statement here

edited Mar 28, 2012 at 17:53

answered Mar 28, 2012 at 16:57

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brian Over a year ago

Steve - Ok I changed those but I think I'm getting an error regarding the session as it is telling me there's no value given for the cmd.

Brian Over a year ago

Also, on the web page when I run the web application it gives me "Thread was being aborted." What does that mean ?

Steve Over a year ago

Can only suggest to check everything about enabling Session State

Shrieks · Accepted Answer · 2012-03-28 17:01:59Z

(1)

Dim read2 As OleDbDataReader = cmd2.ExecuteReader()

and then

(2)

cmd2.ExecuteNonQuery()

Remove (1) - ExecuteNonQuery should do the update.

USER is a keyword in Access, add brackets the same way you have added in the Select statement. Next time, you are faced with a similar problem, print out the statement as Access would see it and try executing it on the database directly - that will point out the errors accurately.

Please use place holders for the update statement similar to the select statement.

Collectives™ on Stack Overflow

ASP.NET - VB.NET - Updating MS_Access table

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related