vb.net - Insert records into a table from more than pc at same time

Question

I want to insert from more one pc at the same time into the same table in sql server and vb.net.

Error:

Conflicts when inserting data at same time

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

Stored Procedure:

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 

question from:https://stackoverflow.com/questions/65891395/insert-records-into-a-table-from-more-than-pc-at-same-time

Answer 1

Your connection object needs to be declared with a Using in the method where it is used so it can be closed and disposed.

Use Using...End Using blocks to ensure that your database objects are closed and disposed. Note the comma at the end of the Using; this includes the command in the the Using.

Pass the connection string directly to the constructor of the connection. Likewise, pass the CommandText and the connection directly to the constructor of the command.

This should be .Add method which takes the SqlDbType as the second parameter. The .AddWithValue method (which shouldn't be used with Sql Server) takes the value as the second parameter.

Never show a message box while a connection is open. The user could have gone to lunch and there sits your open connection. Actually, it will eventually time out but you get the point.

I hope the primary key of MULTIINSERT_TB is an identity field (auto-number) and you are not passing a value for it.

Private ConStr As String = "Your connection string"

Private Sub OpCode()
    Try
        Using con As New SqlConnection(ConStr),
                com As New SqlCommand("insertooks", con)
            com.CommandType = CommandType.StoredProcedure
            com.Parameters.Add("@MULTIINSERT_CODE", SqlDbType.NVarChar).Value = TextBox1.Text
            com.Parameters.Add("@MULTIINSERT_NAME", SqlDbType.NVarChar).Value = TextBox2.Text
            com.Parameters.Add("@MULTIINSERT_DATE ", SqlDbType.Date).Value = DateTimePicker1.Value
            con.Open()
            com.ExecuteNonQuery()
        End Using 'closes and disposes con and com
        MsgBox("Record inserted successfully")
    Catch ex As Exception
        'Throw ex
        MsgBox(ex.Message)
    End Try
End Sub