insert from more pc at same time sql server

Question

hi

i want to insert from more one pc app at same time .
at same table in sql server and vb.net.

error Conflicts When Inserting data At Same Time

stored procedure

Blockquote

ALTER PROCEDURE [dbo].[insertooks]
@MULTIINSERT_CODE NVARCHAR(50) = NULL ,
@MULTIINSERT_NAME NVARCHAR(50) = NULL ,
@MULTIINSERT_DATE DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
INSERT INTO MULTIINSERT_TB
(
MULTIINSERT_CODE,
MULTIINSERT_NAME,
MULTIINSERT_DATE

  )

VALUES
(
@MULTIINSERT_CODE ,
@MULTIINSERT_NAME ,
@MULTIINSERT_DATE
)

END

insert code

Blockquote

Try
Dim com As New SqlCommand()
com.CommandType = CommandType.StoredProcedure
com.CommandText = "insertooks"

        com.Parameters.AddWithValue("@MULTIINSERT_CODE", SqlDbType.NVarChar).Value = TextBox1.Text
        com.Parameters.AddWithValue("@MULTIINSERT_NAME", SqlDbType.NVarChar).Value = TextBox2.Text
        com.Parameters.AddWithValue("@MULTIINSERT_DATE ", SqlDbType.Date).Value = DateTimePicker1.Value

        com.Connection = con
        con.Open()
        com.ExecuteNonQuery()
        con.Close()

        MsgBox("Record inserted successfully")
    Catch ex As Exception
        'Throw ex
        MsgBox(ex.Message)
    Finally
        con.Close()
        con.Dispose()
    End Try

Answer 1

Not knowing the error message, perhaps rigging up the insert for retries might be in order.

Simple example

DECLARE @MaxRetries TINYINT;

SET @MaxRetries = 3;
WHILE @MaxRetries > 0
    BEGIN
        BEGIN TRY

            -- INSERT

            SET @MaxRetries = 0;
        END TRY
        BEGIN CATCH
            IF ERROR_NUMBER() = 1205 -- deadlock
                SET @MaxRetries-=1;
                ELSE
                THROW; -- NOT a deadlock
        END CATCH;
    END;

Answer 2

You need to post the error message.

Answer 3

What I want is to amend the code so that the table lock until the addition is completed and the lock is opened immediately after the inserting.
Since more than one user is not added together in the same table, an error occurs in the Auto number field.

Answer 4

@ahmedAlie
What I want is to amend the code so that the table lock until the addition is completed and the lock is opened immediately after the inserting.
Since more than one user is not added together in the same table, an error occurs in the Auto number field.

This is questionable as to what you are trying to do. What is the reasoning that more than one user cannot add a record to the table concurrently? Why are you trying to lock the table which seems to be a bad approach IMO?