Pb To Retreive Max Value With Aggregate Subquery
Jul 20, 2005
I would like to know which products are my best sells by sellers, but i
would like to retreive this info by product id, seller id and the total
amount of sells for this product.
My Sells table is :
Seller_idProduct_idTotaldate_s
1 2 1020/05/04
2 4 1512/05/04
3 5 2206/06/04
1 5 1807/06/04
4 8 1213/05/04
7 2 1119/05/04
3 4 1421/05/04
2 4 1418/05/04
1 5 1817/06/04
2 5 5008/05/04
etc....
I know how to retreive the total sells by product id and seller id
SELECT Seller_id, Product_id, SUM(Total) AS total
FROM Sells
WHERE date_s > '01/05/04'
GROUP BY Seller_id,Product_id order by Seller_id
Seller_idProduct_idTotal
1 5 36
1 2 10
2 5 50
2 4 29
3 5 22
3 4 14
I would like retreive only the max of total, and the Seller id and
product id, like this :
Seller_idProduct_idTotal
1 5 36
2 5 50
3 5 22
How can i do without using a temp table ?
Thanks for your help.
View 3 Replies
ADVERTISEMENT
Oct 19, 2007
Can any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View 8 Replies
View Related
Apr 25, 2008
I want to return only the sum total of each of the following two columns generated by this query, but when I wrap them in SUM() I get an error stating that I can't use an aggregate function on an aggregate or subquery.
Is there another approach that I might take to sum these?
SELECT CASE soitem.fmultiple
WHEN 1 then
(SELECT funetprice FROM sorels
WHERE (sorels.fsono = shmast.fcsono)
AND sorels.frelease
= SUBSTRING(shitem.fsokey,10,3)
AND sorels.fenumber
= shitem.fenumber) * shitem.fshipqty
ELSE
(SELECT top 1 funetprice FROM sorels
WHERE (sorels.fsono = shmast.fcsono)
AND sorels.finumber
= soitem.finumber) * shitem.fshipqty
END as ExtPrice,
CASE CAST((shitem.fshipqty) as int)
% nullif(CAST(inmast.fnusrqty1 as int),0)
WHEN 0 then
(CAST((shitem.fshipqty) as int) /
nullif(CAST(inmast.fnusrqty1 as int),0))
ELSE
(CAST((shitem.fshipqty) as int) /
nullif(CAST(inmast.fnusrqty1 as int),0)) + 1
END as BoxCount
FROM shmast INNER JOIN shitem ON shmast.fshipno = shitem.fshipno
INNER JOIN soitem ON (soitem.fsono = shmast.fcsono)
AND (Convert(Int,soitem.finumber) = Convert(Int,SUBSTRING(shitem.fsokey,8,10)) / 1000)
LEFT JOIN somast ON (shmast.fcsono = somast.fsono)
LEFT JOIN inmast ON (soitem.fpartno = inmast.fpartno)
WHERE (shmast.fbl_lading='00000000000000003784')
AND (shitem.fshipqty > 0)
View 6 Replies
View Related
Jan 20, 2006
I am working on a view in SQL Server 2005.
I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but
SELECT userid, MAX
((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions AS OD
HAVING (sessionId = O.sessionId))) AS MAXSESSION
FROM dbo.Sessions AS O
GROUP BY userid
but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.'
Is there an elegant solution for this?
Thanks,
View 1 Replies
View Related
Mar 4, 2014
So I wrote this code but keep getting an error.
List all resellers whose annual sales exceed the average annual sales for resellers whose Business Type is "Specialty Bike Shop". Show Business type, Reseller Name, and annual sales. Use appropriate subqueries.
-- 396 Rows
SELECT R.ResellerName
FROM dbo.DimReseller AS R
WHERE
R.ResellerKey IN (
SELECT F.ResellerKey
FROM dbo.FactResellerSales AS F
WHERE R.ResellerKey = F.ResellerKey AND R.BusinessType = 'Specialty Bike Shop'
GROUP BY F.ResellerKey
HAVING R.AnnualSales > AVG(R.AnnualSales)
)
View 15 Replies
View Related
Oct 22, 2015
(select SUM(sales.Total) from sales where StudentHist.Curdate = max(sales.curdate)) AS 'Balance'Iam trying to write a subquery to calculate the total amount of sales until the Curdate in studenthist equals the Curdate in sales table..how to write this query??
View 11 Replies
View Related
Aug 6, 2015
SSMS does not like mine! THis is the error that I receive:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
And this is my syntax:
Select
employeeID
,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) +
COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End)
FROM production
GROUP BY employeeID
ORDER BY employeeID
View 6 Replies
View Related
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
Sep 22, 2015
I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, BillableLastYear is a number, aggregated (sum) from child units.
The second factor, BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
,[UnitID]
,[UnitName]
,[Unit2Name]
,[Unit3Name]
,[Unit4Name]
[Code] .....
View 6 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related
Nov 3, 2007
I'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table.
Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form.
TIA
Steve
View 7 Replies
View Related
Oct 16, 2006
hey guys, i getting a minor problem here. Hope u guys may help me =) I would like to count all the records from a table ' member ' and store the value inside a variable ' iCnt ' i wrote like tis:cmd.commandtext = " select count(*) as total from member" dim iCnt = cmd.ExecuteScalar Hmm.. I am wondering whether my code is correct, it seem like cant work =(Any help is appreciated, thanks u guys.
View 1 Replies
View Related
Jan 24, 2007
if a db field "mynum" of type int contains for example the number 543.Is there a way I can update this field without first selecting the value?so for example set the the current value to +1
View 1 Replies
View Related
Sep 16, 2004
Is there a chance to retreive let's say rows positioned from 30-70 if a query produced result that contains 100 rows?
View 3 Replies
View Related
Mar 30, 2001
Hi,Is it possible to retrieve a single table from a backup in SQL 7.0?
TIA
Jay
View 1 Replies
View Related
Nov 22, 2005
How do i retrieve 0 for null value in the following query
select customerid, count(*) from calls_new_today c, customerdnis cd where c.cpn=cd.dnis and cd.enddate is null
group by customerid
order by customerid
what i mean is when count(*) = 0 no value is returned i want a Zero in the output how do i get it?
View 7 Replies
View Related
Apr 5, 2007
Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???
Thanks :)
View 7 Replies
View Related
May 19, 2004
Hi: Guys
Given the table below I want a select query that returns the AccountRepID with the largest single sale for each RegionID. In the event of a tie choose any single top AccountRepID to return.
CREATE TABLE [Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
RegionID] [int],
[AccountRepID] [int],
[SalesAmount] [money]
)
If the data were
salesid,regionid,accountrepid,salesamount
1,101,31,$50
2,101,32,$25
3,102,31,$25
4,102,32,$25
5,102,31,$15
The query should return
regionid,accountrepid
101,31
102,31 or 102,32
Is there another way to get the data other than the following query:
select regionID,accountrepid FROM Sales
where salesamount in
(select max(salesamount) FROM Sales group by regionid)
Thanks
View 2 Replies
View Related
May 10, 2007
i want to insert a row from t1 to t2 if any of the row is updated in the t1. say if Nth row is updated then the values of the row should be inserted into t2. inside the trigger i need to retrieve the row which is updated, how to do it? any specific global variable like @@identity is there for updation?
View 6 Replies
View Related
Apr 15, 2008
Hello Everyody,
Some background: I have a SQL server 2000 server that I just restored from backup after complete OS reinstallation. I have retored all databases (except the master) and I still have the mdf and ldf file from before reinstallation (master.mdf as well).
Now my problem is that I have 1 application that use a specific SQL user with a password that the application choose itself and of course this application doesn't work anymore because I didn't restore the sql users (I use windows authentification as much as possible). I cannot do anything to the application to use another password but I know the login used.
My questions is "How to I recover the password?". Is there a way to log the password that this application send to sql or from the old master.mdf file?
Any help would be greatly appreciated.
Bruno.
View 7 Replies
View Related
Mar 14, 2008
hi,
can anyone tell me how to retreive a row from a table using rowid (For example i want to retreive fifth row from a table)
View 3 Replies
View Related
Sep 13, 2007
Hi guys, I'm creating a webSite and i'd like to save images to my db. I've been using fileUpload to get the image i want to store, but i can't get it right! After "mastering" the storage of my images , i'd like to be able to retrieve them. I allready have SPs created for both storage & retrieval of images!Can anyone help me please? Thanks,superJB
View 3 Replies
View Related
Jan 2, 2004
Is there a way to create a query that will return all user tables inside a sql db
Thanx
View 4 Replies
View Related
Apr 1, 2008
I have developed a SSIS package on my local box and everything worked fine until I moved it to the production server. I have a Send Mail Task that is triggered by any of the other processes when they fail. It sends an email to the Admin with the current log file. My issue is that when it attempts to grab the log file (as an attachment) it fails with the following error message:
OnError,BILBO,UMIALIKJamesT,Email George,{a73bfc5c-d68e-426a-98ad-55e701670470},{677B8238-8697-454F-8F8A-DD6378E93455},4/1/2008 10:25:28 AM,4/1/2008 10:25:28 AM,-1073573429,0x,Either the file "D:SQL200590DTSPackagesPPROutputoutput.txt" does not exist or you do not have permissions to access the file.
It certainly exists and I've open all permissions up on the folder. What I find even more interesting is that the Package is just a single directory up from the Output file. The Package resides at D:SQL200590DTSPackagesPPR and is run via a batch file. It seems to me that it should have no issue grabbing this file and sending it, and when I don't attach the file it sends just fine.
Thank you in advance. If you need anymore information just let me know and I'll dig it up.
Peace,
James
View 6 Replies
View Related
Mar 13, 2008
I want to allow administrators of the program to change the permissions of the database roles. Is there a way to retreive the the specific permissions granted an denyed to a user and a database role. For instance, a store procedure that you call, pass it the database role and you get whether SELECT is allowed on table1, or if UPDATE is denied on table2, etc.
View 3 Replies
View Related
May 3, 2007
Hi all, I've been struggling with this one for a while, but am still doing something wrong: I have three tables which need to be updated. I have a stored proc which accomplishes the first writes the data in for the first 2 tables. The last table is a one to many, so needs a seperate stored proc which will be called multiple times depending on the number of items in the order. My only question I am trying to get to here is: How do I get the first stored proc to return me the primary key value from the 1st insert (NOT the second)? I've tried a few different methods: the current one shown below returns me "2", as in the number of inserts performed. Dim InsertCmd As New SqlCommand("WriteOrder", oSQLConn)
InsertCmd.CommandType = CommandType.StoredProcedure
InsertCmd.Parameters.AddWithValue("@CartTotal", Session("CartTotal"))
InsertCmd.Parameters.AddWithValue("@CARDFNAME", BillingInfo("CARDFNAME"))
InsertCmd.Parameters.AddWithValue("@CARDLNAME", BillingInfo("CARDLNAME"))
...
InsertCmd.Parameters.AddWithValue("@CONTACTEMAIL", BillingInfo("CONTACTEMAIL"))
InsertCmd.Parameters.AddWithValue("@COMPANYMATCH", 0)
InsertCmd.Parameters.AddWithValue("@RECNUM", 0)
Response.Write("---" & InsertCmd.ExecuteNonQuery().ToString() & "---")
---------------------STORED PROC---------------------
ALTER PROCEDURE [dbo].[WriteOrder]
@CartTotal float,
@CARDFNAME varchar(30),
...
@CONTACTEMAIL varchar(100),
@COMPANYMATCH bit,
@RecNum int = 0 OUTPUT
AS
INSERT INTO [dbo].[ORDER]
([OrderDate], [OrderTotal], [CARDFIRSTNAME], [CARDLASTNAME], [BILLINGADDR1], [BILLINGADDR2], [BILLINGADDR3], [BILLINGCITY], [BILLINGSTATE], [BILLINGPOSTALCODE], [BILLINGCOUNTRY], [BILLINGPHONE], [BILLINGEMAIL], [CCTYPE], [ACCOUNTNUMBER], [CARDEXPIREMONTH], [CARDEXPIREYEAR], [CVV2], [TransactionID], [TransDateStamp], [ProcessorAuthCode])
VALUES
(GetDate() ,@CartTotal , @CARDFNAME, @CARDLNAME, @BILLINGADDRESS1, @BILLINGADDRESS2, @BILLINGADDRESS3, @BILLINGCITY, @BILLINGSTATE, @BILLINGPOSTALCODE, @BILLINGCOUNTRY, @BILLINGPHONE, @BILLINGEMAIL, @CCTYPE, @ACCOUNTNUMBER, @EXPIRATIONMONTH, @EXPIRATIONYEAR, @CVV2,@TransactionID, @TransDateStamp,@ProcessorAuthCode)
SET @RecNum = @@IDENTITY
INSERT INTO [dbo].[CONTACT]
([ORDERID], [CONTACTFNAME], [CONTACTLNAME], [CONTACTADDRESS1], [CONTACTADDRESS2], [CONTACTADDRESS3],
[CONTACTCITY], [CONTACTSTATE], [CONTACTPOSTALCODE], [CONTACTCOUNTRY], [CONTACTPHONE], [CONTACTEMAIL], [COMPANYMATCH])
VALUES
(@@IDENTITY , @CONTACTFNAME, @CONTACTLNAME, @CONTACTADDRESS1, @CONTACTADDRESS2, @CONTACTADDRESS3, @CONTACTCITY, @CONTACTSTATE, @CONTACTPOSTALCODE, @CONTACTCOUNTRY, @CONTACTPHONE, @CONTACTEMAIL, @COMPANYMATCH)
RETURN @RecNum
GO
I've also tried returning parameters like this, with no luck: InsertCmd.Parameters(32).SqlDbType = SqlDbType.Int InsertCmd.Parameters(32).Direction = ParameterDirection.ReturnValue Response.Write("---" & InsertCmd.Parameters(32).Value() & "---")Any help is greatly appreciated!
View 4 Replies
View Related
Jan 28, 2008
I am using ASP.NET 3.5 with SQLDataSource, when a record is inserted is there a way to retreive the newly created GUID value in a field?
I am currently using SELECT @NewID = SCOPE_IDENTITY() to retreive the newly created record unique ID field, but I need the secondary field GUID field's value instead in this case.
Thank you.
View 7 Replies
View Related
May 1, 2007
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
View 4 Replies
View Related
Oct 6, 2007
Hi Friends,
I am Using SQL SERVER 2005
I need to retreive data from Excel file to SQLSERVER table..
Thanks in advance
Regards
Rajkumar.M
View 1 Replies
View Related