locked
Data no longer going into my Access Database RRS feed

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

    Sunday, April 11, 2010 12:06 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 11, 2010 12:50 PM
  • 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
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 11, 2010 2:33 PM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 11, 2010 12:50 PM
  • 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
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 11, 2010 2:33 PM