1. Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

User accounts in VB2005 from MS Access

Discussion in 'Programming & Webmastering' started by Akumos, Jan 26, 2009.

  1. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    535 (0.26/day)
    Thanks Received:
    27
    Location:
    Birmingham, England...
    No error message, just doesn't add to my database which is how I knew it didn't work.

    Will try your ammendments... and study the basics.
  2. iStink

    iStink New Member

    Joined:
    Sep 22, 2008
    Messages:
    648 (0.30/day)
    Thanks Received:
    49
    The reason I convert the textbox's contents into a string variable is to work with them easier and to give the user a sense of stability. We run the replace command against the variable instead of the string so we don't interfere with what the user entered (in case they mess up the password, we could accidentally throw them off even more.) For instance, if someone entered a password which contained an apostrophe, it would mess up the SQL statement if you pointed directly to the textbox. The SQL statement would cut off the password string prematurely because it uses apostrophes, not quotes, to wrap around text and indicate a string.

    Saying textbox1.text = replace(textbox1.text, "'", "''") will actually change text within the textbox, while saying textVar = replace(textbox1.text, "'", "''") will simply adjust the variable and the user won't see it.

    If you enter '' (double apostrophe) into an SQL statement, it gets treated as a single apostrophe within a string, instead of the single apostrophe used to wrap around strings. Know what I mean?

    As far as his error, it has to do with how he's executing the command. The code I posted is for a reader, now you need to use ExecuteNonQuery.

    The code should look something like this:

    Dim usernameString as string = usernameTextBox.text
    Dim passwordString as string = passwordTextBox.text

    usernameString = replace(usernameString, "'", "''")
    passwordString = replace(passwordString, "'", "''")

    Dim SQLString as string = "INSERT INTO USERS (USERNAME, PASSWORD) VALUES ('" & usernameString & '", '" & passwordString & "')"

    Dim dbPath = "C:\Whatever\Whatever.mdb"

    Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbPath

    Dim dbCon As New OleDbConnection(connectionString)

    Try

    dbCon.Open()

    Dim cmd As New OleDbCommand(SQLString, dbCon)
    cmd.ExecuteNonQuery() '<This is what you were missing.

    dbCon.Close()

    Catch ex As System.Exception

    MessageBox.Show("Error: " & vbNewLine & ex.Message)
    Exit Sub

    End Try


    Notice how here we don't need a data reader since we're simply inserting data into the database. There's nothing we are grabbing, just sending a command out to the database.
    Last edited: Jan 27, 2009
  3. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    535 (0.26/day)
    Thanks Received:
    27
    Location:
    Birmingham, England...
    Thank you both for your help. I am getting a 'Syntax error in INSERT INTO statement. Can you help?

    Dim sqlString As String = "INSERT INTO USERS (USERNAME, PASSWORD) VALUES ('" & usernameString & "', '" & passwordString & "')"
  4. iStink

    iStink New Member

    Joined:
    Sep 22, 2008
    Messages:
    648 (0.30/day)
    Thanks Received:
    49
    Access has a list of words not to use as column names, but if you must, you'll need to use brackets in your sql statement. I'm unsure of the list of words, but the line of code appears valid. Try using this:

    Dim sqlString As String = "INSERT INTO USERS ([USERNAME], [PASSWORD]) VALUES ('" & usernameString & "', '" & passwordString & "')"

    Perhaps its password that's causing the issue.

    You could also try renaming the columns within the access database. I ran into this issue when I used the word time as a column name.
    Akumos says thanks.
  5. Akumos

    Akumos

    Joined:
    Nov 30, 2008
    Messages:
    535 (0.26/day)
    Thanks Received:
    27
    Location:
    Birmingham, England...
    Brillitant, that line worked perfectly Thanks again iStink.
  6. DanTheBanjoman SeƱor Moderator

    Joined:
    May 20, 2004
    Messages:
    10,553 (2.81/day)
    Thanks Received:
    1,383
    I wasn't suggesting moving the result of the replace back to the textbox.

    More like Dim sqlString As String = "INSERT INTO USERS ([USERNAME], [PASSWORD]) VALUES ('" & replace(textbox1.text, "'", "''") & "', '" & replace(textbox2.text, "'", "''") & "')".

    And I think the whole issue was the closing bracket I forgot :) At least with Accdb (2007) files you don't have to use those [Access brackets] when accessing data from .NET. Not sure if that's forced with mdb files though.
  7. iStink

    iStink New Member

    Joined:
    Sep 22, 2008
    Messages:
    648 (0.30/day)
    Thanks Received:
    49
    Oh, yeah you're right Dan, you can do it that way just fine. In code like this when you aren't calling on the text a bunch of times, using it like that probably makes more sense. I get into the habit of using variables just so later on in the code it's easier to use them again. Such is the freedom and creativity of programming! lol

Currently Active Users Viewing This Thread: 1 (0 members and 1 guest)

Share This Page