title: How to Pass GridView Values to SQLCommand Parameters in VB.net
body: <p>I have data in a GridView that is being approved by the user for specific data periods (<em>StartDate</em>, ex. '2017-05-01). For each row, if the checkbox for the row is checked, the record is approved and timestamped. If the box is not checked, the record is marked with a 'D' and timestamped. Comments are required on unapproved records but not on approved records.</p>
  
  <p>The problem is that I can't get my update statements to run, and I believe that it's because of the way I'm setting the parameters or <em>StartDate</em>, <em>FileNumber</em> and <em>EmpID</em>. I tried running simple DELETE statements based on <em>UserName</em> and <em>EmpID</em>, and those worked. Any thoughts?</p>
  
  <p>I've tried some variations of <em>Request.QueryString("StartDate")</em> and <em>GridUnapprovedRecords.SelectedRow.FindControl("StartDate")</em>, but I didn't have any luck with those.</p>
  
  <p>The Error:</p>
  
  <pre><code>The parameterized query '(@UserName varchar(13),@EmpID varchar(4),@StartDate varchar(8000' expects the parameter '@StartDate', which was not supplied.
  </code></pre>
  
  <p>The Sub:</p>
  
  <pre><code>Protected Sub UpdateSelectedRecords_Click(ByVal sender As Object, ByVal e As EventArgs)
      Dim cb As CheckBox
      Dim atLeastOneRowApproved As Boolean = False
      Dim strComment As TextBox
      Dim conString As String = ConfigurationManager.ConnectionStrings("MktDataConnectionString").ToString()
      Dim sqlConn As New SqlConnection(conString)
          sqlConn.Open()
      Dim cmd As New SqlCommand(conString,sqlConn)
          cmd.Parameters.Add("@UserName", SqlDbType.VarChar)
          cmd.Parameters.Add("@EmpID", SqlDbType.VarChar)
          cmd.Parameters.Add("@StartDate", SqlDbType.VarChar)
          cmd.Parameters.Add("@FileNumber", SqlDbType.VarChar)
          cmd.Parameters.Add("@Comment", SqlDbType.VarChar)
  
      ' Make changes to dtsp_THS_PerfectAttendanceValidation, row by row
      For Each row As GridViewRow In GridUnapprovedRecords.Rows
  
          ' Select the current row's check box and comment
          cb = CType(row.FindControl("CheckBox1"),CheckBox)
          strComment = CType(row.FindControl("Comment"), TextBox)
  
          ' Set parameter values for UPDATE statement
          cmd.Parameters("@UserName").Value = Row.Page.User.Identity.Name
          cmd.Parameters("@EmpID").Value = GridUnapprovedRecords.DataKeys(row.RowIndex).Value
          cmd.Parameters("@StartDate").Value = row.Cells(0).Text.ToString() 
          cmd.Parameters("@FileNumber").Value = row.Cells(2).Text.ToString() 
          cmd.Parameters("@Comment").Value = row.Cells(5).Text.ToString() 
  
          ' Determine which UPDATE statement to run
          If ((Not (cb) Is Nothing) AndAlso cb.Checked) Then
              ' Approved records; RecordType left as NULL; Comment Optional
              atLeastOneRowApproved = true
              If String.IsNullOrEmpty(strComment.Text) Then
                  ' Ignores comment
                  cmd.CommandText = "UPDATE dtsp_THS_PerfectAttendanceValidation SET UserName = @UserName, ValidationDate = GETDATE() WHERE StartDate = @StartDate AND FileNumber = @FileNumber AND EmpID = @EmpID"
                  cmd.ExecuteNonQuery()
              Else
                  ' Adds Comment
                  cmd.CommandText = "UPDATE dtsp_THS_PerfectAttendanceValidation SET Comment = @Comment, UserName = @UserName, ValidationDate = GETDATE() WHERE StartDate = @StartDate AND FileNumber = @FileNumber AND EmpID = @EmpID"
                  cmd.ExecuteNonQuery()
              End If
          Else
              ' Unapproved records; Same update except that RecordType is set to "D"; Comment Required
              cmd.CommandText = "UPDATE dtsp_THS_PerfectAttendanceValidation SET RecordType = 'D', Comment = @Comment, UserName = @UserName, ValidationDate = GETDATE() WHERE StartDate = @StartDate AND FileNumber = @FileNumber AND EmpID = @EmpID"
              cmd.ExecuteNonQuery()
          End If
      Next
      ' Reload the page
      Response.Redirect(HttpContext.Current.Request.Url.ToString(), True)
  End Sub
  </code></pre>
  
  <p><strong>UPDATE:</strong> While Steve provided some great advice, I ended up having to change around the way I was doing things, because I simply COULD NOT get the values to pass from the gridview to VB variables. The only exception was the textbox I was using for <em>Comment</em>, which had to go through multipe steps before I could do anything with it. I tried using <em>row.FindControl("Comment").Text</em>, but that wouldn't work.</p>
  
  <pre><code>strComment = row.FindControl("Comment")
  strComment.Text
  </code></pre>
  
  <p>Lesson learned: Avoid having to get values from the gridview if you can. It might be possible, but it is pretty difficult to do.</p>