What is my valid insert command?

[theguyinthehat]

Hi--I'm trying to add information to a SQL database...I've had trouble getting it to update (and I'm only doing one entry at a time!). However, even one at a time gives me trouble. The red line of code gives me this error: Update requires a valid InsertCommand when passed DataRow collection with new rows. Please help me know what I'm doing wrong--I think it may be syntax, but I'm think the parameters are correct....

        Dim connect As New SqlConnection("Data Source=IRNTS4SQL;Initial Catalog=Materials;Integrated Security=True")
        connect.Open()
        Dim cmdLotNumber As New SqlCommand("SELECT [Lot_Name] From [FPAD Lot Log]", connect)
        Dim daLotNumber As New SqlDataAdapter(cmdLotNumber)
        Dim dsJobEntry As New DataSet
        daLotNumber.Fill(dsJobEntry, "LotData")
        cboLotNumber.DataSource = dsJobEntry.Tables("LotData")
        cboLotNumber.DisplayMember = "Lot_Name"

        Dim cmdEng As New SqlCommand("SELECT [Engineer_Name] from [FPAD Engineers] WHERE [Inactive] = 0", connect) 'WHERE [Inactive] = False", connect) 'Engineer_Name, [Inactive] FROM [FPAD Engineers] WHERE @Inactive = False", connect)
        Dim daEng As New SqlDataAdapter(cmdEng)
        daEng.Fill(dsJobEntry, "EngData")
        cboEngineer.DataSource = dsJobEntry.Tables("EngData")
        cboEngineer.DisplayMember = "Engineer_Name"


        Dim cmdTool As New SqlCommand("SELECT [Tool Type] from [FPAD Tool Types]", connect) 'WHERE [Inactive] = False", connect) 'Engineer_Name, [Inactive] FROM [FPAD Engineers] WHERE @Inactive = False", connect)
        Dim daTool As New SqlDataAdapter(cmdTool)

        daTool.Fill(dsJobEntry, "ToolData")
        daTool.UpdateCommand = New SqlCommand("Update [FPAD Tool Types]") 'REMOVE
        Dim newRow As DataRow = dsJobEntry.Tables("ToolData").NewRow()
        newRow("Tool Type") = "A24D"
        dsJobEntry.Tables("ToolData").Rows.Add(newRow)

        daTool.Update(dsJobEntry.Tables("ToolData"))


        cboTools.DataSource = dsJobEntry.Tables("ToolData")
        cboTools.DisplayMember = "Tool Type"

[Edneeis]

You are passing only one SQL statement and command to each of the DataAdapters which by default is the SelectCommand. You will need to create an Update, Insert and Delete Command for each DataAdapter to use the Update method on the Adapters.

[Tom.Net]

Wow that hurts my head just looking at it.

Start by moving all your select queries into a single stored procedure (they can all be retrieved with a single call to the database. Put the stored procedure in you Sql Server Database (rename appropiately)

Create Procedure spDataSelect

As

SELECT [Lot_Name] From [FPAD Lot Log]
SELECT [Engineer_Name] from [FPAD Engineers] WHERE [Inactive] = 0
SELECT [Tool Type] from [FPAD Tool Types]

Now your update query is just plain wrong and there is no insert statement set up. So to start, I'm just re-coding the data to retrieve all the info

Dim dsJobEntry As New DataSet

Using con As New SqlConnection(strDbConnection)
    Dim cmdSelect As New SqlCommand
    Dim da As New SqlDataAdapter

    cmdSelect.Connection = con
    cmdSelect.CommandType = CommandType.StoredProcedure
    cmdSelect.CommandText = "spRecordsSelect"

    da.TableMappings.Add("Table", "LotData")
    da.TableMappings.Add("Table1", "EngData")
    da.TableMappings.Add("Table2", "ToolData")

    da.Fill(dsJobEntry)

    da.Dispose()
    cmdSelect.Dispose()
End Using 

cboLotNumber.DataSource = dsJobEntry.Tables("LotData")
cboLotNumber.DisplayMember = "Lot_Name"

cboEngineer.DataSource = dsJobEntry.Tables("EngData")
cboEngineer.DisplayMember = "Engineer_Name"

cboTools.DataSource = dsJobEntry.Tables("ToolData")
cboTools.DisplayMember = "Tool Type"

Much cleaner and faster I bet. Try this out and once your up and running, will add your update & insert commands

[theguyinthehat]

the dsJobEntry.Fill command gave me an exception--The SelectCommand property has not been initialized before calling 'Fill'. I'm not sure I set up the stored procedure correctly.

[BackWoodsCoder]

you need to add one more line of code

da.selectcommand = cmdselect

this associates your data adapter with the sql command you created.

[Tom.Net]

Sorry about that, I must have forgotten a line...

Dim dsJobEntry As New DataSet

Using con As New SqlConnection(strDbConnection)
    Dim cmdSelect As New SqlCommand
    Dim da As New SqlDataAdapter

    cmdSelect.Connection = con
    cmdSelect.CommandType = CommandType.StoredProcedure
    cmdSelect.CommandText = "spRecordsSelect"

    da.TableMappings.Add("Table", "LotData")
    da.TableMappings.Add("Table1", "EngData")
    da.TableMappings.Add("Table2", "ToolData")

    da.SelectCommand = cmdSelect
    da.Fill(dsJobEntry)

    da.Dispose()
    cmdSelect.Dispose()
End Using 

cboLotNumber.DataSource = dsJobEntry.Tables("LotData")
cboLotNumber.DisplayMember = "Lot_Name"

cboEngineer.DataSource = dsJobEntry.Tables("EngData")
cboEngineer.DisplayMember = "Engineer_Name"

cboTools.DataSource = dsJobEntry.Tables("ToolData")
cboTools.DisplayMember = "Tool Type"

[theguyinthehat]

THANK YOU....much faster. As for my original question, I didn't realize an insert command was necessary (I'm used to VB6 using DAO recordsets), so I'm reading up on that myself. Learning stored procedures helps timing a whole lot though.

[Tom.Net]

Glad to hear it helps. The Insert, Update & Delete statements can all be done as stored procedures too and as you already noticed, it does help performance. Although coding the Update & Insert Stored Procedures is alot more coding, simply because you have to define every column that you are updating as a parameter in your command object.

[keystone_paul]

NB there are some drawbacks to using stored procedures so I'd think twice about whether they are the right solution for your problem (for example using them can tie you into a specific database technology, and it means that your logic is getting bundled into your database which often is undesirable).

Thats not to say that they aren't good for what you are doing, just that you need to consider the pros and cons.