my code is
protected void Page_Load(object sender, EventArgs e)
{
objMember.orderpkstring = Convert.ToString( Session["orderpkstring"]);
repeater.ItemTemplate = LoadTemplate("~/View/frmOrderJournal.ascx");
DataSet ds;
ds = objMember.GetJournalforSub(objMember);
repeater.DataSource = null;
if (ds.Tables[0].Rows.Count > 0)
{
repeater.DataSource = ds;
repeater.DataBind();
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "Javascript", "javascript:window.close()", true);
Response.Redirect("frmManageRemitance.aspx");
}
}
and
public DataSet GetJournalforSub(AbstractLayer.baseMember objMember)
{
DataSet dsState;
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@journalstring", objMember.orderpkstring);
dsState = SqlHelper.ExecuteDataset(m_Connection_String, CommandType.StoredProcedure, "proGetJournalForSub", param);
return dsState;
}
and my procedure is
ALTER procedure [dbo].[proGetJournalForSub]
@journalstring xml
as
DECLARE @hDoc int
begin
EXEC sp_xml_preparedocument @hDoc OUTPUT,
@journalstring int
SELECT distinct dbo.Tbl_ILSM_JournalReg.PK, dbo.Tbl_ILSM_JournalReg.JournalName, dbo.Invoice_Table.JrnlYear, dbo.Invoice_Table.JrnlCur,
dbo.Invoice_Table.price, dbo.Invoice_Table.postage, dbo.Invoice_Table.DisOnClient, dbo.Invoice_Table.DisOnTitle, dbo.Invoice_Table.FinalPrice,
dbo.Invoice_Table.Buyr_Id, dbo.Invoice_Table.pk AS invpk, dbo.Invoice_Table.JournalType, dbo.Tbl_ILSM_JournalReg.PublisherFK,
dbo.Tbl_ILSM_Publisher.Discount, dbo.Tbl_ILSM_Publisher.FixDiscount,Tbl_ILSM_Publisher.PublisherName,
Invoice_Table.Invoice_No, C.CustomerName,q.InvoiceJrnlFK,q.SubOrderId,q.Currency,q.PCurrency,
q.ConversonType,
convert(varchar(10), q.ConversonDate,103) as ConversonDate ,Invoice_Table.YearSubscr,q.CurrencyID,q.RemettanceNO,
case
when Invoice_Table.JournalType='PR' then
JY.PrIntPrice
when Invoice_Table.JournalType='PO' then
JY.IntPrice
when Invoice_Table.JournalType='ON' then
JY.OnIntPrice
end
as JrnlPrice,
case
when Invoice_Table.JournalType='PR' then
JY.PrIntPostage
when Invoice_Table.JournalType='PO' then
JY.IntPostage
when Invoice_Table.JournalType='ON' then
''
end
as JrnlPostage
FROM dbo.Invoice_Table INNER JOIN
dbo.Tbl_ILSM_JournalReg ON dbo.Invoice_Table.JournalFK = dbo.Tbl_ILSM_JournalReg.PK INNER JOIN
dbo.Tbl_ILSM_Publisher ON dbo.Tbl_ILSM_JournalReg.PublisherFK = dbo.Tbl_ILSM_Publisher.PK
inner join tbl_ilsm_customer C on C.pk=Invoice_Table.Buyr_Id
inner join tbl_ilsm_jrnl_yrrt JY on JY.journalfk=Tbl_ILSM_JournalReg.pk
and JY.[year]=Invoice_Table.JrnlYear
inner join
(SELECT InvoiceJrnlFK,JournalFK,CustomerPK,SubOrderId,PCurrency,Currency,ConversonType,ConversonDate,CurrencyID,
RemettanceNO
FROM OPENXML(@hdoc, 'DocumentElement/dtJournal',3)
WITH (InvoiceJrnlFK int,
JournalFK int,
CustomerPK int,
SubOrderId varchar(100),
PCurrency varchar(10),
Currency varchar(10),
ConversonType varchar(10),
ConversonDate varchar(30),
CurrencyID int,
RemettanceNO varchar(50)
)) q
on q.CustomerPK=Invoice_Table.Buyr_Id and q.JournalFK=Invoice_Table.JournalFK and
q.InvoiceJrnlFK=Invoice_Table.pk
WHERE (dbo.Invoice_Table.statusofjournal = 'suborder')
end