Possible to have multiple SqlCommand?

Question

Dim conn As New SqlConnection("Database=Clinic_Management_System;Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=|DataDirectory|Clinic Management System.mdf")
Dim cmd As SqlCommand
Dim dr As SqlDataReader
conn.Open()
cmd = New SqlCommand("INSERT INTO record([PatientID],[Prescription],[VisitDate]) Values ('" & PatientIDTextBox.Text & "','" & txtPrescription.Text & "',GetDate()) ", conn)
cmd.ExecuteNonQuery()

For cn As Integer = 0 To DataGridView1.RowCount - 1
    cmd = New SqlCommand("INSERT INTO record_item([RecordID],[ItemID],[Amount]) Values ( (SELECT MAX(RecordID) FROM record)," & DataGridView1.Rows(cn).Cells(0).Value & "," & DataGridView1.Rows(cn).Cells(2).Value & ")", conn)
    cmd.ExecuteNonQuery()

Next
conn.Close()

Is this possible to run 2 SqlCommand together??

Because after executed somehow the 2nd inside the loop did not execute or insert data.

Are you getting an error? Have you tried debugging? What happens at the 2nd cmd.ExecuteNonQuery? — Curtis
– Curtis, Commented Jan 17, 2012 at 17:22
nothing error, after debug and try to insert some data, but database remain empty, only 1st sqlcommand worked — user1151874
– user1151874, Commented Jan 17, 2012 at 17:29

Curtis · Accepted Answer · 2012-01-18 09:18:48Z

2

You don't have 2 SqlCommands.

You have 1 SqlCommand, called cmd, which is executed multiple times.

It is fine to do something like this, however I would have 1 SqlCommand for your INSERT INTO record, and 1 for your INSERT INTO record_item. I think this makes it much easier to understand when looking back at the code at a later date.

Either way, using a SqlCommand like this should not prevent it from executing, therefore I believe there is another issue with your scripting.

I've adapted your code so that it is split into 2 seperate SqlCommand objects, and the queries have been parameterized to prevent SQL Injection:

Dim conn As New SqlConnection("Database=Clinic_Management_System;Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=|DataDirectory|Clinic Management System.mdf")

Dim cmdRecord As New SqlCommand("INSERT INTO record ([PatientID],[Prescription],[VisitDate]) Values (@PatientID, @Prescription, GETDATE())", conn)
cmdRecord.Parameters.Add("@PatientID", SqlDbType.Int).Value = PatientIDTextBox.Text
cmdRecord.Parameters.Add("@Prescription", SqlDbType.NVarChar).Value = txtPrescription.Text

Dim cmdRecordItem As New SqlCommand("INSERT INTO record_item([RecordID],[ItemID],[Amount]) Values ( (SELECT MAX(RecordID) FROM record),@ItemID,@AmountID)", conn)
cmdRecordItem.Parameters.Add("@ItemID", SqlDbType.Int)
cmdRecordItem.Parameters.Add("@Amount", SqlDbType.Decimal)

Dim dr As SqlDataReader
conn.Open()

cmdRecord.ExecuteNonQuery()

For cn As Integer = 0 To DataGridView1.RowCount - 1
    cmdRecordItem.Parameters("@ItemID").Value = DataGridView1.Rows(cn).Cells(0).Value
    cmdRecordItem.Parameters("@Amount").Value = DataGridView1.Rows(cn).Cells(2).Value

    cmdRecordItem.ExecuteNonQuery()

Next
conn.Close()

edited Jan 18, 2012 at 9:18

answered Jan 17, 2012 at 17:21

Curtis

104k69 gold badges280 silver badges359 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrew Barber Over a year ago

The OP only has one SqlCommand variable, but actually creates multiple SqlCommand objects. There are two new SqlCommand() assignments, and one is in a loop.

Curtis Over a year ago

This is true, but the new SqlCommand object might as well just be a change to the CommandText, as everything else is remaining the same.

Andrew Barber Over a year ago

Yes, absolutely... but your answer says he only has one SqlCommand.

user1151874 Over a year ago

thanks for the code, i just realise, the problem is Dim cmdRecordItem As New SqlCommand("INSERT INTO record_item([RecordID],[ItemID],[Amount]) Values ( (SELECT MAX(RecordID) FROM record),@ItemID,@AmountID)", conn) even though i set default value for this 3 items, the value also cannot store inside the database,don't know why...i checked the name,column name all correct

ahmadnaziF · Accepted Answer · 2012-01-17 20:42:52Z

0

One command can only be used once. If you want to use more than one command, declare a new cmd as

Dim cmd2 as SqlCommand

answered Jan 17, 2012 at 20:42

ahmadnaziF

1493 silver badges8 bronze badges

Comments

competent_tech · Accepted Answer · 2012-01-18 03:45:51Z

0

In order to ensure that either all statements complete successfully or that none do, you need to wrap your commands in a transaction.

Using parameters for the commands will also make the code more understandable and safer and you should use using statements where possible.

Also, performing the Select max recordid is a very bad idea (if you have multiple users), but I'll leave that for another time:

    Using conn As New SqlConnection("Database=Clinic_Management_System;Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=|DataDirectory|Clinic Management System.mdf")
        Dim cmdRecord As SqlCommand
        Dim cmdRecordItem As SqlCommand
        Dim oTran As SqlTransaction = Nothing

        conn.Open()
        Try
            cmdRecord = New SqlCommand("INSERT INTO record([PatientID],[Prescription],[VisitDate]) Values (@PatientID, @Prescription, GetDate())", conn)
            cmdRecord.Parameters.AddWithValue("@PatientID", PatientIDTextBox.Text)
            cmdRecord.Parameters.AddWithValue("@Prescription", txtPrescription.Text)

            cmdRecordItem = New SqlCommand("INSERT INTO record_item([RecordID],[ItemID],[Amount]) SELECT ISNULL(MAX(RecordID), 0), @ItemID, @Amount FROM record", conn)
            cmdRecordItem.Parameters.Add("@ItemId")
            cmdRecordItem.Parameters.Add("@Amount")

            oTran = conn.BeginTransaction

            cmdRecord.ExecuteNonQuery()

            For Each oRow As DataGridViewRow In DataGridView1
                cmdRecordItem.Parameters("@ItemId").Value = oRow.Cells(0).Value
                cmdRecordItem.Parameters("@Amount").Value = oRow.Cells(2).Value

                cmdRecordItem.ExecuteNonQuery()
            Next

            oTran.Commit()
        Catch
            If oTran IsNot Nothing Then
                oTran.Rollback()
            End If

            Throw
        Finally
            conn.Close()
        End Try
    End Using

edited Jan 18, 2012 at 3:45

answered Jan 17, 2012 at 19:29

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

2 Comments

user1151874 Over a year ago

this code somehow also not working, like you said, either all complete, or none do

competent_tech Over a year ago

@user1151874: Are there any exceptions being thrown? I have updated the insert record_item to use a different query format in case that is the issue.

Collectives™ on Stack Overflow

Possible to have multiple SqlCommand?

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related