Need help with updating a gridview from code-behind

Hi,

I've got a gridview that displays some (but not all) columns from a sql database table. One of the columns that is NOT displayed is the table's primary key column. I'm putting together this row updating sub based on an article I found on the 'Net. Here's the code I've added for the GridView's edit routine (based on the article):

Protected Sub gvPersonnelType_RowEditing(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.GridViewEditEventArgs)Handles gvPersonnelType.RowEditinggvPersonnelType.EditIndex = e.NewEditIndexgvPersonnelTypeBindData()End SubProtected Sub gvPersonnelType_RowCancellingEdit(ByVal senderAs Object,ByVal eAs GridViewCancelEditEventArgs)gvPersonnelType.EditIndex = -1gvPersonnelTypeBindData()End SubProtected Sub gvPersonnelType_RowUpdating(ByVal senderAs Object,ByVal eAs GridViewUpdateEventArgs)'Dim PersonnelTypeID As Integer'This is not displayed in the gridview, but is the primary key in the databaseDim LaborForceTypeIDAs IntegerDim DefaultPayRate, DefaultPieceRate, RebillAs Decimal'These are money datatype in the databaseDim DefaultAdminCostPercent, MarkUpAs Double'These are float datatype in the database

'Don't know if the following lines properly "capture" the values in the textboxesLaborForceTypeID =Integer.Parse(gvPersonnelType.Rows(e.RowIndex).Cells(0).Text)DefaultPayRate =CType(gvPersonnelType.Rows(e.RowIndex).Cells(1).Controls(0), TextBox).TextDefaultPieceRate =CType(gvPersonnelType.Rows(e.RowIndex).Cells(2).Controls(0), TextBox).TextDefaultAdminCostPercent =CType(gvPersonnelType.Rows(e.RowIndex).Cells(3).Controls(0), TextBox).TextRebill =CType(gvPersonnelType.Rows(e.RowIndex).Cells(4).Controls(0), TextBox).TextMarkUp =CType(gvPersonnelType.Rows(e.RowIndex).Cells(5).Controls(0), TextBox).TextDim gvSqlConn =New SqlConnection("DATABASECONNECTION")gvSqlConn.open()Dim UpdatePersonnelTypeCmdAs New SqlCommand("UPDATE rsrc_PersonnelType SET LaborForceTypeID = @.LaborForceTypeID, " & _"DefaultPayRate = @.DefaultPayRate, DefaultPieceRate = @.DefaultPieceRate, " & _"DefaultAdminCostPercent = @.DefaultAdminCostPercent, Rebill = @.Rebill, Markup = @.Markup", gvSqlConn)'Sql doesn't know what row to update: NEED WHERE STATEMENT!!!UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.LaborForceTypeID", LaborForceTypeID))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.DefaultPayRate", DefaultPayRate))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.DefaultPieceRate", DefaultPieceRate))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.DefaultAdminCostPercent", DefaultAdminCostPercent))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.Rebill", Rebill))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.Markup", MarkUp))UpdatePersonnelTypeCmd.ExecuteNonQuery()gvSqlConn.close()gvPersonnelType.EditIndex = -1gvPersonnelTypeBindData()End Sub

As you can see, I've added some notes in the RowUpdating sub. I don't understand how to use the PersonnelTypeID (the database table's primary key) for the WHERE clause to update the correct row in the database.

As for the GridView itself, it's populated from the code-behind as well (as you can see from the call to "gvPersonnelTypeBindData()") and the SELECT statement used to populate the gridview doesn't use the PersonnelTypeID column either.

I'd appreciate it if someone could point me in the right direction to get this RowUpdating sub working.

Thanks!

Check out DataKeys property of GridView. If data key column is known then in your update method you can ask object id = gridView.DataKeys[e.RowIndex].Value;|||

Hey, DigiMortal. Thanks for replying.

Well, can I just set the DataKeyName property of the Gridivew on the aspx side to the table's primary key column? That doesn't seem to me (in my limited experience, mind you) that doing that would work, would it? I'm skeptical of that only because the primary key column isn't in the SELECT statement, either, so the GridView (and, hence any related changes to the data it displays) isn't really "aware" of the primary key column anyway, is it?

Looking at that code snippet you posted, (I work in VB, so it's "Dim id As Object = gridView.DataKeys(e.RowIndex).Value"), I'm not sure I understand how to implement that into my code. If my primary key (and hence the DataKeyName) is PersonnelTypeID, then would that line be: "Dim PersonnelTypeID As Object = gridView.DataKeys(e.RowIndex).Value"?

Obviously, I'm a bit confused. Could you clarify some?

Thanks!

|||

That would be correct. You need to add the PrimaryKey column to your select list as well.

Then the where clause would be:

WHEREPrimaryKeyField=@.PK

Then add the PK value like:

UpdatePersonnelTypeCmd.Parameters.Add("@.PK",SqlDbType.Int).Value=e.Keys(0)

or

UpdatePersonnelTypeCmd.Parameters.Add("@.PK",SqlDbType.Int).Value=Gridview1.Datakeys(e.RowIndex).Value

|||

Hey, Motley, thanks for piping in.

I did get somewhere on my sub yesterday, but can't tell if I'm getting it right due to another issue going on.

What I did was this:

Dim PersonnelTypeIDAs Object = gvPersonnelType.DataKeys(e.RowIndex).Value'Connection info here...Dim UpdatePersonnelTypeCmdAs New SqlCommand("UPDATE table SET column1 = @.parameter1, " & _" column2 = @.parameter2... WHERE PersonnelTypeID = " & PersonnelTypeID, gvSqlConn)'Then I went and added my sql parameters:UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.parameter1", column1))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.parameter2", column2))'Write to the database table...'Note no mention of the PersonnelTypeID (the PK) other than the first line

But in your (Motley's) post you add the PK field in the parameters, which makes more sense to me than the way I did it. Or did I essentially do that in the first "Dim" line in my code above?

Either way, I can't find out if this is working or not, because I've got another issue going on in this sub as well:

If I leave a textbox empty and click the "Update" link in the GridView, the program errors on this line:

DefaultPieceRate =CType(gvPersonnelType.Rows(e.RowIndex).FindControl("txtEditDefaultPieceRate"), TextBox).Text

Default Piece Rate is a money datatype in the database table and the error reads: "InvalidCastException was unhandled by user code. Conversion from string "" to type 'Decimal' is not valid. When casting from a number, the value must be a number less than infinity."

Obviously, it's becuase the textbox is empty that it's trying to convert "" to decimal. I can have empty textboxes in my INSERT sub and that works, but I'm not quite sure how to allow for empty textboxes in this context.

Could I get some help ironing out this problem so I can get back to the original, PK parameter issue ?

Thanks!

|||

Okay, I got everything working in this sub now! I'll post the working version below, so anyone having the same problem can compare and see how I solved it.

I know this turned out to be a long post, but I learned a lot working on these two issues, so hopefully I can save someone else's time and pain by providing the lessons I learned...

As far as the PK issue, that was a matter of setting the DataKeyNames property of the GridView on the aspx side (I suppose you could do it from the code-behind) to the PK column name. Then I instantiated it as such:

Dim PersonnelTypeIDAs Object = gvPersonnelType.DataKeys(e.RowIndex).Value

Then I simply used that in my UPDATE statement in the where clause:

 ...WHERE PersonnelTypeID = @.PersonnelTypeID"

And since it's a parameter, as Motley suggested in his post, I added this line to my parameters section:

UpdatePersonnelTypeCmd.Parameters.Add("@.PersonnelTypeID", SqlDbType.Int).Value = gvPersonnelType.DataKeys(e.RowIndex).Value

Viola! I have my PK to update my table. Oh, yeah. The PK column has to be in the SELECT statement as well (I know, that's obvious to a lot of people, but I'm still learning and I know there are plenty of other noobs reading these posts, as well).

Then there's the other issue, I was getting an error on blank textboxes. That was solved quite simply by CASTing the offending parameter to the database table's corresponding datatype:

UPDATE tableNameSET column1 = @.parameter1, column2 =CAST(@.parameter2AS money), ...

So, here's the complete working sub:

Protected Sub gvPersonnelType_RowUpdating(ByVal senderAs Object,ByVal eAs GridViewUpdateEventArgs)Dim PersonnelTypeIDAs Object = gvPersonnelType.DataKeys(e.RowIndex).ValueDim LaborForceTypeIDAs IntegerDim DefaultPayRate, DefaultPieceRate, Rebill, DefaultAdminCostPercent, MarkUpAs StringLaborForceTypeID =CType(Me.gvPersonnelType.Rows(e.RowIndex).FindControl("txtEditLaborForceTypeID"), TextBox).TextDefaultPayRate =CType(gvPersonnelType.Rows(e.RowIndex).FindControl("txtEditDefaultPayRate"), TextBox).TextDefaultPieceRate =CType(gvPersonnelType.Rows(e.RowIndex).FindControl("txtEditDefaultPieceRate"), TextBox).TextDefaultAdminCostPercent =CType(gvPersonnelType.Rows(e.RowIndex).FindControl("txtEditDefaultAdminCostPct"), TextBox).TextRebill =CType(gvPersonnelType.Rows(e.RowIndex).FindControl("txtEditRebill"), TextBox).TextMarkUp =CType(gvPersonnelType.Rows(e.RowIndex).FindControl("txtEditMarkUp"), TextBox).TextDim gvSqlConn =New SqlConnection("DATABASECONNECTION")Dim UpdatePersonnelTypeCmdAs New SqlCommand("UPDATE rsrc_PersonnelType SET LaborForceTypeID = @.LaborForceTypeID, " & _"DefaultPayRate = CAST(@.DefaultPayRate AS money), DefaultPieceRate = CAST(@.DefaultPieceRate AS money), " & _"DefaultAdminCostPercent = CAST(@.DefaultAdminCostPercent AS decimal), Rebill = CAST(@.Rebill AS money), " & _"Markup = CAST(@.Markup AS decimal) WHERE PersonnelTypeID = @.PersonnelTypeID", gvSqlConn)UpdatePersonnelTypeCmd.Parameters.Add("@.PersonnelTypeID", SqlDbType.Int).Value = gvPersonnelType.DataKeys(e.RowIndex).ValueUpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.LaborForceTypeID", LaborForceTypeID))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.DefaultPayRate", DefaultPayRate))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.DefaultPieceRate", DefaultPieceRate))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.DefaultAdminCostPercent", DefaultAdminCostPercent))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.Rebill", Rebill))UpdatePersonnelTypeCmd.Parameters.Add(New SqlParameter("@.Markup", MarkUp))Using gvSqlConn gvSqlConn.open()UpdatePersonnelTypeCmd.ExecuteNonQuery()gvSqlConn.close()End UsinggvPersonnelType.EditIndex = -1gvPersonnelType.PageIndex = 0gvPersonnelTypeBindData()End Sub

Hope that helps someone out there!

Cheers!

Now on to the next problem (it's a never-ending thing...)