Solved

crystal report

Posted on 2008-06-12
18
369 Views
Last Modified: 2013-11-26
Yes I am trying to pass a query string parameter which is my batchId field in my database to my crystal report.

my storeprocedure i hard code to
Select * From dbo.SalesItems where BatchId = '2' i can see the information in that column
but when i Select * From dbo.SalesItems where BatchId =@batchId I just see the header not the information.

below I tried to pass the parameter both ways but only 2nd passes the header but only if I use the Select * From dbo.SalesItems where BatchId = '2'

1st does not pass anything and the viewer comes up with error.
no links just a good coder or debugger.
1st  

MyCommand.Parameters.Add(New SqlParameter("@BatchId", SqlDbType.BigInt)).Value = Request.QueryString("id")
 
 

2nd

MyCommand.Parameters.Add(New SqlParameter("@BatchId", SqlDbType.BigInt)).Value = BatchId

Open in new window

0
Comment
Question by:sevensnake77
  • 11
  • 7
18 Comments
 
LVL 8

Accepted Solution

by:
Randy Peterson earned 500 total points
ID: 21772367
There are two things to consider.  What datatype is in your SQL Sproc?  Is it a big int?  If not, then you need to make your VB code match.

Second, make sure the name of your VB parameter ("@BatchId") matches the exact name of the input parameter in your sproc.
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 21772381
Are you able to run your sproc in query analyzer?  What happens when you pass in a value through that (If you are able)?

I would debug the sproc 1st and your VB code 2nd.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21772572
Yes i can run the stored procedure that is not a problem.I can run the stored procedure

Select * From dbo.SalesItems where BatchId =@batchId
but in the viewer does not return the batchId
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21772581
that works to I can even change the parameter to value = 8
8 being one of the batch id's
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21772594
here is the vb code
Dim rpt As New CrystalReport3   ' report created

        Dim myConnection As SqlConnection

        Dim MyCommand As New SqlCommand("SalesbatchRepor", myConnection)

        Dim myDA As New SqlDataAdapter

        Dim myDS As New Dataset78 ' Dataset created

        Dim UserId As String

        Try
 
 

            myConnection = New SqlConnection(Application("A1SConnString"))

            MyCommand.Connection = myConnection

            MyCommand.CommandText = "spA1SGetSalesbatchReport" 'Stored Procedure

            MyCommand.CommandType = CommandType.StoredProcedure

            MyCommand.Parameters.Add(New SqlParameter("@BatchID", SqlDbType.BigInt)).Value = BatchId
 

            myDA.SelectCommand = MyCommand
 
 

            myDA.Fill(myDS, "SalesItems")

            rpt.SetDataSource(myDS)

            myConnection.Open()

            CrystalReportViewer1.ReportSource = rpt ' Connects to viewer using rpt
 

        Catch Excep As Exception

            With Label2

                Label2.Visible = True

                .CssClass = "labelRed"

                .Text = "Sorry Data could not be loaded please try again later"

            End With

        End Try
 

        myConnection.Close()

Open in new window

0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 21772816
Can you post your sproc code??  I want to see how that is.  That is where the issue is, I think.

what viewer are you talking about when you state "but in the viewer does not return the batchId"

Are you meaning that the column headers are returned, but that the data doesn't come back??
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21772998
spoc code
@BatchID Bigint
 

AS    
 
 

Select * From dbo.SalesItems where BatchId = @BatchId

--SELECT	'Return Value' = @return_value

GRANT EXECUTE ON dbo.spA1SGetSalesbatchReport TO WebServer

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21773357
Yes the column headers are returned but the data does not come back unless i do this.
Select * From dbo.SalesItems where BatchId = '2'
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 21773942
It looks like you issue is a casting.  I am guessing that you batchid in the SalesItem table is a int or varchar and that it is not a bigint.  It looks like it works when you make it like an int.  Does it work if you do the following " Select * From dbo.SalesItems where BatchId = 2 "? (Without the quotes)
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 9

Author Comment

by:sevensnake77
ID: 21775157
Yes it does
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 21776065
Do you know what the batchid in the table is?  I am guessing that it is not a Bigint field.  I am betting that if you change the Sproc from bigint to int that it will work just fine.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21779728
nO THAT DID NOT WORK but maybe this will help I think I have to create the stored procedure as innerjoin
or relationship. ok there is 2 databases, when i create a category it generates the batchid and tells me the userid witch is the salesbatch database. then when i click on that category it opens with the storeId and batchid querystring that was generated from the category created. So maybe i need to have a connection between these 2 tables. so how would i right a connection between these 2 tables in a stored procedure, its been a while.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21780358
I will end this if you can tell me where i can add userId from one database

select BatchId from SalesBatch
Union
select BatchId from Salesitems


select BatchId, userID from SalesBatch
Union
select BatchId from Salesitems

not sure how this goes
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 21780891
To get a connection in the 2 tables, you need to use the following syntax:

select *
from SalesBatch SB
join Salesitems SI on SB.BatchId = SI.BatchID

That will get the 2 tables joined together.  Then you can add a where clause etc.

To use the UNION clause, like up above, the select fields from table 1 must match (exactly) to the select fields from table 2.  But they don't jon the tables.
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 21780900
So to get the userid you can do the following:

select SB.BatchId, SB.userID
from SalesBatch SB
join Salesitems SI on SB.BatchId = SI.BatchID
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21781545
ok give me a sec I will try that have to reboot.l
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21812741
ok this works only when I put the batch id itself but I want to pass the parameter any other suggestions.
Thanks
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 21817250
well I found out it has to be on the same page. so last question how do i make a session var to carry the querystring with me to any page or file.

So for instance if the page is Items.aspx?id=41096

how can i store this on my page that this is the query string.?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now