Data no longer going into my Access Database

Question

User1118300756 posted

Hi All.

So I have created a log-in screen that allows you to create a user at the same time. I wanted to make sure that if there was already a UserID registered that if a user tried to register with the same ID that an error message would show rather than my whole website crashing.

This is my code:

    Protected Sub btnCreateProfile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreateProfile.Click
        Dim DataConnection As New OleDbConnection
        Dim DataCommand As New OleDbCommand
        Dim strSQL As String

        DataConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/VisageDatabase.mdb"))

        strSQL = "SELECT UserID FROM [User] WHERE UserID='" & txtUniversityIDNew.Text & "'"

        DataCommand = New OleDbCommand(strSQL, DataConnection)
        DataConnection.Open()

        Dim DBReader As OleDbDataReader = DataCommand.ExecuteReader()

        If DBReader.Read() Then

            If txtUniversityIDNew.Text <> DBReader("UserID") Then
                Dim strSQL2 As String

                strSQL2 = "INSERT INTO [User] (UserID, FirstName, LastName, [Password]) VALUES (?,?,?,?)  "

                DataCommand = New OleDbCommand(strSQL2, DataConnection)

                DataCommand.CommandType = CommandType.Text
                DataCommand.Parameters.AddWithValue("@UserID", txtUniversityIDNew.Text)
                DataCommand.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                DataCommand.Parameters.AddWithValue("@LastName", txtLastName.Text)
                DataCommand.Parameters.AddWithValue("@Password", txtPasswordConfirm.Text)


                DataCommand.ExecuteNonQuery()
                Session("UserID") = txtUniversityIDNew.Text
                Session("FirstName") = txtFirstName.Text
                Session("LastName") = txtLastName.Text


            Else
                lblErrorMessage.Text = "This University ID is already registered at Visage."
            End If
        End If
        DBReader.Close()
        DataConnection.Close()
        If Session("UserID") <> "" Then Response.Redirect("Preferences.aspx")


    End Sub

The code to place the data into the database works fine by itself but when I put it into this if statement the data no longer goes into the database. 

If this the correct way to do this for making sure that a user cannot create a profile with a user id that is already present or is there a simpler way? I have tried to do it with a customvalidator but that did not work at all.

Thanks.

Answer 1

User-1460196090 posted

Hello,

It works for me on the following way

Dim con As New OleDbConnection(ConfigurationManager.ConnectionStrings("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/VisageDatabase.mdb"))
Dim cmd As New OleDbCommand("select * from [User] where UserID='" & txtUniversityIDNew.Text & "'", con)
Using con
    con.Open()
    Dim dr As SqlDataReader = cmd.ExecuteReader()
    If dr.HasRows Then
        ''ID ALREADY EXISTS
    Else
        ''DO INSERT HERE
    End If
End Using

try to modify it for your scenario and if you want to check if the record already exists in database when you use OleDbDataReader, do that using the DBReader.HasRows property.

Hope this helps,
Hajan

Answer 2

User-1199946673 posted

First of all, why are no using parameters in the SELECT query, as opposed to the INSERT query? When you want to know if a value exist, it's a kind of overkill to use a datareader.. Use the followng command:

SELECT COUNT(*) FROM [user]  WHERE userID = @userID

This will return 0 (the userID doesn't exist) or 1 (it does exist). Use ExecuteScalar to retrieve this value. But I would not use a seperate SELECT statement at all if I want to know if the userID already exists. I simply would execute the Insert command in a Try Catch Block. If userID is the Primary Key, the Insert will fail if the UserID already exists:

 

                Try
                    DataConnection.Open()
                    DataCommand.ExecuteNonQuery()
                Catch ex As OleDbException
                    If ex.ErrorCode = -2147467259 Then
                        'Duplicate Key, the UserID already exist
                        lblErrorMessage.Text = "This University ID is already registered at Visage."
                    End If
                End Try