Using SELECT LIKE With ADO Parameter In ASP Page

Jul 23, 2005

Is it possible and how do I do use a select like query with an ado
command's parameter object?

For Example:
Instead of this:
command.CommandText = "SELECT * FROM Table WHERE field LIKE
'%searchString%'

This:
command.CommandText = "SELECT * FROM Table WHERE field LIKE '%?%'
command.Parameters.Append(command.CreateParameter ....

Thanks

View 3 Replies


ADVERTISEMENT

How To Set Default Parameter To Select All For A Multivalue Parameter

Jul 24, 2007

I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.

View 11 Replies View Related

Problem With A Date Parameter In ASP.Net Page

May 9, 2007

The date format in the 'weekending' textbox is 5/5/2007Here's what I've got:
With cmd.Parameters.Add(New SqlParameter("@SQLweekending", SqlDbType.DateTime, weekending.Text))the error message is:"Conversion from string "4/8/2007" to type 'Integer' is not valid."
Then, I tried:.Add(New SqlParameter("@SQLweekending", SqlDbType.DateTime, CDate(weekending.Text)))but, before I even tried it, it gave me a blue squiggly line, with an error message:"Error 2 Value of type 'Date' cannot be converted to 'Integer'"
How can I get this to work?

View 3 Replies View Related

Parameter Slow And Page Refresh

Apr 8, 2008

Hi,

I have a problem with the parameters in SSRS2005.
In fact I have for example a parameter month on a MDX request (See below). When I change the value in the listbox, Reporting services post the page and I am waiting 3s before to click on "view report"
Do you have an idea why this post is called ? For me there is no reason !!!



WITH

MEMBER [Measures].[ParameterCaption] AS '[DIM CALENDRIER].[ANNEE SCOL - MOIS - JOUR].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[DIM CALENDRIER].[ANNEE SCOL - MOIS - JOUR].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[DIM CALENDRIER].[ANNEE SCOL - MOIS - JOUR].CURRENTMEMBER.LEVEL.ORDINAL'



SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]

} ON COLUMNS ,


ORDER(

[DIM CALENDRIER].[ANNEE SCOL - MOIS - JOUR].[CAL - Mois].MEMBERS,[Measures].[ParameterValue],DESC) ON ROWS


FROM ( Select ( filter([DIM CALENDRIER].[ANNEE SCOL - MOIS - JOUR].[CAL - Jour].ALLMEMBERS,

CDate([DIM CALENDRIER].[ANNEE SCOL - MOIS - JOUR].Properties( "CAL - Date" ))>= CDate(vba!format(vba!day(vba!now()),"00") + "/" + vba!format(vba!month(vba!now()),"00") + "/" + vba!format(vba!year(vba!now())-2,"0000"))

AND

CDate([DIM CALENDRIER].[ANNEE SCOL - MOIS - JOUR].Properties( "CAL - Date" ))<= CDate(vba!format(vba!day(vba!now()),"00") + "/" + vba!format(vba!month(vba!now()),"00") + "/" + vba!format(vba!year(vba!now()),"0000"))

)) ON COLUMNS


FROM [TDB MENSUEL])

View 1 Replies View Related

Remove Select All Options From Multi Select Parameter Dropdown

Jun 8, 2007

Hi All



I am using SQL Server 2005 with SP2. I have multi select parameter in the report. In SP2 reporting services gives Select All option in the drop down.



Is there any way I can remove that option from the list?



Thanks

View 4 Replies View Related

How Do I Pass A Parameter To A Sqldatasource In A Code Behind Page

Feb 1, 2007

i have a sqldatasource on my asp.net page -- select * from table where id = @id
i want to set the @id in the backend and set the result to textbox1.text
how do i do this?

View 1 Replies View Related

How To Code An Aspx Page To Run A Stored Procedure With A Parameter

Sep 19, 2007

 My stored proceddure "My Programs" includes a parameter @meid.How do I code an aspx file to run the procedure with a user ID, e.g. EmpID?I tried the following codes, but the error message indicated it can not findthe Stored Procedure.  How do I pass the EmpID as a Stored Procedure parameter?
 <%meid = EmpIdcmd.CommandText = "MyPrograms meid"cmd.CommandType = CommandType.StoredProcedurecmd.Connection = sqlConnection2sqlConnection2.Open()reader3 = cmd.ExecuteReader(meid)While reader3.Read()sb.Append(reader3(i).ToString() + "......<BR> <BR /> ")End While%> 
TIA,Jeffrey

View 3 Replies View Related

Rdlc Show Querystring Parameter In Page Header

Aug 14, 2007

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters
and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if
no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!
Jim

View 14 Replies View Related

SSRS 2005 Multivalue Parameter Not Working On The Asp Page.

Oct 30, 2007

Hi,
i am working on SSRS 2005. i have one problem while accessing report throgh ASP.net pages.

i have used multivalue parameters in the report. on the report server it works fine but on the .asp page when that report is accessed, drop down is not working,

can anybody guide me regarding this , what to do?

thanks and Regards,
Vishruti.

View 1 Replies View Related

Multivalue Parameter Dropdown Not Working In Aspx Page

Feb 21, 2008



Hi All
i have created one report using SSRS 2005, in this report one parameter is multivalue parameter, this report preview working fine and user able to select multivalues. but the problem is i deployed this report in server and opened in client system (reports shown to user using report viewer in aspx pages), i am able to access the report but multivalue parameter dropdown is not working.
when i clicked on multivalue parameter dropdown report refreshing and remaining parameters default vaues coming up..
i am unable to find the solution
please anyone help me

thanks

View 3 Replies View Related

Parameter: C, I, P, Or ALL...how To Select Based On Parameter?

Jul 27, 2006

Hi everyone,

I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...

Any thoughts??

Query:


sql Code:






Original
- sql Code





ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]
(
@status varchar(25),
@course varchar(100),
@datesmalldatetime
)
AS

SELECT
A1.uLastName,
A1.uFirstName,
A2.mName,
A3.tStatus,
A3.tScore,
A3.tStartDate,
A3.tCompleteDate,
A4.cName

FROM VSALCP.dbo.[User] as A1
INNER JOIN FSDLMS.dbo.Student as A5
ON A1.uID = A5.stID

INNER JOIN FSDLMS.dbo.Transcript as A3
ON A3.t_FK_stID = A5.stID

INNER JOIN VSALCP.dbo.Member as A2
ON A2.mID = A1.u_FK_mID

INNER JOIN FSDLMS.dbo.Course as A4
ON A4.cID = A3.t_FK_cID

Where @status = A3.tStatus ...






 ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]    (        @status varchar(25),        @course varchar(100),        @date   smalldatetime    )AS   SELECT    A1.uLastName,    A1.uFirstName,     A2.mName,    A3.tStatus,    A3.tScore,    A3.tStartDate,    A3.tCompleteDate,    A4.cName     FROM VSALCP.dbo.[User] AS A1    INNER JOIN FSDLMS.dbo.Student AS A5    ON A1.uID = A5.stID     INNER JOIN FSDLMS.dbo.Transcript AS A3    ON A3.t_FK_stID = A5.stID     INNER JOIN VSALCP.dbo.Member AS A2    ON A2.mID = A1.u_FK_mID      INNER JOIN FSDLMS.dbo.Course AS A4    ON A4.cID = A3.t_FK_cID WHERE @status = A3.tStatus ...


"If status = ALL select * status"

Thanks for taking a look!

View 3 Replies View Related

Multi Select Parameter From Function - Select All?

Sep 6, 2007



I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END
GO


Anyone have any ideas?

Thanks,
Deb

View 5 Replies View Related

Return An Unique Identifier To An ASP.NET Page To Send It As A Parameter Into Another Stored Procedure

May 1, 2007

Hi !
I have a problem with the unique identifier and  don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
Dim cmd As New SqlCommand
cmd.CommandText = "Insertar_Contacto"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
 Thanks!!
 

View 2 Replies View Related

Reporting Services :: Multiple Chart On A Report Page And Parameter Filter

Jun 1, 2015

Is it possible to have different chart (having their dataset from same shared datasets) like six on one report, then secondly can they all respond to same parameter filter.

View 3 Replies View Related

Reporting Services :: Don't Include Last Page Of SSRS Report Based On Parameter

Mar 9, 2015

I created the report, which has 4 pages. Each page contains bunch of tables and charts, which display data in scope of dataset, created for each page and reflecting certain subject. Each page also has Header and Footer.

My users requested, the report has possibility to include or not include data on Page #4.

Other words, report should display 3 or 4 pages based on the entered parameter.

1. I created parameter named  “Include #### metrics”, which contains options: Y, N
2. For each object on the page 4 I added dependency on the selected parameter in  the visibility option.

Now if user select parameter Not include Page #4, report displays 3 pages as expected and page #4 is empty, but with Header and Footer.

The problem: Their preference is to not to show page 4 at all if someone chooses to not to include #### metrics.

My question is how to force the report to display just 3 selected pages.

View 5 Replies View Related

Reporting Services :: Page Break Based On Sort Parameter Value In SSRS 2008

Nov 19, 2015

I have a SSRS report with data in below format.

ID       Name    Date
1           A         null
2           B        01/01/2012
3           C        01/02/2013

Also, I have a sort parameter @sort and values are (Name, ID, Date)

I want to apply page break whenever @sort=Name. There should be no page break when user selects @sort = ID or Date. Page break should happen only when @sort value = Name

it should be like this...

Page 1:
ID       Name    Date
1           A         null
Page 2:
ID       Name    Date
2          B         01/01/2012
Page 3:
ID       Name    Date
3           C        01/02/2013

I need achieving the above task.

View 2 Replies View Related

Help With An SQL SELECT Statement In ASP Page

Mar 19, 2004

I have built this SQL statement which should
create the RS I need.

strSQL1 = "SELECT [INVENTRY MASTER].BOX_NO FROM [INVENTRY MASTER] WHERE
Left([INVENTRY MASTER].BOX_NO, PatIndex('%821%', [INVENTRY MASTER].BOX_NO) -
1) NOT LIKE '%[1-9]%' AND [INVENTRY MASTER].BOX_NO LIKE '%821%';"

This Line:

objRS1.Open strSQL1, objConn

Causes this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter
passed to the substring function.

I have searched google, and found reference to the error meaning it found a
space in the first position. I tried adding LTRIM into my statement to cure
it but it made no difference, I may be barking up the wrong tree so to speak
;) but I cant find any other information on it.

If anyone has any ideas why this statement does not work I'd be very
grateful, the project has to be completed today, and this is the last thing
to get working now!

Ta

Paul McGuire

View 6 Replies View Related

Select Rows By Page

Sep 22, 2007

What is the best way to do this (least amount of memory required)
Example table
id bigint
name nvarchar(255)

select rows 20 to 30

View 8 Replies View Related

Reporting Services :: All Record Are Displaying On One Page - How To Display Page By Page

Nov 11, 2015

I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.

View 3 Replies View Related

Displaying Select Results On The Page

Aug 28, 2006

I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>"
SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)">
<SelectParameters>
<asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" />
</SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page.  I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.

View 1 Replies View Related

How To Select The Select All Checkbox Parameter?

Aug 7, 2007

When passing the prameter values to a linked report, I want to set one of the parameter at the linked report to "Select All", this was added automatically to the parameter list when multivalue report parameter is used . How to set this "Select All" as a parameter or can it be done? Thanks

View 1 Replies View Related

Select Records From Two Tables, Plus Page And Order Them?

Feb 7, 2008

Hi,

I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....

DECLARE @Temp TABLE
(
IDINTNOT NULL,
NameNVARCHAR(128)NOT NULL,
TypeCHAR(1)NOT NULL
)

INSERT INTO @Temp
SELECT i.ID, i.Name, Type = 'I' FROM Item i
UNION
SELECT p.ID, p.Name, Type = 'P'FROM Package p

SELECT * FROM @Temp
ORDER BY Name ASC

I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?

Many thanks for any help! :)

View 10 Replies View Related

Sqldatasource, Does It Select If Not Bound To A Gridview, Formview On The Page?

Feb 22, 2008

Using 3.5
If I have a sqldatasource on the page, is it run if it is not bound to a data object like a gridview?
Seems like if i want to access the data (like set a label text) from the sqldatasource I have to use code to first create a dataview then pick throught it. This seems like I'm running it twice. I'm new at .net so I dont know how to tell.
I don't want to write data select code programatically when I can just through an SDS on the page, but wondered it it ran just because it's on the page.

View 2 Replies View Related

Page 2 - How To Select Multiple Fields From A Joined Table

Mar 11, 2008

float

View 1 Replies View Related

MSDE [SQL 2000] -- Server Balks When Web Page Loads -- Select Permission Denied

Oct 28, 2006

Hi folks,

I'm having trouble getting off the ground with the Web application walkthrough "Walkthrough: Creating a Web Application Using Visual C# or Visual Basic" in VS.NET Pro 2002 [Academic] documentation. After a bit of fishing around, and consulting the MS Knowledge Base, I got the pubs database installed. I also got the connection to work well enough that the dataset would fill in the IDE.

The problem is that when I try to run the web form, either from the IDE debug menu, or by accessing the .aspx file on localhost using Firefox, I get the error:
SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'.
showing in the browser.
My understanding is that this page is running as ASPNET, and I did already carry out the recommended commands to enable access:
C:>osql -E -S MY-MACHINE-NAMEVSDOTNET -Q "sp_grantlogin 'MY-MACHINE-NAMEASPNET'"
C:>osql -E -S MY-MACHINE-NAMEVSDOTNET -d Pubs -Q "sp_grantdbaccess 'MY-MACHINE-NAMEASPNET'"
both of which commands returned successfully. Any suggestions as to what else I should do to get the necessary permissions to actually display the data in my browser? Does the IIS user account need permission also?

Thanks for any insight into this vexing problem. I must say that along the way, I have had some fun exploring the osql comand-line tool. Using the -E switch, I have been able to run select and upgrade queries, but this is all pretty much fishing in the dark. I would like to get back to actually working with the walthroughs in the Visual Studio documentation.

Thanks,

Joseph

View 4 Replies View Related

How To Select Using A Parameter

May 13, 2005

Hi
I will  like to bind the listbox control only with the rows where the colums text = categoryX
Where do I declare the variable @Category ?
If Not Page.IsPostBack Then
Dim conPubs As SqlConnection
Dim cmdSelect As SqlCommand
Dim dtrAuthors As SqlDataReader
conPubs = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
conPubs.Open()
cmdSelect = New SqlCommand("Select * From Slideshows WHERE ([Slideshows].[Category] = @Category)", conPubs)
dtrAuthors = cmdSelect.ExecuteReader()
ListBox1.DataSource = dtrAuthors
ListBox1.DataBind()
dtrAuthors.Close()
conPubs.Close()
End If
Best Regards
 

View 1 Replies View Related

SqlDataSource + Parameter + SELECT IN

Jul 20, 2007

I'm sure that is really simple, but how do I pass a parameter with multiple value to a SQLdatasource?
ex: SELECT field1 from tblTableA where idTableA IN ( @Param1)
 Let's say I want to pass 1,2,3,4  as Param1   (SELECT field1 from tblTableA where idTableA IN ( 1,2,3,4))
How I am supposed tu use the .SelectParameters.Add()  to pass a list of integers instead of  a single value??
Thanks in advance.

View 2 Replies View Related

SqlDataSource WHERE IN Select Parameter

Dec 5, 2007

Is it possible to use a WHERE-IN statement with a SqlDataSource control. For instance:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
SelectCommand="SELECT [Id], [Name], [Phone] FROM [Table1] WHERE ([Id] IN @Id)">
<SelectParameters>
<asp:Parameter DefaultValue="( 1, 3, 5, 7, 11 )" Name="Id" Type="Int32" />
</SelectParameters></asp:SqlDataSource>
I'm hoping the gridview would then display a table with rows for records 1,3,5,7,11. Thanks for any help...

View 2 Replies View Related

Variable In Select Parameter

Mar 7, 2008

Hi,
I am trying to get the login name of a user, trim off some characters, which works fine, convert this to a string variable, which works fine and then use this variable in an sql select parameter.
I've tried countless things and am not sure why my variable @NewString is not working in the Select command. If I add a default value I know is present, it works, but it can't seem to pick up the variable value of NewString. It prints out as expected in the response.write statement, but I really need it to connect to the corresponding value in the database.
Any ideas are greatly appreciated-code below.
Thanks,
Liz
 
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<SCRIPT LANGUAGE="vb" runat="server">
Public Function LoggedOnUser() As StringReturn (Request.ServerVariables("LOGON_USER"))
End Function
</SCRIPT>
<%Dim MyString As String = LoggedOnUser()
Dim MyChar As Char() = {"O"c, "N"c, "E"c, ""c}Dim NewString As String = MyString.TrimStart(MyChar)Response.Write("Hello ")
Response.Write(NewString)
%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pboConnectionString %>"
SelectCommand="SELECT [LastName], [FirstName], [logon] FROM [Phonebook] WHERE ([logon] = @NewString)">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="NewString" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="logon" HeaderText="logon" SortExpression="logon" />
</Columns></asp:GridView>
</asp:Content>

View 2 Replies View Related

SSRS Parameter Select All Help

Jun 19, 2008

Please can someone provide an overview of how to select all records in the parameter list. I am trying to get an understanding of how this works.

Having a union join with all value is fine, to retrieve the value of 'all'. However, given the data-set there is no 'all' value. From reading the ms course notes, the query syntax is: select from where = (field = @x) or @x = 'all'. How does this syntax work? Does x = all, so all= all = true then returns all records?

Regards,

Nijojo

View 2 Replies View Related

SP Parameter To Determine SELECT Value

Jun 22, 2014

I have a problem with SP when passing in Parameters. Basically something like this:

-- Pass 1, 2 or 3 as parameter
EXEC SP_mySP 1

-- The SP will do the following SQL Statement
SELECT * FROM myTable
WHERE
(
(If @Parameter1 = 1 then myColumn = 'A' or myColumn = 'B')
(If @Parameter1 = 2 then myColumn = 'C')
(If @Parameter1 = 3 then myColumn is not null)
)

How to make the above condition?

View 2 Replies View Related

Select Parameter Order

Jul 20, 2005

Which is more efficientWhere NonindexedColumn=x and IndexedColumn=yorWhere IndexedColumn=y and NonindexedColumn=xor does matter? Will the optimiser work it out?(I'm building the SQL string on the fly in the fron-end)

View 1 Replies View Related

Select With Table Name Parameter

May 16, 2007

Hy , I'he made a stored procedure with 1 parameter @cTableName and I want to return data from that table: select * from @cTableName , that is all. But it does not work. When I execute the stored procedurere I want to send the name of the table : execute MySP "MyTable". Is it possible? Thank you!

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved