storedprocedure and asp newbie

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

storedprocedure and asp newbie

Author

Topic

havey
Starting Member

16 Posts

Posted - 2006-04-17 : 01:14:23

i'm tring to use a store procedure located here:
http://www.sqlteam.com/item.asp?ItemID=2955

is a crossTab query procedure for sql2k.

i'm new to this so plese bear in mind my baby steps...
this is what i have done:
I've add the sp_CrossTab to the mdaDB (that be the db i will query, do i need to add it to master?)

I given exec permissions to the sp_CrossTab (hopefully i was suspose to do this?)

I'm trying to call the sp_CrossTab from and ASP page, This is what i have so far:

TION_STRING = "PROVIDER=SQLOLEDB;DATA SOURCE=xxxxxxxx;UID=xxxxxxx;PWD=xxxxxx;DATABASE=mdaDB"


Set Conn = Server.CreateObject("ADODB.Connection")
 
      
	  Conn.Open(TION_STRING)
Conn.EXECUTE sp_CrossTab = "SELECT SpecialtyID, ProvinceID FROM Member WHERE MD_Select=0","Count(MemberID)","SpecialtyID","ProvinceID"

basically i have a table called Member, in this table i have three columns, MemberID (is unique to each record), SpecialtyID (ranges from 0 to 62) and ProvinceID ranges from 1 to 65.

the result i would like from the sp_CrossTab is
the provinceID from 1-65 across the top, specialtyID 1-62 on the side and the count of each MemberID in each.

Basically i kinda understand that the sp_CrossTab accepts a select statement and three other parameters, but i believe i need only two other parameters.

I would like to know if i am set up correctly?

My error is:
Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'Conn.EXECUTE'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 02:18:57

Instead of
Conn.EXECUTE sp_CrossTab = "SELECT SpecialtyID, ProvinceID FROM Member WHERE MD_Select=0","Count(MemberID)","SpecialtyID","ProvinceID"

Try

Conn.EXECUTE (" sp_CrossTab 'SELECT SpecialtyID, ProvinceID FROM Member WHERE MD_Select=0',"Count(MemberID)','SpecialtyID','ProvinceID'")

Madhivanan

Failing to plan is Planning to fail

havey
Starting Member

16 Posts

Posted - 2006-04-17 : 03:02:33

Thanks for the tip

and for reading my previous post, i ran into a new error:

Procedure 'sp_CrossTab' expects parameter '@GroupBy', which was not supplied. and ideas?

this is what i'm using:

Conn.EXECUTE (" sp_CrossTab 'SELECT SpecialtyID, ProvinceID FROM member WHERE MD_Select=0 and ProvinceID<14','SpecialtyID', 'Count(ProvinceID)'")

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 03:18:42

So you solved the problem now?

Madhivanan

Failing to plan is Planning to fail

havey
Starting Member

16 Posts

Posted - 2006-04-17 : 03:23:04

no can't seem to figure out the error of:

Procedure 'sp_CrossTab' expects parameter '@GroupBy', which was not supplied.

how do i supply a @group value?

I changed the select statement again this is the new one (if it matters:

Conn.EXECUTE (" sp_CrossTab 'SELECT SpecialtyID, ProvinceID, Count(ID) AS myCount FROM Member WHERE MD_Select=0 AND ProvinceID<14 GROUP BY SpecialtyID, ProvinceID ORDER BY SpecialtyID, ProvinceID', 'SpecialtyID', 'ProvinceID' ")

i tried to add a blank parameter to the end like:
ORDER BY SpecialtyID, ProvinceID', 'SpecialtyID', 'ProvinceID', '' ")

but got this error:
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Any suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 03:30:24

Send the parameters in the same order as described in that article. Dont use Order by clause

Madhivanan

Failing to plan is Planning to fail

havey
Starting Member

16 Posts

Posted - 2006-04-17 : 04:19:11

ahhhh table name.....

quote:
EXECUTE crosstab SELECT statement, summary calculation, pivot column, table name

(" sp_CrossTab 'SELECT SpecialtyID, ProvinceID AS myCount FROM Member WHERE MD_Select=0 AND ProvinceID<14 GROUP BY SpecialtyID, ProvinceID', 'Count(MemberID)', 'ProvinceID', 'Member' ")

OK now i'm getting error of :
Invalid column name 'MemberID'.
the column does exist.... i thought it had somthing to do with MemberID bring a primary key with relationships but got same result when use Count(MemberID)

maybe i have not slept in over 26 hours but am i missing something simple?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 04:24:32

Make sure you have column named MemberID in the table

Madhivanan

Failing to plan is Planning to fail

havey
Starting Member

16 Posts

Posted - 2006-04-17 : 04:46:10

I do have

see image:
http://www.geocities.com/bella_cola/image.GIF

Can someone please help this newbie

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-18 : 01:50:09

in a group by, you should also have the same fields enumerated in your select and you're not allowed to have a where criteria specifying otherwise...

in short...

post the dsql string you'll be executing inside your sproc and let's see where that memberid is

a simple 'select @sql' will do it, where @sql is your dsql

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources