Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>
You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>
|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,[vbcol=seagreen]
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client[vbcol=seagreen]
it
>
>
|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>
|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with[vbcol=seagreen]
mode.[vbcol=seagreen]
works[vbcol=seagreen]
If
>
sql

Return different row sets using for xml?

I am returning rows using "for xml raw" from a stored procedure - works
great. But I'd like to reduce the number of interactions between my client
and the database - for example: returning two sets of different data
requires two calls to two different stored procedures.
But can one stored procedure return both data sets using "for xml"? If it
could then I can reduce the number of calls. The two data sets have
absolutely no relation to each other and bear no similarities in structure.
E.g.
To get data from the person table and the hotel table currently requires two
stored procedure calls to GetPeople and GetHotels. Can it be combined to
GetPeopleAndHotels?
<data>
<people>
<person name = "fred"/>
<person name = "bob"/>
</people>
<hotels>
<hotel country = "uk"/>
<hotel country = "us"/>
</hotel>
</data>You can call more than one FOR XML query inside a stored proc.
Also, if you need more semantic markup than what the RAW mode can give you,
you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am returning rows using "for xml raw" from a stored procedure - works
> great. But I'd like to reduce the number of interactions between my client
> and the database - for example: returning two sets of different data
> requires two calls to two different stored procedures.
> But can one stored procedure return both data sets using "for xml"? If it
> could then I can reduce the number of calls. The two data sets have
> absolutely no relation to each other and bear no similarities in
> structure.
> E.g.
> To get data from the person table and the hotel table currently requires
> two
> stored procedure calls to GetPeople and GetHotels. Can it be combined to
> GetPeopleAndHotels?
> <data>
> <people>
> <person name = "fred"/>
> <person name = "bob"/>
> </people>
> <hotels>
> <hotel country = "uk"/>
> <hotel country = "us"/>
> </hotel>
> </data>
>|||Do you know if returning multiple xml result sets would be compatible with
the BizTalk 2004 SQL Adapter?
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> You can call more than one FOR XML query inside a stored proc.
> Also, if you need more semantic markup than what the RAW mode can give
you,
> you can use the AUTO, EXPLICIT, and in SQLServer 2005 the new PATH mode.
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:OAqx8qA6EHA.4040@.TK2MSFTNGP14.phx.gbl...
client
it
>
>|||Sorry, but I don't know. I would assume that you expose the result through
the stream interface and add the root element through the stream property,
it should look like a single XML document.
You may want to ask somebody over in the BTS newsgroup...
Best regards
Michael
"Xerox" <info@.thinkscape.com> wrote in message
news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you know if returning multiple xml result sets would be compatible with
> the BizTalk 2004 SQL Adapter?
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:u5TKP0J6EHA.2608@.TK2MSFTNGP10.phx.gbl...
> you,
> client
> it
>|||Thanks for the help. I tried it out and it does work fine! The two "for xml"
queries return the data to BizTalk as a single XML document.
The only trouble is, and I wonder if you can help me, is that I would like
both queries to return their xml data under a parent tag: ie. return all
<Customer/> tags under <Customers/>.
This is my procedure:
create procedure GetXML as
select 1 as Tag, Null as Parent, EmployeeID as 'Employee!1!EmployeeID' from
employees for xml explicit;
select 1 as Tag, Null as Parent, CustomerID as 'Customer!1!CustomerID' from
customers for xml explicit;
go
which returns xml data like this:
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
<Customer CustomerID="SPLIR" />
But I would like it to return data like this:
<Employees>
<Employee EmployeeID="3" />
<Employee EmployeeID="4" />
</Employees>
<Customers>
<Customer CustomerID="SPLIR" />
</Customers>
Do you know if it is possible? Thank you!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e7xLiVX6EHA.2584@.TK2MSFTNGP10.phx.gbl...
> Sorry, but I don't know. I would assume that you expose the result through
> the stream interface and add the root element through the stream property,
> it should look like a single XML document.
> You may want to ask somebody over in the BTS newsgroup...
> Best regards
> Michael
> "Xerox" <info@.thinkscape.com> wrote in message
> news:%23kdsg8M6EHA.3416@.TK2MSFTNGP09.phx.gbl...
with
mode.
works
If
>

Return Date not DateTime

I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?

SELECT COUNT(DISTINCT DT) FROM Event

SELECTConvert(Varchar,DT,101),Count(*))FROM EventGroup byConvert(Varchar,DT,101)

|||

SELECTCOUNT(DISTINCTDAY(DT)+' /'+MONTH(DT)+' /'+YEAR(DT))FROMEvent

return an id while doing an insert\update to a table

Hi people,

i Have a small issue. I need to be able to retrive an id number of a new row to a table using the the insert into command. I was able to do this in sql 2000 but the same sql does not work now in 2005. here is the code

"Set NoCount On; select user_id from users insert into users (username) values('" & CurrentUser & "')"

This used to work in sql2000,

I am woundering if anyone could help me or point me in the right direction for doing this with SQL 2005

Best regards

RBowden

Did you try putting a semincolumn between the statements (before the insert) ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

I tried putting the ; before the insert function it is still returns 0

any other ideas?

|||

Ah, ok now I know what you mean. You are refering to the OUTPUT clause in SQL Server 2005.

"Set NoCount On; DECLARE @.Somevar VARCHAR(10);insert into users (username) OUTPUT user_id INTO @.SomeVar values('" & CurrentUser & "')"; SELECT @.SomeVar"

Look in the BOL, there should me some examples around that. If you are using an IDENTITY Column for the userid cou can also query the SCOPE_IDENTITY() function for the new identity value.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Cheers,

thank you very much for your help, that worked a treat.

keep up the good advice

all the best

Return a row with columns for each day in a date range

Given in a record in from a Table called WorkSchedule:

idWorkSchedul StartDate EndDate HoursWorked
1 1/1/2000 1/1/2006 8

I need to return for each record in the WorkSchedule Table

1/1/2000 1/2/2000 1/3/2000 1/4/2000..........1/1/2006

8 8 8 8..................8

Please help.

Thank you.

-Robert

Hi Robert,

Let's assume your source table was called "WS":

with Hours (MinDate, MaxDate, WorkDate, WorkHrs)

AS

(

SELECT StartDate as [MinDt], EndDate AS [MaxDt], StartDate AS [WorkDt], HoursWorked

FROM WS

WHERE idWorkSchedul = 1

UNIONALL

SELECT MinDate, MaxDate,DATEADD(day,1, WorkDate)AS [WorkDate], WorkHrs

FROM Hours h

WHERE WorkDate <= MaxDate

)

select*from Hours

This will I think give you a few ideas anyway (you can pivot the resultset if indeed you needed the resultset to mimic the example output you supplied). Also note that we need to return the MaxDt and MinDt so we can limit the recursive function via the WHERE WorkDate <= MaxDate clause as a recursive CTE will not allow a sub query in the where clause.

Cheers,

Rob

|||

is the employee column needed

|||

hi,

Sql server has a limitation of 1024 columns

your requirements exceeds that limitations

regards

joey

here's a tests script. its not finished becaused i encountered the limitation

use northwind
create table dates
(
dateid int identity(1,1),
date1 datetime
)
go

declare @.mydate datetime
select @.mydate ='1/1/2000'
while @.mydate<>'1/31/2010'
Begin
insert dates(date1) values ( @.mydate)
select @.mydate=dateadd(day,1,@.mydate)
end
go

select * from dates
go

create table worksched(
idWorkSchedul int identity(1,1),
StartDate datetime,
EndDate datetime,
HoursWorked int
)
insert worksched(startdate,enddate,hoursworked)
values( '1/1/2000','1/1/2006',8)

declare @.startdate datetime
declare @.enddate datetime
select @.startdate='1/1/2000'
select @.enddate='1/1/2006'
select IDENTITY(int, 1,1) AS ID_Num,
date1 INTO #MYTEMP from dates where date1
between @.startdate and @.enddate

--drop table mytest
CREATE TABLE MYTEST1
(EMPLOYEE_ID VARCHAR(10)
)

DECLARE @.CMD nVARCHAR(200)
DECLARE @.CTR INT
DECLARE @.NAME VARCHAR(10)
SELECT @.CTR=0
WHILE @.CTR<>(SELECT MAX (ID_NUM) FROM #MYTEMP)
BEGIN
SELECT @.CTR=@.CTR+1
SELECT @.NAME = CONVERT( VARCHAR(10), DATE1 ,110) FROM #MYTEMP WHERE ID_NUM=@.CTR
select @.cmd ='ALTER TABLE MYTEST1 ADD ['+ @.NAME +'] INT'
--select @.cmd
exec sp_executesql @.cmd
END

select * from mytest1

|||

Here's an idea that may be of use, though I wouldn't really call it 'rows and columns', it's more of a play-with-strings for display purposes only. Each date will not be a separate column, it's just one long formatted string for the specific purpose.

Using the following example, to generate the days in the range is pretty straight forward with a number table.

create table #workSched
( id int not null, StartDate datetime not null, EndDate datetime not null, hrs int not null )

insert #workSched
select 1, '20060101', '20060331', 8 union all
select 2, '20060401', '20060831', 8

Assuming we have these two rows, then this query would produce a 'normal' resultset for each day between start and end
(the 'n - 1' is due to my numberstable starts with one, not zero)
Also, it's necessary to do this one workid at a time, it won't work for all in one go with just a straight query. However, it may be possible to package the idea into a UDF to get a simulation of a 'single-pass' (though performance may still be an issue)

select id,
dateadd(day, n -1, startDate) as workDay,
hrs
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

We could use this and build two strings, one with days and the other with the hours, keeping formatting in mind so that the two would be spaced accordingly.

declare @.workDay varchar(8000), @.hrs varchar(8000)
select @.workDay = '', @.hrs = ''

-- build the 'row' of dates
select @.workDay = @.workDay + convert(char(10), dateadd(day, n -1, startDate), 121) + ' '
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

-- buld the 'row' of hours, evenly spaced according to date
select @.hrs = @.hrs + convert(char(10), hrs) + ' '
from #workSched
join nums
on n -1 <= datediff(day, startdate, enddate)
and id = 1

-- display
select @.workDay
union all
select @.hrs

-- ....
2006-01-01 2006-01-02 2006-01-03 ....
8 8 8 ....

If you're looking for something for display or reporting use, then perhaps this idea could work for you..?
(it's not that pretty, but it works.. =;o)

/Kenneth

|||You can do the pivoting on the client side easily especially since you may have large number of date values. If you are building a report then it is a very trivial operation. So send the data as rows (dates as rows) and pivot on the client side. Solutions in TSQL will require dynamic SQL or fixed column names and other procedural techniques which will slow in terms of performance.

return

whats wrong with this SP? I want @.id to contain the row identity of the newly created row as a return value.

ALTER PROCEDURE setCountry
(
@.name varchar( 50 ) = NULL,
@.alt varchar( 24 ) = NULL,
@.code varchar( 3 ) = NULL,
@.id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )
@.id = @.@.identity
RETURN

INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )select@.id = @.@.identity

couple of things :
if you'd like to return this id back to asp.net you need to return it as an OUTPUT parameter..check out BOL for OUTPUT Parameters in stored procs..

also i'd recommend using SCOPE_IDENTITY() rather than @.@.IDENTITY. check out BOL again for the differences between them.

hth|||Thanks - but what is BOL?|||RETURN @.id

??

personally I'd do it this way

SET NOCOUNT ON
-- do insert
...
SELECT @.@.Identity|||BOL = Books On Line - best reference for sql server 2000. Free Download from microsoft.

hth|||Thanks! - I got the BOL acronym too - duh - Books On Line. I will try it now and actually may use SCOPE_IDENTITY() in place of @.@.identity.|||Atrax, I think the "return" method is better as it won't incur a result set. Although I'd use a OUTPUT param rather than return, I prefer to have that indicate some form of "state of the operation".|||Okay - now I can retrieve the result using ExecuteScalar - or DataReader or both?? Because when I run it in VS I dont see the results of the procedures. I mean it adds the row, but I don't see any output in the OUTPUT window.|||if you just need to return the ID you'd be better off using executescalar().

in vb.net

dim userID as integer
...
'open connection
...
userid=sqlcommand.ExecuteScalar()
...
'close connection

and use OUTPUT parameter to return the output form the stored proc...BOL had some samples no how to do it..

hth

Retriving data with same ID into single row

Hi,
I have a problem with retriving data from one table.
Table looks like this:
ID CONTACT EMPLOYEE_ID
And now, one employee can how more than one contact, like this
ID CONTACT EMPLOYEE_ID
15 e-mail 553
16 phone 553
..and so on.
How can I retrive this contacts, for same employee into single row, and divi
ded with 'coma'?
Now a can retrive them but I recieve multiple rows?
Thanks for helpHi
Do such reports on the client side.
It you want to do that by T-SQL be aware that it is not relyable solution
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"s3v3n" <s3v3n.1yuclt@.mail.codecomments.com> wrote in message
news:s3v3n.1yuclt@.mail.codecomments.com...
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ..and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||Check this good article with source code "Returning a Comma-Delimited List o
f
Related Records"
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||[Based on the 4guysrolla article .. ]
Create table empTest
(
[ID] int identity,
Contact varchar(100),
Employee_Id int
)
Go
Insert into empTest values ( 'abc@.email.com', 554)
Insert into empTest values ( '090909090', 554)
Go
Create function dbo.udf_GetEmpDetails(@.EmpID int)
Returns Varchar(1000) as
BEGIN
DECLARE @.Contact varchar(1000)
SELECT @.Contact = COALESCE(@.Contact + ', ', '') + s.Contact
FROM empTest s
WHERE s.EMPLOYEE_ID = @.EmpID
RETURN @.Contact
END
Go
Select distinct Employee_ID,
dbo.udf_GetEmpDetails(EMPLOYEE_ID) as ListOfContacts
From empTest f
Go
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:

> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>

Retrieving selected gridview column values for SQLDatasource asp:controlparameters

Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@." at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):

<

asp:GridViewID="gvLoadEditProductPrices"runat="server"AutoGenerateColumns="False"AllowSorting="True"DataSourceID="SqlDataSource1"DataKeyNames="ProductID"><Columns><asp:CommandFieldShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"HeaderStyle-BackColor="white"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="Product"HeaderText="Product"SortExpression="Product"ReadOnly="True"/><asp:BoundFieldDataField="ProductCat"HeaderText="ProductCat"SortExpression="ProductCat"ReadOnly="True"/><asp:BoundFieldDataField="VarRate"HeaderText="VarRate"SortExpression="VarRate"/><asp:BoundFieldDataField="loadid"HeaderText="loadid"InsertVisible="False"ReadOnly="True"SortExpression="loadid"/><asp:BoundFieldDataField="loadamount"HeaderText="loadamount"SortExpression="loadamount"ReadOnly="True"/><asp:BoundFieldDataField="ProductCol"HeaderText="ProductCol"SortExpression="ProductCol"ReadOnly="True"/><asp:BoundFieldDataField="PageID"HeaderText="PageID"SortExpression="PageID"ReadOnly="True"/></Columns></asp:GridView>

and the sqldatasource's info:

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>"ProviderName="System.Data.SqlClient"SelectCommand="spGetLoadEditProductPrices"SelectCommandType="StoredProcedure"UpdateCommand="spUpdateProductPrices"UpdateCommandType="StoredProcedure"><UpdateParameters><asp:ControlParameterName="ProductID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter><asp:ControlParameterName="LoadID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter><asp:ControlParameterName="PageID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter><asp:ControlParameterName="ProductCol"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter><asp:ControlParameterName="NewRate"Type="Double"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter></UpdateParameters><SelectParameters><asp:ControlParameterControlID="ddlEstLoadsPerAcre"Name="LoadID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="txtEditType"Name="PageName"PropertyName="Text"Type="String"/></SelectParameters></asp:SqlDataSource>

TIA,

John

Nevermind...after hours of testing many different combinations and scenarios, I found that I had to add each field that the control parameter needs to reference in the gridview to the "DataKeyNames" property in the gridview. But, any field I wanted to be updated thru the gridview edit had to use the PropertyName="SelectedValue" as opposed to the PropertyName="SelectedDataKey.Values('fieldname')". I also had to use single quotes for the field name.

John

Retrieving Identity Value after using DataAdapter.Update on DataTable

I am trying to retrieve the Identity Value on an Inserted Table Row.. However, I am inserting this row by creating a new DataRow, inserting it into the DataTable, and using SqlDataAdapter.Update (which would then auto-create the insertion string, insert, and then close the connection ).

I want to retrieve the Identity Value of what I just inserted.. I tried using a "SELECT @.@.IDENTITY", but that returned null.. I think its because @.@.IDENTITY only works for a connection session, and the SqlDataAdapter closes the connection after it inserts..

Any ideas / workarounds would be welcome! Thanks!

I posted this to the MySQL forum by accident too Sorry! :\Write your own insert function & attach it into SqlDataAdapter.InsertCommand property.|||How will writing my own insertion string allow me to retrieve the Identity value after its inserted?|||1st of all: try to use stored procedures. It's more robust & more elegant solution than inline SQL statements. Example statement (for Northwind database):

CREATE PROCEDURE CategoryInsert
(
 @.CategoryName nvarchar(15)
)
AS
 INSERT INTO Categories (CategoryName) VALUES (@.CategoryName )
 SELECT @.@.IDENTITY
<code>
And in code-behind:
<code>
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
conn.Open();
SqlCommand sqlInsert = new SqlCommand("CategoryInsert", conn);
sqlInsert.CommandType = CommandType.StoredProcedure;
SqlParameter paramCategoryName = new SqlParameter("@.CategoryName", SqlDbType.NVarChar, 15);
paramCategoryName.Value = "New category";
sqlInsert.Parameters.Add(paramCategoryName);
object o = sqlInsert.ExecuteScalar();
if(o != null)
lblFeedback.Text = o.ToString();
conn.Close();

Retrieving Identity after insert

Hey,

I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @.MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems.

Thanks,
Lang

hi,

can you try using @.@.Identity please.

morever please put some code what exactly you've done.

regards,

satish.

|||

Scope Identity is safer than @.@.Identity. @.@.Identity could possibly give you the wrong ID back if your table has triggers that also insert records.

Is @.MY_ID being returned as an output parameter or is this something you are simply doing in a stored procedure with no object/class interaction ?

Retrieving Data from next row

I have a little problem. An example of the data I work with is pasted below.
Qry_TestCtrack NAME STATUSTEXT ASSEMBLED LastAssembled
Bell B30 Dumper 1st Startup 03/03/2005 07:17:29 03/03/2005 07:17:29
Bell B30 Dumper Normal 03/03/2005 07:18:29 03/03/2005 07:18:29
Bell B30 Dumper Normal 03/03/2005 07:19:29 03/03/2005 07:19:29
Bell B30 Dumper Normal 03/03/2005 07:20:29 03/03/2005 07:20:29
Bell B30 Dumper Normal 03/03/2005 07:21:29 03/03/2005 07:21:29
Bell B30 Dumper Normal 03/03/2005 07:22:29 03/03/2005 07:22:29
Bell B30 Dumper Normal 03/03/2005 07:23:29 03/03/2005 07:23:29
Bell B30 Dumper Ignition off 03/03/2005 07:24:06 03/03/2005 07:24:06
The Query I'm making needs to give me the next row's ASSEMBLED as the
Previous row's LastAssembled so that I can calculate the time that was
carried out on that action. Anyone that can give me a helping hand? ^^
Thanks ^^Hi
Look at below example
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:10','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:10','20000610 20:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
20:23','20000610 20:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
20:25','20000610 21:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:00','20000610 21:15')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:16','20000610 21:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:25','20000610 21:35')
SELECT
DISTINCT StartTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon <= S1.StartTimeCon),S1.StartTimeCon),0) = 0
"Nightshade" <Nightshad3@.telkomsa.net> wrote in message
news:d18otn$1l6h$1@.newsreader02.ops.uunet.co.za...
> I have a little problem. An example of the data I work with is pasted
below.
> Qry_TestCtrack NAME STATUSTEXT ASSEMBLED LastAssembled
> Bell B30 Dumper 1st Startup 03/03/2005 07:17:29 03/03/2005 07:17:29
> Bell B30 Dumper Normal 03/03/2005 07:18:29 03/03/2005 07:18:29
> Bell B30 Dumper Normal 03/03/2005 07:19:29 03/03/2005 07:19:29
> Bell B30 Dumper Normal 03/03/2005 07:20:29 03/03/2005 07:20:29
> Bell B30 Dumper Normal 03/03/2005 07:21:29 03/03/2005 07:21:29
> Bell B30 Dumper Normal 03/03/2005 07:22:29 03/03/2005 07:22:29
> Bell B30 Dumper Normal 03/03/2005 07:23:29 03/03/2005 07:23:29
> Bell B30 Dumper Ignition off 03/03/2005 07:24:06 03/03/2005 07:24:06
>
> The Query I'm making needs to give me the next row's ASSEMBLED as the
> Previous row's LastAssembled so that I can calculate the time that was
> carried out on that action. Anyone that can give me a helping hand? ^^
> Thanks ^^
>|||Thank you for the response. I think me previous example was a bit confusing.
In my previous example I had the Column LastAssembled. That was my failure
to get it to show as I want it.
This is what I have to work with.
Qry_TestCtrack NAME STATUSTEXT ASSEMBLED
Bell B30 Dumper 1st Startup 03/03/2005 07:17:29
Bell B30 Dumper Normal 03/03/2005 07:18:29
Bell B30 Dumper Normal 03/03/2005 07:19:29
Bell B30 Dumper Normal 03/03/2005 07:20:29
Bell B30 Dumper Normal 03/03/2005 07:21:29
Bell B30 Dumper Normal 03/03/2005 07:22:29
Bell B30 Dumper Normal 03/03/2005 07:23:29
Bell B30 Dumper Ignition off 03/03/2005 07:24:06
Thus There's only 1 timestamp per event. But the one timestamp to the next
is the lenght of the action. Thus what I'm tyring, is to create another
tale, or just a function, wich would either place the 2nd line's Assembled
next to the 1s't lines assembled, so I can work out the difference between
them, or even directly subtract the first line from the 2nd line. End of the
day I need to caculate how much time was spent on each action, so I can just
add the totals of the actions.
Thanks again ^^
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMNawEgKFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi
> Look at below example
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:10','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:10','20000610 20:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 20:23','20000610 20:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 20:25','20000610 21:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:00','20000610 21:15')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:16','20000610 21:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:25','20000610 21:35')
>
> SELECT
> DISTINCT StartTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon <= S1.StartTimeCon),S1.StartTimeCon),0) = 0
>
>
> "Nightshade" <Nightshad3@.telkomsa.net> wrote in message
> news:d18otn$1l6h$1@.newsreader02.ops.uunet.co.za...
> below.
>|||Hi
Does that mean the query I posted does not work?
Please post DDL + expected result.
"Nightshade" <Nightshad3@.telkomsa.net> wrote in message
news:d18r49$1lbg$1@.newsreader02.ops.uunet.co.za...
> Thank you for the response. I think me previous example was a bit
confusing.
> In my previous example I had the Column LastAssembled. That was my failure
> to get it to show as I want it.
> This is what I have to work with.
> Qry_TestCtrack NAME STATUSTEXT ASSEMBLED
> Bell B30 Dumper 1st Startup 03/03/2005 07:17:29
> Bell B30 Dumper Normal 03/03/2005 07:18:29
> Bell B30 Dumper Normal 03/03/2005 07:19:29
> Bell B30 Dumper Normal 03/03/2005 07:20:29
> Bell B30 Dumper Normal 03/03/2005 07:21:29
> Bell B30 Dumper Normal 03/03/2005 07:22:29
> Bell B30 Dumper Normal 03/03/2005 07:23:29
> Bell B30 Dumper Ignition off 03/03/2005 07:24:06
>
> Thus There's only 1 timestamp per event. But the one timestamp to the next
> is the lenght of the action. Thus what I'm tyring, is to create another
> tale, or just a function, wich would either place the 2nd line's Assembled
> next to the 1s't lines assembled, so I can work out the difference between
> them, or even directly subtract the first line from the 2nd line. End of
the
> day I need to caculate how much time was spent on each action, so I can
just
> add the totals of the actions.
> Thanks again ^^
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eMNawEgKFHA.3928@.TK2MSFTNGP09.phx.gbl...
0
07:17:29
>

Retrieving Column Name and Value for each row using GetSchemaTable

I know I can iterate through the schema table using the following.

Can I grab the actual value of each field while looping through the rows and columns?

schemaTable = reader.GetSchemaTable();

foreach (DataRow myDataRow in schemaTable.Rows)

{

foreach (DataColumn myDataColumn in schemaTable.Columns)

{

Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn.ColumnName].ToString() );

}

}

An example is if one of the columns in the schema is called Firstname I would like to return:

Row 1

column name = Firstname

value= Bob

column name = Lastname

value= Smith

Row 2

column name = Firstname

value= Greg

column name = Lastname

value= Jones

What about:

int i=0;

foreach (DataRow myDataRow in schemaTable.Rows)

{

Console.WriteLine(string.Format("Row {0}", (string)(i++));

foreach (DataColumn myDataColumn in schemaTable.Columns)

{

Console.WriteLine(string.Format("Column Name = {0}",myDataColumn.ColumnName);

Console.WriteLine(string.Format("value = {0}" , myDataRow[myDataColumn.ColumnName].ToString());

}

}

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks this works for me.

retrieving BCP number of rows process

Thank you in advance
I am trying to retrieve the number of row that BCP process upon
completion,also how to find out what errors have occured when using the BCP
command.
thank you
tomasHi Tomas,
BCP is designed in a way that you cannot have all the functions in it. but
then they work pretty fast.
Since its not mentioned in your post, I am gonna assume that you are doing a
bcp OUT to a text file.
you can specify the maximum number of acceptable error in your bcp command.
In addition to that if you are coding all this in QA using xp_cmdshell you
can also create say something like balancing file to report the count(*) of
how many records you bcp.
It would be be interesting to know (from others) if you can actually count
the number of rows returned in bcp. As far as i know, there is no direct way
of doing it.
Hope this helps
ABhishek
"Tomas" wrote:

> Thank you in advance
> I am trying to retrieve the number of row that BCP process upon
> completion,also how to find out what errors have occured when using the BC
P
> command.
> thank you
> tomas|||Hi tomas,
In addition to what i wrote earlier today, I would suggest you to use the
following
declare @.someCommand
set @.someCommand = 'bcp "Select blah blah" queryout "c:/whatever place"
-U<userID> -P<pass> -c
exec master..xp_cmdshell @.somecommand
When u run it thru QA you will get some output as
"Starting copy....
XXXX rows sucessfully bulk-copies to host file, Total received: XXXX
XXXX rows copied
Network Packet size (byted) : xxxxx
clock time(ms.) total xxxx
"
Then going forward you can also specify the maximum acceptable errors in
your bcp command line.
Does it helps ' Do let me know if there was something else that you wanted
to acheive.
Abhishek
"Tomas" wrote:

> Thank you in advance
> I am trying to retrieve the number of row that BCP process upon
> completion,also how to find out what errors have occured when using the BC
P
> command.
> thank you
> tomas

Retrieve the Identifier of the newly added row

Howdie y'all,

I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one...

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32)
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
GO

I actually would like to get value of the UserId column for the newly added record.

Can anyone of you folks help me with this?

Cheers,

Wes

UserID being a primary key and identity column? For example modifying the proc as follows

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32),
@.NewID int OUTPUT
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
SET @.NewID = SCOPE_IDENTITY()
GO

This way you can get it via output parameter after the query is executed.

|||

Thank for this fast answer! It works like a charm now!

Cheers,

Wes

|||And to tag onto Teemu's response...
You might find a function called @.@.IDENTITY that seems to return thesame kind of information. Don't be tempted to use it, however. Itreturns the last identity value added with a wider scope thanSCOPE_IDENTIY(). It's appropriate only in very rare situations, andnever in the scenario you are using.
Don
|||

Funny you mention the @.@.Identity... I first tried that. I found it on msdn but for some reason I didn't trust it so I decided to ask the question to people who know what they are doing instead of just copy paste the code.

I'll start my mcsd course soon now and will follow the path of sql-server. I think I'll start a website where people can get extensive info on how to create and enter stored procedures on sql-server to become an MVP. I've found it hard to get some good information. I see a lot of differences in approach and syntax( should I use OUTPUT or just OUT or are they the same?) and no real good tutorial on where to start and how to learn with explanation.

Thanks again you both,

Wesley

retrieve only 1 row for each ID (was "Help on SQL! Urgent..")

i have a table called tblpictures which look something like this..

filename|ID
----
1 |p1
2 |p1
3 |p2
4 |p2
5 |p3

is there a way to retrieve only 1 row for each ID? how will the select statement looks like?? please help me..Which row would you want?

If you just want a distinct list of IDs, then this will do:

select distinct ID from YourTable|||erm no i want to show 1 filename and 1 ID for each ID|||You could probably get away with :

select max(filename), id from tblPictures group by id

if this is what you are looking for ...

:cool:

Retrieve One Row at a time

Hi,

I am going to be difficult here... How do I retrieve one row at a
time from a table without using a cursor?

For example, I have a table with 100 rows. I want to retrieve the
data from row 1, do some stuff to it and send it on to anther table,
then I want to grab
row 2, do some stuff to it and send it to another table.

Here is how I am envisioning it:

WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
rowcount)
BEGIN
SELECT * FROM [TABLE] BUT ONLY ONE ROW
... MANIPULATE THE DATA
INSERT into another table
END

Other notes, I am using SQL Sever 2000...
Thanks and in advance and as always the help is greatly appreciated.

Regards,

CLRI don't know why you don't want to use a cursor which is probably the most
suitable means to solve your problem. But anyway, you have some other
options like these:

1. Add a flag to your table. After proccessing each record set the flag and
select the next nonprocessed record (using select top 1).

2. Copy all the records you want into a temporary table and again using
selectp top 1 read them one by one and delete them after processing.

3. Use a temporary table as a list of processed records, after processing
each record add its key to this list and select next record where its key
does not belong to this list.

If you give us more information about what you are exactly looking for and
what your problem is, you'll have a better chance to get the solution.

Shervin

"Chris" <chris@.dagran.com> wrote in message
news:736fadb1.0309301643.572f3730@.posting.google.c om...
> Hi,
> I am going to be difficult here... How do I retrieve one row at a
> time from a table without using a cursor?
> For example, I have a table with 100 rows. I want to retrieve the
> data from row 1, do some stuff to it and send it on to anther table,
> then I want to grab
> row 2, do some stuff to it and send it to another table.
> Here is how I am envisioning it:
> WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
> rowcount)
> BEGIN
> SELECT * FROM [TABLE] BUT ONLY ONE ROW
> ... MANIPULATE THE DATA
> INSERT into another table
> END
> Other notes, I am using SQL Sever 2000...
> Thanks and in advance and as always the help is greatly appreciated.
> Regards,
> CLR|||Chris (chris@.dagran.com) writes:
> For example, I have a table with 100 rows. I want to retrieve the
> data from row 1, do some stuff to it and send it on to anther table,
> then I want to grab
> row 2, do some stuff to it and send it to another table.
> Here is how I am envisioning it:
> WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
> rowcount)
> BEGIN
> SELECT * FROM [TABLE] BUT ONLY ONE ROW
> ... MANIPULATE THE DATA
> INSERT into another table
> END
> Other notes, I am using SQL Sever 2000...
> Thanks and in advance and as always the help is greatly appreciated.

SELECT TOP 1 @.key = keycol, @.var1 = col1, @.var2 = col2'
FROM tbl
WHERE keycol > @.key
ORDER BY keycol

If you have a multi-column, you can still do this, but logic becomes
hairier.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I want to thank you both for your answers they have helped
tremendously. We are going to use a cursor for our problem as well, I
just wanted another way of handling what we are triyng to accomplish.
We have a table with over a million rows, which from one row we will
query about 5 other tables to extract more information which will be
sent to a new table, then we grab the next row and so on and so forth.
We want to try using a cursor and anther method to see which way
would be more CPU friendly. I feel it doesn't really matter which way
we go, they both will take over my computer. Thanks though for your
responses, it has helped us out a lot!

Regards,

CLR|||Chris (chris@.dagran.com) writes:
> I want to thank you both for your answers they have helped
> tremendously. We are going to use a cursor for our problem as well, I
> just wanted another way of handling what we are triyng to accomplish.
> We have a table with over a million rows, which from one row we will
> query about 5 other tables to extract more information which will be
> sent to a new table, then we grab the next row and so on and so forth.

A million rows iteratively? That could take a couple of days! Sometimes
this can be justified, if it's sort of a one time operation. (Actually,
I was recently involved in writing a task that took 3 days to complete.)

But if you can find a set-based operation, you can win lots of
performance.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Retrieve id of newly added row in stored procedure

Hi,

I am trying to return the id of the newly added row after the insertstatement in my stored procedure. I was told to "RETURNSCOPE_IDENTITY()", which i did. The problem is i do not know how to getthis value in asp.net?

I added this code below in my business layer. myDAL refers to an objectof my DAL layer. In my DAL layer, i have a addPara() method which willhelp me dynamically add as many parameters. Someone please advise me onwhat to do. Thanks

myDAL.addPara("@.RETURN_VALUE", , SqlDbType.Int, , ParameterDirection.ReturnValue)

Stored Procedure:
CREATE PROCEDURE [ADDPROMOTION]
(
@.PROMOTIONNAME VARCHAR (100),
@.PROMOSTARTDATE DATETIME,
@.PROMOENDDATE DATETIME,
@.DISCOUNTRATE INT,
@.PROMODESC VARCHAR(100)

)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@.PROMOTIONNAME, @.DISCOUNTRATE, @.PROMOSTARTDATE, @.PROMOENDDATE, @.PROMODESC)

-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()
GOTry @.@.identity

Refer :http://tinyurl.com/5sdht|||

SCOPE_IDENTITY() is definetely a more accurate function than @.@.IDENTITY. Check out Books On Line for more info. To get your code working, create an OUTPUT parameter in the stored proc:

CREATE PROCEDURE [ADDPROMOTION]
(
@.PROMOTIONNAME VARCHAR (100),
@.PROMOSTARTDATE DATETIME,
@.PROMOENDDATE DATETIME,
@.DISCOUNTRATE INT,
@.PROMODESC VARCHAR(100)
@.MSTRPROMID INT OUTPUT
)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@.PROMOTIONNAME, @.DISCOUNTRATE, @.PROMOSTARTDATE, @.PROMOENDDATE, @.PROMODESC)
SELECT @.MSTRPROMID = SCOPE_IDENTITY()
-- Now return the InventoryID of the newly inserted record

GO

Now change your front end code to retrieve the ID

check the (2) section ofthis article for sample code.

|||

Look at the @.RETURN_VALUE parameter after you call the stored procedure.

Normally (Without any Dal stuff):

debug.print cmd.parameters("@.RETURN_VALUE).value

Where cmd is your sqlcommand/oledbcommand etc

Retrieve first row only in many-to-many relationship

I have a db with three tables - books, sections, and a joining table.
The normal way of getting a many to many relationship (i.e. one book
may belong to many sections, and one section may contain many books)

I want to extract the data with a single row for each book so that I
only retrieve the first section description for any book. (e.g. title,
author, section, description)

Structure as follows:

tbl_book
book_id, title, author, description etc...

tbl_section
section_id, section_desc

tbl_book_section
book_id, section_id

DBA is away and I can't figure this out at all...any help gratefully
received.Try this. I'm assuming that by "first section" you mean the lowest numbered
section_id.

SELECT B1.book_id, B1.title, B1.author, B1.description,
S2.section_id, S2.section_desc
FROM tbl_book AS B1
JOIN
(SELECT book_id, MIN(section_id) AS section_id
FROM tbl_book_section
GROUP BY book_id) AS S1
ON B1.book_id = S1.book_id
JOIN tbl_section AS S2
ON S1.section_id = S2.section_id

--
David Portas
----
Please reply only to the newsgroup
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Since SQL is a set-oriented language, there is no such concept as a
first row in a table. The next basic principle is that all
relationships are shown as values in a column. Therefore, you must
have a section number of some kind in the DDL that you did not post
for this to make sense.

Book_id ought to be an ISBN, but we have no idea what section_id is
like and if it has an ordering.

When the DBA gets back, ask him to read and use ISO-11179 naming
standards. What he ias given you says that you only have one book
about furniture, specifically tables.|||David

That did the trick thanks.

Gareth

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<ceydnc72CbDgi5XdRVn-gQ@.giganews.com>...
> Try this. I'm assuming that by "first section" you mean the lowest numbered
> section_id.
> SELECT B1.book_id, B1.title, B1.author, B1.description,
> S2.section_id, S2.section_desc
> FROM tbl_book AS B1
> JOIN
> (SELECT book_id, MIN(section_id) AS section_id
> FROM tbl_book_section
> GROUP BY book_id) AS S1
> ON B1.book_id = S1.book_id
> JOIN tbl_section AS S2
> ON S1.section_id = S2.section_id|||Joe

I don't know what you mean by DDL, but the other guy who posted a
reply clearly understood what I was asking about.

The database in question existed before the DBA (female by the way)
joined the company and the reason I want the query is to extract the
data for a new ecommerce system.

Naming conventions are indeed a good thing...

joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0401161012.58f5d22a@.posting.google.com>...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications.
> Since SQL is a set-oriented language, there is no such concept as a
> first row in a table. The next basic principle is that all
> relationships are shown as values in a column. Therefore, you must
> have a section number of some kind in the DDL that you did not post
> for this to make sense.
> Book_id ought to be an ISBN, but we have no idea what section_id is
> like and if it has an ordering.
> When the DBA gets back, ask him to read and use ISO-11179 naming
> standards. What he ias given you says that you only have one book
> about furniture, specifically tables.|||> I don't know what you mean by DDL, but the other guy who posted a
> reply clearly understood what I was asking about.

I guessed what you wanted but it is useful to post DDL for questions like
this:
www.aspfaq.com/5006

--
David Portas
----
Please reply only to the newsgroup
--|||"Gareth" <gareth900@.hotmail.com> wrote in message
news:c105346f.0401170201.51b5e4b1@.posting.google.c om...
> Joe
> I don't know what you mean by DDL, but the other guy who posted a
> reply clearly understood what I was asking about.

DDL - Data Description Language.

Basically the SQL commands to create the tables with keys, constraints, etc.
that you want. This allows folks answering your question to create a test
setup on their own servers. Generally you'll get answers that have been
fully tested that way.

Joe Celko is a bit of curmudgeon, but he's also arguably one of the better
experts on the SQL language out there. He has several books to his name and
knows his stuff. And yes, he's opinionated. :-)

> The database in question existed before the DBA (female by the way)
> joined the company and the reason I want the query is to extract the
> data for a new ecommerce system.
> Naming conventions are indeed a good thing...|||Greg - DDL - makes sense now...

In future I'll do this - didn't realise the conventions in the group.

Thanks

Gareth|||>> I don't know what you mean by DDL .. <<

Data Definition Language. SQL has three sublanguages and this is one of
them. It is also the minimal netiquette in SQL newsgroups.

>> the other guy who posted a reply clearly understood what I was asking
about. <<

No, he guessed lucky. What if section_id had been a title, like
"Introduction" or "preamble" which was not in alphabetic order?

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> Joe Celko is a bit of curmudgeon, ... <<

Hey, if I had any friends, they'd tell you what a great guy I am!|||"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0401191734.1252bc8e@.posting.google.c om...
> >> Joe Celko is a bit of curmudgeon, ... <<
> Hey, if I had any friends, they'd tell you what a great guy I am!

Hey, you say that like I was saying something that wasn't nice. :-)

retrieve data from the gridview

i need to retrieve data from a particular field of the gridview according to the selected row to stored it into session ....

what i had done so far as following:

Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
DetailsView1.PageIndex = GridView1.SelectedIndex
Session.Add("receiverName", GridView1.??)

End Sub

??? is the part i am not sure what to code... i tried different method by seems to have problem of convertion.

You can access the seleced row of gridview by using the SelectedRows property, see:

http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridview.selectedrows(d=ide).aspx