Execute SQL commands

[x-ice]

I have the following code.

'Set SQL statements
        With daStaff
            .DeleteCommand.CommandText = "DELETE FROM Staff" 'Delete statement
            .InsertCommand.CommandText = "INSERT INTO Staff" 'Insert statement
            .UpdateCommand.CommandText = SQLUpdateStr 'Update statement
        End With

How would i execute the "Delete, Insert or Update' CommandText?

When i am in the code window i see .ExecuteNonQuery, .ExecuteReader and .ExecuteScalar when i type daStaff.UpdateCommand. in the dropdown list. Which one, if any would i use?

[<ABX]

.ExecuteNonQuery ' Returns the Number of rows affected, you should use this one.
.ExecuteReader ' is for SELECT statements (where your getting values from a table)
.ExecuteScalar ' is for when a single value is returned

[x-ice]

When i try to execute Delete, Insert or Update command i get an error (below). To execute the command i use the following code, where am i going wrong?

[B]daStaff.InsertCommand.ExecuteNonQuery()[/B]

ERROR: "ExecuteNonQuery: Connection property has not been initialized."

The code in bold is the error code.

Here is the code i use to set the SQL commands.

Private Sub SetSQLStatements()
        'Add parameters to use in SQL statements
        With cmdStaff.Parameters
            .Add("@EmpID", txtEmpID.Text)
            .Add("@Surname", txtSurname.Text)
            .Add("@Forename", txtForename.Text)
            .Add("@Gender", txtGender.Text)
            .Add("@DOB", dtpDOB.Text)
            .Add("@Address", txtAddress.Text)
            .Add("@Postcode", txtPostcode.Text)
            .Add("@TelNum", txtTelNum.Text)
            .Add("@Contract", txtContract.Text)
            .Add("@Salary", txtSalary.Text)
            .Add("@Position", txtPosition.Text)
            .Add("@StartDate", dtpStartDate.Text)
            .Add("@BookRight", cboBookingRights.Text)
            .Add("@LoginRight", cboLoginRights.Text)
            .Add("@MemberRight", cboMemberRights.Text)
            .Add("@ManageRight", cboManageRights.Text)
        End With
        Dim SQLUpdateStr As String
        'SQL Update string
        SQLUpdateStr = "UPDATE Staff SET Address = @Address, BookingRights = @BookRight," & _
        "Contract = @Contract, DOB = @DOB, Forename = @Forename, Gender = @Gender," & _
        "LoginRights = @LoginRight, ManageRights = @ManageRight, MemberRights = @MemberRight," & _
        "Position = @Position, Postcode = @Postcode, Salary = @Salary, StartDate = @StartDate," & _
        "Surname = @Surname, [Telephone Number] = @TelNum"
        'Create instances of OLEDB commands
        Dim cmdSQLDel As New OleDbCommand("DELETE FROM Staff WHERE EmployeeID = @EmpID") 'Delete statement
        Dim cmdSQLIns As New OleDbCommand("INSERT INTO Staff") 'Insert statement
        Dim cmdSQLUpd As New OleDbCommand(SQLUpdateStr) 'Update statement
        'Set SQL statements
        With daStaff
            .DeleteCommand = cmdSQLDel
            .InsertCommand = cmdSQLIns
            .UpdateCommand = cmdSQLUpd
        End With
    End Sub

[mar_zim]

where is your connection?

Dim cmdSQLDel As New OleDbCommand("DELETE FROM Staff WHERE EmployeeID = @EmpID",[b]myconnection[/b]) 'Delete statement
        Dim cmdSQLIns As New OleDbCommand("INSERT INTO Staff",[b]myconnection[/b]) 'Insert statement
        Dim cmdSQLUpd As New OleDbCommand(SQLUpdateStr)
        cmdsqlupd.connection=[b]myconnection[/b]

[x-ice]

where is your connection?

Dim cmdSQLDel As New OleDbCommand("DELETE FROM Staff WHERE EmployeeID = @EmpID",[b]myconnection[/b]) 'Delete statement
        Dim cmdSQLIns As New OleDbCommand("INSERT INTO Staff",[b]myconnection[/b]) 'Insert statement
        Dim cmdSQLUpd As New OleDbCommand(SQLUpdateStr)
        cmdsqlupd.connection=[b]myconnection[/b]

Ok, i've done that but i am getting a syntax error in my SQL statements now. What is wrong with these SQL statements?[Highlight=VB]Dim SQLUpdateStr As String
'SQL Update string
SQLUpdateStr = "UPDATE Staff SET Address = @Address, BookingRights = @BookRight," & _
"Contract = @Contract, DOB = @DOB, Forename = @Forename, Gender = @Gender," & _
"LoginRights = @LoginRight, ManageRights = @ManageRight, MemberRights = @MemberRight," & _
"Position = @Position, Postcode = @Postcode, Salary = @Salary, StartDate = @StartDate," & _
"Surname = @Surname, [Telephone Number] = @TelNum"
'Create instances of OLEDB commands
Dim cmdSQLDel As New OleDbCommand("DELETE * FROM Staff WHERE EmployeeID = @EmpID", conStaff) 'Delete statement
Dim cmdSQLIns As New OleDbCommand("INSERT INTO Staff", conStaff) 'Insert statement
Dim cmdSQLUpd As New OleDbCommand(SQLUpdateStr) 'Update statement
'Set connection for 'cmdSQLUpd'
cmdSQLUpd.Connection = conStaff

[jmcilhinney]

Your commands are already attached to a DataAdapter so don't be using ExecuteNonQuery for each command. The Update method of the DataAdapter executes all three commands. Assuming that each command contains valid SQL code in its CommandText and your connection issues are worked out, a single call to the Update method of your DataAdapter will update all deleted, inserted and updated rows (in that order).
The easiest way to create a DataAdapter is in the design window. If it is desireable, or at least OK, that your DataAdapter have form-level scope, add it to your form in the designer and then you can simply set properties or, easier still, use the "Configure Data Adapter..." function at the bottom of the Properties window.

[mar_zim]

x-ice: ok how do you declare your dataadapter?

do you pass a connection to your dataadapter?
like this one?

With daStaff
[b]        .DeleteCommand.connection = myconnection[/b]
        [b].InsertCommand.connection = myconnection[/b]
        [b].UpdateCommand.connection = myconnection[/b]
            .DeleteCommand = cmdSQLDel
            .InsertCommand = cmdSQLIns
            .UpdateCommand = cmdSQLUpd
        End With

[x-ice]

Here is the code for the whole form.

#Region "Public Declaration"
    'Create connection
    Dim conStaff As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=XGamesDB.mdb;Persist Security Info=False")
    'Create command
    Dim cmdStaff As New OleDbCommand("SELECT * FROM Staff Where EmployeeID = " & EmpID, conStaff)
    'Create data adapter
    Dim daStaff As New OleDbDataAdapter(cmdStaff)
    'Create dataset
    Dim dsStaff As New DataSet()
#End Region
 
#Region "Form Code"
    Private Sub frmManageStf_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Open connection
        conStaff.Open()
        'Fill Data Adapter
        daStaff.Fill(dsStaff, "Staff")
        'Bind data to controls
        Call BindData()
        'Set 'Insert, Delete and Update' SQL statements
        Call SetSQLStatements()
    End Sub
 
    Private Sub frmManageStf_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        'Close connection, if its open
        If conStaff.State = ConnectionState.Open Then conStaff.Close()
    End Sub
#End Region
 
#Region "Sub Procedures"
    Private Sub BindData()
        'Bind data to controls
        txtEmpID.DataBindings.Add(New Binding("Text", dsStaff, "Staff.EmployeeID"))
        txtSurname.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Surname"))
        txtForename.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Forename"))
        txtGender.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Gender"))
        dtpDOB.DataBindings.Add(New Binding("Text", dsStaff, "Staff.DOB"))
        txtAddress.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Address"))
        txtPostcode.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Postcode"))
        txtTelNum.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Telephone Number"))
        txtContract.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Contract"))
        txtSalary.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Salary"))
        txtPosition.DataBindings.Add(New Binding("Text", dsStaff, "Staff.Position"))
        dtpStartDate.DataBindings.Add(New Binding("Text", dsStaff, "Staff.StartDate"))
        cboBookingRights.DataBindings.Add(New Binding("Text", dsStaff, "Staff.BookingRights"))
        cboLoginRights.DataBindings.Add(New Binding("Text", dsStaff, "Staff.LoginRights"))
        cboMemberRights.DataBindings.Add(New Binding("Text", dsStaff, "Staff.MemberRights"))
        cboManageRights.DataBindings.Add(New Binding("Text", dsStaff, "Staff.ManageRights"))
    End Sub
 
    Private Sub SetSQLStatements()
        'Add parameters to use in SQL statements
        With cmdStaff.Parameters
            .Add("@EmpID", txtEmpID.Text)
            .Add("@Surname", txtSurname.Text)
            .Add("@Forename", txtForename.Text)
            .Add("@Gender", txtGender.Text)
            .Add("@DOB", dtpDOB.Text)
            .Add("@Address", txtAddress.Text)
            .Add("@Postcode", txtPostcode.Text)
            .Add("@TelNum", txtTelNum.Text)
            .Add("@Contract", txtContract.Text)
            .Add("@Salary", txtSalary.Text)
            .Add("@Position", txtPosition.Text)
            .Add("@StartDate", dtpStartDate.Text)
            .Add("@BookRight", cboBookingRights.Text)
            .Add("@LoginRight", cboLoginRights.Text)
            .Add("@MemberRight", cboMemberRights.Text)
            .Add("@ManageRight", cboManageRights.Text)
        End With
        Dim SQLUpdateStr As String
        'SQL Update string
        SQLUpdateStr = "UPDATE Staff SET Address = @Address, BookingRights = @BookRight," & _
        "Contract = @Contract, DOB = @DOB, Forename = @Forename, Gender = @Gender," & _
        "LoginRights = @LoginRight, ManageRights = @ManageRight, MemberRights = @MemberRight," & _
        "Position = @Position, Postcode = @Postcode, Salary = @Salary, StartDate = @StartDate," & _
        "Surname = @Surname, [Telephone Number] = @TelNum"
        'Create instances of OLEDB commands
        Dim cmdSQLDel As New OleDbCommand("DELETE * FROM Staff WHERE EmployeeID = @EmpID", conStaff) 'Delete statement
        Dim cmdSQLIns As New OleDbCommand("INSERT INTO Staff", conStaff) 'Insert statement
        Dim cmdSQLUpd As New OleDbCommand(SQLUpdateStr) 'Update statement
        'Set connection for 'cmdSQLUpd'
        cmdSQLUpd.Connection = conStaff
        'Set SQL statements
        With daStaff
            .DeleteCommand = cmdSQLDel
            .InsertCommand = cmdSQLIns
            .UpdateCommand = cmdSQLUpd
        End With
    End Sub
#End Region
 
    Private Sub btnAddEmp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddEmp.Click
        Try
            daStaff.DeleteCommand.ExecuteNonQuery()
            'Whatever command i try to execute, i still get a syntax error in my SQL queries
        Catch Ex As Exception
            MessageBox.Show(Ex.Message)
        End Try
    End Sub