retrieving selected join records

Hi,
I have the folowing 3 (SS2005) tables:

CREATE TABLE [dbo].[tblSubscription](
[SubscriptionID] [int] IDENTITY(1000000,1) NOT NULL,
[SubscriberID] [int] NOT NULL,
[Status] [int] NOT NULL,
[JournalID] [int] NOT NULL,

CREATE TABLE [dbo].[tblTransaction](
[TransactionID] [bigint] IDENTITY(100000000,1) NOT NULL,
[TransactionTypeID] [int] NOT NULL,
[SubscriptionID] [int] NOT NULL,
[Created] [datetime] NOT NULL,

CREATE TABLE [dbo].[tblMailing](
[MialingID] [bigint] IDENTITY(1000000000,1) NOT NULL,
[SubscriptionID] [int] NOT NULL,
[MailTypeID] [int] NOT NULL,
[MailDate] [datetime] NOT NULL

So for each subscription there can be 1 or more transactions and 0 or
more mailings, and the mailings are not necassarily related to the
transactions. What I am having difficulty doing is this:

I wish to select tblMailing.MailingID, tblMailing.MailDate,
tblMailing.SubscriptionID (or tblSubscription.SubscriptionID),
tblSubscription.SubscriberID, tblSubscription.Status,
tblTransaction.TransactionID, tblTransaction.Created, but I only wish
to retrieve rows from the transaction table where
tblTransaction.Created is the latest dated transaction for that
subscription.
I.E. (maybe this makes more sense..:) I wish to select all rows from
tblMailing along with each mailing's relevent subscription details,
including details of the LATEST TRANSACTION for each of those
subscriptions.

I am currently working along the lines of MAX(tblTransaction.Created)
and possibly GROUP BY in a subquery, but cannot quite figure out the
logic.

Any help appreciated.

Thanks, KoG

King:

Are you wanting the subscription record to appear in the report even if there are as of yet no mailings? That is, do I need to use an outer join or an inner join? I am for the moment assuming that you want the inner join.

Dave

|||

set nocount on
declare @.tblSubscription table
( subscriptionID integer not null,
subscriberID integer not null,
status integer not null,
journalID integer not null,
primary key (subscriptionID)
)

declare @.tblTransaction table
( transactionID integer not null,
transactionTypeId integer not null,
subscriptionID integer not null,
created datetime not null
primary key (transactionID),
unique (subscriptionID, transactionID)
)

declare @.tblMailing table
( mailingId bigint not null,
subscriptionID integer not null,
mailTypeId integer not null,
mailDate datetime not null,
primary key (mailingID),
unique (subscriptionId, mailingId)
)

insert into @.tblSubscription values (1000001, 1000001, 1, 1)
insert into @.tblSubscription values (1000002, 1000002, 1, 1)
insert into @.tblSubscription values (1000003, 1000001, 2, 1)
--select * from @.tblSubscription

insert into @.tblTransaction values (1000001, 1, 1000001, '3/15/6' )
insert into @.tblTransaction values (1000002, 2, 1000001, '4/7/6' )
insert into @.tblTransaction values (1000003, 1, 1000002, '4/3/6' )
insert into @.tblTransaction values (1000004, 1, 1000003, '5/8/6' )
insert into @.tblTransaction values (1000005, 2, 1000003, '10/14/6')
insert into @.tblTransaction values (1000006, 4, 1000003, '9/1/6' )
--select * from @.tblTransaction

insert into @.tblMailing values (1000001, 1000001, 1, '3/15/6' )
insert into @.tblMailing values (1000002, 1000001, 2, '4/4/6' )
insert into @.tblMailing values (1000003, 1000003, 1, '5/9/6' )
insert into @.tblMailing values (1000004, 1000003, 3, '9/3/6' )
--select * from @.tblMailing

--set statistics io on
select m.mailingId,
m.MailDate,
s.subscriptionId,
s.subscriberId,
s.Status,
t.TransactionId,
t.created
from @.tblSubscription s
inner join @.tblMailing m
on s.subscriptionId = m.subscriptionId
inner join
( select q.subscriptionId,
q.transactionId,
row_number () over
( partition by q.subscriptionId
order by q.created desc, q.transactionId desc
) as Seq,
created
from @.tblTransaction q
) t
on t.subscriptionId = s.subscriptionId
and seq = 1
--set statistics io off

-- -- Sample Output: -

-- mailingId MailDate subscriptionId subscriberId Status TransactionId created
-- -- -- -- -- - --
-- 1000001 2006-03-15 00:00:00.000 1000001 1000001 1 1000002 2006-04-07 00:00:00.000
-- 1000002 2006-04-04 00:00:00.000 1000001 1000001 1 1000002 2006-04-07 00:00:00.000
-- 1000003 2006-05-09 00:00:00.000 1000003 1000001 2 1000005 2006-10-14 00:00:00.000
-- 1000004 2006-09-03 00:00:00.000 1000003 1000001 2 1000005 2006-10-14 00:00:00.000

|||Hi Dave,

I only wish to select subscription rows where there is a mailing associated with the subscription. In fact, the driver of the query should be the mailings table, so for each row in tblMailing get the relevent subscription (& latest transaction) data. That means there may be several rows where the data in the subscription-related columns (& hence transaction related ones too) are the same, as a subscription may have several mailings.

I assume that means the inner join is required..

Thanks, Nick
sql

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 an image from SQL, test for null

I have an employee directory application that displays employees in a gridview. When a record is selected, a new page opens and displays all info about the employee, including their photo. I have the code working that displays the photos, however, when no photo is present an exception is thrown that "Unable to cast object of type System.DbNull to System.Byte[]". I'm not sure how to test for no photo before trying to write it out.

My code is as follows (with no error trapping):

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load,Me.Load

Dim tempAsString

Dim connPhotoAs System.Data.SqlClient.SqlConnection

Dim connstringAsString

connstring = Web.Configuration.WebConfigurationManager.ConnectionStrings("connPhoto").ConnectionString

connPhoto =New System.Data.SqlClient.SqlConnection(connstring)

temp = Request.QueryString("id")

Dim SqlSelectCommand2As System.Data.SqlClient.SqlCommand

Dim sqlstringAsString

sqlstring ="Select * from dbo.PhotoDir WHERE (CMS_ID = " + temp +")"

SqlSelectCommand2 =New System.Data.SqlClient.SqlCommand(sqlstring, connPhoto)

Try

connPhoto.Open()

Dim myDataReaderAs System.Data.SqlClient.SqlDataReader

myDataReader = SqlSelectCommand2.ExecuteReader

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

connPhoto.Close()

Catch SQLexecAs System.Data.SqlClient.SqlException

Response.Write("Read Failed : " & SQLexec.ToString())

EndTry

EndSub

EndClass

If you could point me in the right direction I would appreciate it.

lwhalen618:

when no photo is present an exception isthrown that "Unable to cast object of type System.DbNull toSystem.Byte[]

lwhalen618:

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

did you try to check for nulls ??

DoWhile (myDataReader.Read())
if Not IsDBNull(myDataReader.Item("ImportedPhoto")) then
Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))
End if
Loop

hope it works... pls let me know

Good Luck./.

|||

I did try testing for null but was doing it incorrectly. Your code worked fine. Thanks!

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