Click here to Skip to main content
14,391,933 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi, im new to VB.net, im trying to insert my data to the database but i seem to keep getting error. What did i do wrong?

Imports System.Data.OleDb

Public Class frmRegister

    Dim provider As String
    Dim datafile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection
    Dim gender As String
    Dim age As Int32

    Private Sub BtnReg_Click(sender As Object, e As EventArgs) Handles btnReg.Click
        If txtFName.Text = "" Or txtUsername.Text = "" Or txtPassword.Text = "" Or txtEmail.Text = "" Then
            MsgBox("Please fill in the info")
        Else

            If radFemale.Checked Then
                gender = "Female"
            ElseIf radMale.Checked Then
                gender = "Male"
            End If

            age = DateTime.Today.Year - dateAge.Value.Year

            provider = "Provider=Microsoft.JET.OLEDB.4.0;Data Source="
            datafile = "C:\Users\User\Desktop\Part 5\CSC 301\Project\Flight\test.mdb"
            connString = provider & datafile
            myConnection.ConnectionString = connString
            myConnection.Open()
            Dim str As String
            str = "Insert into User ([Username],[Password],[Full Name],[Email],[Gender],[Birthdate],[Age]) Values (?,?,?,?,?,?,?) "
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            cmd.Parameters.Add(New OleDbParameter("Username", CType(txtUsername.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Password", CType(txtPassword.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Full Name", CType(txtFName.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Email", CType(txtEmail.Text, String)))
            cmd.Parameters.Add(New OleDbParameter("Gender", CType(gender, String)))
            cmd.Parameters.Add(New OleDbParameter("Birthdate", CType(dateAge.Value, String)))
            cmd.Parameters.Add(New OleDbParameter("Age", CType(age, String)))

            Try
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                myConnection.Close()
                txtEmail.Clear()
                txtFName.Clear()
                txtPassword.Clear()
                txtUsername.Clear()
                MsgBox("create success")
            Catch ex As Exception
                MsgBox("Error")
            End Try

        End If
    End Sub
End Class


What I have tried:

I changed my coding from a simpler way to connect it to the database to these, but stills getting the error
Posted
Updated 3-Dec-19 15:31pm
Comments
Richard Deeming 3-Dec-19 13:22pm
   
If you want us to help you fix an error, you need to tell us what the error is.

Click the green "Improve question" link and update your question to add the full details of the error you're getting. Remember to indicate which line of code it's thrown from.

NB: You'll probably need to change your Catch block, which currently ignores the exception and just displays a generic "Error" message. Have it display the exception details, or write the exception details to a log.
Wafiqque 3-Dec-19 20:16pm
   
Thanks, after I changed my Catch block, this is the error that I'm getting

Syntax error in INSERT INTO statement.
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at
Flight.frmRegister.BtnReg_Click(Object sender, EventArgs e) in C:\Users\User\Desktop\Part 5\CSC 301\Project\Flight\Flight\Register.vb:line 42
Richard Deeming 3-Dec-19 13:23pm
   
Also, you're storing passwords as plain text. Don't do that.

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]
MadMyche 3-Dec-19 13:38pm
   
It would be helpful if you provided the actual error....
There is a flaw in your logic, for the age calculation; but not enough to warrant an exception on it's own.
Richard Deeming 3-Dec-19 13:40pm
   
I'd say there's a flaw with storing the age in the database at all, given it's not a static value. :)
MadMyche 3-Dec-19 13:50pm
   
Maybe the intention is age at time of registration
Rate this:
Please Sign up or sign in to vote.

Solution 2

You could start with
Catch ex As Exception
   MsgBox(ex.Message + vbCrLf + ex.StackTrace)
This would at least give you a more meaningful message than a generic "Error".
   
Comments
Wafiqque 3-Dec-19 20:15pm
   
Thanks, this helps showing the real error message
Rate this:
Please Sign up or sign in to vote.

Solution 1

Quote:
What did i do wrong?

What is wrong is the try/catch in your code, the way you did it is hiding the real error message and position.

Advice: never use a try/catch while debugging code. A try/catch should be used only to handle special cases after you know that normal cases are handled correctly.
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

I fixed it by putting a square bracket on the table name, it seem to be a sql-injection if I'm not mistaken
   
Comments
Richard Deeming 4-Dec-19 6:00am
   
It's not a SQL Injection[^] issue. It's that User is a reserved word in MS Access:
List of reserved words in Access 2002 and in later versions of Access[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100