Help with relational query - many to many

Hi all! I am working on a piece of SQL at the moment and I'm getting a little confused.
I have 3 tables: Items, Attributes and a table linking them. I have 5 attributes and an item can have any of the 5 attributes. So my linking table holds the ItemID and the AttributeID and there can be 1-5 entries for each Item.
A user can search for items based on Attributes; so they can tick 5 checkboxes that represent the 5 Attributes. So I need to build a query based on their choices. At the moment I'm using:
Select * FROM Items
INNER JOIN linking on Link_ItemID = Item_ID
WHERE Link_AttributeID IN (10, 13, 17)
But this brings out the Item that have either AttributeID of 10 or 13 or 17 whereas I need it to pull outONLYitems that have a AttributeID of 10 AND 13 AND 17.
Can anyone help with this query? Sorry if this is badly worded. The solutions is prolly something really simple I have overlooked... :S
I've also tried:
Select * FROM Items
INNER JOIN linking on Link_ItemID = Item_ID
WHERE Link_AttributeID = 10 AND AttributeID = 13 etc
But obviously that won't work! :sSelect *
from items i
where exists (select null from linking where link_itemID = i.item_id and link_attributeID = 10) and
exists (select null from linking where link_itemID = i.item_id and link_attributeID = 13) and
exists (select null from linking where link_itemID = i.item_id and link_attributeID = 17)

Nick|||Sorry for the late reply! Thanks for your post nick!
i got it working :)

Help with Query

I have two tables, one with all the items (iv00101) and one with history of sales per month (iv30102). I need to make a query where it displays the item number, the item description, and the sum of the sales of the months I select.

for example:

select a.itemnmbr as Number, a.itemdesc as Description, sum(b.smrysales) as Sales
from iv00101 as a left join iv30102 as b on a.itemnmbr = b.itemnmbr
where (b.month = 12 and b.year=2003)

that gives me no problem when the item has a sales history on december/2003. But if an item was created january/2004 and I make the same query, that item doesn't appear beacuse it has no sales history on december/2003. I need it to appear with the sum(b.smrysales) as Sales = 0

any ideas?change WHERE to AND so that the conditions involving b.month and b.year are part of the ON clause

Help with inserting a new record into database - error Must declare the scalar variable "@

Hi,

Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :

Must declare the scalar variable "@.BookMarkArrayA".

when it reaches the .insert command, I've tried using a local variable temp in place of the array element and.ToString , but still get the same error

This is the code :

PublicSub NewCustomer()

Dim tempAsString =" "

Dim IDAsInteger = 1

'Restore the array from the view state

BookMarkArrayA =Me.ViewState("BookMarkArrayA")

temp = BookMarkArrayA(6)

Dim CustomerAs SqlDataSource =New SqlDataSource()

Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

Customer.Insert()

EndSub

Cheers

Ken

I'm not sure where you got the (6) syntax from?

Try this. Change these 3 lines:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

to this and you should have better luck:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Integer).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value =@.BookMarkArrayA)

|||

Terri,

Thanks for the reply, it didn't compile as the SqlDbType wasn't recognised.

The origional error was @.BookMarkArray was an undeclared scalar variable, which suggests to me that this part of the command requires a pointer to the actual variable BookMarkArray denoted by putting the @. symbol first.

The (6) syntak by the way, was the element of the array I wanted to load into the DB.

The code fits with other examples I've looked up, I'm proberbly missing something simple.

Ken

|||Boy did I screw up the code. I am sorry about that. Here's what it *should* be:

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.Program)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Int).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value = BookMarkArray(6)

|||

Terri,

I still get the error that SqlDbType is not declared, am i missing inheriting a library or some thing

Ken

|||Well, try qualifying it by adding the namespace in front and see if that takes care of it.

SqlClient.SqlDbType.Int|||

Terri,

I get the error SqlClient not declared, so I assume that I'm not inheriting something.

I tried the following code which worked, but I can't find a method to change the String value 'Test Program' into a parameter:

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText =

"INSERT Into Customer (Program) VALUES ('Test Program')"

.Connection = sqlConnection1

EndWith

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

Ken

|||Try that code block like this (updates in pink):

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText = "INSERT Into Customer (Program) VALUES (@.Program)"

.Connection = sqlConnection1

EndWith

cmd.Parameters.Add("@.Program", System.Data.SqlDbType.VarChar, 99).Value = "Test Program"

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

|||

Terri,

Thanks, that worked, althrough I'd tried variations of adding parameters before without success.

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient and when you look at the methods, it says you must reference this namespace but don't tell you how.

Ken

|||

KenWalker:

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient

If you are using code inline (ie, not a separate .vb file), put this at the top of the page, right below the @.Page directive:

<%@. Import Namespace="System.Configuration" %>

If you are using code beside/behind, put this at the very top of your code:

Imports System.Data.Client

|||

I'm lazy, so I put it in web.config under the system.web section:

<pagestheme="default">

<namespaces>

<addnamespace="System.Data"/>

<addnamespace="System.Data.SqlClient"/>

<addnamespace="System.Configuration.ConfigurationManager"/>

</namespaces>

</pages>

|||

Thanks Terry, I have solved the problem using -Dim cmdAsNew System.Data.SqlClient.SqlCommand and then adding the parameters that way.

Ken