CODEWITHSUNDEEP
databasevb.netms-access
Metanore
Metanore

Reputation: 1

'Characters found after end of SQL statement.'

First I'd like to clarify that I'm trying to code a registration form which inserts whatever the user has typed into the textboxes, into the fields of the access database. However, I ran into a weird error that tells me to insert a semicolon after the SQL statement, which I did and thus received another error telling me it was an invalid character. To resolve that, I inserted the semicolon correctly... after the ) sqlRegister = sqlRegister & txt_email.Text & "' );"

Now, I get a new error: System.Data.OleDb.OleDbException: 'Characters found after end of SQL statement.'

Here is my module for runSQL:

Public Function runSQL(ByVal query As String) As DataTable 'connection variable is declared to establish connection to database
    Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=library_db.accdb")
    'dt variable is declared as a new table where data from the access database will be stored
    Dim dt As New DataTable 'variable declared to search through list of records in access database
    Dim DataAdapter As OleDb.OleDbDataAdapter

    Connection.Open() 'Initialise the connection to the access database
    DataAdapter = New OleDb.OleDbDataAdapter(query, Connection) 'Retrieves records as instructed by query
    dt.Clear() 'The data of the data table is cleared and filled with data retrieved by the DataAdapter
    DataAdapter.Fill(dt)
    Connection.Close() 'Connection to the access database is closed to reduce the number of open connections
    Return dt
End Function 
End Module

This is my registration form:

        username = txt_user.Text
        password = txt_pass.Text
        email = txt_email.Text

        If txt_user.Text = "" Or txt_pass.Text = "" Or txt_email.Text = "" Or emailCheck(txt_email.Text) Then
            MsgBox("Please fill in all boxes in order to register")
        Else
            sqlRegister = "Insert into accounts(userID, "
            sqlRegister = sqlRegister & "Username, [Password], Email) values (' "
            sqlRegister = sqlRegister & userID & "', '"
            sqlRegister = sqlRegister & txt_user.Text & "', '"
            sqlRegister = sqlRegister & txt_pass.Text & "', '"
            sqlRegister = sqlRegister & txt_email.Text & "');'"
            runSQL(sqlRegister)
            MessageBox.Show("Registered!")

        End If

Upvotes: 0

Views: 3053

Answers (2)

Gustav
Gustav

Reputation: 55981

Accept the error message and remove the character(s):

sqlRegister = sqlRegister & txt_email.Text & "');"

Upvotes: 0

Mary
Mary

Reputation: 15091

To keep your data access code and your user interface code separate, you should not build sql queries in the user interface just pass the data to the data access code.

Connections and several other classes provided by ADO.net use unmanaged code internally. They have .Dispose methods to release these resources. The coder is responsible for calling the .Dispose method. Fortunately .net provides Using...End Using blocks to handle this for us.

You are not retrieving data so there no need for a DataTable or DataAdapter. Always use Parameters to avoid sql injection. When you have an auto-number field, you do not pass any value. This value is provided by the database. You will have to check the datatype and size of the fields in your database because I had to guess. You are inserting a user so you want to .ExecuteNonQuery not .Fill a datatable.

It appears you are saving passwords as plain text. This should NEVER be done even in sample code but that is for your research.

Public Sub InsertUser(UName As String, PWord As String, EMail As String)
    Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=library_db.accdb"),
            cmd As New OleDbCommand("Insert Into accounts (UserName, [Password], Email) Values (@UName, @PWord, @Mail);", cn)
        cmd.Parameters.Add("@UName", OleDbType.VarChar, 100).Value = UName
        cmd.Parameters.Add("@PWord", OleDbType.VarChar, 100).Value = PWord
        cmd.Parameters.Add("@Mail", OleDbType.VarChar, 200).Value = EMail
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Private Sub OPCode()
    If txt_user.Text = "" OrElse txt_pass.Text = "" OrElse txt_email.Text = "" OrElse emailCheck(txt_email.Text) Then
        MsgBox("Please fill in all boxes in order to register")
        Exit Sub
    End If
    Try
        InsertUser(txt_user.Text, txt_pass.Text, txt_email.Text)
        MessageBox.Show("Registered!")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Upvotes: 2

Related Questions