asked on

How to create a button which passes parameters coming from two drop down list

Hi,

I already have a button (in ASP.NET) which calls a procedure (SQL Server 2000) and generates a XML file. But i want that when the button is invoked (OnClick event) it gets the value on the two drop down list and passes it as the parameters of the procedure.

Here is my webpage code...
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SQLClient"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Style="z-index: 100; left: 264px; position: absolute;
top: 144px" Text="Button" />
    
<asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="SqlDataSource3"
DataTextField="NationalUPN" DataValueField="NationalUPN" Style="z-index: 103;
left: 248px; position: absolute; top: 48px">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:CENTRISConnectionString %>"
SelectCommand="SELECT [NationalUPN] FROM [Student]"></asp:SqlDataSource>
<asp:DropDownList ID="DropDownList4" runat="server" DataSourceID="SqlDataSource4"
DataTextField="Name" DataValueField="Name" Style="z-index: 105; left: 248px;
position: absolute; top: 80px">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:CENTRISConnectionString %>"
SelectCommand="SELECT [Name] FROM [School] ORDER BY [Name]"></asp:SqlDataSource>

</div>
</form>
</body>
</html>

And attached is my button code...

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Using conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SMI_CENTRISConnectionString").ConnectionString)
            Dim cmd As New Data.SqlClient.SqlCommand("SMI_P001_XML_Download_Test", conn)
            cmd.CommandType = Data.CommandType.StoredProcedure
            conn.Open()
            Dim da As New Data.SqlClient.SqlDataAdapter(cmd)
            Dim ds As New Data.DataSet
            da.Fill(ds)
            Dim nowString As String = Now.ToString("dd-MM-yyyy hh.mm.ss")
            Dim fileName As String = "D:\TEMP\SMI_Student_Transfer_" & nowString & ".xml"
            Dim doc As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(ds)
            Dim transform As New System.Xml.Xsl.XslCompiledTransform()
 
            transform.Load("D:\Inetpub\wwwroot\WebSite2\SMI_Tracker_Download1.xsl")
 
            Dim writer As System.Xml.XmlTextWriter = New System.Xml.XmlTextWriter(fileName, System.Text.Encoding.UTF8)
 
            writer.Formatting = System.Xml.Formatting.Indented
            writer.Indentation = 4
            transform.Transform(doc, Nothing, writer)
 
        End Using
    End Sub

Open in new window

David H.H.Lee

hi jsuanque,
>>..gets the value on the two drop down list and passes it as the parameters of the procedure
Assume the parameter for item 1 is @ID1, item 2 is @ID2 in your store procedure.
eg:

SP:>> SMI_P001_XML_Download_Test
Query:>>
Select * from tblTest where ID=@ID AND ID2=@ID2

Try this:

....            
            Dim cmd As New Data.SqlClient.SqlCommand("SMI_P001_XML_Download_Test", conn)
            cmd.CommandType = Data.CommandType.StoredProcedure
            conn.Open()
            
            'Add parameter for WHERE clause.
            cmd.Parameters.Add("@ID1", SqlDbType.Int)
            cmd.Parameters("@ID1").Value = DropDownList3.SelectedValue.ToString
 
            cmd.Parameters.Add("@ID2", SqlDbType.Int)
            cmd.Parameters("@ID2").Value = DropDownList4.SelectedValue.ToString
... 
 
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Open in new window

jsuanque

ASKER

hi X_Xom,

I've got an error..."Name 'SqlDBType" not declared."

Is this item (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx)
needs to be included? If yes, where?

Thanks.

David H.H.Lee

Hi jsuanque,
You can import this namespace:
>>System.Data.SqlClient

or declare this type separately:
>>cmd.Parameters.Add("@ID1", System.Data.SqlClient.SqlDbType.Int)

More details for SqlDbType:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.sqldbtype.aspx

Lastly, the link that i posted previously is giving you an example how to add parameters inside your existing sql command.

ASKER CERTIFIED SOLUTION

David H.H.Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsuanque

ASKER

Took me awhile to absorb it ...but finally got it based on your recommendations. Thanks