Update a SQL databaseusing a dropdown list,

[grafiksinc]

Hello all,

First off I am using VB.net
I have a dropdown list in the Item template.
I would like the user to select a value and update the database without clicking an update button.

Here is the Code for the DDL

<asp:DropDownList ID="DropDownList1" runat="server" 
                            DataSourceID="SqlDataSource2" DataTextField="provider" 
                            DataValueField="provider" AutoPostBack="True" AppendDataboundItems="true" OnSelectedIndexChanged = "ddl_SelectedChange"
                             SelectedValue='<%# Bind("provider") %>'>
                            <asp:ListItem Value=""></asp:ListItem>
                        </asp:DropDownList>

here is my Behind code I am not sure what to put after the Dim statement

Protected Sub ddl_SelectedChange(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim ddl = DirectCast(sender, DropDownList)

Any help would be greatly appreciated. :)

End Sub

[kvprajapati]

>here is my Behind code I am not sure what to put after the Dim statement

Write code which updates your database.

Steps:

1. Create connection & command objects
2. Open connection and execute the command.
3. Close the connection.

[reach_yousuf]

Hi Dude

Certains things are unclear
1)Is your dropdown list in Grid view
2) if yes, is a row by row edit or full grid in edit mode

[grafiksinc]

Hi Dude

Certains things are unclear
1)Is your dropdown list in Grid view
2) if yes, is a row by row edit or full grid in edit mode

Its in a grid view and its in Normale mode but i put the Dropdown list in the ItemTemplate.
Thanks

[dnanetwork]

create an ItemTemplate
Add DropDown...Bind DropDown

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
       // Do your Update thing..for DropDown and Update the database..
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        DropDownList dr = (DropDownList)e.Row.FindControl("DropDownList1");

    }

[grafiksinc]

I will translate that to VB and see how it goes.
Will update you tomorrow.

Tommy

[grafiksinc]

All,

Just to make sure I am clear my goal is when the user selects a value from the dropdown the autopostback change will be updated in the database.

Here the behind code I came up with:
But I think I am missing something because on this line:

[B]Dim stselectedDatakey As String = GridView1.DataKeys(row.RowIndex).Values("Datakey1").ToString()
 [/B]

I get Object reference not set to an instance of an object. When a selected a value in the dropdowm list.

Public Sub dd_OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        'This will get the dropdownlist which has been clicked
        Dim ddl As DropDownList = DirectCast(sender, DropDownList)

        'The row will be the currently selected row from which the dropdownlist
        'was clicked
        Dim row As GridViewRow = DirectCast(ddl.NamingContainer, GridViewRow)

        'Now we know which row is clicked we can get the datakey for that row

        Dim stselectedDatakey As String = GridView1.DataKeys(row.RowIndex).Values("Datakey1").ToString()

        'Standard stored procedure can be used to update the database.
        'The selected value is the new value to be updated into the database
        'stselectedDatakey is the datakey for that row

        Dim sqlcommand_ddupdate As New SqlCommand("update_table", con)
        sqlcommand_ddupdate.CommandType = CommandType.StoredProcedure

        sqlcommand_ddupdate.Parameters.Add(New SqlParameter("@value1", SqlDbType.Int))
        sqlcommand_ddupdate.Parameters("@value1").Value = ddl.SelectedValue

        sqlcommand_ddupdate.Parameters.Add(New SqlParameter("@value2", SqlDbType.VarChar))
        sqlcommand_ddupdate.Parameters("@value2").Value = stselectedDatakey
    End Sub

[grafiksinc]

Alright I have solved my Problem

Please see the code below.
Here is the .ASP side

<asp:DropDownList ID="provider" runat="server" 
                            DataSourceID="dr" DataTextField="provider" 
                            DataValueField="provider" AutoPostBack="True" AppendDataboundItems="true" OnSelectedIndexChanged="dd_OnSelectedIndexChanged"
                             SelectedValue='<%# Bind("provider") %>'>
                            <asp:ListItem Value=""></asp:ListItem>
                         </asp:DropDownList>

Here is the Behind Code

Protected Sub dd_OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        '*******************************************************
        '*This will get the dropdownlist which has been clicked*
        '*******************************************************
        Dim ddl As DropDownList
        ddl = DirectCast(sender, DropDownList)
        '***********************************************************************
        '*The row will be the currently selected row from which the dropdownlist*
        '*was clicked                                                           *
        '***********************************************************************
        Dim row As GridViewRow = DirectCast(ddl.NamingContainer, GridViewRow)
        '******************************
        '*Get the datakey for that row*
        '******************************
        Dim DataKey As String = GridView1.DataKeys(row.RowIndex).Values("IEN").ToString()
        '**********************
        '*Define Data Objects *
        '**********************
        Dim conn As SqlConnection
        Dim comm As SqlCommand

        '******************************************
        '*Read the connectionstring from we.config*
        '******************************************
        Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("ER_BloodHoundConnectionString").ConnectionString
        '***********************
        '*Initialize Connection*
        '***********************
        conn = New SqlConnection(ConnectionString)
        '****************
        '*Create Command*
        '****************
        comm = New SqlCommand("UPDATE VW_Paitent SET provider=@provider WHERE IEN=@IEN", conn)
        '************************
        '*Add command Perameters*
        '************************
        comm.Parameters.Add("@Provider", System.Data.SqlDbType.NVarChar, 50)
        comm.Parameters("@Provider").Value = ddl.SelectedItem.Value

        comm.Parameters.Add("@IEN", System.Data.SqlDbType.Float)
        comm.Parameters("@IEN").Value = DataKey
        '*******************************************
        '*Enclose Database code in Try catch Finaly*
        '*******************************************
        Try
            '*********************
            '*Open the connection*
            '*********************
            conn.Open()
            '*********************
            '*Execute The command*
            '*********************
            comm.ExecuteNonQuery()
        Finally
            '**********************
            '*Close the connection*
            '**********************
            conn.Close()
        End Try
    End Sub