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

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" <> wrote in message
news:8e2e01c35b64$9c7a8c00$...
> 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

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" <>
!Sender: "Rob Wire" <>
!References: <8e2e01c35b64$9c7a8c00$> <#>
!Subject: Re: DDL values with DataReader and stored procedures
!Date: Wed, 6 Aug 2003 12:29:31 -0700
!Lines: 122
!Message-ID: <070a01c35c51$0f20f3b0$>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="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" <> wrote in message
!>news:8e2e01c35b64$9c7a8c00$...
!>> 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]

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" <>
>!Sender: "Rob Wire" <>
>!References: <8e2e01c35b64$9c7a8c00$>
<#>
>!Subject: Re: DDL values with DataReader and stored
procedures
>!Date: Wed, 6 Aug 2003 12:29:31 -0700
>!Lines: 122
>!Message-ID: <070a01c35c51$0f20f3b0$>
>!MIME-Version: 1.0
>!Content-Type: text/plain;
>! charset="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" <> wrote in message
>!>news:8e2e01c35b64$9c7a8c00$.. .
>!>> 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

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! - 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" <>
!Sender: "Rob Wire" <>
!References: <8e2e01c35b64$9c7a8c00$> <#>
<070a01c35c51$0f20f3b0$> <>
!Subject: Re: DDL values with DataReader and stored procedures
!Date: Fri, 8 Aug 2003 12:39:42 -0700
!Lines: 205
!Message-ID: <010501c35de4$d028b040$>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="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" <>
!>!Sender: "Rob Wire" <>
!>!References: <8e2e01c35b64$9c7a8c00$>
!<#>
!>!Subject: Re: DDL values with DataReader and stored
!procedures
!>!Date: Wed, 6 Aug 2003 12:29:31 -0700
!>!Lines: 122
!>!Message-ID: <070a01c35c51$0f20f3b0$>
!>!MIME-Version: 1.0
!>!Content-Type: text/plain;
!>! charset="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" <> wrote in message
!>!>news:8e2e01c35b64$9c7a8c00$...
!>!>> 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]

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" <>
!Sender: "Rob Wire" <>
!References: <8e2e01c35b64$9c7a8c00$>
<#>
<070a01c35c51$0f20f3b0$>
<>
!Subject: Re: DDL values with DataReader and stored procedures
!Date: Fri, 8 Aug 2003 12:39:42 -0700
!Lines: 205
!Message-ID: <010501c35de4$d028b040$>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="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" <>
!>!Sender: "Rob Wire" <>
!>!References: <8e2e01c35b64$9c7a8c00$>
!<#>
!>!Subject: Re: DDL values with DataReader and stored
!procedures
!>!Date: Wed, 6 Aug 2003 12:29:31 -0700
!>!Lines: 122
!>!Message-ID: <070a01c35c51$0f20f3b0$>
!>!MIME-Version: 1.0
!>!Content-Type: text/plain;
!>! charset="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" <> wrote in message
!>!>news:8e2e01c35b64$9c7a8c00$...
!>!>> 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]