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.co m>
!Sender: "Rob Wire" <st*@hotmail.co m>
!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: AcNd5NAmk8VGjaZ QT1K+C0BMkc2Vuw ==
!Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
!Path: cpmsftngxa06.ph x.gbl
!Xref: cpmsftngxa06.ph x.gbl microsoft.publi c.dotnet.framew ork.aspnet:1662 57
!NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
!X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.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.co m>
!>!Sender: "Rob Wire" <st*@hotmail.co m>
!>!References: <8e************ *************** *@phx.gbl>
!<#e*********** ***@tk2msftngp1 3.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: AcNcUQ8g52Xt2Lx CT8+b0l82kNicsQ ==
!>!X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!>!Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
!>!Path: cpmsftngxa06.ph x.gbl
!>!Xref: cpmsftngxa06.ph x.gbl
!microsoft.publ ic.dotnet.frame work.aspnet:165 489
!>!NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
!>!X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.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.inser t "Select All" at position 0. Next line
!would
!>!be to set
!>!>items(0).va lue 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.co m> wrote in message
!>!>news:8e**** *************** *********@phx.g bl...
!>!>> 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.
!>!>> spRptAttachment s 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.EventArg s) Handles MyBase.Load
!>!>> If Not IsPostBack Then
!>!>> Dim cmd As
!System.Data.Sq lClient.SqlComm and
!>!>> cmd = New System.Data.Sql Client.SqlComma nd
!>!>> ("Web_CompanyLi st", Me.SqlConnectio n1)
!>!>> cmd.CommandType =
!>!CommandType. StoredProcedure
!>!>> Me.SqlConnectio n1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sql client.SqlDataR eader =
!cmd.ExecuteRea der
!>!>> (CommandBehavio r.CloseConnecti on)
!>!>> ddlCompany.Data Source = myDataReader
!>!>> ddlCompany.Data ValueField = "CompanyID"
!>!>> ddlCompany.Data TextField = "CompanyNam e"
!>!>> ddlCompany.Data Bind()
!>!>> ddlCompany_Sele ctedIndexChange d(Nothing,
!>!>> Nothing)
!>!>> End If
!>!>> End Sub
!>!>>
!>!>> Private Sub ddlCompany_Sele ctedIndexChange d(ByVal
!>!>> sender As System.Object, ByVal e As System.EventArg s)
!>!>> Handles ddlCompany.Sele ctedIndexChange d
!>!>> Dim param As
!System.Data.Sq lClient.SqlPara meter
!>!>> Dim cmd As System.Data.Sql Client.SqlComma nd
!>!>> Dim intCompanyID As Integer
!>!>> cmd = New System.Data.Sql Client.SqlComma nd
!>!>> ("WEB_GetLocati onFromCompanyID ", Me.SqlConnectio n1)
!>!>> cmd.CommandType = CommandType.Sto redProcedure
!>!>> param = cmd.Parameters. Add(New
!>!>> System.Data.Sql Client.SqlParam eter("@CompanyI D",
!>!>> SqlDbType.Int))
!>!>> param.Direction = ParameterDirect ion.Input
!>!>> ' ********Selecte d Value of Option List*******
!>!>> param.Value = ddlCompany.Item s
!>!>> (ddlCompany.Sel ectedIndex).Val ue
!>!>> Me.SqlConnectio n1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sql client.SqlDataR eader =
!cmd.ExecuteRea der
!>!>> (CommandBehavio r.CloseConnecti on)
!>!>> ddlLocation.Dat aSource = myDataReader
!>!>> ddlLocation.Dat aValueField
!= "CompanyLocatio nID"
!>!>> ddlLocation.Dat aTextField = "CoLo"
!>!>> ddlLocation.Dat aBind()
!>!>> End Sub
!>!>>
!>!>> Private Sub btnSearch_Click (ByVal sender As
!>!>> System.Object, ByVal e As System.EventArg s) Handles
!>!>> btnSearch.Click
!>!>> Dim prmDateFrom As
!>!>> System.Data.Sql Client.SqlParam eter
!>!>> Dim prmCoID As
!>!System.Data. SqlClient.SqlPa rameter
!>!>> Dim prmColo As
!>!System.Data. SqlClient.SqlPa rameter
!>!>> Dim cmd As System.Data.Sql Client.SqlComma nd
!>!>> Dim intCompanyID As Integer
!>!>> cmd = New System.Data.Sql Client.SqlComma nd
!>!>> ("spRptAttachme nts", Me.SqlConnectio n1)
!>!>> cmd.CommandType = CommandType.Sto redProcedure
!>!>> prmDateFrom = cmd.Parameters. Add(New
!>!>> System.Data.Sql Client.SqlParam eter("@DateFrom ",
!>!>> SqlDbType.DateT ime))
!>!>> prmCoID = cmd.Parameters. Add(New
!>!>> System.Data.Sql Client.SqlParam eter("@CompanyI D",
!>!>> SqlDbType.Int))
!>!>> prmColo = cmd.Parameters. Add(New
!>!>> System.Data.Sql Client.SqlParam eter
!("@CompanyLoca tionID",
!>!>> SqlDbType.Int))
!>!>> prmDateFrom.Dir ection =
!ParameterDirec tion.Input
!>!>> prmCoID.Directi on = ParameterDirect ion.Input
!>!>> prmColo.Directi on = ParameterDirect ion.Input
!>!>> ' ********Selecte d Value of Option List*******
!>!>> prmDateFrom.Val ue = tbDateFrom.Text
!>!>> prmCoID.Value = ddlCompany.Item s
!>!>> (ddlCompany.Sel ectedIndex).Val ue
!>!>> prmColo.Value = ddlLocation.Ite ms
!>!>> (ddlLocation.Se lectedIndex).Va lue
!>!>> Me.SqlConnectio n1.Open()
!>!>> Dim myDataReader As
!>!>> System.Data.Sql client.SqlDataR eader =
!cmd.ExecuteRea der
!>!>> (CommandBehavio r.CloseConnecti on)
!>!>> DataGrid1.DataS ource = myDataReader
!>!>> DataGrid1.DataB ind()
!>!>>
!>!>> End Sub
!>!>>
!>!>
!>!>
!>!>.
!>!>
!>!
!>
!>
!>.
!>
!
