Send TextField and ValueField of DropDownList to two SQL table columns

megnin
megnin used Ask the Experts™
on
How can I send the selected ListItem Text of a DropDownList to one database field and the Value to another field?

The GridView and DropDownList are databound to the [Applicants] table.  The DropDownList data source is the [Vouchers] table.
<asp:DropDownList ID="DropDownList1" runat="server" 
      	  SelectedValue='<%# Bind("VoucherTypeIssued") %>'
      	  DataTextField="VoucherType" 
      	  DataValueField="VoucherNumber"
      	  AppendDataBoundItems="True"
      	  DataSourceID="SqlDataSource3">
<asp:ListItem Text="" Value="" />
</asp:DropDownList>

SqlDataSource3 is just SelectCommand="SELECT VoucherType, VoucherNumber FROM Vouchers"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
i sthis is what you arelooking for?
int selectvalue=0;
string selectname;
     selectvalue=DropDownList1.SelectedItem.Value;
select name=DropDownList1.SelectedItem.Text;
====
use insert

insert into mytable (value,name) values(selectvalue,selectname)

Open in new window

Author

Commented:
That code wouldn't convert into VB.NET, so I'm not sure.

I'm using the code below with a different variant of the DropDownList above.  I've had no luck getting that to work, so I'm starting from scratch.  With the code below, I've never been able to get the DataKey value of the GridView row that's currently being Edited

        Dim index As Integer = GridView1.SelectedIndex  '<--Need EditIndex not SelectedIndex
        Dim ApplicantID As Integer = GridView1.DataKeys(index).Value  '<-- Trying to get selected ApplicantID from GridView.  Need it from row being Edited.  I don't want to enable Selection.
    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 index As Integer = GridView1.SelectedIndex
        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 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

Open in new window

Commented:
here is the vb.net code
Dim selectvalue As Integer = 0
Dim selectname As String
selectvalue = DropDownList1.SelectedItem.Value
selectname = DropDownList1.SelectedItem.Text


====
I understand to want write this to db then do an insert like

insert into mytable (value,name) values(selectvalue,selectname)
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Author

Commented:
No, I need to do an UPDATE not an INSERT.  :-)

This is pretty much the same thing, only this can "see" the DropDownList inside the GridView.
The problem I'm having with this is that "index" variable needs to be set to GridView1.EditIndex instead of SelectedIndex to make it work (I don't want to enable Selection.  Only Editing in the GridView.)


Dim VoucherNumber As String = CType(sender, DropDownList).SelectedValue
Dim VoucherType As String = CType(sender, DropDownList).SelectedItem.Text

Dim index As Integer = GridView1.SelectedIndex
Dim ApplicantID As Integer = GridView1.DataKeys(index).Value

Dim sqlUpdateVoucherTypeIssued As New Data.SqlClient.SqlCommand("UPDATE [Applicants] SET [VoucherTypeIssued] = @VoucherType WHERE ([ApplicantID] = @ApplicantID)", conn)
Hi,

Did you try:

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

/Carl.

Author

Commented:
Carl,
Yes, I tried that and it also worked and the correct values were sent to the Applicants table.  While in debug, with a breakpoint in the DDL changed event, I verified that [Applicants].[VoucherTypeIssued] had been updated to the correct value.  As soon as the DDL Changed event finished, the control on the .aspx page (I'm confident) is doing another update and setting that value right back to NULL.

All the code works.  To solve the problem, that last update needs to be prevented.  
*** Note that the "VoucherTypeIssued" DropDownList is bound to the "VoucherNumber" field instead of it's parent column "VoucherTypeIssued" field, by design.  But that may be what's causing the VoucherTypeIssued column to be set back to NULL in the database.

<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:
Is there an event in the DropDownList that happens later, say after the DropDownList has attempted to write values to the database, where I could change this to so that this happens after the DDL has finished writing to the database?

OnSelectedIndexChanged="ddlVoucherTypeIssued_SelectedIndexChanged"

Perhaps OnDataBound or OnUnload?

Author

Commented:
OnDataBound didn't make any difference.  OnUnload caused an error.

***  In the GridView, I click "Edit" on the row I want to issue a Voucher to that Applicant.
Then in the DropDownList I select the Voucher to issue to that person.
Instead of clicking "Save" in the GridView, I click "Cancel" and everything that went into the database when the DDL changed event fired stays.

So, it's the Gridview Save event that is overwriting the good value with NULL.

Author

Commented:
Okay, now I know that the data is actually going to both SQL tables, but the GridView Save event is overwriting one of the values.

In the GridView, I click "Edit" on the row I want to issue a Voucher to that Applicant.
Then in the DropDownList I select the Voucher to issue to that person.
Instead of clicking "Save" in the GridView, I click "Cancel" and everything that went into the database when the DDL changed event fired stays.

So, it's the Gridview Save event that is overwriting the good value with NULL.

Is there a way (or another way) to disable the Save from the GridView?  Everything that needs to happen is done in the code behind and all the GridView Save does is overwrite a good value with NULL in the database.

Author

Commented:
I'm going to try not showing the "Save" button and changing the text of the Cancel button 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:
There is really no need to click Save, Cancel or anything after makeing a selection from the DropDownList.  Is there a way to cancel "Edit Mode" programatically?
Hi,

To cancel the edit of the gridview, simply add:

GridView1.EditIndex = -1
GridView1.DataBind()

/Carl.

Author

Commented:
Yeah, that worked great.  Now I don't even have to click a button.  Just select the item from the DropDownList and done.  :-)

Author

Commented:
Thanks a lot.  I appreciate the help.  The issue I had turned out to be the GridView "Save" overwriting the value in the database with a NULL after it had already been set correctly before.

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