I have got spComparison_GL vsMeas and I need to use this in VB instead of sql. How do I go about declaring variables and making sure that I can still get the same results as I would in a stored procedure. I tried using ADODC which didn't work. Below is the SP -
CREATE proc dbo.FIN_spComparison_GLvsMeas
-
@BranchID varchar(4)
-
as
-
-
select EB.BranchID,
-
ACC.AccountID,
-
sum (GL.Amt) as 'GL_ActualYTD',
-
Isnull(ABB.ValBudgetYTD, 0) ValBudgetYTD,
-
Isnull(Round(ABB.ValActualYTD, 2), 0) * -1 Meas_ValActualYTD,
-
Round(IsNull(Sum(GL.Amt), 0) - Isnull((ABB.ValActualYTD * -1), 0), 2) as Diff_Act_GLvsMeas,
-
Round(IsNull(Sum(GL.Amt), 0) - Isnull((ABB.ValBudgetYTD * -1), 0), 2) as Diff_Bud_GLvsMeas,
-
EB.Branch, EB.AreaID, EB.Area,
-
EB.RegionID, EB.Region, EB.DivisionID, EB.Division,
-
EB.Cluster, EB.CLID, EB.[Exec], EB.SBU, EB.BU,
-
ACC.AccountName, ACC.SubHeaderName,
-
SUM(CASE GL.cycl_num WHEN 200801 THEN GL.amt ELSE 0 END) AS A1,
-
SUM(CASE GL.cycl_num WHEN 200802 THEN GL.amt ELSE 0 END) AS A2,
-
SUM(CASE GL.cycl_num WHEN 200803 THEN GL.amt ELSE 0 END) AS A3,
-
SUM(CASE GL.cycl_num WHEN 200804 THEN GL.amt ELSE 0 END) AS A4,
-
SUM(CASE GL.cycl_num WHEN 200805 THEN GL.amt ELSE 0 END) AS A5,
-
SUM(CASE GL.cycl_num WHEN 200806 THEN GL.amt ELSE 0 END) AS A6,
-
SUM(CASE GL.cycl_num WHEN 200807 THEN GL.amt ELSE 0 END) AS A7,
-
SUM(CASE GL.cycl_num WHEN 200808 THEN GL.amt ELSE 0 END) AS A8,
-
SUM(CASE GL.cycl_num WHEN 200809 THEN GL.amt ELSE 0 END) AS A9,
-
SUM(CASE GL.cycl_num WHEN 200810 THEN GL.amt ELSE 0 END) AS A10,
-
SUM(CASE GL.cycl_num WHEN 200811 THEN GL.amt ELSE 0 END) AS A11,
-
SUM(CASE GL.cycl_num WHEN 200812 THEN GL.amt ELSE 0 END) AS A12,
-
Isnull(ABBPM.B1, 0) B1, Isnull(ABBPM.B2, 0) B2, Isnull(ABBPM.B3, 0) B3, Isnull(ABBPM.B4, 0) B4, Isnull(ABBPM.B5, 0) B5, Isnull(ABBPM.B6, 0) B6,
-
Isnull(ABBPM.B7, 0) B7, Isnull(ABBPM.B8, 0) B8, Isnull(ABBPM.B9, 0) B9, Isnull(ABBPM.B10, 0) B10, Isnull(ABBPM.B11, 0) B11, Isnull(ABBPM.B12, 0) B12
-
from GL_Trans_YTD GL
-
left outer join Accounts_vw ACC
-
on ACC.AccountID = GL.AcID
-
left outer join Execbbranches_vw EB
-
on EB.BranchID = GL.BrID
-
left outer join tblValActBudBasPerMonth ABBPM
-
on ABBPM.BranchID = GL.BrID
-
and ABBPM.AccountID = GL.AcID
-
left outer join tblValActBudBas ABB
-
on ABB.BranchID = GL.BrID
-
and ABB.AccountID = GL.AcID
-
where GL.BrID = @BranchID
-
and GL.AcID > 6000
-
group by GL.AcID,
-
ACC.AccountID, ACC.AccountName, ACC.SubHeaderName,
-
EB.BranchID, EB.Branch, EB.AreaID, EB.Area,
-
EB.RegionID, EB.Region, EB.DivisionID, EB.Division,
-
EB.Cluster, EB.CLID, EB.[Exec], EB.SBU, EB.BU,
-
ABBPM.B1, ABBPM.B2, ABBPM.B3, ABBPM.B4, ABBPM.B5, ABBPM.B6,
-
ABBPM.B7, ABBPM.B8, ABBPM.B9, ABBPM.B10, ABBPM.B11, ABBPM.B12,
-
ABB.ValActualYTD, ABB.ValBudgetYTD
-
order by 6, 7, 2
-
GO
-
Thanks in advice for helping
1 1075
Please find a related discussion here .
You only have to change the connection string.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Brad H McCollum |
last post by:
I've designed a very basic SQL Server Stored Procedure that I'm using
via a Visual Basic 6.0 front-end file in retrieving records into a
data entry form.
I can't for the life of me get the records retrieved via the Stored
Procedure to be edited.
I can't even run the stored procedure in MSDE's T-SQL utility and then
edit any of the records shown to me.
|
by: Nashat Wanly |
last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual C# .NET
View products that this article applies to.
This article was previously published under Q310070
For a Microsoft Visual Basic .NET version of this article, see 308049.
For a Microsoft Visual C++ .NET version of this article, see 310071.
For a Microsoft Visual J# .NET version of this article, see 320627.
This article refers to the following Microsoft .NET...
|
by: Douglas Buchanan |
last post by:
I can't seem to open SQLS2k Stored Procedures in the IDE
I am running MDE 2003 Version 7.1.3088
I have a MSDN professional subscription and did a complete install of
vs.net
Help explains how to do it:
1. In Server Explorer, expand the Stored Procedures folder.
2. Double-click the name of the stored procedure you want to open.
|
by: Mark Dicken |
last post by:
Hi All
I have found the following Microsoft Technet 'Q'
Article :-
Q210368 -ACC2000: How to Pass an Array as an Argument to a Procedure
(I've also copied and pasted the whole contents into the bottom of
this email)
|
by: Crazy Cat |
last post by:
Using Visual Basic .NET and SQL Server 2005
I attempt to add a query that is a call to a stored procedure in the
DataSet Designer.
The TableAdapter Query Configuration wizard finds my stored procedure
fine but generates
the following error when I attempt to add the query
Invalid object name '#temp'.
| |
by: Crazy Cat |
last post by:
I'm calling several SQL Server 2005 stored procedures from Visual Basic
2005. I've got one stored procedure that locks up at the same point
everytime, but if I copy the stored procedure from the SQL Profiler
trace and paste it into SQL Server Management Studio it runs fine -- I
even tried setting the timeout in Visual Studio to unlimited to see if
the procedure would ever complete and it doesn't. What the hell is
going on? Here is the...
|
by: Groningen |
last post by:
Hi,
I was wondering if someone could help me on this one.
I've made a stored procedure in SQL Server Enterprise Manager. When I call the stored procedure in Visual Basic I didn't succeed to return a SQL value.
I'm calling the procedure using the following VB source code:
Dim param1 As ADODB.Parameter
Dim aantal As Integer
Set ADOCMD = New ADODB.Command
|
by: endriu |
last post by:
Hi
I want to recive some data from db2 (as400) using ado.net in visual
basic. Also i want to access them by stored procedure that return a
cursor. So, i create very simple stored procedure on as400:
P1 : BEGIN BEGIN DECLARE CUR CURSOR WITH RETURN FOR SELECT PRICE,
QUANTITY
EXPORT . SALES ;
OPEN CUR ;
|
by: AJIT PITALE |
last post by:
Hi,
I am using Visual Basic 6 as front end and Visual FoxPro as a Database.
I am trying to pass a parameter from VB to Visual FoxPro's Stored Procedure.
I am getting an error showing "Data type mismatch"
My Connection String is :CNN.ConnectionString = "Provider=VFPOLEDB.1;Data Source=P:\pay.dbc;Mode=ReadWrite|Share Deny Write"
CNN.Open
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |