title: Insert and Select at the same time in a stored procedure
body: <p>I have the following stored procedure:</p>
  
  <pre><code>create procedure Insert_Maintenance
  (
  @PlateNo nvarchar(10),
  @MaintenanceType nvarchar(150),
  @Name nvarchar(300),
  @MaintenanceDate date,
  @Qty int,
  @IndivisualVal float,
  @TotalVal float,
  @Notes nvarchar(300)
  )
  as
  insert into Maintenance(MaintenanceNo, PlateNo, MaintenanceType, AutoCenterNo,MaintenanceDate, Qty, IndivisualVal, TotalVal, Notes)
  values ((1+(select Max(MaintenanceNo) from Maintenance)), @PlateNo, @MaintenanceType, (select AutoCenterNo from AutoCenter where Name = @Name), @MaintenanceDate, @Qty, @IndivisualVal, @TotalVal, @Notes)
  Select Max(MaintenanceNo) AS MNo from Maintenance
  </code></pre>
  
  <p>A user will insert new maintenance operation via VB.NET interface. The information of the maintenance will be inserted into the database. The ID of the maintenance will increase by 1; as it is shown in the code. Since this is the case, after inserting the information I want to show a message that tells the user that information entered correctly and the ID of this operation is the value of <code>MNo</code>.</p>
  
  <p>Basically, the problem is not with the VB.NET code because the message works fine but the problem is related to the select statement that shows the maintenance ID to the user. </p>
  
  <p>The code in VB.NET</p>
  
  <pre><code>Dim ConnectString As String
          ConnectString = ""
          Dim connection As New SqlConnection(ConnectString)
  
          'Dim parm1 As SqlParameter
          'parm1 = New SqlParameter("@MaintenanceNo", SqlDbType.Int)
          'parm1.Value = MaintenanceNoTextBox.Text
  
          Dim parm2 As SqlParameter
          parm2 = New SqlParameter("@PlateNo", SqlDbType.NVarChar)
          parm2.Value = PlateNoComboBox.Text
  
          Dim parm3 As SqlParameter
          parm3 = New SqlParameter("@MaintenanceType", SqlDbType.NVarChar)
          parm3.Value = MaintenanceTypeTextBox.Text
  
          Dim parm4 As SqlParameter
          parm4 = New SqlParameter("@Name", SqlDbType.NVarChar)
          parm4.Value = NameComboBox.Text
  
          Dim parm5 As SqlParameter
          parm5 = New SqlParameter("@MaintenanceDate", SqlDbType.Date)
          parm5.Value = MaintenanceDateDateTimePicker.Value
  
          Dim parm6 As SqlParameter
          parm6 = New SqlParameter("@Qty", SqlDbType.Int)
          parm6.Value = QtyTextBox.Text
  
          Dim parm7 As SqlParameter
          parm7 = New SqlParameter("@IndivisualVal", SqlDbType.Float)
          parm7.Value = IndivisualValTextBox.Text
  
          Dim parm8 As SqlParameter
          parm8 = New SqlParameter("@TotalVal", SqlDbType.Float)
          parm8.Value = TotalValTextBox.Text
  
          Dim parm9 As SqlParameter
          parm9 = New SqlParameter("@Notes", SqlDbType.NVarChar)
          parm9.Value = NotesTextBox.Text
  
          Dim cmd As SqlCommand = New SqlCommand()
          cmd.Connection = connection
          cmd.CommandType = CommandType.StoredProcedure
          cmd.CommandText = "Insert_Maintenance"
          'cmd.Parameters.Add(parm1)
          cmd.Parameters.Add(parm2)
          cmd.Parameters.Add(parm3)
          cmd.Parameters.Add(parm4)
          cmd.Parameters.Add(parm5)
          cmd.Parameters.Add(parm6)
          cmd.Parameters.Add(parm7)
          cmd.Parameters.Add(parm8)
          cmd.Parameters.Add(parm9)
  
          Try
              connection.Open()
              cmd.ExecuteNonQuery()
              Dim dreader As SqlDataReader
              dreader = cmd.ExecuteReader()
              Dim a As String
              a = dreader("MNo").ToString
              MessageBox.Show("Information entered, ID is " + a)
              dreader.Close()
              'MaintenanceNoTextBox.Text = ""
              PlateNoComboBox.Text = ""
              MaintenanceTypeTextBox.Text = ""
              NameComboBox.Text = ""
              'MaintenanceDateDateTimePicker.Value = ""
              QtyTextBox.Text = ""
              IndivisualValTextBox.Text = ""
              TotalValTextBox.Text = ""
              NotesTextBox.Text = ""
          Catch ex As Exception
              MessageBox.Show("Something wrong  (" + ex.Message + ")")
          Finally
              connection.Close()
          End Try
  </code></pre>
  
  <p>Any suggestions guys !! Thanks</p>