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" <st*@hotmail.com>
!Sender: "Rob Wire" <st*@hotmail.com>
!References: <8e****************************@phx.gbl>
<#e**************@tk2msftngp13.phx.gbl>
<07****************************@phx.gbl>
<mq**************@cpmsftngxa06.phx.gbl>
!Subject: Re: DDL values with DataReader and stored procedures
!Date: Fri, 8 Aug 2003 12:39:42 -0700
!Lines: 205
!Message-ID: <01****************************@phx.gbl>
!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" <st*@hotmail.com>
!>!Sender: "Rob Wire" <st*@hotmail.com>
!>!References: <8e****************************@phx.gbl>
!<#e**************@tk2msftngp13.phx.gbl>
!>!Subject: Re: DDL values with DataReader and stored
!procedures
!>!Date: Wed, 6 Aug 2003 12:29:31 -0700
!>!Lines: 122
!>!Message-ID: <07****************************@phx.gbl>
!>!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" <ST*@hotmail.com> wrote in message
!>!>news:8e****************************@phx.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
!>!>>
!>!>
!>!>
!>!>.
!>!>
!>!
!>
!>
!>.
!>
!