Professional Web Applications Themes

DDL values with DataReader and stored procedures - ASP.NET General

For the code below, how could I add an item in the drop down lists for both company and location to be an "All" selection that would send to the stored proc. spRptAttachments a value of "%" so that it would bring back all attachments at all companies or all locations at a company? Thank you, Rob. Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then Dim cmd As System.Data.SqlClient.SqlCommand cmd = New System.Data.SqlClient.SqlCommand ("Web_CompanyList", Me.SqlConnection1) cmd.CommandType = CommandType.StoredProcedure Me.SqlConnection1.Open() Dim myDataReader As System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader (CommandBehavior.CloseConnection) ddlCompany.DataSource = myDataReader ddlCompany.DataValueField = "CompanyID" ...

  1. #1

    Default DDL values with DataReader and stored procedures

    For the code below, how could I add an item in the drop
    down lists for both company and location to be an "All"
    selection that would send to the stored proc.
    spRptAttachments a value of "%" so that it would bring
    back all attachments at all companies or all locations at
    a company? Thank you, Rob.

    Private Sub Page_Load(ByVal sender As System.Object,
    ByVal e As System.EventArgs) Handles MyBase.Load
    If Not IsPostBack Then
    Dim cmd As System.Data.SqlClient.SqlCommand
    cmd = New System.Data.SqlClient.SqlCommand
    ("Web_CompanyList", Me.SqlConnection1)
    cmd.CommandType = CommandType.StoredProcedure
    Me.SqlConnection1.Open()
    Dim myDataReader As
    System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    (CommandBehavior.CloseConnection)
    ddlCompany.DataSource = myDataReader
    ddlCompany.DataValueField = "CompanyID"
    ddlCompany.DataTextField = "CompanyName"
    ddlCompany.DataBind()
    ddlCompany_SelectedIndexChanged(Nothing,
    Nothing)
    End If
    End Sub

    Private Sub ddlCompany_SelectedIndexChanged(ByVal
    sender As System.Object, ByVal e As System.EventArgs)
    Handles ddlCompany.SelectedIndexChanged
    Dim param As System.Data.SqlClient.SqlParameter
    Dim cmd As System.Data.SqlClient.SqlCommand
    Dim intCompanyID As Integer
    cmd = New System.Data.SqlClient.SqlCommand
    ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
    cmd.CommandType = CommandType.StoredProcedure
    param = cmd.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("CompanyID",
    SqlDbType.Int))
    param.Direction = ParameterDirection.Input
    ' ********Selected Value of Option List*******
    param.Value = ddlCompany.Items
    (ddlCompany.SelectedIndex).Value
    Me.SqlConnection1.Open()
    Dim myDataReader As
    System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    (CommandBehavior.CloseConnection)
    ddlLocation.DataSource = myDataReader
    ddlLocation.DataValueField = "CompanyLocationID"
    ddlLocation.DataTextField = "CoLo"
    ddlLocation.DataBind()
    End Sub

    Private Sub btnSearch_Click(ByVal sender As
    System.Object, ByVal e As System.EventArgs) Handles
    btnSearch.Click
    Dim prmDateFrom As
    System.Data.SqlClient.SqlParameter
    Dim prmCoID As System.Data.SqlClient.SqlParameter
    Dim prmColo As System.Data.SqlClient.SqlParameter
    Dim cmd As System.Data.SqlClient.SqlCommand
    Dim intCompanyID As Integer
    cmd = New System.Data.SqlClient.SqlCommand
    ("spRptAttachments", Me.SqlConnection1)
    cmd.CommandType = CommandType.StoredProcedure
    prmDateFrom = cmd.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("DateFrom",
    SqlDbType.DateTime))
    prmCoID = cmd.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("CompanyID",
    SqlDbType.Int))
    prmColo = cmd.Parameters.Add(New
    System.Data.SqlClient.SqlParameter("CompanyLocati onID",
    SqlDbType.Int))
    prmDateFrom.Direction = ParameterDirection.Input
    prmCoID.Direction = ParameterDirection.Input
    prmColo.Direction = ParameterDirection.Input
    ' ********Selected Value of Option List*******
    prmDateFrom.Value = tbDateFrom.Text
    prmCoID.Value = ddlCompany.Items
    (ddlCompany.SelectedIndex).Value
    prmColo.Value = ddlLocation.Items
    (ddlLocation.SelectedIndex).Value
    Me.SqlConnection1.Open()
    Dim myDataReader As
    System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    (CommandBehavior.CloseConnection)
    DataGrid1.DataSource = myDataReader
    DataGrid1.DataBind()

    End Sub

    Rob Wire Guest

  2. #2

    Default Re: DDL values with DataReader and stored procedures

    After you bind your dropdownlist and close the datareader just do an
    items.insert "Select All" at position 0. Next line would be to set
    items(0).value to % or whatever character you want.

    (Note sometimes I do "Choose One" as my position 0 with a value of empty
    string "" so I can use a required field validator. Same concept)

    Hope this helps.

    "Rob Wire" <STShotmail.com> wrote in message
    news:8e2e01c35b64$9c7a8c00$a001280aphx.gbl...
    > For the code below, how could I add an item in the drop
    > down lists for both company and location to be an "All"
    > selection that would send to the stored proc.
    > spRptAttachments a value of "%" so that it would bring
    > back all attachments at all companies or all locations at
    > a company? Thank you, Rob.
    >
    > Private Sub Page_Load(ByVal sender As System.Object,
    > ByVal e As System.EventArgs) Handles MyBase.Load
    > If Not IsPostBack Then
    > Dim cmd As System.Data.SqlClient.SqlCommand
    > cmd = New System.Data.SqlClient.SqlCommand
    > ("Web_CompanyList", Me.SqlConnection1)
    > cmd.CommandType = CommandType.StoredProcedure
    > Me.SqlConnection1.Open()
    > Dim myDataReader As
    > System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    > (CommandBehavior.CloseConnection)
    > ddlCompany.DataSource = myDataReader
    > ddlCompany.DataValueField = "CompanyID"
    > ddlCompany.DataTextField = "CompanyName"
    > ddlCompany.DataBind()
    > ddlCompany_SelectedIndexChanged(Nothing,
    > Nothing)
    > End If
    > End Sub
    >
    > Private Sub ddlCompany_SelectedIndexChanged(ByVal
    > sender As System.Object, ByVal e As System.EventArgs)
    > Handles ddlCompany.SelectedIndexChanged
    > Dim param As System.Data.SqlClient.SqlParameter
    > Dim cmd As System.Data.SqlClient.SqlCommand
    > Dim intCompanyID As Integer
    > cmd = New System.Data.SqlClient.SqlCommand
    > ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
    > cmd.CommandType = CommandType.StoredProcedure
    > param = cmd.Parameters.Add(New
    > System.Data.SqlClient.SqlParameter("CompanyID",
    > SqlDbType.Int))
    > param.Direction = ParameterDirection.Input
    > ' ********Selected Value of Option List*******
    > param.Value = ddlCompany.Items
    > (ddlCompany.SelectedIndex).Value
    > Me.SqlConnection1.Open()
    > Dim myDataReader As
    > System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    > (CommandBehavior.CloseConnection)
    > ddlLocation.DataSource = myDataReader
    > ddlLocation.DataValueField = "CompanyLocationID"
    > ddlLocation.DataTextField = "CoLo"
    > ddlLocation.DataBind()
    > End Sub
    >
    > Private Sub btnSearch_Click(ByVal sender As
    > System.Object, ByVal e As System.EventArgs) Handles
    > btnSearch.Click
    > Dim prmDateFrom As
    > System.Data.SqlClient.SqlParameter
    > Dim prmCoID As System.Data.SqlClient.SqlParameter
    > Dim prmColo As System.Data.SqlClient.SqlParameter
    > Dim cmd As System.Data.SqlClient.SqlCommand
    > Dim intCompanyID As Integer
    > cmd = New System.Data.SqlClient.SqlCommand
    > ("spRptAttachments", Me.SqlConnection1)
    > cmd.CommandType = CommandType.StoredProcedure
    > prmDateFrom = cmd.Parameters.Add(New
    > System.Data.SqlClient.SqlParameter("DateFrom",
    > SqlDbType.DateTime))
    > prmCoID = cmd.Parameters.Add(New
    > System.Data.SqlClient.SqlParameter("CompanyID",
    > SqlDbType.Int))
    > prmColo = cmd.Parameters.Add(New
    > System.Data.SqlClient.SqlParameter("CompanyLocati onID",
    > SqlDbType.Int))
    > prmDateFrom.Direction = ParameterDirection.Input
    > prmCoID.Direction = ParameterDirection.Input
    > prmColo.Direction = ParameterDirection.Input
    > ' ********Selected Value of Option List*******
    > prmDateFrom.Value = tbDateFrom.Text
    > prmCoID.Value = ddlCompany.Items
    > (ddlCompany.SelectedIndex).Value
    > prmColo.Value = ddlLocation.Items
    > (ddlLocation.SelectedIndex).Value
    > Me.SqlConnection1.Open()
    > Dim myDataReader As
    > System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    > (CommandBehavior.CloseConnection)
    > DataGrid1.DataSource = myDataReader
    > DataGrid1.DataBind()
    >
    > End Sub
    >

    Eric Wise Guest

  3. #3

    Default Re: DDL values with DataReader and stored procedures

    Hello Rob,

    I have read the code that you paste. It seems that you populate companyID and name in ddlCompany drop down list. Then
    populate ddlLocation drop down list according to the selected value in ddlCompany.

    In the search button onclick handler, you ran a SP according to values from drop down list.

    However, I am quite clear on your questions? Could you please explain more on what you want to implement, what the
    problem is so that we could provide more information to you?

    Thanks very much.

    Best regards,
    Yanhong Huang
    Microsoft Online Partner Support

    Get Secure! - [url]www.microsoft.com/security[/url]
    This posting is provided "AS IS" with no warranties, and confers no rights.

    --------------------
    !Content-Class: urn:content-classes:message
    !From: "Rob Wire" <stshotmail.com>
    !Sender: "Rob Wire" <stshotmail.com>
    !References: <8e2e01c35b64$9c7a8c00$a001280aphx.gbl> <#e5ogy2WDHA.3924tk2msftngp13.phx.gbl>
    !Subject: Re: DDL values with DataReader and stored procedures
    !Date: Wed, 6 Aug 2003 12:29:31 -0700
    !Lines: 122
    !Message-ID: <070a01c35c51$0f20f3b0$a101280aphx.gbl>
    !MIME-Version: 1.0
    !Content-Type: text/plain;
    ! cht="iso-8859-1"
    !Content-Transfer-Encoding: 7bit
    !X-Newsreader: Microsoft CDO for Windows 2000
    !Thread-Index: AcNcUQ8g52Xt2LxCT8+b0l82kNicsQ==
    !X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    !Newsgroups: microsoft.public.dotnet.framework.aspnet
    !Path: cpmsftngxa06.phx.gbl
    !Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet:165489
    !NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
    !X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    !
    !How could I apply the below to a wildcard int primary key
    !id field? i.e. to avoid getting:
    !
    !Syntax error converting the varchar value '%' to a column
    !of data type int
    !
    !>-----Original Message-----
    !>After you bind your dropdownlist and close the datareader
    !just do an
    !>items.insert "Select All" at position 0. Next line would
    !be to set
    !>items(0).value to % or whatever character you want.
    !>
    !>(Note sometimes I do "Choose One" as my position 0 with a
    !value of empty
    !>string "" so I can use a required field validator. Same
    !concept)
    !>
    !>Hope this helps.
    !>
    !>"Rob Wire" <STShotmail.com> wrote in message
    !>news:8e2e01c35b64$9c7a8c00$a001280aphx.gbl...
    !>> For the code below, how could I add an item in the drop
    !>> down lists for both company and location to be an "All"
    !>> selection that would send to the stored proc.
    !>> spRptAttachments a value of "%" so that it would bring
    !>> back all attachments at all companies or all locations
    !at
    !>> a company? Thank you, Rob.
    !>>
    !>> Private Sub Page_Load(ByVal sender As System.Object,
    !>> ByVal e As System.EventArgs) Handles MyBase.Load
    !>> If Not IsPostBack Then
    !>> Dim cmd As System.Data.SqlClient.SqlCommand
    !>> cmd = New System.Data.SqlClient.SqlCommand
    !>> ("Web_CompanyList", Me.SqlConnection1)
    !>> cmd.CommandType =
    !CommandType.StoredProcedure
    !>> Me.SqlConnection1.Open()
    !>> Dim myDataReader As
    !>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    !>> (CommandBehavior.CloseConnection)
    !>> ddlCompany.DataSource = myDataReader
    !>> ddlCompany.DataValueField = "CompanyID"
    !>> ddlCompany.DataTextField = "CompanyName"
    !>> ddlCompany.DataBind()
    !>> ddlCompany_SelectedIndexChanged(Nothing,
    !>> Nothing)
    !>> End If
    !>> End Sub
    !>>
    !>> Private Sub ddlCompany_SelectedIndexChanged(ByVal
    !>> sender As System.Object, ByVal e As System.EventArgs)
    !>> Handles ddlCompany.SelectedIndexChanged
    !>> Dim param As System.Data.SqlClient.SqlParameter
    !>> Dim cmd As System.Data.SqlClient.SqlCommand
    !>> Dim intCompanyID As Integer
    !>> cmd = New System.Data.SqlClient.SqlCommand
    !>> ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
    !>> cmd.CommandType = CommandType.StoredProcedure
    !>> param = cmd.Parameters.Add(New
    !>> System.Data.SqlClient.SqlParameter("CompanyID",
    !>> SqlDbType.Int))
    !>> param.Direction = ParameterDirection.Input
    !>> ' ********Selected Value of Option List*******
    !>> param.Value = ddlCompany.Items
    !>> (ddlCompany.SelectedIndex).Value
    !>> Me.SqlConnection1.Open()
    !>> Dim myDataReader As
    !>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    !>> (CommandBehavior.CloseConnection)
    !>> ddlLocation.DataSource = myDataReader
    !>> ddlLocation.DataValueField = "CompanyLocationID"
    !>> ddlLocation.DataTextField = "CoLo"
    !>> ddlLocation.DataBind()
    !>> End Sub
    !>>
    !>> Private Sub btnSearch_Click(ByVal sender As
    !>> System.Object, ByVal e As System.EventArgs) Handles
    !>> btnSearch.Click
    !>> Dim prmDateFrom As
    !>> System.Data.SqlClient.SqlParameter
    !>> Dim prmCoID As
    !System.Data.SqlClient.SqlParameter
    !>> Dim prmColo As
    !System.Data.SqlClient.SqlParameter
    !>> Dim cmd As System.Data.SqlClient.SqlCommand
    !>> Dim intCompanyID As Integer
    !>> cmd = New System.Data.SqlClient.SqlCommand
    !>> ("spRptAttachments", Me.SqlConnection1)
    !>> cmd.CommandType = CommandType.StoredProcedure
    !>> prmDateFrom = cmd.Parameters.Add(New
    !>> System.Data.SqlClient.SqlParameter("DateFrom",
    !>> SqlDbType.DateTime))
    !>> prmCoID = cmd.Parameters.Add(New
    !>> System.Data.SqlClient.SqlParameter("CompanyID",
    !>> SqlDbType.Int))
    !>> prmColo = cmd.Parameters.Add(New
    !>> System.Data.SqlClient.SqlParameter("CompanyLocati onID",
    !>> SqlDbType.Int))
    !>> prmDateFrom.Direction = ParameterDirection.Input
    !>> prmCoID.Direction = ParameterDirection.Input
    !>> prmColo.Direction = ParameterDirection.Input
    !>> ' ********Selected Value of Option List*******
    !>> prmDateFrom.Value = tbDateFrom.Text
    !>> prmCoID.Value = ddlCompany.Items
    !>> (ddlCompany.SelectedIndex).Value
    !>> prmColo.Value = ddlLocation.Items
    !>> (ddlLocation.SelectedIndex).Value
    !>> Me.SqlConnection1.Open()
    !>> Dim myDataReader As
    !>> System.Data.Sqlclient.SqlDataReader = cmd.ExecuteReader
    !>> (CommandBehavior.CloseConnection)
    !>> DataGrid1.DataSource = myDataReader
    !>> DataGrid1.DataBind()
    !>>
    !>> End Sub
    !>>
    !>
    !>
    !>.
    !>
    !


    Yan-Hong Huang[MSFT] Guest

  4. #4

    Default Re: DDL values with DataReader and stored procedures

    What I would like to do is have the ddl have a fake record
    in the list that would send to the stored procedure a wild
    card like '%' that would work with the int value of the
    primary key field in the table that the drop down is
    displaying, hence to return all rows no matter what the id
    is if that is selected.
    >-----Original Message-----
    >Hello Rob,
    >
    >I have read the code that you paste. It seems that you
    populate companyID and name in ddlCompany drop down list.
    Then
    >populate ddlLocation drop down list according to the
    selected value in ddlCompany.
    >
    >In the search button onclick handler, you ran a SP
    according to values from drop down list.
    >
    >However, I am quite clear on your questions? Could you
    please explain more on what you want to implement, what
    the
    >problem is so that we could provide more information to
    you?
    >
    >Thanks very much.
    >
    >Best regards,
    >Yanhong Huang
    >Microsoft Online Partner Support
    >
    >Get Secure! - [url]www.microsoft.com/security[/url]
    >This posting is provided "AS IS" with no warranties, and
    confers no rights.
    >
    >--------------------
    >!Content-Class: urn:content-classes:message
    >!From: "Rob Wire" <stshotmail.com>
    >!Sender: "Rob Wire" <stshotmail.com>
    >!References: <8e2e01c35b64$9c7a8c00$a001280aphx.gbl>
    <#e5ogy2WDHA.3924tk2msftngp13.phx.gbl>
    >!Subject: Re: DDL values with DataReader and stored
    procedures
    >!Date: Wed, 6 Aug 2003 12:29:31 -0700
    >!Lines: 122
    >!Message-ID: <070a01c35c51$0f20f3b0$a101280aphx.gbl>
    >!MIME-Version: 1.0
    >!Content-Type: text/plain;
    >! cht="iso-8859-1"
    >!Content-Transfer-Encoding: 7bit
    >!X-Newsreader: Microsoft CDO for Windows 2000
    >!Thread-Index: AcNcUQ8g52Xt2LxCT8+b0l82kNicsQ==
    >!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    >!Newsgroups: microsoft.public.dotnet.framework.aspnet
    >!Path: cpmsftngxa06.phx.gbl
    >!Xref: cpmsftngxa06.phx.gbl
    microsoft.public.dotnet.framework.aspnet:165489
    >!NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
    >!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    >!
    >!How could I apply the below to a wildcard int primary
    key
    >!id field? i.e. to avoid getting:
    >!
    >!Syntax error converting the varchar value '%' to a
    column
    >!of data type int
    >!
    >!>-----Original Message-----
    >!>After you bind your dropdownlist and close the
    datareader
    >!just do an
    >!>items.insert "Select All" at position 0. Next line
    would
    >!be to set
    >!>items(0).value to % or whatever character you want.
    >!>
    >!>(Note sometimes I do "Choose One" as my position 0 with
    a
    >!value of empty
    >!>string "" so I can use a required field validator.
    Same
    >!concept)
    >!>
    >!>Hope this helps.
    >!>
    >!>"Rob Wire" <STShotmail.com> wrote in message
    >!>news:8e2e01c35b64$9c7a8c00$a001280aphx.gbl.. .
    >!>> For the code below, how could I add an item in the
    drop
    >!>> down lists for both company and location to be
    an "All"
    >!>> selection that would send to the stored proc.
    >!>> spRptAttachments a value of "%" so that it would bring
    >!>> back all attachments at all companies or all
    locations
    >!at
    >!>> a company? Thank you, Rob.
    >!>>
    >!>> Private Sub Page_Load(ByVal sender As
    System.Object,
    >!>> ByVal e As System.EventArgs) Handles MyBase.Load
    >!>> If Not IsPostBack Then
    >!>> Dim cmd As
    System.Data.SqlClient.SqlCommand
    >!>> cmd = New System.Data.SqlClient.SqlCommand
    >!>> ("Web_CompanyList", Me.SqlConnection1)
    >!>> cmd.CommandType =
    >!CommandType.StoredProcedure
    >!>> Me.SqlConnection1.Open()
    >!>> Dim myDataReader As
    >!>> System.Data.Sqlclient.SqlDataReader =
    cmd.ExecuteReader
    >!>> (CommandBehavior.CloseConnection)
    >!>> ddlCompany.DataSource = myDataReader
    >!>> ddlCompany.DataValueField = "CompanyID"
    >!>> ddlCompany.DataTextField = "CompanyName"
    >!>> ddlCompany.DataBind()
    >!>> ddlCompany_SelectedIndexChanged(Nothing,
    >!>> Nothing)
    >!>> End If
    >!>> End Sub
    >!>>
    >!>> Private Sub ddlCompany_SelectedIndexChanged(ByVal
    >!>> sender As System.Object, ByVal e As System.EventArgs)
    >!>> Handles ddlCompany.SelectedIndexChanged
    >!>> Dim param As
    System.Data.SqlClient.SqlParameter
    >!>> Dim cmd As System.Data.SqlClient.SqlCommand
    >!>> Dim intCompanyID As Integer
    >!>> cmd = New System.Data.SqlClient.SqlCommand
    >!>> ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
    >!>> cmd.CommandType = CommandType.StoredProcedure
    >!>> param = cmd.Parameters.Add(New
    >!>> System.Data.SqlClient.SqlParameter("CompanyID",
    >!>> SqlDbType.Int))
    >!>> param.Direction = ParameterDirection.Input
    >!>> ' ********Selected Value of Option List*******
    >!>> param.Value = ddlCompany.Items
    >!>> (ddlCompany.SelectedIndex).Value
    >!>> Me.SqlConnection1.Open()
    >!>> Dim myDataReader As
    >!>> System.Data.Sqlclient.SqlDataReader =
    cmd.ExecuteReader
    >!>> (CommandBehavior.CloseConnection)
    >!>> ddlLocation.DataSource = myDataReader
    >!>> ddlLocation.DataValueField
    = "CompanyLocationID"
    >!>> ddlLocation.DataTextField = "CoLo"
    >!>> ddlLocation.DataBind()
    >!>> End Sub
    >!>>
    >!>> Private Sub btnSearch_Click(ByVal sender As
    >!>> System.Object, ByVal e As System.EventArgs) Handles
    >!>> btnSearch.Click
    >!>> Dim prmDateFrom As
    >!>> System.Data.SqlClient.SqlParameter
    >!>> Dim prmCoID As
    >!System.Data.SqlClient.SqlParameter
    >!>> Dim prmColo As
    >!System.Data.SqlClient.SqlParameter
    >!>> Dim cmd As System.Data.SqlClient.SqlCommand
    >!>> Dim intCompanyID As Integer
    >!>> cmd = New System.Data.SqlClient.SqlCommand
    >!>> ("spRptAttachments", Me.SqlConnection1)
    >!>> cmd.CommandType = CommandType.StoredProcedure
    >!>> prmDateFrom = cmd.Parameters.Add(New
    >!>> System.Data.SqlClient.SqlParameter("DateFrom",
    >!>> SqlDbType.DateTime))
    >!>> prmCoID = cmd.Parameters.Add(New
    >!>> System.Data.SqlClient.SqlParameter("CompanyID",
    >!>> SqlDbType.Int))
    >!>> prmColo = cmd.Parameters.Add(New
    >!>> System.Data.SqlClient.SqlParameter
    ("CompanyLocationID",
    >!>> SqlDbType.Int))
    >!>> prmDateFrom.Direction =
    ParameterDirection.Input
    >!>> prmCoID.Direction = ParameterDirection.Input
    >!>> prmColo.Direction = ParameterDirection.Input
    >!>> ' ********Selected Value of Option List*******
    >!>> prmDateFrom.Value = tbDateFrom.Text
    >!>> prmCoID.Value = ddlCompany.Items
    >!>> (ddlCompany.SelectedIndex).Value
    >!>> prmColo.Value = ddlLocation.Items
    >!>> (ddlLocation.SelectedIndex).Value
    >!>> Me.SqlConnection1.Open()
    >!>> Dim myDataReader As
    >!>> System.Data.Sqlclient.SqlDataReader =
    cmd.ExecuteReader
    >!>> (CommandBehavior.CloseConnection)
    >!>> DataGrid1.DataSource = myDataReader
    >!>> DataGrid1.DataBind()
    >!>>
    >!>> End Sub
    >!>>
    >!>
    >!>
    >!>.
    >!>
    >!
    >
    >
    >.
    >
    Rob Wire Guest

  5. #5

    Default Re: DDL values with DataReader and stored procedures

    Hello Rob,

    Sorry for the late response. I am not quite familar with SQL statements. So I am not sure of how to transfer a value like % for a
    int value type. What I knew is that we could only use %, * in string type values.

    My suggestion is to implement it in stored procedure level. For an example, when stored procedure received one specific
    value, it will return all the rows no matter what ID is selected.

    Please let me know if you have any concerns on it. Thanks.

    Best regards,
    Yanhong Huang
    Microsoft Online Partner Support

    Get Secure! - [url]www.microsoft.com/security[/url]
    This posting is provided "AS IS" with no warranties, and confers no rights.

    --------------------
    !Content-Class: urn:content-classes:message
    !From: "Rob Wire" <stshotmail.com>
    !Sender: "Rob Wire" <stshotmail.com>
    !References: <8e2e01c35b64$9c7a8c00$a001280aphx.gbl> <#e5ogy2WDHA.3924tk2msftngp13.phx.gbl>
    <070a01c35c51$0f20f3b0$a101280aphx.gbl> <mqllFiLXDHA.1536cpmsftngxa06.phx.gbl>
    !Subject: Re: DDL values with DataReader and stored procedures
    !Date: Fri, 8 Aug 2003 12:39:42 -0700
    !Lines: 205
    !Message-ID: <010501c35de4$d028b040$a601280aphx.gbl>
    !MIME-Version: 1.0
    !Content-Type: text/plain;
    ! cht="iso-8859-1"
    !Content-Transfer-Encoding: 7bit
    !X-Newsreader: Microsoft CDO for Windows 2000
    !X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    !Thread-Index: AcNd5NAmk8VGjaZQT1K+C0BMkc2Vuw==
    !Newsgroups: microsoft.public.dotnet.framework.aspnet
    !Path: cpmsftngxa06.phx.gbl
    !Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet:166257
    !NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
    !X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    !
    !What I would like to do is have the ddl have a fake record
    !in the list that would send to the stored procedure a wild
    !card like '%' that would work with the int value of the
    !primary key field in the table that the drop down is
    !displaying, hence to return all rows no matter what the id
    !is if that is selected.
    !
    !>-----Original Message-----
    !>Hello Rob,
    !>
    !>I have read the code that you paste. It seems that you
    !populate companyID and name in ddlCompany drop down list.
    !Then
    !>populate ddlLocation drop down list according to the
    !selected value in ddlCompany.
    !>
    !>In the search button onclick handler, you ran a SP
    !according to values from drop down list.
    !>
    !>However, I am quite clear on your questions? Could you
    !please explain more on what you want to implement, what
    !the
    !>problem is so that we could provide more information to
    !you?
    !>
    !>Thanks very much.
    !>
    !>Best regards,
    !>Yanhong Huang
    !>Microsoft Online Partner Support
    !>
    !>Get Secure! - [url]www.microsoft.com/security[/url]
    !>This posting is provided "AS IS" with no warranties, and
    !confers no rights.
    !>
    !>--------------------
    !>!Content-Class: urn:content-classes:message
    !>!From: "Rob Wire" <stshotmail.com>
    !>!Sender: "Rob Wire" <stshotmail.com>
    !>!References: <8e2e01c35b64$9c7a8c00$a001280aphx.gbl>
    !<#e5ogy2WDHA.3924tk2msftngp13.phx.gbl>
    !>!Subject: Re: DDL values with DataReader and stored
    !procedures
    !>!Date: Wed, 6 Aug 2003 12:29:31 -0700
    !>!Lines: 122
    !>!Message-ID: <070a01c35c51$0f20f3b0$a101280aphx.gbl>
    !>!MIME-Version: 1.0
    !>!Content-Type: text/plain;
    !>! cht="iso-8859-1"
    !>!Content-Transfer-Encoding: 7bit
    !>!X-Newsreader: Microsoft CDO for Windows 2000
    !>!Thread-Index: AcNcUQ8g52Xt2LxCT8+b0l82kNicsQ==
    !>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    !>!Newsgroups: microsoft.public.dotnet.framework.aspnet
    !>!Path: cpmsftngxa06.phx.gbl
    !>!Xref: cpmsftngxa06.phx.gbl
    !microsoft.public.dotnet.framework.aspnet:165489
    !>!NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
    !>!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    !>!
    !>!How could I apply the below to a wildcard int primary
    !key
    !>!id field? i.e. to avoid getting:
    !>!
    !>!Syntax error converting the varchar value '%' to a
    !column
    !>!of data type int
    !>!
    !>!>-----Original Message-----
    !>!>After you bind your dropdownlist and close the
    !datareader
    !>!just do an
    !>!>items.insert "Select All" at position 0. Next line
    !would
    !>!be to set
    !>!>items(0).value to % or whatever character you want.
    !>!>
    !>!>(Note sometimes I do "Choose One" as my position 0 with
    !a
    !>!value of empty
    !>!>string "" so I can use a required field validator.
    !Same
    !>!concept)
    !>!>
    !>!>Hope this helps.
    !>!>
    !>!>"Rob Wire" <STShotmail.com> wrote in message
    !>!>news:8e2e01c35b64$9c7a8c00$a001280aphx.gbl.. .
    !>!>> For the code below, how could I add an item in the
    !drop
    !>!>> down lists for both company and location to be
    !an "All"
    !>!>> selection that would send to the stored proc.
    !>!>> spRptAttachments a value of "%" so that it would bring
    !>!>> back all attachments at all companies or all
    !locations
    !>!at
    !>!>> a company? Thank you, Rob.
    !>!>>
    !>!>> Private Sub Page_Load(ByVal sender As
    !System.Object,
    !>!>> ByVal e As System.EventArgs) Handles MyBase.Load
    !>!>> If Not IsPostBack Then
    !>!>> Dim cmd As
    !System.Data.SqlClient.SqlCommand
    !>!>> cmd = New System.Data.SqlClient.SqlCommand
    !>!>> ("Web_CompanyList", Me.SqlConnection1)
    !>!>> cmd.CommandType =
    !>!CommandType.StoredProcedure
    !>!>> Me.SqlConnection1.Open()
    !>!>> Dim myDataReader As
    !>!>> System.Data.Sqlclient.SqlDataReader =
    !cmd.ExecuteReader
    !>!>> (CommandBehavior.CloseConnection)
    !>!>> ddlCompany.DataSource = myDataReader
    !>!>> ddlCompany.DataValueField = "CompanyID"
    !>!>> ddlCompany.DataTextField = "CompanyName"
    !>!>> ddlCompany.DataBind()
    !>!>> ddlCompany_SelectedIndexChanged(Nothing,
    !>!>> Nothing)
    !>!>> End If
    !>!>> End Sub
    !>!>>
    !>!>> Private Sub ddlCompany_SelectedIndexChanged(ByVal
    !>!>> sender As System.Object, ByVal e As System.EventArgs)
    !>!>> Handles ddlCompany.SelectedIndexChanged
    !>!>> Dim param As
    !System.Data.SqlClient.SqlParameter
    !>!>> Dim cmd As System.Data.SqlClient.SqlCommand
    !>!>> Dim intCompanyID As Integer
    !>!>> cmd = New System.Data.SqlClient.SqlCommand
    !>!>> ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
    !>!>> cmd.CommandType = CommandType.StoredProcedure
    !>!>> param = cmd.Parameters.Add(New
    !>!>> System.Data.SqlClient.SqlParameter("CompanyID",
    !>!>> SqlDbType.Int))
    !>!>> param.Direction = ParameterDirection.Input
    !>!>> ' ********Selected Value of Option List*******
    !>!>> param.Value = ddlCompany.Items
    !>!>> (ddlCompany.SelectedIndex).Value
    !>!>> Me.SqlConnection1.Open()
    !>!>> Dim myDataReader As
    !>!>> System.Data.Sqlclient.SqlDataReader =
    !cmd.ExecuteReader
    !>!>> (CommandBehavior.CloseConnection)
    !>!>> ddlLocation.DataSource = myDataReader
    !>!>> ddlLocation.DataValueField
    != "CompanyLocationID"
    !>!>> ddlLocation.DataTextField = "CoLo"
    !>!>> ddlLocation.DataBind()
    !>!>> End Sub
    !>!>>
    !>!>> Private Sub btnSearch_Click(ByVal sender As
    !>!>> System.Object, ByVal e As System.EventArgs) Handles
    !>!>> btnSearch.Click
    !>!>> Dim prmDateFrom As
    !>!>> System.Data.SqlClient.SqlParameter
    !>!>> Dim prmCoID As
    !>!System.Data.SqlClient.SqlParameter
    !>!>> Dim prmColo As
    !>!System.Data.SqlClient.SqlParameter
    !>!>> Dim cmd As System.Data.SqlClient.SqlCommand
    !>!>> Dim intCompanyID As Integer
    !>!>> cmd = New System.Data.SqlClient.SqlCommand
    !>!>> ("spRptAttachments", Me.SqlConnection1)
    !>!>> cmd.CommandType = CommandType.StoredProcedure
    !>!>> prmDateFrom = cmd.Parameters.Add(New
    !>!>> System.Data.SqlClient.SqlParameter("DateFrom",
    !>!>> SqlDbType.DateTime))
    !>!>> prmCoID = cmd.Parameters.Add(New
    !>!>> System.Data.SqlClient.SqlParameter("CompanyID",
    !>!>> SqlDbType.Int))
    !>!>> prmColo = cmd.Parameters.Add(New
    !>!>> System.Data.SqlClient.SqlParameter
    !("CompanyLocationID",
    !>!>> SqlDbType.Int))
    !>!>> prmDateFrom.Direction =
    !ParameterDirection.Input
    !>!>> prmCoID.Direction = ParameterDirection.Input
    !>!>> prmColo.Direction = ParameterDirection.Input
    !>!>> ' ********Selected Value of Option List*******
    !>!>> prmDateFrom.Value = tbDateFrom.Text
    !>!>> prmCoID.Value = ddlCompany.Items
    !>!>> (ddlCompany.SelectedIndex).Value
    !>!>> prmColo.Value = ddlLocation.Items
    !>!>> (ddlLocation.SelectedIndex).Value
    !>!>> Me.SqlConnection1.Open()
    !>!>> Dim myDataReader As
    !>!>> System.Data.Sqlclient.SqlDataReader =
    !cmd.ExecuteReader
    !>!>> (CommandBehavior.CloseConnection)
    !>!>> DataGrid1.DataSource = myDataReader
    !>!>> DataGrid1.DataBind()
    !>!>>
    !>!>> End Sub
    !>!>>
    !>!>
    !>!>
    !>!>.
    !>!>
    !>!
    !>
    !>
    !>.
    !>
    !


    Yan-Hong Huang[MSFT] Guest

  6. #6

    Default Re: DDL values with DataReader and stored procedures

    Hello Rob,

    Do you still have any question on the issue? Please post here if you have
    follow up questions.

    For SQL server stored procedures, please refer to SQL books online to get
    code samples. I think the logic should be:

    USE pubs
    GO
    CREATE PROCEDURE au_info
    ID int,
    AS
    IF (ID == ***)
    THEN
    SELECT * from ...
    ELSE
    SELECT au_lname, au_fname, title, pub_name
    FROM authors a INNER JOIN titleauthor ta
    ON a.au_id = ta.au_id INNER JOIN titles t
    ON t.title_id = ta.title_id INNER JOIN publishers p
    ON t.pub_id = p.pub_id
    WHERE au_fname = ID
    GO

    Hope that helps.

    Best regards,
    Yanhong Huang
    Microsoft Online Partner Support

    Get Secure! C www.microsoft.com/security
    This posting is provided "AS IS" with no warranties, and confers no rights.

    --------------------
    !Content-Class: urn:content-classes:message
    !From: "Rob Wire" <com>
    !Sender: "Rob Wire" <com>
    !References: <8e2e01c35b64$9c7a8c00$gbl>
    <#phx.gbl>
    <070a01c35c51$0f20f3b0$gbl>
    <phx.gbl>
    !Subject: Re: DDL values with DataReader and stored procedures
    !Date: Fri, 8 Aug 2003 12:39:42 -0700
    !Lines: 205
    !Message-ID: <010501c35de4$d028b040$gbl>
    !MIME-Version: 1.0
    !Content-Type: text/plain;
    ! cht="iso-8859-1"
    !Content-Transfer-Encoding: 7bit
    !X-Newsreader: Microsoft CDO for Windows 2000
    !X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    !Thread-Index: AcNd5NAmk8VGjaZQT1K+C0BMkc2Vuw==
    !Newsgroups: microsoft.public.dotnet.framework.aspnet
    !Path: cpmsftngxa06.phx.gbl
    !Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.aspnet:166257
    !NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
    !X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    !
    !What I would like to do is have the ddl have a fake record
    !in the list that would send to the stored procedure a wild
    !card like '%' that would work with the int value of the
    !primary key field in the table that the drop down is
    !displaying, hence to return all rows no matter what the id
    !is if that is selected.
    !
    !>-----Original Message-----
    !>Hello Rob,
    !>
    !>I have read the code that you paste. It seems that you
    !populate companyID and name in ddlCompany drop down list.
    !Then
    !>populate ddlLocation drop down list according to the
    !selected value in ddlCompany.
    !>
    !>In the search button onclick handler, you ran a SP
    !according to values from drop down list.
    !>
    !>However, I am quite clear on your questions? Could you
    !please explain more on what you want to implement, what
    !the
    !>problem is so that we could provide more information to
    !you?
    !>
    !>Thanks very much.
    !>
    !>Best regards,
    !>Yanhong Huang
    !>Microsoft Online Partner Support
    !>
    !>Get Secure! - www.microsoft.com/security
    !>This posting is provided "AS IS" with no warranties, and
    !confers no rights.
    !>
    !>--------------------
    !>!Content-Class: urn:content-classes:message
    !>!From: "Rob Wire" <com>
    !>!Sender: "Rob Wire" <com>
    !>!References: <8e2e01c35b64$9c7a8c00$gbl>
    !<#phx.gbl>
    !>!Subject: Re: DDL values with DataReader and stored
    !procedures
    !>!Date: Wed, 6 Aug 2003 12:29:31 -0700
    !>!Lines: 122
    !>!Message-ID: <070a01c35c51$0f20f3b0$gbl>
    !>!MIME-Version: 1.0
    !>!Content-Type: text/plain;
    !>! cht="iso-8859-1"
    !>!Content-Transfer-Encoding: 7bit
    !>!X-Newsreader: Microsoft CDO for Windows 2000
    !>!Thread-Index: AcNcUQ8g52Xt2LxCT8+b0l82kNicsQ==
    !>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    !>!Newsgroups: microsoft.public.dotnet.framework.aspnet
    !>!Path: cpmsftngxa06.phx.gbl
    !>!Xref: cpmsftngxa06.phx.gbl
    !microsoft.public.dotnet.framework.aspnet:165489
    !>!NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
    !>!X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    !>!
    !>!How could I apply the below to a wildcard int primary
    !key
    !>!id field? i.e. to avoid getting:
    !>!
    !>!Syntax error converting the varchar value '%' to a
    !column
    !>!of data type int
    !>!
    !>!>-----Original Message-----
    !>!>After you bind your dropdownlist and close the
    !datareader
    !>!just do an
    !>!>items.insert "Select All" at position 0. Next line
    !would
    !>!be to set
    !>!>items(0).value to % or whatever character you want.
    !>!>
    !>!>(Note sometimes I do "Choose One" as my position 0 with
    !a
    !>!value of empty
    !>!>string "" so I can use a required field validator.
    !Same
    !>!concept)
    !>!>
    !>!>Hope this helps.
    !>!>
    !>!>"Rob Wire" <com> wrote in message
    !>!>news:8e2e01c35b64$9c7a8c00$gbl...
    !>!>> For the code below, how could I add an item in the
    !drop
    !>!>> down lists for both company and location to be
    !an "All"
    !>!>> selection that would send to the stored proc.
    !>!>> spRptAttachments a value of "%" so that it would bring
    !>!>> back all attachments at all companies or all
    !locations
    !>!at
    !>!>> a company? Thank you, Rob.
    !>!>>
    !>!>> Private Sub Page_Load(ByVal sender As
    !System.Object,
    !>!>> ByVal e As System.EventArgs) Handles MyBase.Load
    !>!>> If Not IsPostBack Then
    !>!>> Dim cmd As
    !System.Data.SqlClient.SqlCommand
    !>!>> cmd = New System.Data.SqlClient.SqlCommand
    !>!>> ("Web_CompanyList", Me.SqlConnection1)
    !>!>> cmd.CommandType =
    !>!CommandType.StoredProcedure
    !>!>> Me.SqlConnection1.Open()
    !>!>> Dim myDataReader As
    !>!>> System.Data.Sqlclient.SqlDataReader =
    !cmd.ExecuteReader
    !>!>> (CommandBehavior.CloseConnection)
    !>!>> ddlCompany.DataSource = myDataReader
    !>!>> ddlCompany.DataValueField = "CompanyID"
    !>!>> ddlCompany.DataTextField = "CompanyName"
    !>!>> ddlCompany.DataBind()
    !>!>> ddlCompany_SelectedIndexChanged(Nothing,
    !>!>> Nothing)
    !>!>> End If
    !>!>> End Sub
    !>!>>
    !>!>> Private Sub ddlCompany_SelectedIndexChanged(ByVal
    !>!>> sender As System.Object, ByVal e As System.EventArgs)
    !>!>> Handles ddlCompany.SelectedIndexChanged
    !>!>> Dim param As
    !System.Data.SqlClient.SqlParameter
    !>!>> Dim cmd As System.Data.SqlClient.SqlCommand
    !>!>> Dim intCompanyID As Integer
    !>!>> cmd = New System.Data.SqlClient.SqlCommand
    !>!>> ("WEB_GetLocationFromCompanyID", Me.SqlConnection1)
    !>!>> cmd.CommandType = CommandType.StoredProcedure
    !>!>> param = cmd.Parameters.Add(New
    !>!>> System.Data.SqlClient.SqlParameter("CompanyID",
    !>!>> SqlDbType.Int))
    !>!>> param.Direction = ParameterDirection.Input
    !>!>> ' ********Selected Value of Option List*******
    !>!>> param.Value = ddlCompany.Items
    !>!>> (ddlCompany.SelectedIndex).Value
    !>!>> Me.SqlConnection1.Open()
    !>!>> Dim myDataReader As
    !>!>> System.Data.Sqlclient.SqlDataReader =
    !cmd.ExecuteReader
    !>!>> (CommandBehavior.CloseConnection)
    !>!>> ddlLocation.DataSource = myDataReader
    !>!>> ddlLocation.DataValueField
    != "CompanyLocationID"
    !>!>> ddlLocation.DataTextField = "CoLo"
    !>!>> ddlLocation.DataBind()
    !>!>> End Sub
    !>!>>
    !>!>> Private Sub btnSearch_Click(ByVal sender As
    !>!>> System.Object, ByVal e As System.EventArgs) Handles
    !>!>> btnSearch.Click
    !>!>> Dim prmDateFrom As
    !>!>> System.Data.SqlClient.SqlParameter
    !>!>> Dim prmCoID As
    !>!System.Data.SqlClient.SqlParameter
    !>!>> Dim prmColo As
    !>!System.Data.SqlClient.SqlParameter
    !>!>> Dim cmd As System.Data.SqlClient.SqlCommand
    !>!>> Dim intCompanyID As Integer
    !>!>> cmd = New System.Data.SqlClient.SqlCommand
    !>!>> ("spRptAttachments", Me.SqlConnection1)
    !>!>> cmd.CommandType = CommandType.StoredProcedure
    !>!>> prmDateFrom = cmd.Parameters.Add(New
    !>!>> System.Data.SqlClient.SqlParameter("DateFrom",
    !>!>> SqlDbType.DateTime))
    !>!>> prmCoID = cmd.Parameters.Add(New
    !>!>> System.Data.SqlClient.SqlParameter("CompanyID",
    !>!>> SqlDbType.Int))
    !>!>> prmColo = cmd.Parameters.Add(New
    !>!>> System.Data.SqlClient.SqlParameter
    !("CompanyLocationID",
    !>!>> SqlDbType.Int))
    !>!>> prmDateFrom.Direction =
    !ParameterDirection.Input
    !>!>> prmCoID.Direction = ParameterDirection.Input
    !>!>> prmColo.Direction = ParameterDirection.Input
    !>!>> ' ********Selected Value of Option List*******
    !>!>> prmDateFrom.Value = tbDateFrom.Text
    !>!>> prmCoID.Value = ddlCompany.Items
    !>!>> (ddlCompany.SelectedIndex).Value
    !>!>> prmColo.Value = ddlLocation.Items
    !>!>> (ddlLocation.SelectedIndex).Value
    !>!>> Me.SqlConnection1.Open()
    !>!>> Dim myDataReader As
    !>!>> System.Data.Sqlclient.SqlDataReader =
    !cmd.ExecuteReader
    !>!>> (CommandBehavior.CloseConnection)
    !>!>> DataGrid1.DataSource = myDataReader
    !>!>> DataGrid1.DataBind()
    !>!>>
    !>!>> End Sub
    !>!>>
    !>!>
    !>!>
    !>!>.
    !>!>
    !>!
    !>
    !>
    !>.
    !>
    !

    Yan-Hong Guest

Similar Threads

  1. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  4. PHP and returned values from Informix stored procedures
    By Steve Weet in forum PHP Development
    Replies: 4
    Last Post: November 6th, 04:09 PM
  5. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139