Solved

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

Posted on 2009-03-30
5
159 Views
Last Modified: 2013-11-26
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" />
        &nbsp; &nbsp;&nbsp;
        <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

0
Comment
Question by:jsuanque
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:David H.H.Lee
Comment Utility
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

0
 

Author Comment

by:jsuanque
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:David H.H.Lee
Comment Utility
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.


0
 
LVL 29

Accepted Solution

by:
David H.H.Lee earned 250 total points
Comment Utility
Amendment ,it should return from System.Data namespace:
cmd.Parameters.Add("@ID1", System.Data.SqlDbType.Int)
0
 

Author Closing Comment

by:jsuanque
Comment Utility
Took me awhile to absorb it ...but finally got it based on your recommendations. Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now