Multi-Value parameter syntax error

I am new to reporting services...
I've created a report that uses a multi-value parameter based ona dataset
usign a simple select statement. It works fine when a single value is
selected, but when multiple values are selected returns the error Incorrect
Syntax near ','.
How can the report code be modified to pass multiple values with the correct
syntax?See my response to your other posting (which is basically, show us what you
did). My guess is you have an incorrect SQL statement OR you are not going
against SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:7342117C-9715-477F-A9F4-E748C2EA3A77@.microsoft.com...
>I am new to reporting services...
> I've created a report that uses a multi-value parameter based ona dataset
> usign a simple select statement. It works fine when a single value is
> selected, but when multiple values are selected returns the error
> Incorrect
> Syntax near ','.
> How can the report code be modified to pass multiple values with the
> correct
> syntax?
>

Multi-Table Update?

Being an access guy, I am having a hard time understanding why I can't do a join statement on an UPDATE?

What is the alternatives??

I have to match two tables up and use records from one to update the other and creating a view isn't working...I would have suggested a view. What kind of problems are you getting with this approach? Are you trying to update a key value?|||
Another option is to use a stored procedure. You can update multiple tables, one after the other, using a stored procedure. Within the stored procedure you can use variables to hold values retrieved from one table and insert them into the next one.

|||you tend to lose update capabilities with a join when its a many to many join.

if your joining with a primary key it should work|||I'm going to have to learn some more about stored procedures because that would be excellent to do multiple updates like that..

Is there a code sample or link where I can learn more about doing the updates in that manor?

thx|||
Probably your best bet is to find a good book on the topic. In the meantime, you can hava look at the books online:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_4hk5.asp?frame=true

You can also download some of theStarter Kits from this website for lots of great samples (see the tab above).

Hope this helps.

Multitable Select

I have 2 tables involved in a select statement that I cannot figure out quite how to do.

Table 1- GroupedItems
int SharedId
int GroupId

Table 2- SharedItems
int SharedId
int ItemId

In Table 1 SharedId is unique
In Table 2 ItemId is unique

Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId

Example
Table 1
1 - 1
2 - 1
3 - 2

Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5

What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId

If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.

You can use something like this query in 2005 to get a unique set of items:

create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go

select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1

|||Thank you for your help.

This is exactley what I needed.
|||

More straight forward way to write the query is to do below:

select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)

And depending on your indexes/data this might give better performance than the ROW_NUMBER approach. For this particular example, the above approach will be twice as fast as the ROW_NUMBER approach. And if you have an index on say sharedId then the performance difference will be more significant.

|||True enough. I am always just a bit concerned about duplicate and ordering so I just defaulted to doing it that way. I should have thought about this better way since there is a single value to correlate with. Thanks!|||Thanks to both of you.

This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row number

Multitable Select

I have 2 tables involved in a select statement that I cannot figure out quite how to do.

Table 1- GroupedItems
int SharedId
int GroupId

Table 2- SharedItems
int SharedId
int ItemId

In Table 1 SharedId is unique
In Table 2 ItemId is unique

Table 1 represents all of a group's shared Items
Table 2 matches a group of Items to a common sharedId

Example
Table 1
1 - 1
2 - 1
3 - 2

Table 2
1 - 1
1 - 2
2 - 3
2 - 4
3 - 5

What I am trying to do is have a select command where you would pass in a GroupId and it would select just one Item from table 2 for each SharedId

If you were to specify Group 1 you would Get
ItemIds 1 and 3 from Table 2, although its not needed that the results are the top items.

You can use something like this query in 2005 to get a unique set of items:

create table SharedItems
(
sharedId int,
itemId int primary key
)
insert into SharedItems
select 1,1
union all
select 1,2
union all
select 2,3
union all
select 2,4
union all
select 3,5
go

select sharedId, itemId
from ( select sharedId, itemId, row_number() over (partition by sharedId order by itemId) as rowNumber
from SharedItems ) as numbered
where numbered.rowNumber = 1

|||Thank you for your help.

This is exactley what I needed.|||

More straight forward way to write the query is to do below:

select s1.sharedId, s1.itemId
from SharedItems as s1
where s1.itemId = (select min(s2.itemId) from SharedItems as s2 where s2.sharedId = s1.sharedId)

And depending on your indexes/data this might give better performance than the ROW_NUMBER approach. For this particular example, the above approach will be twice as fast as the ROW_NUMBER approach. And if you have an index on say sharedId then the performance difference will be more significant.

|||True enough. I am always just a bit concerned about duplicate and ordering so I just defaulted to doing it that way. I should have thought about this better way since there is a single value to correlate with. Thanks!|||Thanks to both of you.

This approach looks a little more like what I was expecting, but I'm glad I was able to learn about the row numbersql

Multirow insert statement,... how?

In MySQL I was able to insert multiple rows into the db like this:

REPLACE INTO Products (productid, price) VALUES (1, 55), (2, 88), (3, 99);

In transact-Sql (Sql Server 2000), this "REPLACE" keyword means something different. What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row.

How do I do this in SQL Server 2000?

Also, I can do this in MySQL:

INSERT INGORE INTO Products (productid, price) VALUES (1, 55), (2, 88), (3, 99);

This meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will ignore that new row's insert and keep the values of the old row.

How do I do that also in SQL Server 2000?

ThanksYou can't easily. MySQL is CISC to MS SQLs RISC.
There is no equiv' REPLACE and no IGNORE. You have do everything the hard way.

You can use insert with a not exists (yuk) or populate a temp table var with the rows you want and load the same keyed values from the database. That way you can see the gaps (inserts) and the ones with data (updates). Still not gr8 but what you gonna do!|||Darn,... I have experience developing aps with both MySQL and SQL server and I think this is the first time ever that it's harder to do something with SQL SERVER. Usually, it's MySql that makes things harder.

No wonder why I couldn't find any documentation for the life of me.
Oh well!|||You could use, and I'm trying not to be sick when saying it, a datagram. I think that's got some of the features you want <shudder>. Might be worth a look though.|||How do I use a datagram? Can you give me some examples?|||try
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/dotnet_8704.asp|||You can insert multiple rows into a table by doing the following (works for SQL 7 & 2000, not sure about MySQL):

insert into yourtable(field1, field2, field3)
select
customerid,
firstname,
lastname
from
customers
where
customerid between 1 and 10

This would insert several rows from the customers table into yourtable.|||youre kiddin me right tingent? That is absolutely off the subject of what we are talking about|||"What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row."

Surely, though, by definition you will only ever have ONE row that matches the primary key so isn't this in effect an UPDATE statement? Or does the replace statement insert a new row if a match isn't found (you didn't mention it did!) ?|||Ouch...ok, I admit I didn't completly read the post so I didn't know what REPLACE was doing in MySQL. My bad...|||But the SQL i wrote does exactly what the the thread title says, Multi-row insert statement.|||>> But the SQL i wrote does exactly what the the thread title says, Multi-row insert statement

True but you do need to read the question. I think you caused a tiny little bit of offense 'cause judging by the question I think they know how do a standard insert. Don't worry though, no ones perfect - and don't let some of the posters tell you otherwise ;)|||I apologize, I didn't mean it at all the way it sounded. Sometimes things don't come off the right way when you type them over the internet. If we were in person and I would've said that to you, you would've took it the right way because I would've said it in a joking way.

Sorry|||Tingent, again, I apologize for the way that sounded.

The Mult-Row insert statement that you have extracts rows from another area and inserts them into the table. I need to insert brand new rows.|||hat's off to you javan15, what I jolly good egg you are.

Multiples in a select statement

Is it possible to write a single select statement that would pull the following from a table named tblPayments.

I need the 'first payment date and amount', 'last payment date and amount', 'largest payment date and amount', and 'total payments' for each client by a date range.

tblPayments
ClientID
PaymentDate
PaymentAmount

Thanks for any suggestions.

create table p (ClientID int, PaymentDate datetime, PaymentAmount numeric(10,2))
insert into p
select 0,'1/1/2004',100 union all
select 0,'1/2/2004',80 union all
select 0,'1/3/2004',110 union all
select 1,'1/5/2004',87 union all
select 1,'1/12/2004',180 union all
select 1,'1/13/2004',10
select
d.clientID,
d.firstPmtDate,
p1.PaymentAmount,
d.lastPmtDate,
p2.PaymentAmount,
p3.PaymentDate maxPmtDate,
d.maxPmt,
d.ttlPmt
from
(
select
clientID,
min(paymentdate) firstPmtDate,
max(paymentDate) lastPmtDate,
max(PaymentAmount) maxPmt,
sum(PaymentAmount) ttlPmt
from p
--where PaymentDate between @.startDate and @.endDate
group by clientid
) d
join p p1 on p1.clientid = d.clientid and p1.paymentdate = d.firstPmtDate
join p p2 on p2.clientid = d.clientid and p2.paymentdate = d.lastPmtDate
join p p3 on p3.clientid = d.clientid and p3.PaymentAmount = d.maxPmt
--where p3.PaymentDate between @.startDate and @.endDate

|||Thank you, but this does not appear to return the amounts associated with each last and first payment dates. That is where I run into problems. I could be reading it bad?

Thanks again,|||Did you run the query in QA with the sample data?|||Ok, It worked in QA. I'm having some trouble understanding how you did this. How can I learn to understand this type of process?

Thanks for any suggestions,|||I'm having trouble converting this to my data. Is there anything specific I should know to do this?

Thanks again,|||What are you having trouble with specifically?|||Well, I get no errors but there is no data being returned.

Thanks,|||To demonstrate the query, I created a table called p, which represents your table tblPayments, it is an example. Then the table p is aliased as p1, p2,p3 where the joins are occuring to simplify the columns assignments. The alias d is used to declare the derived table which perform the aggregate functions to obtain max,min,etc...

If you want to use this query for you purposes you can change the table assignment of p in the from and join clauses to tblPayments.

Does that help to clarify?|||Yes, and that is what I did and I know there is data but none is being returned. This very puzzling.

Thanks again,|||I had not changed the table name in the joins and had listed my table AS p in the From clause. Now I get data, but I get many records for each client. I was hoping to end up with one row for each client. Any thoughts on this?

Thanks again,|||Can you post the revised query you are using..|||Here it is. Thanks,

select

d.Client_ID,

d.firstPmtDate,

p1.AmountPaid,

d.lastPmtDate,

p2.AmountPaid,

p3.PaymentDate maxPmtDate,

d.maxPmt,

d.ttlPmt

from

(

select

Client_ID,

min(paymentdate) firstPmtDate,

max(paymentDate) lastPmtDate,

max(AmountPaid) maxPmt,

sum(AmountPaid) ttlPmt

from tblPayments

--where PaymentDate between @.startDate and @.endDate

group by Client_ID

) d

join tblPayments p1 on p1.Client_ID = d.Client_ID and p1.paymentdate = d.firstPmtDate

join tblPayments p2 on p2.Client_ID = d.Client_ID and p2.paymentdate = d.lastPmtDate

join tblPayments p3 on p3.Client_ID = d.Client_ID and p3.AmountPaid = d.maxPmt

--where p3.PaymentDate between @.startDate and @.endDate|||It is somewhat difficult to fully understand the data behavior w/o seeing the data and variations that occur. If the rows are displaying duplicate data for each client you can modify the main select to select distinct which will limit dups from the results set.

Hope that helps.sql

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?
Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare one
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare on
e
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

multiple where statement in a SP

Hello, a small question. I have a search page on the site where the user can search for other users, they can fill in age, gender etc

The question is, how should i extract all that information from the database, i am using SP in MS SQL 2005, C#.

For each of the options is there a possibility to press "not specified", how do i build up a query in the sql server?

Should i just

IF (@.Age1 <> '')
BEGIN
SET @.SearchString = @.SearchString + 'AND (profile_publicinfo.age = ' + @.Age1 + ')'
END

and then continue like this?

so it would look something like this:

SELECT username, gender, signupdate .... FROM profile_publicinfo FULL OUTER JOIN ..... WHERE (@.SearchString)

any ideas?

I highly recomomend Erland Sommarskog's article:Dynamic Search Conditions in T-SQL.|||Thanks, Erland Sommarskog's article solved my problems.sql

Multiple Where Clause on One Report

Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@.Stat) from the statement below:

" WHERE contractinfo.termdate >= GETDATE()
AND provider.status= 'Active' AND provider.credentialstatus = (@.Stat)"

This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie

"WHERE contract.description NOT LIKE 'NON%' "

But if the user selects 'B' then the Where clause would go to a different set of constraint ie

"WHERE contract.description LIKE 'NON%' "

Thanks

You might be able to change your WHERE clause from this:

Code Snippet

WHERE contractinfo.termdate >= GETDATE()

AND provider.status= 'Active' AND provider.credentialstatus = (@.Stat)

to this:

Code Snippet

WHERE contractinfo.termdate >= GETDATE()

AND provider.status= 'Active'

AND ( @.stat = 'A' AND contact.description not like 'NON%' OR

@.stat = 'B' AND contact.description like 'NON%'

)

|||Thanks for the help Kent that worked great, the only thing I did different for my report was leaving the original (@.Stat) parameter in also otherwise it would not have filtered it by the "A or B" condition and then adding your script suggestion.. Thanks again

Multiple view from a single select statement

Hi,
I want to write an SQL query which should return me 2 kinds of outputs depending on which condition is true. i.e.

the query should be something like

select someview if (condition1 = true)
else select someotherview if (condition2 = true)

where someview is a set of columns from one table only, and someotherview is a set of columns which is a superset of someview (& is generated from two tables, which have no common field)

Let me explain this further --
I want to select data from one table and a single field from other table, however if I the first table does not return any data, I still want data from the other table to be returned.
Can I write a SQL query to do this?

-- Amitdo a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?|||Originally posted by edwinjames
do a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?

I know, that I can use a union, however I want to support TimesTen & Oracle using thsame query...TT does not allow union while oracle does, can I write another query which emulates union?|||I am sorry I am not farmiliar with TT.

Does it support outer joins?|||Originally posted by edwinjames
I am sorry I am not farmiliar with TT.

Does it support outer joins?

Yes it dows, hence I am now using outer joins|||Glad to be of help

:)

multiple view create

Hi
How can I create multiple views in a single SQL script ?
(I get an error: 'CREATE VIEW' must be the first statement in a query
batch.)
thanksYou can use the batch terminator, GO, to seperate the batches:
CREATE VIEW xyz
AS
SELECT
..
GO
CREATE VIEW abc
AS
SELECT
..
GO
... etc
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"romy" <romy1000@.hotpop.com> wrote in message
news:e6ml7BM5FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I create multiple views in a single SQL script ?
> (I get an error: 'CREATE VIEW' must be the first statement in a query
> batch.)
>
> thanks
>sql

Multiple Variables Assigned To One Select

Hello,

Is there a way to assign multiple variables to one select statement as in the following example?

DECLARE @.FirstName VARCHAR(100)

DECLARE @.MiddleName VARCHAR(100)

DECLARE @.LastName VARCHAR(100)

@.FirstName, @.MiddleName, @.LastName = SELECT FirstName, MiddleName, LastName FROM USERS WHERE username='UniqueUserName'

I don't like having to use one select statement for each variable I need to pull from a query. This is in reference to a stored procedure.

Thank you!

Cody

Hi, you can use the below syntax.

SELECT

@.FirstName = FirstName,

@.MiddleName = MiddleName,

@.LastName = LastName

FROM USERS

WHERE username = 'UniqueUserName'

Eralper

http://www.kodyaz.com

Multiple tables used in select statement makes my Update statement not work?

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown.

I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:

----------------------------------------

SELECT:

SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName

FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID)

WHERE (People.PersonID = ?)

UPDATE:

UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ?

----------------------------------------

The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed.

If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.

Thank you in advance for any time, help, and/or advice you can give.

Brian

I just wanted to add that I am working with Visual Web Developer 2005 Express. If any one knows how I can get around this issue please let me know, or even if you might know of a place that could possibly have a document that approaches this I have no problem doing the research. I have spent hours searching forums and doing google and microsoft searches for tutorials and have had no luck. I definately havn't come across anything say this is not possible, I just need to know what I am missing.

Thanks for your time in advance,

Brian

|||

You shouldn't really be using ?'s for parameters unless you are using odbc or something. That's not standard practice for sql server, but it is the only way to do it for databases that don't support named parameters.

That aside the problem is the foreign key fields. You don't want to update the base table with the text representations of the foreign keys, you need to update the base table with the foreign key itself. After an edit, you don't have those values anywhere, all you have is the text version. There are many approaches to solving your problem. Looking the values up in the sqldatasource_updating event, and populating the parameters with the looked up id's. You can even come up with some sql to do the lookup for you as well although it does get a bit messy. The alternative that I use is a custom column type specifically for this purpose that pulls the foreign key values in from another sqldatasource and when in "edit" mode, it presents them to the user as a dropdownlist. Unfortunately I can't share that code with you, but there are probably other examples of it on the web, I'd google for "dropdownfield" or "asp.net dropdownfield".

|||

Motley:

You shouldn't really be using ?'s for parameters unless you are using odbc or something. That's not standard practice for sql server, but it is the only way to do it for databases that don't support named parameters.

The question marks were put in place by the software. I was trying to get the UPDATE to work on my own and i was setting the fields I wanted to be updated equal to @.fieldName. I could not get this to update so I used the "Advanced" feature in the quesry wizard (I guess it's called) and selected the button to auto-generate the SQL statement and this worked. The UPDATE statement it generated is the one I pasted above. My database is an access database for the time being, but I am using the SqlDataSource method to connect to my database, must be thats why it used the question marks?

Motley:

That aside the problem is the foreign key fields. You don't want to update the base table with the text representations of the foreign keys, you need to update the base table with the foreign key itself. After an edit, you don't have those values anywhere, all you have is the text version. There are many approaches to solving your problem. Looking the values up in the sqldatasource_updating event, and populating the parameters with the looked up id's. You can even come up with some sql to do the lookup for you as well although it does get a bit messy. The alternative that I use is a custom column type specifically for this purpose that pulls the foreign key values in from another sqldatasource and when in "edit" mode, it presents them to the user as a dropdownlist. Unfortunately I can't share that code with you, but there are probably other examples of it on the web, I'd google for "dropdownfield" or "asp.net dropdownfield".

The way I have it setup is so that the field contains a INT value, but the value it shows to the user is a text value. The drop down list you are suggesting is exactly what I am using. When the user is in Read Only mode, everything is shown as a label. When they enter Edit mode, all the fields with foreign keys turn into drop down lists. The drop down list shows a string value, but when the choice is made, its value is and integer. I believe this is working right because the technique I use in order to select a user to edit is to select a name from a drop down list that is external from my gridview (same thing with my detailview). The value of the drop down list is numerical based on the primary key of the PersonID, but the value shown in the drop down list is the name that corrisponds to the given PersonID. This works great and only shows me the record of the person I want to see. Because this worked, I just assumed that useing the same technique in the Edit view would be working the same way. I think this is what you mentioned in what you mentioned above. I will paste all the acctual code from the page I am working on that uses DetailsView.

<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
DeleteCommand="DELETE FROM [People] WHERE [PersonID] = ?" InsertCommand="INSERT INTO [People] ([FirstName], [LastName], [FullName], [PropertyID], [InviteTypeID], [RSVP], [Wheelchair], [MealID], [TransportationID]) VALUES (?, ?, ?, ?, ?, ?, ?,?,?)"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT People.*, Property.[House/Day Hab], InviteType.InviteTypeName, Meals.MealName, Transportation.TransportationName FROM Transportation INNER JOIN (Meals INNER JOIN (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) ON Meals.MealID=People.MealID) ON Transportation.TransportationID=People.TransportationID WHERE (PersonID = ?)"
UpdateCommand="UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ?, [MealID] = ?, [TransportationID] = ?, WHERE [PersonID] = ?">
<DeleteParameters>
<asp:Parameter Name="PersonID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FullName" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="PersonID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="FullNameDDL" Name="People.PersonID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>

<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />

<asp:Parameter Name="PropertyID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="MealID" Type="Int32" />
<asp:Parameter Name="TransportationID" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
<asp:DropDownList ID="FullNameDDL" runat="server" DataSourceID="GetNames" DataTextField="FullName"
DataValueField="PersonID" Style='left: 5px; position: relative; top: 190px' AutoPostBack='True'>
</asp:DropDownList><asp:AccessDataSource ID="GetNames" runat="server" DataFile="H:\Visual Studio 2005\WebSites\test\App_Data\DinnerGuest.mdb"
SelectCommand="SELECT [PersonID], [FullName] FROM [People]"></asp:AccessDataSource>

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SqlDataSource1"
Height="50px" Style='left: 201px; position: relative; top: 152px' Width='125px'>
<RowStyle Wrap="False" />
<Fields>
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" ConvertEmptyStringToNull="False" NullDisplayText="<NULL>" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" ConvertEmptyStringToNull="False" />
<asp:TemplateField HeaderText="PropertyID" SortExpression="PropertyID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="GetProperty" DataTextField="column1"
DataValueField="PropertyID" SelectedValue='<%# Bind("PropertyID") %>' Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetProperty" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [PropertyID], [House/Day Hab] AS column1 FROM [Property]">
</asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="GetProperty" DataTextField="column1"
DataValueField="PropertyID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetProperty" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [PropertyID], [House/Day Hab] AS column1 FROM [Property]">
</asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("[House/Day Hab]") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="InviteTypeID" SortExpression="InviteTypeID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="GetInviteName"
DataTextField="InviteTypeName" DataValueField="InviteTypeID" SelectedValue='<%# Bind("InviteTypeID") %>'
Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetInviteName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [InviteType]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList4" runat="server" DataSourceID="GetInviteName"
DataTextField="InviteTypeName" DataValueField="InviteTypeID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetInviteName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [InviteType]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("InviteTypeName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CheckBoxField DataField="RSVP" HeaderText="RSVP" SortExpression="RSVP" />
<asp:CheckBoxField DataField="Wheelchair" HeaderText="Wheelchair" SortExpression="Wheelchair" />
<asp:TemplateField HeaderText="MealID" SortExpression="MealID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList5" runat="server" DataSourceID="GetMealName" DataTextField="MealName"
DataValueField="MealID" SelectedValue='<%# Bind("MealID") %>' Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetMealName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [MealID], [MealName] FROM [Meals]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList6" runat="server" DataSourceID="GetMealName" DataTextField="MealName"
DataValueField="MealID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetMealName" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT [MealID], [MealName] FROM [Meals]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("MealName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="TransportationID" SortExpression="TransportationID" ConvertEmptyStringToNull="False">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList7" runat="server" DataSourceID="GetTransportationType"
DataTextField="TransportationName" DataValueField="TransportationID" SelectedValue='<%# Bind("TransportationID") %>'
Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetTransportationType" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Transportation]"></asp:SqlDataSource>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList8" runat="server" DataSourceID="GetTransportationType"
DataTextField="TransportationName" DataValueField="TransportationID" Style='position: relative'>
</asp:DropDownList><asp:SqlDataSource ID="GetTransportationType" runat="server" ConnectionString="<%$ ConnectionStrings:DinnerGuestConnectionString %>"
ProviderName="<%$ ConnectionStrings:DinnerGuestConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Transportation]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("TransportationName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
</div>
</form>

|||

Yes, using access would require using ?'s since it doesn't support named parameters.

The DropDownField control I mentioned makes things a bit easier for you because you don't have to create templated fields (They become DropDownFields), your main select is kept simple if you want (No joins needed, it can pull the text values for you if you want).

But since you've already gone through the trouble, it appears the only problem I can find is that your update parameters do not match your update command.

UpdateCommand="UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ?, [MealID] = ?, [TransportationID] = ?, WHERE [PersonID] = ?"

UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FullName" Type="String" />
<asp:Parameter Name="LocationID" Type="Int32" />
<asp:Parameter Name="InviteTypeID" Type="Int32" />
<asp:Parameter Name="RSVP" Type="Boolean" />
<asp:Parameter Name="Wheelchair" Type="Boolean" />
<asp:Parameter Name="PersonID" Type="Int32" />
</UpdateParameters>

LocationID should be PropertyID. MealID needs to be added. TransportationID needs to be added. PersonID should either be a controlparameter -- pulling it's value from the person dropdown, or you should add it to the detailview's datakeynames property. All the parameters need to be in the exact order they are mentioned in the update command.

|||

Thank you so much for all your help. I was definately only relying on Visual Web Developer to get this going which seems to be my down fall. As I made changes to my data source and changed my queries, I guess the code that is automatically developed doesn't follow along with the changes I make very well. Thanks again for taking the time to look through my code, I will definately make sure I am checking it more, instead of just relying on the software. I guess if the software could do everything I wanted without me seeing the code then I probably would have a much more difficult time finding work haha. Live and learn I guess.

Thanks again,

Brian

multiple tables in grant statement

hello everyone,
is it possible to list multiple tables in a grant statement.
so instead of using:
GRANT ALL ON AuditColumns TO ADMIN
GRANT ALL ON AuditConfig TO ADMIN
i can just use:
GRANT ALL ON AuditColumns, AuditConfig TO ADMIN
thank you for your help,
abraham lunaIt cannot be done :(|||You can do it an easy way:
SELECT 'GRANT SELECT ON ' + table_name + ' TO whoever', *
FROM INFORMATION_SCHEMA.tables
Run this, obviously add some WHERE criteria to limit the tables, copy the
result set to the main window and execute that. Easy eh?
Let me know how you get on.
Damien
"Abraham Andres Luna" wrote:

> hello everyone,
> is it possible to list multiple tables in a grant statement.
> so instead of using:
> GRANT ALL ON AuditColumns TO ADMIN
> GRANT ALL ON AuditConfig TO ADMIN
> i can just use:
> GRANT ALL ON AuditColumns, AuditConfig TO ADMIN
>
> thank you for your help,
> abraham luna
>
>|||ty for your reply
i ended copying and pasting lots of those statements and then modifying it
as needed

Multiple table update

Hi There

I am using tsql in Sql Server 2005.

Is it possible/how do i update multiple tables in a single update statement.

I want to do something like this.

UPDATE A, B, C, D SET A.Col1 = ?, B.Col2 = ?, C.Col3 = ?, D.Col4 = ?

FROM TableA AS A

JOIN TABLEB AS B ON B.ID = A.ID

JOIN TABLEC AS C ON C.ID = A.ID

JOIN TABLED AS D ON D.ID = A.ID

Basically all the tables are related, i want to update values in all the tables in a single update but i just cannot seem to get the syntax correct, BOL also has no example of multiple table update so i am not even sure if this is possible.

Thanx

you can not update multiple tables in single update statement . what u can do is put all these different update statement in a begin tran/commit tran

Madhu

multiple subcubes in one MDX query

Is it possible to use more than one subcube in one MDX statement? Putting a subcube in the FROM clause is great, but sometimes you need more than one.

One subcube in the from clause is nice, but sometimes you need to use one subcube to limit the totals for one column, but then you need a different subcube to limit the totals of another column. For instance, if users can dynamically pick a list of stores and you want the total sales for the stores they pick, a subcube in the from clause will do that. But then if you want a second column to show the totals of peer stores (which are not in the subcube), then you're in trouble.

I think I know the answer to this question already, but I thought I'd ask just to make sure.

The silence is deafening ;-)

In SQL you can use multiple subqueries such as:

select column1
,column2 = (select x from y where a=b)
,column3 = (select x from z where c=d)
from aaa

In MDX, I think you can only have a subcube in the FROM clause, so you can't do more than one subcube per query. Is that correct?

|||

No - you can only use subcubes in the from clause, if you have more than one, they have to be nested - effectively narrowing the scope, which would not help in your example.

In the example you have given I would consider approaching it from the other way. Create a subcube in the from clause that picks all the peer stores, then use a calculated measure or a tuple to pick out the total for the particular store that the user selected.

multiple stored procedure...or 1 dynamic procedure?

Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...

my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...

if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...

this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...

but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??

Cheers,
JustinHi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH|||Hi freefall

Did you read the link Jesse gave you? Read that and you shouldn't need much more tutelage on the use of dynamic SQL.

Just FYI - you would probably find most of the people on here would say you are going the wrong way and shouldn't be looking to use dynamic SQL for this. Instead have a number of hard coded sprocs specific to the tables and actions you want to perform on the tables. This will result in more secure and efficient code that is easy to debug.

HTH

Hi,

Yeah I did have a quick read of the dynamic sql like Jesse provided...and was able to solve the previous problem with it...as i was a bit under the pump when i read it, i did not read the entire thing, and missed anything relating different amounts of dynamic columns...

You have pretty much answered what i was asking anyway, so it looks like im in for a long session of writing stored procs...

Thanks Again,
Justin

multiple statements on insert trigger (mssql2000)

Can I have more than 1 statement fire on an insert trigger as I have
illustrated below, or do I use 2 insert triggers?
.....or is there a better solution?
Thanks Soc.
++++++++++++++++++++++++++++++++++++++++
+++++
CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
FOR INSERT
AS
update table1 set column1=column2 where column3='GREEN' and column4 is null
update table1 set column5='peter' where column3='GREEN' where
column5<>'john'
++++++++++++++++++++++++++++++++++++++++
+++++soc
I'm not sure I inderstand you.
Triggers are fired per statement not per Insert. Why do you perform two
updating on the same table within a trigger, can explain what are you trying
to do?
"soc" <zxc0@.yahoo.com> wrote in message
news:%23al7PS2OFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Can I have more than 1 statement fire on an insert trigger as I have
> illustrated below, or do I use 2 insert triggers?
> .....or is there a better solution?
> Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
> CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
> FOR INSERT
> AS
> update table1 set column1=column2 where column3='GREEN' and column4 is
null
> update table1 set column5='peter' where column3='GREEN' where
> column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
>|||Can an insert trigger do 2 updates along the lines of the trigger below?
"soc" <zxc0@.yahoo.com> wrote in message
news:%23al7PS2OFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Can I have more than 1 statement fire on an insert trigger as I have
> illustrated below, or do I use 2 insert triggers?
> .....or is there a better solution?
> Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
> CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
> FOR INSERT
> AS
> update table1 set column1=column2 where column3='GREEN' and column4 is
> null
> update table1 set column5='peter' where column3='GREEN' where
> column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
>|||On Thu, 7 Apr 2005 12:25:52 +0100, soc wrote:

>Can I have more than 1 statement fire on an insert trigger as I have
>illustrated below, or do I use 2 insert triggers?
>.....or is there a better solution?
>Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
>CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
>FOR INSERT
>AS
>update table1 set column1=column2 where column3='GREEN' and column4 is null
>update table1 set column5='peter' where column3='GREEN' where
>column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
Hi soc,
You can use as many statements as you wish in the code of a trigger. But
you can't use two WHERE clauses in one UPDATE statement (as you do in
your second update).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Multiple statement handles on one connection

I want to use multiple concurrently active statement
handles on a single connection handle. An ODBC call to
SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
indicating that there is a limit of 1 active statement on
a single connection. If I try to SQLExecDirect on another
statement handle on the same connection handle I get an
error complaining that there is another active statement
on the same connection.
Is there any way to configure either SQLServer or its ODBC
driver to allow multiple concurrently active statement
handles on the same connection? This is pretty common
database programming practice to Exec one query, then use
a loop with SQLFetch and then execute multiple SQL
statements on another statement handle within the loop.
Even lowly MSAccess allows multiple concurrently active
statement handles on the same connection.
My ODBC driver version is 2000.85.1022.00.
My SQLServer version SQL Server Developer Edition 8.00.194
(RTM)This is not possible with current versions of SQL Server or the SQL Server
ODBC Driver, when using the default "firehose" cursor. You can use
server-side cursors, which allow you to fetch a single row at a time from
the server (and therefore free up the connection between each row), or you
can cache the data from the first statement yourself (therefore freeing up
the connection). And of course, you can open up a second connection for the
second statement.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Lee Scheffler" <anonymous@.discussions.microsoft.com> wrote in message
news:b47e01c4077d$ef011af0$a101280a@.phx.gbl...
> I want to use multiple concurrently active statement
> handles on a single connection handle. An ODBC call to
> SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
> indicating that there is a limit of 1 active statement on
> a single connection. If I try to SQLExecDirect on another
> statement handle on the same connection handle I get an
> error complaining that there is another active statement
> on the same connection.
> Is there any way to configure either SQLServer or its ODBC
> driver to allow multiple concurrently active statement
> handles on the same connection? This is pretty common
> database programming practice to Exec one query, then use
> a loop with SQLFetch and then execute multiple SQL
> statements on another statement handle within the loop.
> Even lowly MSAccess allows multiple concurrently active
> statement handles on the same connection.
> My ODBC driver version is 2000.85.1022.00.
> My SQLServer version SQL Server Developer Edition 8.00.194
> (RTM)