CODEWITHSUNDEEP
asp.netsqlvb.netms-access
Brian
Brian

Reputation: 2051

ASP.NET - VB.NET - Updating MS_Access table

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

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 !

Upvotes: 0

Views: 1141

Answers (2)

Steve
Steve

Reputation: 216363

  • 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

Upvotes: 1

Shrieks
Shrieks

Reputation: 141

(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.

Upvotes: 1

Related Questions