[blueberrys42]

I have problem to insert data to access database. So far I have this code :

Public Class SignUpForm

    Dim cnnOLEDB As New OleDbConnection
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim cmd As OleDbCommand

    Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\QuizVBdb.accdb"

    Private Sub SubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
        Dim usernamestring, passwordstring, firstnamestring, lastnamestring, coursestring, sectionstring As String
        Dim InsertQuery As String

        usernamestring = UsernameTextBox.Text
        passwordstring = PasswordTextBox.Text
        firstnamestring = FirstnameTextBox.Text
        lastnamestring = LastnameTextBox.Text
        coursestring = CourseTextBox.Text
        sectionstring = SectionTextBox.Text

        InsertQuery = "INSERT INTO logintable (username,password,firstname,lastname,course,section) VALUES ('" & usernamestring & "','" & passwordstring & "','" & firstnamestring & "','" & lastnamestring & "','" & coursestring & "','" & sectionstring & "')"

        cmd = New OleDbCommand(InsertQuery, strConnectionString)

        strConnectionString.Open()
        cmdOLEDB = New OleDbCommand(InsertQuery, strConnectionString)
        cmdOLEDB.ExecuteNonQuery()
        strConnectionString.Close()

    End Sub

    Private Sub CancelButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelButton.Click

        LoginForm.Show()
        Me.Close()

    End Sub
End Class

but it have a error at

cmd = New OleDbCommand(InsertQuery, strConnectionString)

"Unable to cast object of type 'System.String' to type 'System.Data.OleDb.OleDbConnection'."

any suggestions are appreciated. Thanks in advance.

[CharlieMay]

Move where you're dimming the strconnectionstring up above the cnnoledb so that you can use it for the connection.

   Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\QuizVBdb.accdb"

    Dim cnnOLEDB As New OleDbConnection(strConnectionString)
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim cmd As OleDbCommand

Then change all occurrences of strConnectionString in your remaining code to cnnOLEDB

You are trying to use nothing more than a string as the connection. You need to pass the string to the OleDBConnection object (cnnOLEDB) and then use that connection where needed.

[blueberrys42]

CharlieMay, on 18 October 2010 - 03:26 AM, said:

Move where you're dimming the strconnectionstring up above the cnnoledb so that you can use it for the connection.

   Dim strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\QuizVBdb.accdb"

    Dim cnnOLEDB As New OleDbConnection(strConnectionString)
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand
    Dim cmd As OleDbCommand

Then change all occurrences of strConnectionString in your remaining code to cnnOLEDB

You are trying to use nothing more than a string as the connection. You need to pass the string to the OleDBConnection object (cnnOLEDB) and then use that connection where needed.

Thanks, I have change my code but getting new error "Unrecognized database format 'H:\My Document\Visual Studio 2008\Projects\QuizTrial\QuizTrial\bin\Debug\QuizVBdb.accdb'."

Thank you again.

[CharlieMay]

.accdb doesn't work under jet 4.0,

Check out http://www.connectio...com/access-2007 for a valid connection string to connect to a 2007 database. (It will have something to do with ACE.12.0)

Also, while we're still working on this, lets get you started off on the right foot.

Instead of injecting your variables into your sql statement, let's look at changing your code to use parameters.

To use a parameter, add it with the variable included.

    Private Sub SubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
        Dim usernamestring, passwordstring, firstnamestring, lastnamestring, coursestring, sectionstring As String
        Dim InsertQuery As String

        InsertQuery = "INSERT INTO logintable 
(username, [password], firstname, lastname, course, section) VALUES (@user,@pw,@fname,@lname,@course,@section)
        cmd = New OleDbCommand(InsertQuery, strConnectionString)
        cmd.parameters.addwithvalue("@user",usernametextbox.text)
        cmd.parameters.addwithvalue("@pw",PasswordTextBox.Text)
        cmd.parameters.addwithvalue("@fname", FirstNameTextBox.Text)
        ... Continue on in this fashion to complete the rest of the parameters.

        strConnectionString.Open()
        cmdOLEDB = New OleDbCommand(InsertQuery, strConnectionString)
        cmdOLEDB.ExecuteNonQuery()
        strConnectionString.Close()

This not only makes the code easier to read but secures it against SQL injections. It also helps when you have strings containing things like apostrophes (') which will break your code if you don't handle them.

This post has been edited by CharlieMay: 18 October 2010 - 05:51 AM

[blueberrys42]

Thanks again. i tried change my code like you suggest but now it said "Syntax error in INSERT INTO statement."
I have refer to others post but still getting this error.

Public Class SignUpForm


    Dim cnnOLEDB As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\QuizVBdb.accdb;")
    Dim cmdOLEDB As New OleDbCommand
    Dim cmdInsert As New OleDbCommand
    Dim cmdUpdate As New OleDbCommand
    Dim cmdDelete As New OleDbCommand

    
    Private Sub SubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
        'Dim usernamestring, passwordstring, firstnamestring, lastnamestring, coursestring, sectionstring As String
        Dim InsertQuery As String

        InsertQuery = "INSERT INTO logintable (username,password,firstname,lastname,course,section) VALUES (@user,@pw,@fname,@lname,@course,@section)"

        Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)
        cmd.Parameters.AddWithValue("@user", UsernameTextBox.Text)
        cmd.Parameters.AddWithValue("@pw", PasswordTextBox.Text)
        cmd.Parameters.AddWithValue("@fname", FirstnameTextBox.Text)
        cmd.Parameters.AddWithValue("@course", CourseTextBox.Text)
        cmd.Parameters.AddWithValue("@section", SectionTextBox.Text)

        cnnOLEDB.Open()
        cmdOLEDB = New OleDbCommand(InsertQuery, cnnOLEDB)
        cmdOLEDB.ExecuteNonQuery()
        cnnOLEDB.Close()

    End Sub
End Class

[CharlieMay]

Ahh, you didn't catch the minor change I made
enclose password inside of [] as it is a keyword

So try:

InsertQuery = "INSERT INTO logintable (username,[password],firstname,lastname,course,section) VALUES (@user,@pw,@fname,@lname,@course,@section)"

[blueberrys42]

I have try it but same error happen.

        InsertQuery = "INSERT INTO logintable (username,[password],firstname,lastname,course,section) VALUES (@user,@pw,@fname,@lname,@course,@section)"

        Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)
        cmd.Parameters.AddWithValue("@user", UsernameTextBox.Text)
        cmd.Parameters.AddWithValue("@pw", PasswordTextBox.Text)
        cmd.Parameters.AddWithValue("@fname", FirstnameTextBox.Text)
        cmd.Parameters.AddWithValue("@lname", LastnameTextBox.Text)
        cmd.Parameters.AddWithValue("@course", CourseTextBox.Text)
        cmd.Parameters.AddWithValue("@section", SectionTextBox.Text)

        cnnOLEDB.Open()
        cmdOLEDB = New OleDbCommand(InsertQuery, cnnOLEDB)
        cmdOLEDB.ExecuteNonQuery()
        cnnOLEDB.Close()

[CharlieMay]

OK, let's try this. I think you need to open the connection prior to setting the command. And the cmdOLEDB = New OleDbCommand(InsertQuery, cnnOLEDB) is duplicated which might be causing a problem.

Move the cnnOLEDB.Open() up above the first Dim cmd As OleDBCommand = new OLEDB.Command(InsertQuery, cnnOLEDB)

Then get rid of the line cmdOLEDB = New OleDbCommand(InsertQuery, cnnOLEDB)

Also, it appears that section is a keyword also, so wrap it in square brackets like you did with password.

This post has been edited by CharlieMay: 18 October 2010 - 05:53 PM

[blueberrys42]

oh. Thank you very much. now I can insert the data. Thanks again.
can you tell me how to define which one the keyword?

This post has been edited by blueberrys42: 18 October 2010 - 06:02 PM

[CharlieMay]

keyword?

The keyword is a word used in the language that might confuse the sql statement. To alleviate any issue, you can do what some do and always enclose your table names in square brackets or others put the prefix tbl onto their tables in the database this keeps you from using table names that might interfere with actual SQL keywords.

For example if you had a table called SELECT it could confuse the query because SELECT is a keyword used to select rows in an sql statement.

BTW, did you change the code you showed right above my posts because if you haven't there are other issues.

Nevermind, I was looking at the wrong post

This post has been edited by CharlieMay: 18 October 2010 - 06:13 PM

[blueberrys42]

This code work for me.

Private Sub SubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitButton.Click

        Dim InsertQuery As String

        InsertQuery = "INSERT INTO logintable (username,[password],firstname,lastname,course,[section]) VALUES (@user,@pw,@fname,@lname,@course,@section)"

        cnnOLEDB.Open()
        Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)
        cmd.Parameters.AddWithValue("@user", UsernameTextBox.Text)
        cmd.Parameters.AddWithValue("@pw", PasswordTextBox.Text)
        cmd.Parameters.AddWithValue("@fname", FirstnameTextBox.Text)
        cmd.Parameters.AddWithValue("@lname", LastnameTextBox.Text)
        cmd.Parameters.AddWithValue("@course", CourseTextBox.Text)
        cmd.Parameters.AddWithValue("@section", SectionTextBox.Text)

        cmd.ExecuteNonQuery()
        cnnOLEDB.Close()
        MessageBox.Show("Signup complete.")
        LoginForm.Show()
        Me.Close()

    End Sub

[Unixguy32]

code works great!!!

[DimitriV]

The last post in this thread was 2010. blueberrys42 hasn't been online since Feb 15 2011 03:32 AM.