error messgae - 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 2005 Forums

Transact-SQL (2005)

error messgae

Author

Topic

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 04:36:32

Hello all, I have the following error message in one of my stored procedures but no idea how to rectify it, any help would be brill.

ERROR
Msg 207, Level 16, State 1, Procedure spGetZonesType, Line 6(in red)
Invalid column name 'storeID'

Stored Procedure
SELECT
zoneID
,ZoneName
FROM tblzones
WHERE zoneID
IN (SELECT DISTINCT fk_zoneID FROM tblStores2Zones WHERE fk_storeID = (storeID))

END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-29 : 05:11:00

Make sure the column storeID is available in the table tblStores2Zones

Madhivanan

Failing to plan is Planning to fail

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 05:29:03

available? you mean make sure its in that table? it is as fk_storeID, i thought that would have been enough?

quote:
Originally posted by madhivanan

Make sure the column storeID is available in the table tblStores2Zones

Madhivanan

Failing to plan is Planning to fail

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-29 : 05:33:13

what exactly is the storeID? is it a column in tblStores2Zones or some other table? Or a value in fk_storeID column?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 05:58:00

storeID is a column in another table (tblstores). The column in tblstores2zones is, fk_storeID, that is how the two tables are linked.

Im playing around with a stored procedure because I have a problem with some data not being returned to a drop down menu in an ASP page properly. I think it may be becuase of the Stored Procedure

quote:
Originally posted by Mangal Pardeshi

what exactly is the storeID? is it a column in tblStores2Zones or some other table? Or a value in fk_storeID column?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-29 : 06:04:23

[code]

SELECT
zoneID
,ZoneName
FROM tblzones
WHERE zoneID
IN (SELECT DISTINCT Z.fk_zoneID
FROM tblStores2Zones as Z JOIN tblstores AS s
ON z.fk_storeID = S.storeID)
[/code]

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 06:22:24

Ha ha, brilliant it has worked, thank you soooo much, onto the next part of my project.

but i have to ask what was i doing wrong? what was the error? You seem to have much knowledge.

quote:
Originally posted by Mangal Pardeshi

SELECT
zoneID
,ZoneName
FROM tblzones
WHERE zoneID
IN (SELECT DISTINCT Z.fk_zoneID
FROM tblStores2Zones as Z JOIN tblstores AS s
ON z.fk_storeID = S.storeID)

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 06:32:27

Nooooo, something is not working properly, hope you can help.

let me explain.

I have a drop down menu that should display specific records for a shop based on the shops unique id number (storeID) but when i click from shop1 to shop 2, it displays the same zoneID and ZoneName for them all....arr :-(.

please save my mind from this madness.

Regards
MG

quote:
Originally posted by Mangal Pardeshi

SELECT
zoneID
,ZoneName
FROM tblzones
WHERE zoneID
IN (SELECT DISTINCT Z.fk_zoneID
FROM tblStores2Zones as Z JOIN tblstores AS s
ON z.fk_storeID = S.storeID)

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-29 : 06:38:43

Sorry not able to follow you.

What is shop1 to shop 2? What is expected at shop1 and shop2? And what is the logic?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 06:42:40

Im think i may need to add something to the SQL statement where by it checks the storeid number and fk_store id number match. So it should then bring back data specific to that store/shop?

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-29 : 06:46:01

yes, may be... :)

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 07:13:01

Let me explain, well try and explain.

I have an ASP page that pulls information from a database. the information that is displayed to the user is in a drop down menu. this drop down menu should only disply records that relate specifically to a certain shop / store.

This "information" is, what types of displays they have in their shops. displays are things like cabinets, window displays, shelves etc.

so for example, shop number 1 should have 2 window displays, 3 cabinets, but shop number 2 should have 1 window display and 4 cabinets.

at the moment all shops are showing the same types of displays(zoneNames) and the same number for displays (eg window display = zoneid 1).

hope this makes sense.

I think i have to set it so the SQL statement checks what value the storeID is and return the the zonesName associated with that store?

So i think something like this perhaps?

SELECT
zoneID
,ZoneName
,storeID
FROM tblzones
WHERE zoneID
IN (SELECT DISTINCT Z.fk_zoneID
FROM tblStores2Zones as Z JOIN tblstores AS s
ON z.fk_storeID = S.storeID)
AND StoreID = FK_storeID

quote:
Originally posted by Mangal Pardeshi

Sorry not able to follow you.

What is shop1 to shop 2? What is expected at shop1 and shop2? And what is the logic?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-29 : 07:37:00

may be yes!!
But You will need to rewrite your query a bit.
StoreId column exists is in tblzones table?
If not then, my suggestion is - remove the IN clause and rewrite the query using JOIN with tblStores2Zones and tblstores tables.

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 07:54:49

I thought i may. Im very new to this field but the keys areas im using are the following

the tables im working with are:

tblzoneTypes
columns (zoneTypeID - ZoneTypeName)
1 cabinet
2 gondola
3 fish tank
4 window promo
5 triple window promo
6 Memory Display
7 gondola two
8 kiosks

zoneID | ZoneName | fk_zonetypeID
1 Cabinet one - small 1
2 Cabinet two - middle 1
3 Cabinet three - large 1
4 Cabinet four - double bay 1
5 WINDOW FRONT LEFT 1 5
6 WINDOW FRONT LEFT 2 5
8 Memory Display 6
9 Goldola 2

tblStore2Zones
store2ZoneID | fk_storeID | fk_zoneID | active
1 1 1 True
2 1 2 True
3 1 3 True
4 1 5 True
5 2 1 True
6 2 3 True
7 2 5 True
8 3 1 True
9 3 5 True

tblstores
storeID | StoreNumber | storeName
1 1 First Store
2 2 second store
3 3 Third Store
5 4 fourth
6 5 fifth store
7 6 sixth
8 7 seven
9 8 eight
10 9 nine
11 10 ten
12 11 eleven

the ASP code is this:

SELECT FLOORPLAN LISTINGS <select name="zoneID" id="zoneID">
<option value="0" > - Cabinets - </option>
<%
sSQL = "exec spGetZonesType"
SET oRS = oDB.Execute(sSQL)
response.Write iZoneID & "-"
If Not oRS.EOF Then
While Not oRS.EOF
%><option value="<%=oRS("zoneTypeName")%>" <%
If cint(checkValue(iZoneID)) = ("zoneID") Then
response.write " selected "
End If
%>><%=oRS("zoneTypeName") & "-" & oRS("zoneName")%></option><%
oRS.MoveNext
Wend
End If
%>

quote:
Originally posted by Mangal Pardeshi

may be yes!!
But You will need to rewrite your query a bit.
StoreId column exists is in tblzones table?
If not then, my suggestion is - remove the IN clause and rewrite the query using JOIN with tblStores2Zones and tblstores tables.

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-29 : 08:05:50

A complete query may look like this


SELECT Z.zoneID 
,Z.ZoneName
,S.storeID
,zt.ZoneTypeName

FROM tblZone Z INNER JOIN tblzoneTypes AS ZT
ON Z.fk_zoneTypeID  = ZT.zoneTypeID 
tblStore2Zones AS sz
ON Z.zoneID = SZ.fk_zoneID 
INNER JOIN tblstores AS S
ON SZ.fk_storeID = S.storeId

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 10:17:42

My original Stored procedure was this.

SELECT
storeID
,zoneID
,zoneName

FROM
tblstores s
LEFT JOIN tblStores2Zones s2z
on storeID = s2z.fk_storeID
LEFT JOIN tblZones z
on fk_zoneID = z.zoneID
LEFT JOIN tblZoneTypes zt
on fk_ZoneTypeID = zt.zoneTypeID

WHERE storeid = fk_storeID

but it didnt work.

I just used what you wrote and i get the following error.

Msg 102, Level 15, State 1, Procedure spGetZonesType, Line 12
Incorrect syntax near 'tblStore2Zones'

SELECT Z.zoneID
,Z.ZoneName
,S.storeID
,zt.ZoneTypeName

FROM tblZone Z INNER JOIN tblzoneTypes AS ZT
ON Z.fk_zoneTypeID = ZT.zoneTypeID
tblStore2Zones AS sz
ON Z.zoneID = SZ.fk_zoneID
INNER JOIN tblstores AS S
ON SZ.fk_storeID = S.storeId

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 10:19:35

sorry it's this line

tblStore2Zones AS sz

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-29 : 11:34:46

A JOIN is missing...try this..

SELECT Z.zoneID, 
       Z.ZoneName, 
       S.storeID, 
       zt.ZoneTypeName 
FROM   tblZone Z 
       INNER JOIN tblzoneTypes AS ZT 
         ON Z.fk_zoneTypeID = ZT.zoneTypeID 
       INNER JOIN tblStore2Zones AS sz 
         ON Z.zoneID = SZ.fk_zoneID 
       INNER JOIN tblstores AS S 
         ON SZ.fk_storeID = S.storeId

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-30 : 04:29:36

Hi thank you for your post,

This unfortunately still brings back all the results, and is not specific to a shop.

If i add this:

WHERE s.storename ='second shop'

all information will be brought back, but again, if i select a different store, then it will again bring back information about the "second shop", because it wont cycle through the table.

it has to bring back information that is unique to a shop

regards
MG

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-07-30 : 06:53:33

Hi all,

With my constant questions on how to get this problems sorted, i felt it only right tell you it has now been sussed.

The problem I had was because i wasn't passing a parameter through the query.

the final sql query was:

CREATE PROCEDURE [dbo].[spGetZonesType]
@iStoreID DECIMAL
AS
BEGIN

SELECT
zoneID
,ZoneName
,ZoneTypeName
FROM tblZones z
INNER JOIN tblZoneTypes AS zt
ON z.zoneID = zt.zoneTypeID
INNER JOIN tblstores2zones AS s2z
on z.zoneID = s2z.fk_zoneID
inner join tblstores AS s
on s2z.fk_storeID = s.storeID

WHERE storeID = @iStoreID

END

Thanks to all for help in getting it sorted.

Regards
MG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources