Problem with Shape Statment - SQL Server Forums

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)

Problem with Shape Statment

Author

Topic

azim
Starting Member

29 Posts

Posted - 2003-07-06 : 14:06:34


Dim StrSQL As String
StrSQL = "Select * From QryBooksTabs"

If cboSubNo1.Text <> "" Or cboSubNo2.Text <> "" Then
StrSQL = StrSQL & " Where "
StrSQL = StrSQL & " tblBookTabs.SubNo  Between " & Val(cboSubNo1.Text) & " And " & Val(cboSubNo2.Text) & ""
End If

SQL = "SHAPE {SELECT * FROM `tblKinds` Where  " & StrSQL & "}  AS CmdKind APPEND ({SELECT * FROM `QryBooksTabs`}  AS CmdQryBooksTabs RELATE 'KindName' TO 'KindName') AS CmdQryBooksTabs"

DataEnvironment1.rsCmdKind.Open SQL

I Recieved This Error


No value given for one or more parameters

Please Help

azim

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-06 : 16:40:19

Sorry, azim, but what does it mean?

SHAPE {SELECT * FROM tblKinds Where
Select * From QryBooksTabs Where tblBookTabs.SubNo Between 555 And 777}
AS CmdKind
APPEND ({SELECT * FROM QryBooksTabs} AS CmdQryBooksTabs RELATE KindName
TO KindName) AS CmdQryBooksTabs

- Vit

dsdeming

479 Posts

Posted - 2003-07-07 : 08:52:15

From BOL entry for ShapeLanguage:

quote:
Const SHAPE_TITLEAUTHORS = _
"SHAPE {SELECT au_id, au_lname, au_fname FROM authors} " & _
"APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS " & _
"WHERE TA.title_id = TS.title_id} " & _
"AS title_chap RELATE au_id TO au_id)"

I haven't ever used the SHAPE command, but comparing what's in BOL to the code you supplied, one obvious difference is the single quotes you have around your table and column names. That could be causing some of your problem.

Dennis

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-07 : 09:37:31

ummm, Dennis...i dont think Vit was questioning the SHAPE command, he was trying to point out that the concatenated string did not make sense. BTW, the shape statement syntax that Azim has used is fine (I think he is using it for VB Data Reports), he's just trying to add some dynamic parameters, but the strings are going a little screwy.

Vit, cut the sarcasm a little, will ya?

Azim, check your StrSql, it needs a little fixing.

Owais

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-07 : 13:51:02

Wow!
Owais, is it so? Is the syntax correct?
Be honest I never used the Shape command in my practice,
just only once for my 'private' needs... :)

- Vit

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-07 : 14:43:19

Yup, this would have been perfectly legal syntax:

SHAPE {SELECT * FROM tblKinds} AS CmdKind
APPEND ({SELECT * FROM QryBooksTabs} AS CmdQryBooksTabs RELATE KindName TO KindName) AS CmdQryBooksTabs

The SHAPE Command lets you form joins and hierarchical relationships. Good for reporting, particularly Cross-tab reports (in MS-Access).

Owais

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 14:54:14

Owais,

Can you help me understand this..

I'm trying to build an example using Northwind"



USE Northwind
GO

SHAPE {SELECT * FROM Orders} AS CmdOrder 
APPEND ({SELECT * FROM [Order Details]} AS CmdOrderDetail RELATE OrderId TO OrderId) AS CmdTabs

And I get:

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

I'm assuming Syntax

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 14:58:05

Hey,

I even cut and pasted the code from bol..


USE Pubs
GO
SHAPE {SELECT au_id, au_lname, au_fname FROM authors} 
APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS 
            WHERE TA.title_id = TS.title_id}
        AS title_chap RELATE au_id TO au_id)

GO

and get the same message.

What gives? Is this a Enterprise Edition thingee?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 15:00:32

[homer]dooooooooooooh[/homer]

It's not a T-SQL thingee...

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-07 : 15:01:52

Owais,

your syntax I understand OK.
But what about azim's construction?
I can bet my life I will never understand it. :)

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 15:41:14

Well,

not that I know to much about this, but logically I think this is a problem:


If cboSubNo1.Text <> "" Or cboSubNo2.Text <> "" Then
StrSQL = StrSQL & " Where "
StrSQL = StrSQL & " tblBookTabs.SubNo  Between " & Val(cboSubNo1.Text) & " And " & Val(cboSubNo2.Text) & ""
End If

If I think it should be an AND and not an OR.

And what about Null values?

An empty string is not the same as Null (the absence of anything).

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-08 : 02:38:54

You are right, Brett...it is not standard T-SQL. If you look at the (VB) example given in BOL, it uses the MSDataShape provider rather than the SQL Server Provider (SQLOLEDB). The MSDataShape OLEDB provider takes care of deciphering the query and returning the hierarchical recordset. And you can't run it from Query Analyzer! The only places you can use it are:

[*]VB Data Reports (really lousy tool) using the DataEnvironment
[*]Access Crosstab reports
[*]DTS Data Pumps

I think what Azim wants to do is something like this:

SHAPE {SELECT * FROM `tblKinds`} AS CmdKind APPEND ({SELECT * FROM `QryBooksTabs` WHERE SubNo Between xxxx and yyyy} AS CmdQryBooksTabs RELATE 'KindName' TO 'KindName') AS CmdQryBooksTabs

Those wierd quotes around the table-names are tell-tale signs of having been generated by the VB Data Environment.

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources