Error When Report Called Form VB.net 1

[Auguy]

SQL 2000 reporting services. I have a report based on a stored proc and I am passing a 'Group1' parameter which consists of a concatenation of two fields and some literal text that is used for grouping and a textbox on the report. Sample for parameter Group1

'Zip ' + ShipZip + ' To ' + DestZip

This works fine. Also if I test the stored proc using the query analyzer it works fine. Sample

set @Group1 = '''Zip '' + ShipZip + '' To '' + DestZip'

Here is a part of the stored proc

...
Declare @SqlCommand varchar(4000)
Set @SqlCommand = 'Select ' + @Group1 + ' As GroupBy
...
Begin
Execute (@SqlCommand)
End

The problem comes in when I try to call report from a VB.net program by building the URL and displaying the report in browser control in the program. If I only specify one field in the Group1, it works fine. But if I try and concatenate two fields or some literal text it gives me an error "Incorrect syntax near the word 'To'. I've tried eliminating the literal and just passing

&Group1=ShipZip + DestZip

then I get an error "Incorrect syntax near the keyword 'as'. I guess I can change how I'm doing the grouping. Just wondered if there was a simple fix. Thought I would try here before posting in the vb.net forum.

Auguy
Northwest Ohio

 

[bburnell]

I'm assuming this is asp.net, correct? Just a thought, have you tried putting single or double quotes around the literal?

&Group1='ShipZip + DestZip'

Not sure if that will work or not, but I thought it would be worth a try. Wish I was more help, but I do all of my reports in Windows or on the server interface.

~Brett

--------------------------
Web/.net Programmer & DBA
Central PA

 

[Auguy]

Thanks Brett. It's a windows program. Yes I have tried the single and double quotes. If I do as you suggested Group1 comes back as the text ShipZip + DestZip, not the field values. I'm experimenting passing them as separate values. I will probably go that route, probably better code anyway.

Auguy
Northwest Ohio

 

[bburnell]

Could you just make multiple parameters and just populate them separately?

--------------------------
Web/.net Programmer & DBA
Central PA

 

[Auguy]

Yes, that's what I meant by "passing them as separate values". That will work, but I was hoping to create the a field of the text I'm grouping on, something like "43560 To 75432" because I want to change the grouping depending on a user selection and was trying to avoid building the text in the report.

Auguy
Northwest Ohio

 

[bburnell]

Can you put that into a string variable and pass that string to the report parameter? That seems like it would work.

--------------------------
Web/.net Programmer & DBA
Central PA

 

[Auguy]

I think it's a matter of getting the quote marks in the correct spot to indicate I want the fields to concatenate.

Auguy
Northwest Ohio

 

[bburnell]

That's what it sounds like to me as well!

--------------------------
Web/.net Programmer & DBA
Central PA

 

[xlbo]

Is it maybe an issue with '+' in the url....reserved character that needs replacing with something like %20 ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bburnell]

Not sure what you are using for VS version, but I am using VS 2008 and SQL 2005. Couldn't you do this?

parmList.Add(New ReportParameter("Group1", "Zip " & ShipZip.ToString.Trim & " To " & DestZip.ToString.Trim, True))

VB wants the "&" not the "+"

--------------------------
Web/.net Programmer & DBA
Central PA

 

[Auguy]

Thanks Brett & Geoff. I will give %20 a try when I get a chance.

Auguy
Northwest Ohio

 

[xlbo]

%20 won;t work for +

I think that's the decode for a space

%2B is the code for a +

Would try Brett's suggestion 1st though..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

Thanks to all for the help, will reply as soon as I have time to test.

Auguy
Northwest Ohio

 

[Auguy]

Thanks Geoff, %2B did the trick. Tthe following seems to be working.

&Group1=ShipZip + %2B + DestZip

Auguy
Northwest Ohio

 

[xlbo]

Good news!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Auguy]

Just a follow up. Thanks to Geoff I now pass a "Grouping String" to the report, which passes it on to the stored proc. This "Grouping String" is concatenation of two to four fields. This lets me set the grouping in the report to the column created by the stored proc for the "Grouping string". Therefore I don't have to do anything on the report to change the grouping, it is always set to this column and I can still drill down to the detail. Right now I have nine different groupings I let the user select.
1. Shipping State/City to Destination State/City
2. Shipping State/City to Destination State
3. Shipping State/City to Destination Zip
4. Shipping State to Destination State/City
5. Shipping State to Destination State
6. Shipping State to Destination Zip
7. Shipping Zip to Destination State/City
8. Shipping Zip to Destination State
9. Shipping Zip to Destination Zip

The actual string I pass looks like the following for # 2

&Group1=ShipState + %2B + ', ' + %2B + ShipCity + %2B + ' To ' + %2B + DestState

On the report it looks like this

OH, CLEVELAND To TX
OH, COLUMBUS To TX
...

The user may also select from one to 5 digits of the zip code for the grouping. This report is being used to analyze shipping costs and profits between various shipping and destination locations.


Auguy
Northwest Ohio