How to Pass GridView Values to SQLCommand Parameters in VB.net

Question

I have data in a GridView that is being approved by the user for specific data periods (StartDate, 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.

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 StartDate, FileNumber and EmpID. I tried running simple DELETE statements based on UserName and EmpID, and those worked. Any thoughts?

I've tried some variations of Request.QueryString("StartDate") and GridUnapprovedRecords.SelectedRow.FindControl("StartDate"), but I didn't have any luck with those.

The Error:

The parameterized query '(@UserName varchar(13),@EmpID varchar(4),@StartDate varchar(8000' expects the parameter '@StartDate', which was not supplied.

The Sub:

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

UPDATE: 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 Comment, which had to go through multipe steps before I could do anything with it. I tried using row.FindControl("Comment").Text, but that wouldn't work.

strComment = row.FindControl("Comment")
strComment.Text

Lesson learned: Avoid having to get values from the gridview if you can. It might be possible, but it is pretty difficult to do.

I store StartDate as DATE. It's like it's using VARCHAR(8000) as a default until it figures out what it's supposed to be. — grichmer
– grichmer, Commented Jun 27, 2017 at 17:27
Do not pass a VarChar parameter if you have a column of type Date. Convert the string on the grid to a date variable and pass a parameter of type Date with the date variable (and do the same with other types also, you shouldn't rely on the database to make this conversion. It will do them according to its rules on the server machine) — Steve
– Steve, Commented Jun 27, 2017 at 17:29
The date in the grid is coming from the same table that I'm updating, which means it's starting out as DATE. I had applied a MMMM yyyy mask to the date for the grid, but removing that doesn't seem to help. When I apply something like CType to convert on the vb-side, I get an error saying Conversion from string "" to type 'Date' is not valid. Should I be using a specific method for conversion? — grichmer
– grichmer, Commented Jun 27, 2017 at 17:43
Do not confuse the value of date variable with the way in which is displayed. That's a formatting applied somewhere to display the date as us humans like. To a possible way to convert look at my answer. However the fact that it shows you the cell.value as "" means that something is not correct in your choice of the column. — Steve
– Steve, Commented Jun 27, 2017 at 17:54

Steve · Accepted Answer · 2017-06-27 17:35:58Z

1

If you have a column of type Date for the StartDate field, then do not pass a parameter of type VarChar. This will force the database to execute a conversion following the rules of its installation on the server machine.

You should always pass a parameter with a value appropriate for the type of the receiving column,

So start to declare the parameter as

cmd.Parameters.Add("@StartDate", SqlDbType.Date)

then convert the cell value to a date variable and use that date to set the parameter's value. No conversion required and a date is correctly interpreted by the database engine

Dim start As DateTime
if Not DateTime.TryParse(row.Cells(0).Text, start) Then
   ' Message about invalid date and return
else
   cmd.Parameters("@StartDate").Value = start

answered Jun 27, 2017 at 17:35

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

grichmer Over a year ago

I've made the change to the definition of the parameter, but I keep getting conversion errors on the gridview's StartDate field. String was not recognized as a valid DateTime. Could you please tell me the best way to convert the value to date?

Steve Over a year ago

If your users are allowed to edit that date then use always DateTime.TryParse. If not and the source of your data is certain then you can use Convert.ToDateTime or DateTime.Parse. Can you try to use the debugger and check what is the value of the cell that you pass to the conversion method?

grichmer Over a year ago

Users are not allowed to edit the dates, and I am certain that the original field in the table is DATE (2017-05-01), though in the gridview, it displays as 5/1/2017 12:00:00 AM. Could this be causing an issue? I've never had to use Convert.ToDateTime or DateTime.Parse, but I don't seem to be having any luck with them. I keep seeing String was not recognized as a valid DateTime.

Steve Over a year ago

OK, but what you see as value of the cell if you put a breakpoint on that line and then run your code through the debugger? And what is your locale setting?

grichmer Over a year ago

I'm working with ASP.net and VB.net. To my knowledge, the only way for me to see what's being passed is to try to send the data to a SQL Server table as a string. I've gave this a shot but couldn't get that to work either. If there's another method, I'll try that. This is my first time working with vb.net and a gridview.

|

Usama Jamil · Accepted Answer · 2017-06-27 17:14:23Z

0

SQL statements and stored procedures often include parameters that are evaluated at run time. An SQL statement written with parameters is referred to as a parameterized SQL statement.

When using the SqlDataSource control, you can specify SQL queries and statements that use parameters. This helps make your data-binding scenarios more flexible by reading and writing database information based on values that are evaluated at run time. You can get parameter values from various sources, including ASP.NET application variables, user identities, and user-selected values. You can use parameters to supply search criteria for data retrieval; to supply values to be inserted, updated, or deleted in a data store; and to supply values for sorting, paging, and filtering.

SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = @EmpID"

InsertCommand="INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName);

             SELECT @EmpID = SCOPE_IDENTITY()"

UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName

               WHERE EmployeeID=@EmployeeID"

DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"

ConnectionString="<%$ ConnectionStrings:NorthwindConnection %> "OnInserted="EmployeeDetailsSqlDataSource_OnInserted" RunAt="server">

<asp:Parameter Name="EmpID" Type="Int32" DefaultValue="0" />

<asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0"/>

For more details follow https://msdn.microsoft.com/en-us/library/z72eefad.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-3

answered Jun 27, 2017 at 17:14

Usama Jamil

6610 bronze badges

3 Comments

Steve Over a year ago

Sorry but I haven't understood how this answers the question? What are you suggesting here?

grichmer Over a year ago

Yeah, I'm not sure what you're suggesting. I understand the concept of UPDATE statements. What I don't understand is how to pass the value of a cell in a gridview row to a SQLCommand parameter.

Usama Jamil Over a year ago

Follow the link msdn.microsoft.com/en-us/library/…

Collectives™ on Stack Overflow

How to Pass GridView Values to SQLCommand Parameters in VB.net

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related