Insert into

[student21]

Hi i have written my code to to insert data that has been entered into my textboxes n my form into my access database.
i am gettin an error message saying: OLEDBEXEPTION OCCURED...Syntax error in INSERT INTO statement.

i was wondering if someone could look over my code and tell me where im going wrong
the code is written for when the button on the form is pressed.

my code is...

OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection

        OleDbInsertCommand1.CommandText = "INSERT INTO Admin,(Employee_Forename, Employee_Surname, Admin_Username, Admin_Password) VALUES (Employee_ForenameTextBox.Text, Employee_SurnameTextBox.Text, Admin_UsernameTextBox.Text, Admin_PasswordTextBox.Text)"
        OleDbInsertCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydatabase.mdb"

        Try

            OleDbConnection1.Open()

            OleDbInsertCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()

[kvprajapati]

Change commandtext.

OleDbInsertCommand1.CommandText = "INSERT INTO Admin (Employee_Forename, Employee_Surname, Admin_Username, Admin_Password) VALUES (@p1,@p2,@p3,@p4)"

OleDbInsertCommand1.Connection = Me.OleDbConnection1

OleDbInsertCommand1.Parameters.AddWithValue("@p1",Employee_ForenameTextBox.Text)
OleDbInsertCommand1.Parameters.AddWithValue("@p2", Employee_SurnameTextBox.Text)
OleDbInsertCommand1.Parameters.AddWithValue("@p3", Admin_UsernameTextBox.Text)
OleDbInsertCommand1.Parameters.AddWithValue("@p4", Admin_PasswordTextBox.Text)
....

[student21]

hi thanks for the help but i change my command text to the code you gave me but i am faced with the following error..

Error	1	'AddWithValues' is not a member of 'System.Data.OleDb.OleDbParameterCollection'.	C:\Users\me\Documents\System\System\System\CreateNewAdmin.vb	57	9	System

[kvprajapati]

I am sorry! Please use AddWithValue method.

[student21]

thank you, im quite new to vb.net i have altered my code to

OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection
        OleDbInsertCommand1.CommandText = "INSERT INTO Admin (Employee_Forename, Employee_Surname, Admin_Username, Admin_Password) VALUES (@p1,@p2,@p3,@p4)"
        OleDbInsertCommand1.Connection = Me.OleDbConnection1
        OleDbInsertCommand1.Parameters.AddWithValue("@p1", Employee_ForenameTextBox.Text)
        OleDbInsertCommand1.Parameters.AddWithValue("@p2", Employee_SurnameTextBox.Text)
        OleDbInsertCommand1.Parameters.AddWithValue("@p3", Admin_UsernameTextBox.Text)
        OleDbInsertCommand1.Parameters.AddWithValue("@p4", Admin_PasswordTextBox.Text)
        OleDbInsertCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydatabase.mdb"

        Try

            OleDbConnection1.Open()

            OleDbInsertCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()

i get no error message, but nothin inserts into my access db?

[kvprajapati]

Check the database (.mdb) located under Bin\Debug folder.

[student21]

i have checked the database (.mdb) located under Bin\Debug folder and the new inforamtion has saved in there but when i run the app again and login with the new data that was saved or search for it it is no longer there? have i done something wrong?

[kvprajapati]

From the solution explorer (visual studio project) select a .mdb database file and set file property - Copy to Output Directory = Copy if newer.

[student21]

thank you i think it is working now...
i have copied the same code for the delete part

OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection
        OleDbDeleteCommand1.CommandText = "DELETE FROM `Employees` WHERE ((`EmployeeID` = ?) AND ((? = 1 AND `DepartmentID` IS NULL) OR (`DepartmentID` = ?)) AND ((? = 1 AND `Forename` IS NULL) OR (`Forename` = ?)) AND ((? = 1 AND `Surname` IS NULL) OR (`Surname` = ?)) AND ((? = 1 AND `Role` IS NULL) OR (`Role` = ?)) AND ((? = 1 AND `Email` IS NULL) OR (`Email` = ?)) AND ((? = 1 AND `Telephone` IS NULL) OR (`Telephone` = ?)) AND ((? = 1 AND `Extension` IS NULL) OR (`Extension` = ?)) AND ((? = 1 AND `Mobile` IS NULL) OR (`Mobile` = ?)))"


        OleDbDeleteCommand1.Connection = Me.OleDbConnection1
        OleDbDeleteCommand1.Parameters.AddWithValue("@p1", ForenameTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p2", SurnameTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p3", RoleTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p4", EmailTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p5", TelephoneTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p6", ExtensionTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p7", MobileTextBox.Text)
        OleDbDeleteCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Wincanton.mdb"

        Try

            OleDbConnection1.Open()

            OleDbDeleteCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()

but for the "@p1" etc i have a lot of parameters so would i just put "?" because the code at the moment is telling me that on the executenonquery line that No value given for one or more required parameters.

[kvprajapati]

Don't use complex delete sql statement.

OleDbDeleteCommand1.CommandText = "DELETE FROM Employees WHERE EmployeeID = @p1"

OleDbDeleteCommand1.Connection = Me.OleDbConnection1
OleDbDeleteCommand1.Parameters.AddWithValue("@p1", EmployeeIDTextBox.Text)
...

[student21]

OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection


        OleDbDeleteCommand1.CommandText = "DELETE FROM Employees WHERE Forename = @p1, Surname = @p2, Role = @p3, Email = @p4, Telephone = @p5, Extension = @p6, Mobile = @p7"
        OleDbDeleteCommand1.Connection = Me.OleDbConnection1
        OleDbDeleteCommand1.Parameters.AddWithValue("@p1", ForenameTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p2", SurnameTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p3", RoleTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p4", EmailTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p5", TelephoneTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p6", ExtensionTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p7", MobileTextBox.Text)
        OleDbDeleteCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydatabase.mdb"

        Try

            OleDbConnection1.Open()

            OleDbDeleteCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()
    End Sub

getting this error?
Syntax error (comma) in query expression 'Forename = @p1, Surname = @p2, Role = @p3, Email = @p4, Telephone = @p5, Extension = @p6, Mobile = @p7'

[student21]

hey i tried

Private Sub BindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorDeleteItem.Click
        OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand

        OleDbConnection1 = New System.Data.OleDb.OleDbConnection


        OleDbDeleteCommand1.CommandText = "DELETE FROM Employees WHERE Forename = @p1, Surname = @p2, Role = @p3, Email = @p4, Telephone = @p5, Extension = @p6, Mobile =@p7"
        OleDbDeleteCommand1.Connection = Me.OleDbConnection1
        OleDbDeleteCommand1.Parameters.AddWithValue("@p1", ForenameTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p2", SurnameTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p3", RoleTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p4", EmailTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p5", TelephoneTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p6", ExtensionTextBox.Text)
        OleDbDeleteCommand1.Parameters.AddWithValue("@p7", MobileTextBox.Text)
        OleDbDeleteCommand1.Connection = Me.OleDbConnection1

        OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mydatabase.mdb"

        Try

            OleDbConnection1.Open()

            OleDbDeleteCommand1.ExecuteNonQuery()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.ToString)

        End Try

        OleDbConnection1.Close()
    End Sub

but i now get this error
Syntax error (comma) in query expression 'Forename = @p1, Surname = @p2, Role = @p3, Email = @p4, Telephone = @p5, Extension = @p6, Mobile =@p7'.
i have tried putting them in () and not using , but i still get the error

[kvprajapati]

You need to use single (primary key based) field in where clause to delete a record. Please read my previous post.

In case, you want to adhere your query in post #12 then put OR operator between two expression.

OleDbDeleteCommand1.CommandText = "DELETE FROM Employees WHERE Forename = @p1 OR  Surname = @p2 OR  Role = @p3 OR  Email = @p4 OR  Telephone = @p5 OR  Extension = @p6 OR  Mobile =@p7"