asked on

crystal report

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

ASKER CERTIFIED SOLUTION

Randy Peterson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Randy Peterson

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.

Seven price

ASKER

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

Seven price

ASKER

that works to I can even change the parameter to value = 8
8 being one of the batch id's

Seven price

ASKER

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

Randy Peterson

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??

Seven price

ASKER

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

Seven price

ASKER

Yes the column headers are returned but the data does not come back unless i do this.
Select * From dbo.SalesItems where BatchId = '2'

Randy Peterson

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)

Seven price

ASKER

Yes it does

Randy Peterson

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.

Seven price

ASKER

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.

Seven price

ASKER

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

Randy Peterson

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.

Randy Peterson

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

Seven price

ASKER

ok give me a sec I will try that have to reboot.l

Seven price

ASKER

ok this works only when I put the batch id itself but I want to pass the parameter any other suggestions.
Thanks

Seven price

ASKER

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.?