passing parameter from asp.net application to reporting services report

Hi,

There is a .net application which has a screen with four options(text boxes)

Each of these should take to reports generated by SQL REp Services

This is the requirment

The School selected in the App should be passed on to the report

How should I pass the parameter in my report

so that when user selects a link or text box report for that particular school number is displayed

Thanks

sowree

Are you displaying the report in a report viewer control, or opening up a new browser window with the report URL?

BobP

|||

hi

the report is being viewed using report manager, so the report I have deployed to report server must be displayed when the user selects the menu selecting a school number in the asp app.

I need to know how i pass the parameter in my report rdl 's stored procedure to select the school number selected in the ASP application

Thanks

|||

You would create a parameter in the report rdl to accept the school number, and then use that parameter in the call to the stored proc.

For example: You create a parameter called @.SchoolNumber

exec spgSchoolInfo @.SchooNumber

To pass the parameter to the report in report manager, you would put the school number in the URL: (http://msdn2.microsoft.com/en-US/library/ms153586.aspx)

Example: http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&SchoolNumber=1234

HTH

BobP

Passing multi-valued parameter to a Sp

Hello,

I am forced to use a Sp in teh first place as my query is too long for the text dataset. I have a parameter Time_Period which ia a multivalued one but it is not allowing me to use this in the SP. using RS 2005.

Any help appereciated.

Thanks

You can use multivalue input params from RS to a stored proc. The input param comes in like a single string like @.Input = 'Val1,Val2,Val3' and then you can split these with a table function which returns 'Val1',Val2','Val3' . Then, use an IN stmt in your where statement in the stored proc ie WHERE Val IN (SELECT Item FROM dbo.SplitParams ('''+ @.Input + ''', '',''))'. See this thread for more info.

sql

Passing multi-valued parameter to a Sp

Hello,

I am forced to use a Sp in teh first place as my query is too long for the text dataset. I have a parameter Time_Period which ia a multivalued one but it is not allowing me to use this in the SP. using RS 2005.

Any help appereciated.

Thanks

You can use multivalue input params from RS to a stored proc. The input param comes in like a single string like @.Input = 'Val1,Val2,Val3' and then you can split these with a table function which returns 'Val1',Val2','Val3' . Then, use an IN stmt in your where statement in the stored proc ie WHERE Val IN (SELECT Item FROM dbo.SplitParams ('''+ @.Input + ''', '',''))'. See this thread for more info.

Passing long text strings to a stored procedure

Hello!

I am attempting to insert a group of records into a SQL Server 2000 database table. The data for each of these records is the same, with the exception of a foreign key (hereafter known as the 'RepKey') and the generated primary key. To improve performance and cut down on the network traffic, I pack the RepKeys in a comma-delimited string and send it as a single parameter, with the intention of parsing it in the stored proicedure to obtain each individual RepKey, or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

My problem is that there may be 1000's of items in this string. Using a varchar(8000) as the parameter type is too short, while using the 'text' data type does not allow me to perform any string operations on it. Any ideas on how to make one network call and insert multiple records that breaks the 8000 character barrier?

One thing that I cannot do is add a table so that the record is stored once, then mapped to each individual rep key. The database structure cannot change. Other solutions that I may not have considered are welcome. Thanks!Can you add a global temp table?
You could write all the data to a disk file then bcp it in to the global table.
Could also create a disconnected recordset on the golobal temp table, diconnect it, populate it then connect it to commit the records then use that.

You could use a text datatype then use substring to parse it in chunks and use char functions on it.|||Nigelrivett idea of a text file sounds interesting. What if the parameter used in your stored procedure was the path to a file containing the list of RepKeys? Once in your procedure you use BULK INSERT into a temporay table (Globle table if needed like nigelrivett suggested) then perform the same looping as you would have done before.

sp_MyProc (RepKeyFile AS varchar(50), ....)

CREATE TABLE #temptable ...
BULK INSERT #temptable FROM @.RepKeyFile
.
.
.
CREATE CURSOR on #temptable
loop through

The only problem is your point on:
or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

I thought that you could create a local text variable and while looping append the RepKey to the local text field, SET @.txt = @.txt + ',' + @.RepKey. However I got an error when trying to create a local variable as type text.

Msg 2739, Level 16, State 1, Server ATLAS, Line 1
The text, ntext, and image data types are invalid for local variables.|||Thanks guys - I ended up using the substring procedure to break off chunks, then used an INSERT..SELECT statement that looks like the following:

SET @.query = "INSERT INTO RepContact([fields])"
SET @.query = @.query + "SELECT RepKey, [@.vars] FROM Rep WHERE RepKey IN (" + @.currentString + ")"
exec(@.query)

@.CurrentString is the current list of keys. Each time through the loop, as long as there are still items, the query is run.

Thanks again - if anyone has any ideas on speeding this up, it would be greatly appreciated. (The insert runs a bit slower than I would have hoped).

Everett|||I was thinking that the text file would hold the keys delimitted by crlf so that the bcp would insert them into separate rows and you wouldn't have to do any further processing.|||I think that I would prefer to leave it as it is and avoid writing to and reading from disk. Wouldn't this make it slower, not faster? Anyways, thanks again.|||>> Wouldn't this make it slower, not faster?

Depends on the data and environment.
the bcp will be non-logged so the inser will be faster. It will reduce the handshaking across the network and reduce the amount of manipulation needed before the insert into the production tables.
It would probably end up slower but maybe not. It does give an automatic record of the dta inserted from the text files and makes it easy to make the insert asynchronous if you need to.|||I'll try it during the week and advise you of the outcome.

Thanks again for all of your help.

Passing LinkButton text value as Sql Query parameter!

Hi,

Could you inform me programmatically how can I pass LinkButton text value as Sql Query parameter?

I tried the

1command.CommandText ="SELECT DISTINCT [Description] FROM [Projects] WHERE ([Type] = " & SqlDbType.Text = LinkButton12.Text &")" 

but it does not work!!! 

 Thanks in advance!!!!

Try changing the line to this:

command.CommandText = "SELECT DISTINCT [Description] FROM [Projects] WHERE ([Type] = '" & LinkButton12.Text & "')"

The statement "SqlDbType.Text = LinkButton12.Text" probably evaluated to a boolean which threw off your query.|||Ok,thank you very much ZeroDefinition...

Passing documents > 8000 chars to sp_xml_preparedocument

We are storing incoming xmldocumkents in a TEXT column of a table.
I've been trying to find a way of lifting that text data out of the table and passing the data to sp_xml_preparedocument for further processing with OPENXML.
This is fine if the data is < 8000 bytes, however our documents are going to be larger than that. Given that we cannot create a local variable of type TEXT, how do we work around this?
We made one attempt to chunk the data into a temporary table and rebuild it into an sp TEXT parameter as below, but it didn't work.
Any ideas greatly appreciated
Thanks
Mark
CREATE TABLE [dbo].[tblMessage] (
[tblMessageId] [int] IDENTITY (1, 1) NOT NULL ,
[tblmessageXML] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--This is still < 8000
INSERT INTO tblMessage (tblmessageXML) VALUES ('<NewDataSet><Table><tblPrimaryQueueID>5</tblPrimaryQueueID><tblPeopleStagingID>9</tblPeopleStagingID><tblPeopleStagingStatus>0</tblPeopleStagingStatus><tblPeopleStagingPeopleID>1 6</tblPeopleStagingPeopleID><
tblPeopleStagingPeopleFName>Homer</tblPeopleStagingPeopleFName><tblPeopleStagingPeopl eLName>Simpson</tblPeopleStagingPeopleLName><tblPeopleStagingPeopl eCauswayURN>16201</tblPeopleStagingPeopleCauswayURN></Table></NewDataSet>')
create procedure xmltest2(@.XMLTEXT TEXT)
AS
declare @.dl as int
declare @.Ct as int
declare @.cs as Int
--declare @.XMLtext as VARCHAR(8000)
declare @.tmpXML as VARCHAR(8000)
declare @.MessID as INT
SET @.MessID = 1
set @.cs = 20
select @.dl = DataLength(tblMessageXML) from tblMessage
drop table #t
create table #t (tid INT IDENTITY, tmid INT, tvc VARCHAR(20))
SET @.ct = 1
WHILE @.CT < @.DL
BEGIN
INSERT INTO #t (tmid, tvc)
SELECT @.MessID, SUBSTRING(tblMessageXML, @.CT, @.cs)
FROM tblMessage
--WHERE tblMessageID = @.MessID
SET @.CT = @.CT + @.CS
END
DECLARE XML_CURSOR CURSOR FAST_FORWARD
FOR
SELECT tvc
FROM #t
ORDER BY TID
OPEN XML_CURSOR
FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
--SET @.XMLTEXT = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.XMLTEXT = @.XMLTEXT + @.TMPXML
FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
END
CLOSE XML_CURSOR
DEALLOCATE XML_CURSOR
Hi
Check out:
http://sqlxml.org/faqs.aspx?faq=42
John
"Mark McCormick" <anonymous@.discussions.microsoft.com> wrote in message
news:0C8F6CD3-A116-40C4-9468-9B31F975B32F@.microsoft.com...
> We are storing incoming xmldocumkents in a TEXT column of a table.
> I've been trying to find a way of lifting that text data out of the table
and passing the data to sp_xml_preparedocument for further processing with
OPENXML.
> This is fine if the data is < 8000 bytes, however our documents are going
to be larger than that. Given that we cannot create a local variable of
type TEXT, how do we work around this?
> We made one attempt to chunk the data into a temporary table and rebuild
it into an sp TEXT parameter as below, but it didn't work.
> Any ideas greatly appreciated
> Thanks
> Mark
>
> CREATE TABLE [dbo].[tblMessage] (
> [tblMessageId] [int] IDENTITY (1, 1) NOT NULL ,
> [tblmessageXML] [text] COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> --This is still < 8000
> INSERT INTO tblMessage (tblmessageXML) VALUES
('<NewDataSet><Table><tblPrimaryQueueID>5</tblPrimaryQueueID><tblPeopleStagi
ngID>9</tblPeopleStagingID><tblPeopleStagingStatus>0</tblPeopleStagingStatus
><tblPeopleStagingPeopleID>16</tblPeopleStagingPeopleID><tblPeopleStagingPeo
pleFName>Homer</tblPeopleStagingPeopleFName><tblPeopleStagingPeopl eLName>Sim
pson</tblPeopleStagingPeopleLName><tblPeopleStagingPeopl eCauswayURN>16201</t
blPeopleStagingPeopleCauswayURN></Table></NewDataSet>')
> create procedure xmltest2(@.XMLTEXT TEXT)
> AS
> declare @.dl as int
> declare @.Ct as int
> declare @.cs as Int
> --declare @.XMLtext as VARCHAR(8000)
> declare @.tmpXML as VARCHAR(8000)
> declare @.MessID as INT
> SET @.MessID = 1
> set @.cs = 20
> select @.dl = DataLength(tblMessageXML) from tblMessage
> drop table #t
> create table #t (tid INT IDENTITY, tmid INT, tvc VARCHAR(20))
> SET @.ct = 1
> WHILE @.CT < @.DL
> BEGIN
> INSERT INTO #t (tmid, tvc)
> SELECT @.MessID, SUBSTRING(tblMessageXML, @.CT, @.cs)
> FROM tblMessage
> -- WHERE tblMessageID = @.MessID
> SET @.CT = @.CT + @.CS
> END
> DECLARE XML_CURSOR CURSOR FAST_FORWARD
> FOR
> SELECT tvc
> FROM #t
> ORDER BY TID
>
> OPEN XML_CURSOR
> FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
> --SET @.XMLTEXT = ''
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.XMLTEXT = @.XMLTEXT + @.TMPXML
> FETCH NEXT FROM XML_CURSOR INTO @.TMPXML
> END
> CLOSE XML_CURSOR
> DEALLOCATE XML_CURSOR
>
>
|||Another good one to have a look at
http://www.experts-exchange.com/Data..._20670044.html

Passing contents of text file into variable?

Is there a way to get the contents of a text into a variable in PL/SQL?Hello,

if you mean you want to read a file into a pl/sql var the use the
SYS.UTL_FILE to load th content.

Hope that helps ?

Best regards

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Passing a large parameter to s stored procedure

We have a developer who is trying to pass a large
parameter (type = text) into a stored procedure. The
parameter he is trying to pass is the contents of a 56mb
XML file.
The procedure works great with the next largest XML file
(about 30mb), but we are getting a network error when
trying to pass this large parameter.
Has anyone run into this... Any ideasHi
The batch size is limited to (65'536 * Network Packet Size) or (65'536 * 512
= 33Mb)
The developer neeeds to chunk the data in.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconservingresourceswhenwritingblobvaluestosqlserver.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthappendchunkx.asp
Regards
Mike
"WEX" wrote:
> We have a developer who is trying to pass a large
> parameter (type = text) into a stored procedure. The
> parameter he is trying to pass is the contents of a 56mb
> XML file.
> The procedure works great with the next largest XML file
> (about 30mb), but we are getting a network error when
> trying to pass this large parameter.
> Has anyone run into this... Any ideas
>

Passing a large parameter to s stored procedure

We have a developer who is trying to pass a large
parameter (type = text) into a stored procedure. The
parameter he is trying to pass is the contents of a 56mb
XML file.
The procedure works great with the next largest XML file
(about 30mb), but we are getting a network error when
trying to pass this large parameter.
Has anyone run into this... Any ideas
Hi
The batch size is limited to (65'536 * Network Packet Size) or (65'536 * 512
= 33Mb)
The developer neeeds to chunk the data in.
http://msdn.microsoft.com/library/de...ql server.asp
http://msdn.microsoft.com/library/de...pendchunkx.asp
Regards
Mike
"WEX" wrote:

> We have a developer who is trying to pass a large
> parameter (type = text) into a stored procedure. The
> parameter he is trying to pass is the contents of a 56mb
> XML file.
> The procedure works great with the next largest XML file
> (about 30mb), but we are getting a network error when
> trying to pass this large parameter.
> Has anyone run into this... Any ideas
>

passing @parameters and TEXT to xp_sendmail message!

How do you pass parameters and text to the message area... i know how to pass parameters or pass text how do i do both... for example.
Exec master.dbo.xp_sendmail @.recipients = 'johndoe' @.message = 'hello and @.number'
PLZ HELP!!!!!!DECLARE @.strSQL varchar(8000)

SELECT @.strSQL = 'master.dbo.xp_sendmail @.recipients = ''johndoe'' @.message = ''hello and ' + @.number + ''''

EXECUTE(@.strSQL)

Pass text variable to stored proc

Hi,
I use a stored proc to load XML data into tables. The stored proc takes as
input the XML as a text parameter:
spLoadXML (@.XMLText text)
I have a table "tblXMLContent" with a column of text type containing the XML
text.
How do I pass the content of the column to the stored proc?
ThanksIt depends on how you are calling the stored procedure.
If you are calling it from the application (odbc,jdbc) then you should be
able to find the correlated datatype for a text column and pass it in.
If you are calling it from a stored procedure, you can not declare a
variable of type text and therefore I don't think you can unless the xml is
a parameter to that stored procedure as well. The only hack I have been able
to come up with is declare multiple varchar(8000) columns and parse the text
column 8000 bytes at a time into these variables
i.e.
Declare @.vc1 varchar(8000)
,@.vc2 varchar(8000)
Select @.vc1 = substring(text,1,8000)
,@.vc2 = substring(text,8001,8000)
from txtTable
Then I would use the exec statement that would call the proc
i.e.
exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
Hope this helps.
"fleo" <fleo@.discussions.microsoft.com> wrote in message
news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
> Hi,
> I use a stored proc to load XML data into tables. The stored proc takes
> as
> input the XML as a text parameter:
> spLoadXML (@.XMLText text)
> I have a table "tblXMLContent" with a column of text type containing the
> XML
> text.
> How do I pass the content of the column to the stored proc?
> Thanks|||Thanks JI
But OMG!!!! What a waste of time. I can't believe Microsoft haven't
thought of that. I mean they didn't test their OPENXML with normal XML files
'
Do you know if the process is easier in SQL Server 2005?
"JI" wrote:

> It depends on how you are calling the stored procedure.
> If you are calling it from the application (odbc,jdbc) then you should be
> able to find the correlated datatype for a text column and pass it in.
> If you are calling it from a stored procedure, you can not declare a
> variable of type text and therefore I don't think you can unless the xml i
s
> a parameter to that stored procedure as well. The only hack I have been ab
le
> to come up with is declare multiple varchar(8000) columns and parse the te
xt
> column 8000 bytes at a time into these variables
> i.e.
> Declare @.vc1 varchar(8000)
> ,@.vc2 varchar(8000)
> Select @.vc1 = substring(text,1,8000)
> ,@.vc2 = substring(text,8001,8000)
> from txtTable
> Then I would use the exec statement that would call the proc
> i.e.
> exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
> Hope this helps.
> "fleo" <fleo@.discussions.microsoft.com> wrote in message
> news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
>
>|||I use a DTS to get the text column value into a global variable and call the
sp.
Then I call the DTS from another sp.
"JI" wrote:

> It depends on how you are calling the stored procedure.
> If you are calling it from the application (odbc,jdbc) then you should be
> able to find the correlated datatype for a text column and pass it in.
> If you are calling it from a stored procedure, you can not declare a
> variable of type text and therefore I don't think you can unless the xml i
s
> a parameter to that stored procedure as well. The only hack I have been ab
le
> to come up with is declare multiple varchar(8000) columns and parse the te
xt
> column 8000 bytes at a time into these variables
> i.e.
> Declare @.vc1 varchar(8000)
> ,@.vc2 varchar(8000)
> Select @.vc1 = substring(text,1,8000)
> ,@.vc2 = substring(text,8001,8000)
> from txtTable
> Then I would use the exec statement that would call the proc
> i.e.
> exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
> Hope this helps.
> "fleo" <fleo@.discussions.microsoft.com> wrote in message
> news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
>
>

Pass text (or, best, ntext) type value to an OLE Automation function call

Hello!
Is that possible to create a user-defined function, that would take value of
type text, and pass it to the OLE Automation function call?
The SP code I use to do OLE Aut. call, is as follows:
DECLARE @.object int
DECLARE @.hr int
DECLARE @.property nvarchar(255)
DECLARE @.src nvarchar(255), @.desc nvarchar(255)
DECLARE @.return nvarchar(255)
EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
PRINT @.return
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
****
I have currently working version, that uses char type, but I'd prefer to use
text or even ntext type. It seems like, that if it is not possible to pass
the text-type value to the OLE function call, I will be furced to pass a
record ID instead, and read the text value within the external code from the
same database, what actually sounds like risky way.
Thanks,
PavilsThere is no way to declare a local n/text variable. So, the answer is 'no'.
-oj
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:ufTfn5KTFHA.548@.tk2msftngp13.phx.gbl...
> Hello!
> Is that possible to create a user-defined function, that would take value
> of type text, and pass it to the OLE Automation function call?
> The SP code I use to do OLE Aut. call, is as follows:
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.property nvarchar(255)
> DECLARE @.src nvarchar(255), @.desc nvarchar(255)
> DECLARE @.return nvarchar(255)
> EXEC @.hr = sp_OACreate 'Blah.Something', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.object, 'myMeth', @.return OUT, 'Test string'
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> PRINT @.return
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> ****
> I have currently working version, that uses char type, but I'd prefer to
> use text or even ntext type. It seems like, that if it is not possible to
> pass the text-type value to the OLE function call, I will be furced to
> pass a record ID instead, and read the text value within the external code
> from the same database, what actually sounds like risky way.
> Thanks,
> Pavils
>

Pass literal text along with a parameter from Report Server

Hi, Is there any way to pass "list{" + Parameters!Parameter3.Value +
"}" as the whole parameter? Right now I have the text part in the
input text box and the user types the value inside the braces. I'm
talking to Informix db which is why I have this strange format.
Thanks, FredKeep the literal text in the query itself with quotes and allow users to just
enter the values and you build the query with literal text's
Amarnath
"fparc" wrote:
> Hi, Is there any way to pass "list{" + Parameters!Parameter3.Value +
> "}" as the whole parameter? Right now I have the text part in the
> input text box and the user types the value inside the braces. I'm
> talking to Informix db which is why I have this strange format.
> Thanks, Fred
>

Pass Date Range from VB to CR

Hi All,

i m Currently using VB,CR 9 and MS-Access

i have Create Cr report,

in my Vb form two text boxes Start date and end date,Simply i just want to display my report between two dates..

how can i do this..

AnyoneCan help me...

Thanx in Advance

Regards,
SabinaI am glad that you ask this question because I have the same problem and wanted to ask this to. I hope that someone can help us.

Greetings, Sjaaaf|||Create 2 global variables in module.bas
Public gstrFrom as String
Public gstrTo as String

Let say your form frmPrint.frm has textboxes txtFrom.text and txtTo.text
Put inside command button Print

Private Sub cmdPrint_Click()
gstrFrom = Trim(txtFrom.text)
gstrTo = Trim(txtTo.text)

Dim RptViewer As New frmRPrint 'frmRPrint.frm has a CRViewer object
RptViewer.Show
End Sub

Inside frmRPrint code section:

Option Explicit

Public Report As New ProdReport 'ProdReport.dsr is a Designer file

Private Sub Form_Load()
Dim adoc As ADODB.Command
Dim strSQL As String
Dim conn As ADODB.Connection

Me.Left = (Screen.Width - Me.Width) / 2
Me.Top = (Screen.Height - Me.Height) / 2

Screen.MousePointer = vbHourglass

Set adoc = New ADODB.Command
Set conn = New ADODB.Connection

conn.Open ConnString 'Connection String to MS Access Database
adoc.ActiveConnection = conn

strSQL = "SELECT ItemID, Quantity" & _
" FROM Production" & _
" WHERE ProdDate BETWEEN '" & gstrFrom & "' AND '" & gstrTo & "'"
adoc.CommandText = strSQL
adoc.CommandType = adCmdText

Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName

Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")

conn.Close
Set conn = Nothing

CRViewer1.ReportSource = Report

CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

Note:
You also can use
strSQL = "... WHERE ProdDate > '" & gstrFrom & "' AND ProdDate <'" & gstrTo & "'"
If your date is in date time format, concantenate the Todate with "11.59 pm" or "< gstrTo +1" so it will also include records on that day or else it will only display records until d/m/yy 12.00am|||hi

Thanx u very much..

If i get any error,than i will again ask u question.....again thnx

God with u always,

Regards,

Sabina|||hi

As u gave me Code of the passing Date range i did it

but when i use

Report.Database.AddADOCommand conn, adoc
Report.AutoSetUnboundFieldSource crBMTName

Report.ItemID.SetUnboundFieldSource ("{ADO.ItemID}")
Report.Quantity.SetUnboundFieldSource ("{ADO.Quantity}")

this codding lines

it not show me database,AutoSetUnboundFieldSource crBMTName like these properties

my report is a .dsr file...if i have Crystal report (.rpt file)than wha can i do...?

tell me plz what wil i do ,where Database etc. properties not display ...othertings u sais it is fine

Thanx in Advance

Regards,

Sabina|||Hi..

Instead of doing that one, try this one

XSTART = txtFrom.text
XEND = txtEnd.txt

Dim APP As New CRAXDRT.Application
Dim REPORT As CRAXDRT.Report

REPORT = APP.OpenReport("<your path>\<your reportfilename>.rpt")
REPORT.RecordSelectionFormula = "{<Table.Datefield>} >= #" & XSTART & "# AND {<Table.Datefield>} <= #" & XEND & "#"

Don't forget to Add the Crystal Report ActiveX Designer Run Time Library to your COM References ^_^|||Try this...

Place 2 Formula Fields in yr report and in the editor write "Date".

Then write the below code in yr VB codings and then execute the report.
CrystalReport1.Formulas(0) = "fdate='" & Format(DTPicker1.Value, "MMMM yyyy") & "'"
CrystalReport1.Formulas(1) = "tdate='" & Format(DTPicker2.Value, "MMMM yyyy") & "'"

Revert, if u still require assistance..