How to get the DataKey Value of the GridView row being Edited when Select is not enabled?

megnin
megnin used Ask the Experts™
on
In a GridView SelectedIndexChanged event I'm doing this to get the value of a DropDownList:
Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue

I need to get the value DataKey / Primary Key of the row being edited...
Dim ApplicantID As Integer = [GridView1 DataKey value]

...so I can UPDATE a column in the Applicants table with this:
... .SqlCommand("UPDATE [Applicants] SET [VoucherTypeIssued] = @VoucherType WHERE ([ApplicantID] = @ApplicantID)", conn)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
Do you have a reference to your edited row?
If so you can the value from:
CustomerGridView.DataKeys[editedRow.RowIndex].Value

Keep in mind that value is an object type, you need to cast (CType) to your relevant primary key type.

Author

Commented:
I don't have a reference to my edit row.  How would I reference it?  There's only one GridView on the page, so I just left the name GridView1.  I can post some of my existing code when I get to work this morning.  
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Author

Commented:
Informaniac, I looked at that page already.  I wasn't able to figure out much from the example.  Sorry, my experience level here is pretty low.

Author

Commented:
The GridView has two columns, VoucherNumber and VoucherTypeIssued (among others).  In the VoucherTypeIssued column I've put a DropDownList with SqlDataSource3 for the data source, but bound to the VoucherNumber field so that the VoucherNumber selected in the DropDownList goes into the database, [dbo].[Vouchers].[VoucherNumber].  I get the VoucherNumber in the GridView and the database, but I can't figure out how to also get the VoucherType into [dbo].[Vouchers].[VoucherTypeIssued].  (I'm also already doing some code behind to put 'Voucher Issued' into the [Voucher] table, [dbo].[Vouchers].[VoucherStatus].)  That's where I'm trying to reference the Edit Row of the GridView so I can pull the DataKey value, ApplicantID, to put the VoucherNumber into [dbo].[Applicants].[VoucherNumber], but I don't know how to get the ApplicantID of the row I'm Editing.  I think I could do it if I had Selection enabled in the GridView, but I don't have any use for selecting a row.  I'm just using Edit enabled.

<asp:DropDownList ID="ddlVoucherTypeIssued" runat="server" Width="100px"
                    DataSourceID="SqlDataSource3"
                    SelectedValue='<%# Bind("VoucherNumber") %>'
                    DataTextField="VoucherType"
                    DataValueField="VoucherNumber"
                    AppendDataBoundItems="True"
                    OnSelectedIndexChanged="ddlVoucherTypeIssued_SelectedIndexChanged" >
<asp:ListItem Text="" Value=""></asp:ListItem>
</asp:DropDownList>

Author

Commented:
Here's where I'm stuck:
Dim ApplicantID As Integer = CType(GridView1.???DataKeys.????

so I can do this:
sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("ApplicantID", ApplicantID)

Author

Commented:
I found some stuff and pieced this together and about to try it.
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        Dim ApplicantID As Integer = CType(sender, GridView).DataKeys(e.Row.RowIndex).Value.ToString()
        Dim VoucherType As String = CType(sender, DropDownList).SelectedValue
        Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DigitalAccessConnectionString").ConnectionString)
        Dim sqlUpdateVoucherTypeIssued As New Data.SqlClient.SqlCommand("UPDATE [Applicants] SET [VoucherTypeIssued] = @VoucherType WHERE ([ApplicantID] = @ApplicantID)", conn)
        sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("VoucherType", VoucherType)
        sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("ApplicantID", ApplicantID)
        Try
            If Not conn.State = Data.ConnectionState.Open Then
                conn.Open()
            End If
            If Not sqlUpdateVoucherTypeIssued.ExecuteNonQuery() > 0 Then
                'If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 Then
                Throw New Exception("Number of rows affected was 0 - no update occured")
            End If
        Catch ex As Exception
            Response.Write(ex)
        Finally
            If Not conn.State = Data.ConnectionState.Closed Then
                conn.Close()
            End If
        End Try

    End Sub

Open in new window

Author

Commented:
um, nope.
SqlException: Must declare the scalar variable "@ApplicantID".

Author

Commented:
oh, that was from code left in a different subroutine.  I fixed that, but it still didn't write any data to the Applicants table.

Author

Commented:
Below is the GridView markup, SqlDataSource and code behind, including commented out things that I found in Google searches and such but didn't get to work....

The one item that I need is the DataKey value for the row being edited.

Protected Sub ddlVoucherTypeIssued_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim ApplicantID As Integer = 103 '????CType(sender, GridView).DataKeys(e.Row.RowIndex).Value.ToString()

If I put "103" in the place I need the DateKey then I get the correct value in ApplicantID 103, so that all works.  I just need that value to be the ApplicantID of the row being edited.
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AutoGenerateColumns="False" DataKeyNames="ApplicantID">  
      <Columns>  
        <asp:CommandField ShowEditButton="True" UpdateText="Save" />  
        <asp:BoundField DataField="ApplicantID" HeaderText="ApplicantID" InsertVisible="False" ReadOnly="True" SortExpression="ApplicantID" Visible="False" />  
        <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" />  
        <asp:BoundField DataField="VoucherNumber" HeaderText="Voucher Number" SortExpression="VoucherNumber" />  
        <asp:BoundField DataField="VoucherTypeRequested" HeaderText="Type Requested" SortExpression="VoucherTypeRequested" />  
        <asp:TemplateField HeaderText="Type Issued" SortExpression="VoucherTypeIssued">  
                <EditItemTemplate>  
                <asp:DropDownList ID="ddlVoucherTypeIssued" runat="server" Width="100px"  
                                                  DataSourceID="SqlDataSource3"   
                                                  SelectedValue='<%# Bind("VoucherNumber") %>'  
                                                  DataTextField="VoucherType"   
                                                  DataValueField="VoucherNumber"  
                                                  AppendDataBoundItems="True"  
                                                  OnSelectedIndexChanged="ddlVoucherTypeIssued_SelectedIndexChanged"  
                                                  >  
                                                <asp:ListItem Text="" Value=""></asp:ListItem>  
                </asp:DropDownList>  
                </EditItemTemplate>  
                <ItemTemplate>  
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("VoucherTypeIssued") %>'></asp:Label>  
                </ItemTemplate>  
        </asp:TemplateField>  
      </Columns>  
</asp:GridView>  
  
  
  
<asp:SqlDataSource ID="SqlDataSource3" runat="server"   
        ConnectionString="<%$ ConnectionStrings:DigitalAccessConnectionString %>"   
        SelectCommand=" SELECT VoucherType, VoucherNumber  
                                        FROM [DigitalAccess].[dbo].[Vouchers]  
                                         WHERE VoucherNumber NOT IN  
                                          (  
                                                SELECT VoucherNumber  
                                                FROM [DigitalAccess].[dbo].[Applicants]  
                                                WHERE VoucherNumber IS NOT NULL  
                                          )  
                   ORDER BY VoucherType, VoucherNumber">  
</asp:SqlDataSource>  
  
  
'###########################################  
  
    Protected Sub ddlVoucherTypeIssued_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)  
        Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue  
        Dim VoucherType As String = CType(sender, DropDownList).SelectedItem.Text  
        Dim ApplicantID As Integer = 103 ' CType(sender, GridView).DataKeys(e.Row.RowIndex).Value.ToString()  
  
        Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DigitalAccessConnectionString").ConnectionString)  
        Dim sqlUpdateVoucherIssuingStatus As New Data.SqlClient.SqlCommand("UPDATE [Vouchers] SET [VoucherStatus] = 'Voucher Issued' WHERE ([VoucherNumber] = @VoucherNumber)", conn)  
        Dim sqlUpdateVoucherTypeIssued As New Data.SqlClient.SqlCommand("UPDATE [Applicants] SET [VoucherTypeIssued] = @VoucherType WHERE ([ApplicantID] = @ApplicantID)", conn)  
        sqlUpdateVoucherIssuingStatus.Parameters.AddWithValue("VoucherNumber", VoucherNumber)  
        sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("VoucherNumber", VoucherNumber)  
        sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("VoucherType", VoucherType)  
        sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("ApplicantID", ApplicantID)  
        Try  
            If Not conn.State = Data.ConnectionState.Open Then  
                conn.Open()  
            End If  
            If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 And Not sqlUpdateVoucherTypeIssued.ExecuteNonQuery() > 0 Then  
                'If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 Then  
                Throw New Exception("Number of rows affected was 0 - no update occured")  
            End If  
        Catch ex As Exception  
            Response.Write(ex)  
        Finally  
            If Not conn.State = Data.ConnectionState.Closed Then  
                conn.Close()  
            End If  
        End Try  
    End Sub  
  
    Protected Sub GridView1_(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs)  
        ''Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)  
        ''Public Sub GridView1_DataBind(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound  
        '    Dim ApplicantID As Integer = CType(sender, GridView).DataKeys(e.Row.RowIndex).Value.ToString()  
        '    'Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue  
        '    Dim VoucherType As String = CType(sender, DropDownList).SelectedValue  
  
        '    Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DigitalAccessConnectionString").ConnectionString)  
        '    'Dim sqlUpdateVoucherIssuingStatus As New Data.SqlClient.SqlCommand("UPDATE [Vouchers] SET [VoucherStatus] = 'Voucher Issued' WHERE ([VoucherNumber] = @VoucherNumber)", conn)  
        '    Dim sqlUpdateVoucherTypeIssued As New Data.SqlClient.SqlCommand("UPDATE [Applicants] SET [VoucherTypeIssued] = @VoucherType WHERE ([ApplicantID] = @ApplicantID)", conn)  
        '    'sqlUpdateVoucherIssuingStatus.Parameters.AddWithValue("VoucherNumber", VoucherNumber)  
        '    'sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("VoucherNumber", VoucherNumber)  
        '    sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("VoucherType", VoucherType)  
        '    sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("ApplicantID", ApplicantID)  
        '    Try  
        '        If Not conn.State = Data.ConnectionState.Open Then  
        '            conn.Open()  
        '        End If  
        '        'If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 And Not sqlUpdateVoucherTypeIssued.ExecuteNonQuery() > 0 Then  
        '        If Not sqlUpdateVoucherTypeIssued.ExecuteNonQuery() > 0 Then  
        '            'If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 Then  
        '            Throw New Exception("Number of rows affected was 0 - no update occured")  
        '        End If  
        '    Catch ex As Exception  
        '        Response.Write(ex)  
        '    Finally  
        '        If Not conn.State = Data.ConnectionState.Closed Then  
        '            conn.Close()  
        '        End If  
        '    End Try  
  
    End Sub  
  
    Protected Sub GridView_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)  
        If e.Row.RowType = DataControlRowType.DataRow AndAlso (e.Row.RowState And DataControlRowState.Edit) = DataControlRowState.Edit Then  
            ' Find control  
            Dim dl As DropDownList = DirectCast(e.Row.FindControl("ddlVoucherTypeIssued"), DropDownList)  
        End If  
  
    End Sub  
    Sub GridView1_RowUpdated(ByVal sender As Object, ByVal e As GridViewUpdatedEventArgs)  
  
        ' The update operation was successful. Retrieve the row being edited.  
        Dim index As Integer = GridView1.EditIndex  
        Dim row As GridViewRow = GridView1.Rows(index)  
  
        ' Notify the user that the update was successful.  
        Console.Write("Updated record " & row.Cells(1).Text + ".")  
  
    End Sub 
Toggle HighlightingOpen in New WindowSelect All

Open in new window

Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
Ok now I can see your issue:
Protected Sub ddlVoucherTypeIssued_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
{
   Dim editedRow As GridViewRow = CType(CType(sender, Control).NamingContainer, GridViewRow)
   Dim ApplicantID As Integer =  CType(GridView1.DataKeys[editedRow.RowIndex].Value, Integer)

  'Your code follows
}

above I show you how to get the target row and the ApplicantID the rest of your code requires.

Author

Commented:
:-)  That looks like what I'm looking for.  Let me go plug it in.  Be right back...

Author

Commented:
I'm not familiar with this problem or how to fix it...
10-20-2010-5-53-19-PM.png

Author

Commented:
After accepting the suggested solution and the suggested solution for the problem that created and got this:

 CType(GridView1.DataKeys,[editedRow].RowIndex.Value], Integer)

I've got a "Name ApplicantID" is not declared, I'm trying to find the cause of and hopefully this will do the trick...
Senior Software Engineer
Top Expert 2009
Commented:
My bad in VB.net you use () not [] for indexing: (I was translating my C# code)
Protected Sub ddlVoucherTypeIssued_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
{
   Dim editedRow As GridViewRow = CType(CType(sender, Control).NamingContainer, GridViewRow)
   Dim ApplicantID As Integer =  CType(GridView1.DataKeys(editedRow.RowIndex).Value, Integer)

  'Your code follows
}

Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
Can you check that DataKeyNames="ApplicantID" is still on your markup. if you refresh schema it is deleted from gridview.
Put a breakpoint here
Dim ApplicantID As Integer =  CType(GridView1.DataKeys(editedRow.RowIndex).Value, Integer)
and tell the contents of:
GridView1.DataKeys
and
GridView1.DataKeys(editedRow.RowIndex).Value

Author

Commented:
Oh, yes, I didn't think of that.  I should have realized I never see square brackes anywhere. ;-)

Author

Commented:
I think the ApplicantID thing was a result of the square brackets too.

Author

Commented:
Yes, () resolved the ApplicantID not declared issue.  VoucherTypeIssued did not go into the database so I'm going to set the breakpoints and see what the values are.

Author

Commented:
I selected the second row down to edit.  The ApplicantID of that person is '8'.  There are a total of 28 people in the table.

With the breakpoint set, the ApplicantID value was '8', good.
GridView1.DataKeys value was 28, the total number of records.
editedRow.RowIndex was 1

According to the values it should have worked, but nothing went into the VoucherTypeIssued field of the Applicants table.

With an ApplicantID of an existing applicant hard coded in, the correct VoucherTypeIssued went into that applicant record, so I'm going to examine my code more closely and see if I changed something I shouldn't have, maybe a typo somewhere or something.  The values look like they should work...

I'm only going to work on this a few minutes more today then I have to go home and I'll resume in the morning.  I think this is going to work, though.  Thank you!

Author

Commented:
I hard coded
Dim ApplicantID As Integer = 73

and the VoucherTypeIssued value went into the correct record.
I'll set the breakpoint again and look closely at the format of the ApplicantID value.  That 8 was a correct value, though.

Author

Commented:
Wierd.  I checked the database after each step.

After line 5 below, the correct VoucherTypeIssued value was in the VoucherTypeIssued field of the Applicants table and VoucherNumber was still NULL.

After the data connection closed, VoucherTypeIssued was NULL and VoucherNumber had the correct number in it.  It was like the UPDATE to VoucherTypeIssued transaction was Rolled Back.


       Try
            If Not conn.State = Data.ConnectionState.Open Then
                conn.Open()
            End If
            If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 And Not sqlUpdateVoucherTypeIssued.ExecuteNonQuery() > 0 Then
                'If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 Then
                Throw New Exception("Number of rows affected was 0 - no update occured")
            End If
        Catch ex As Exception
            Response.Write(ex)
        Finally
            If Not conn.State = Data.ConnectionState.Closed Then
                conn.Close()
            End If
        End Try

Open in new window

Author

Commented:
Not Rolled Back, I don't think.  The value was not erased  on the conn.Close().  It was not erased until after the End Sub.

Right after the End Sub, I checked it and it was NULL, the I stepped again and
<ItemTemplate>
   <asp:Label ID="Label1" runat="server" Text='<%# Eval("VoucherTypeIssued") %>'></asp:Label>
</ItemTemplate>

the ItemTemplate label for VoucherTypeIssued looped through all 28 rows.  I think the control on the .aspx page had something to do with it.  Maybe because I have the dropdownlist in the EditItemTemplate of teh VoucherTypeIssued, but have it databound to the VoucherNumber,  SelectedValue='<%# Bind("VoucherNumber") %>'

Here is the DDL:
<asp:DropDownList ID="ddlVoucherTypeIssued" runat="server"
    DataSourceID="SqlDataSource3" 
    SelectedValue='<%# Bind("VoucherNumber") %>'
    DataTextField="VoucherType" 
    DataValueField="VoucherNumber"
    AppendDataBoundItems="True"
    OnSelectedIndexChanged="ddlVoucherTypeIssued_SelectedIndexChanged">
  <asp:ListItem Text="" Value=""></asp:ListItem>
</asp:DropDownList>

Open in new window

Author

Commented:
I changed the SelectedValue in the DDL to:
SelectedValue='<%# Bind("VoucherTypeIssued") %>'

and I just got the VoucherNumber in the VoucherTypeIssued field.
I couldn't find, like, a SelectedText or something to bind with.  I think if I just remove it, I may get either two NULL fields instead of one or an error...
Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
What about this value:
GridView1.DataKeys(editedRow.RowIndex).Value

Are you sure ApplicantID is stored as integer in your SqlDataSource1 table?
try Dim ApplicantID As String =  CType(GridView1.DataKeys[editedRow.RowIndex].Value, String)

I m going to a meeting now, I wil get back to you later

Author

Commented:
No, the original code works perfectly.  All the data that is supposed to go into the Applicant record goes in.  After the Sub finishes, then the VoucherTypeIssued field which had the good value written to it becomes NULL again.

Author

Commented:
I was thinking of trying out putting a TextBox into the EditItemTemplate along with the DropDownList and binding the textbox to VoucherTypeIssued and unbinding the DDL, which still has SqlDataSource3 to populate it.  ...just trial and error... probably more "error" ;-)

Author

Commented:
Okay, all that did was break it.  ;-)

I'm still sure it's the DDL that is responsible for taking the good value that your code puts into the database and makes it NULL again.  The value is actually in the SQL database after the code executes but before the Sub ends.  Then when the Sub ends, something, probably the DDL is setting it back to NULL.  
Miguel OzSenior Software Engineer
Top Expert 2009

Commented:
I am glad tha my code solved your original question, but I do not quite follow your C#/SQL code:
Is these the lines you are refering?
Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue  
Dim VoucherType As String = CType(sender, DropDownList).SelectedValue  
if so try:  
Dim VoucherNumber As String = String.Copy(CType(sender, DropDownList).SelectedValue)  
Dim VoucherType As String = String.Copy(CType(sender, DropDownList).SelectedValue)

and make sure it contains a value.
I am making a clean copy in case the reference is being updated when you update the table. Be aware that markup binding updates your data every time you change your table.
Another option you have is to use ObjectDataSource and write your SQL statements there, but I have not done this before with a SQL db.  
Hi,

To start with, you state:

"In a GridView SelectedIndexChanged event I'm doing this to get the value of a DropDownList:
Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue"

"sender" in this context would then be the gridview, not the dropdownlist? To properly reference the dropdownlist you would need to do something like:

Dim VoucherNumber As String = Ctype(e.Row.FindControl("ddlVoucherTypeIssued"), DropDownList).SelectedValue.ToString()

And to get the DataKey value for the current row being edited:

Dim ApplicantID As Integer = CInt(GridView1.DataKeys(GridView1.EditIndex).Value)

Or am I completely missing the point here?

/Carl.

Author

Commented:
mas_oz2003, I replaced...
Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue  
Dim VoucherType As String = CType(sender, DropDownList).SelectedValue  
with... "if so try:  "...
Dim VoucherNumber As String = String.Copy(CType(sender, DropDownList).SelectedValue)  
Dim VoucherType As String = String.Copy(CType(sender, DropDownList).SelectedValue)

and it did the exact same.  All the correct values go into the database, but when the ddlVoucherTypeIssued_SelectedIndexChanged event, which is called from the OnSelectedIndexChanged of the DropDownList, ddlVoucherTypeIssued, finishes, then the value in [Applicants].[VoucherTypeIssue] gets set back to NULL.

Current pertinent markup, datasource, code behind and table structure below for reference:
<asp:DropDownList ID="ddlVoucherTypeIssued" runat="server"
    DataSourceID="SqlDataSource3" 
    SelectedValue='<%# Bind("VoucherNumber") %>'
    DataTextField="VoucherType" 
    DataValueField="VoucherNumber"
    AppendDataBoundItems="True"
    OnSelectedIndexChanged="ddlVoucherTypeIssued_SelectedIndexChanged">
  <asp:ListItem Text="" Value=""></asp:ListItem>
</asp:DropDownList>

<asp:SqlDataSource ID="SqlDataSource3" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConectionString%>" 
SelectCommand="SELECT VoucherType, VoucherNumber
FROM [DigitalAccess].[dbo].[Vouchers]

'#########################################################################

    Protected Sub ddlVoucherTypeIssued_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        'Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue
        'Dim VoucherType As String = CType(sender, DropDownList).SelectedItem.Text
        Dim VoucherNumber As String = String.Copy(CType(sender, DropDownList).SelectedValue)
        Dim VoucherType As String = String.Copy(CType(sender, DropDownList).SelectedItem.Text)

        Dim index As Integer = GridView1.SelectedIndex
        Dim editedRow As GridViewRow = CType(CType(sender, Control).NamingContainer, GridViewRow)
        Dim ApplicantID As Integer = CType(GridView1.DataKeys(editedRow.RowIndex).Value, Integer)

        'Dim ApplicantID As Integer =  GridView1.DataKeys(index).Value ' CType(sender, GridView).DataKeys(e.Row.RowIndex).Value.ToString()
        'Dim oTest As Integer = CType(Me.GridView1.Rows(index).FindControl("ddlVoucherTypeIssued"), DropDownList).SelectedValue

        Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("DigitalAccessConnectionString").ConnectionString)
        Dim sqlUpdateVoucherIssuingStatus As New Data.SqlClient.SqlCommand("UPDATE [Vouchers] SET [VoucherStatus] = 'Voucher Issued' WHERE ([VoucherNumber] = @VoucherNumber)", conn)
        Dim sqlUpdateVoucherTypeIssued As New Data.SqlClient.SqlCommand("UPDATE [Applicants] SET [VoucherTypeIssued] = @VoucherType, [VoucherNumber] = @VoucherNumber WHERE ([ApplicantID] = @ApplicantID)", conn)
        sqlUpdateVoucherIssuingStatus.Parameters.AddWithValue("VoucherNumber", VoucherNumber)
        sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("VoucherNumber", VoucherNumber)
        sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("VoucherType", VoucherType)
        sqlUpdateVoucherTypeIssued.Parameters.AddWithValue("ApplicantID", ApplicantID)
        Try
            If Not conn.State = Data.ConnectionState.Open Then
                conn.Open()
            End If
            If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 And Not sqlUpdateVoucherTypeIssued.ExecuteNonQuery() > 0 Then
                'If Not sqlUpdateVoucherIssuingStatus.ExecuteNonQuery() > 0 Then
                Throw New Exception("Number of rows affected was 0 - no update occured")
            End If
        Catch ex As Exception
            Response.Write(ex)
        Finally
            If Not conn.State = Data.ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
    End Sub

'############################################################################

And the tables from the SQL 2005 db:

CREATE TABLE [dbo].[Applicants]
(
  [ApplicantID] [int] IDENTITY(1,1) NOT NULL,
  [LastName] [varchar](50) NULL,
  [VoucherTypeIssued] [varchar](50) NULL,
  [VoucherNumber] [varchar](25) NULL,
(

CREATE TABLE [dbo].[Vouchers]
(
  [VoucherID] [int] IDENTITY(1,1) NOT NULL,
  [VoucherNumber] [varchar](25) NULL,
  [VoucherType] [varchar](50) NULL,
  [VoucherStatus] [varchar](25) NULL,
(

Open in new window

Author

Commented:
Carl, I don't think you're missing the point.

Yesterday, when you asked me to debug step through the code checking the values, I found that these lines do produce the correct values and they get written  to the database correctly.

Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue
Dim VoucherType As String = CType(sender, DropDownList).SelectedItem.Text
Dim ApplicantID As Integer = CType(GridView1.DataKeys(editedRow.RowIndex).Value, Integer)

The remaining problems is that last something causing a final update to the database setting [Applicants].[VoucherTypeIssued] back to NULL, erasing the correct value.  This is happening immediately after the DDL Index Changed even is finished.

Author

Commented:
The culpret seems to be the GridView "Save", so I'm going to try not showing the "Save" button and changing the Cancel button text to "Save".

<asp:CommandField ShowEditButton="True" ShowSelectButton="False" UpdateText="" CancelText="Save" />

Author

Commented:
That worked.  ;-)  I set the command field buttons just like above, so when the user things they are clicking the "Save" button, they are just clicking the "Cancel" button because the data has already been written to the DB.

I also put another SqlCommand in the code behind to make the adjustment in the case an existing Voucher is reissued as a different Type, or more accurately if a person who already has a voucher is selected and a different voucher is then selected for them.  Carl, I used your hidden field code to enable me to do that.    Thank you very much for that.  It works like a charm.  :-D

Author

Commented:
Thanks a lot for the help!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial