Query will not update SQL Server

Question

In the below code, my second query will not insert into the SQL database, but the first one will update. I can copy the query (from the msgbox i added for testing) and paste it in SQL Server Management Studio, and it will execute fine. I also do not get any error messages back from SQL, though i'm not sure if that code is correct (it was copied + pasted from another source). Also, can i simplify the code to pass both queries at the same time?

Dim Conn As New System.Data.SqlClient.SqlConnection         'sql server datastream connection
Dim Cmd As New System.Data.SqlClient.SqlCommand             'sql command vars
Dim SqlQuery As String                                      'string var used to hold various SQL queries
Dim data As System.Data.SqlClient.SqlDataReader             'datareader object variable
Dim MVDataset As New DataSet
Dim MVDatatable As DataTable
Dim MVDatarow As DataRow

Private Sub MVUpdateButton_Click(sender As Object, e As EventArgs) Handles MVUpdateButton.Click

  vbyn = MsgBox("Are you sure you want to update Tally Sheet Master Variables?" & vbCrLf & vbCrLf & "Changes to these variables will change the functionality of the Tally Sheet!", vbYesNo, )
  Try
    Select Case vbyn
      Case vbNo
        GoTo MVTableUpdateBypass
      Case vbYes
        'get new data from textboxes
        Vers = TextBox1.Text
        If TextBox2.Text = True Then
          Testing = 1
        Else
          Testing = 0
        End If
        FlatFeeCharge = TextBox3.Text
        PrepricingCharge = TextBox4.Text
        SendMailAcct = TextBox5.Text
        SendMailPW = TextBox6.Text
        TestingEmail = TextBox7.Text
        PrePricingEmail = TextBox8.Text
        ImperataEmail = TextBox9.Text

        'update existing active row to mark inactive
        SqlQuery = "Update MasterVars set Active = 0 where PKEY = " & PKEY & ";"
        MsgBox(SqlQuery)
        If Conn.State = ConnectionState.Closed Then
          Conn.ConnectionString = "Data Source=SQL01;Initial Catalog=TallySheet;Integrated Security=SSPI;"
        End If
        Conn.Open()
        Dim MVDataAdapter As New SqlDataAdapter(SqlQuery, Conn)
        Dim MVUpdateCommand As SqlCommand
        MVUpdateCommand = New SqlCommand(SqlQuery)
        MVDataAdapter.UpdateCommand = MVUpdateCommand

        'insert new active row
        SqlQuery = "Insert into MasterVars (Vers, Testing, FlatFeeCharge, PrePricingCharge, SendMailAcct, SendMailPW, TestingEmail, PrePricingEmail, ImperataEmail, DTS, UserName, Active) Values (" & "'" & Vers & "', " & Testing & ", '" & FlatFeeCharge & "'" & ", '" & PrepricingCharge & "'" & ", '" & SendMailAcct & "'" & ", '" & SendMailPW & "'" & ", '" & TestingEmail & "'" & ", '" & PrePricingEmail & "'" & ", '" & ImperataEmail & "'" & ", '" & Date.Now & "'," & "'QGDOMAIN\" & Environment.UserName & "'," & 1 & ");"
        MsgBox(SqlQuery)
        Dim MVInsertCommand As SqlCommand
        MVInsertCommand = New SqlCommand(SqlQuery)
        MVDataAdapter.InsertCommand = MVInsertCommand
        MVDataAdapter.Fill(MVDataset, "MasterVars")
      End Select
    Catch ex As SqlException
      Dim i As Integer
      Dim errormessages As String
      errormessages = ""
      For i = 0 To ex.Errors.Count - 1
        errormessages = errormessages & " " & ("Index #" & i.ToString() & ControlChars.NewLine _
                & "Message: " & ex.Errors(i).Message & ControlChars.NewLine _
                & "LineNumber: " & ex.Errors(i).LineNumber & ControlChars.NewLine _
                & "Source: " & ex.Errors(i).Source & ControlChars.NewLine _
                & "Procedure: " & ex.Errors(i).Procedure & ControlChars.NewLine)
      Next i
    Console.WriteLine(errorMessages.ToString())
  End Try

  'reload form with updated variables
  Conn.Close()
  Conn.Dispose()
MVTableUpdateBypass:
End Sub

Answer 1

The Fill method of the SqlDataAdapter executes the SelectCommand not the UpdateCommand or the InsertCommand. In any case these two commands (and the DeleteCommand) are executed when you call the Update method of the adapter.
Moreover the Update method runs the commands looking for rows changed/added/deleted in the DataTable/DataSet retrieved by the SelectCommand and works only for those rows.

But you don't need an SqlDataAdapter to execute your two queries. You should simply construct an SqlCommand with both texts separated by a semicolon and call ExecuteNonQuery

SqlQuery = "Update MasterVars set Active = 0 where PKEY = @key;" & _
           "Insert into MasterVars (Vers, Testing, .....) VALUES (@p1, @o2, ....)"
Using Conn = New SqlConnection("Data Source=SQL01;......")
Using cmd = New SqlCommand(SqlQuery, Conn)
    Conn.Open()
    cmd.Parameters.Add("@key", SqlDbType.Int).Value = PKEY 
    cmd.Parameters.Add("@p1", SqlDbType.NVarChar).Value = vers 
    cmd.Parameters.Add("@p2", SqlDbType.Int).Value = testing
    ... and so on with other parameters .... 
    cmd.ExecuteNonQuery()
End Using
End Using

In this incomplete example (too many parameters to write down) I have concatenated the two sql texts in a single string and prepared it with parameter placeholders. Then I build the parameter collection with the exact datatypes required by your table and finally call ExecuteNonQuery to run everything on the database side.

Notice that is not needed to keep global objects like the connection or the command. It is always better to create a local variable, use and destroy it when done. In particular disposable objects like the connection and the command should always created in a Using block