I have a problem I can't seem to solve although it should be easy. I've done
these many times before. But I keep getting an exception, "Line 1: Incorrect
syntax near 'GetFinancialDa ta'."
This query works fine through Query Analyzer or if I do not set SQLParameter
for the query. I hope someone spots what I overlooked. Please let me know
if I have missed something. Thanks in advance.
Here is the query:
ALTER PROCEDURE GetFinancialDat a @SearchDate VARCHAR(50) AS
IF @SearchDate = 'All'
BEGIN
SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net,
e.Value AS Incentives
FROM dbo.dimDate d
INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX
INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM
dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX
INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX
ORDER BY d.[Date], r.Retailer ASC
END
ELSE
BEGIN
SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net,
e.Value AS Incentives
FROM dbo.dimDate d
INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX
INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM
dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX
INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX
WHERE d.[Date] = @SearchDate
ORDER BY d.[Date], r.Retailer ASC
END
Here is the code that calls the Stored Procedure:
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim cn As New SqlConnection(" workstation
id=(local);uid= <Removed>;passw ord=<Removed>;i nitial catalog=TestDB" )
Dim cmd As New SqlCommand("Get FinancialData", cn)
Dim adpt As New SqlDataAdapter( cmd)
Dim returnTable As New DataTable
Dim stbFinancialDat a As New StringBuilder
Dim dr As DataRow
Dim drObject As Object
Dim blnFirstPass As Boolean = True
Dim prmDate As New SqlParameter
With prmDate
.ParameterName = "@SearchDat e"
.SqlDbType = SqlDbType.VarCh ar
.Size = 50
.Direction = ParameterDirect ion.Input
.DbType = DbType.String
.Value = "All"
End With
adpt.SelectComm and.Parameters. Add(prmDate)
adpt.Fill(retur nTable)
With stbFinancialDat a
For Each dr In returnTable.Row s
If .Length > 0 Then
.Append(",")
End If
blnFirstPass = True
For Each drObject In dr.ItemArray
If blnFirstPass = False Then
.Append(":")
End If
.Append(drObjec t)
blnFirstPass = False
Next
Next
End With
cn.Dispose()
cmd.Dispose()
adpt.Dispose()
TextBox1.Text = stbFinancialDat a.ToString
End Sub 3 1398
Hi Rob, t's not clear which line causes the exception, but the following may
help narrow down the problem.
1) If you use SQL Profiler, you should be able to see the exact syntax sent
to the server. This will allow you to identify if it's SQL or the code
causing the exception
2) You can simplify the procedure to be this:
ALTER PROCEDURE GetFinancialDat a @SearchDate VARCHAR(50) AS
SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net,
e.Value AS Incentives
FROM dbo.dimDate d
INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX
INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM
dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX
INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX
WHERE ((d.[Date] = @SearchDate) OR (@SearchDate = 'ALL'))
ORDER BY d.[Date], r.Retailer ASC
Sorry it's not a difinitive answer for you.
Regards
"Rob" <Ro*@discussion s.microsoft.com > wrote in message
news:99******** *************** ***********@mic rosoft.com... I have a problem I can't seem to solve although it should be easy. I've done these many times before. But I keep getting an exception, "Line 1: Incorrect syntax near 'GetFinancialDa ta'."
This query works fine through Query Analyzer or if I do not set SQLParameter for the query. I hope someone spots what I overlooked. Please let me know if I have missed something. Thanks in advance.
Here is the query: ALTER PROCEDURE GetFinancialDat a @SearchDate VARCHAR(50) AS IF @SearchDate = 'All' BEGIN SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net, e.Value AS Incentives FROM dbo.dimDate d INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX ORDER BY d.[Date], r.Retailer ASC END ELSE BEGIN SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net, e.Value AS Incentives FROM dbo.dimDate d INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX WHERE d.[Date] = @SearchDate ORDER BY d.[Date], r.Retailer ASC END
Here is the code that calls the Stored Procedure:
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button1.Click Dim cn As New SqlConnection(" workstation id=(local);uid= <Removed>;passw ord=<Removed>;i nitial catalog=TestDB" ) Dim cmd As New SqlCommand("Get FinancialData", cn) Dim adpt As New SqlDataAdapter( cmd) Dim returnTable As New DataTable Dim stbFinancialDat a As New StringBuilder Dim dr As DataRow Dim drObject As Object Dim blnFirstPass As Boolean = True Dim prmDate As New SqlParameter
With prmDate .ParameterName = "@SearchDat e" .SqlDbType = SqlDbType.VarCh ar .Size = 50 .Direction = ParameterDirect ion.Input .DbType = DbType.String .Value = "All" End With
adpt.SelectComm and.Parameters. Add(prmDate)
adpt.Fill(retur nTable)
With stbFinancialDat a For Each dr In returnTable.Row s If .Length > 0 Then .Append(",") End If blnFirstPass = True For Each drObject In dr.ItemArray If blnFirstPass = False Then .Append(":") End If .Append(drObjec t) blnFirstPass = False Next Next End With
cn.Dispose() cmd.Dispose() adpt.Dispose()
TextBox1.Text = stbFinancialDat a.ToString
End Sub
Try
Dim cmd As New SqlCommand("Get FinancialData @SearchDate", cn)
You set the parameter later, but you never specified the parameter in your
SqlCommand.
"Rob" <Ro*@discussion s.microsoft.com > wrote in message
news:99******** *************** ***********@mic rosoft.com... I have a problem I can't seem to solve although it should be easy. I've done these many times before. But I keep getting an exception, "Line 1: Incorrect syntax near 'GetFinancialDa ta'."
This query works fine through Query Analyzer or if I do not set SQLParameter for the query. I hope someone spots what I overlooked. Please let me know if I have missed something. Thanks in advance.
Here is the query: ALTER PROCEDURE GetFinancialDat a @SearchDate VARCHAR(50) AS IF @SearchDate = 'All' BEGIN SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net, e.Value AS Incentives FROM dbo.dimDate d INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX ORDER BY d.[Date], r.Retailer ASC END ELSE BEGIN SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net, e.Value AS Incentives FROM dbo.dimDate d INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX WHERE d.[Date] = @SearchDate ORDER BY d.[Date], r.Retailer ASC END
Here is the code that calls the Stored Procedure:
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button1.Click Dim cn As New SqlConnection(" workstation id=(local);uid= <Removed>;passw ord=<Removed>;i nitial catalog=TestDB" ) Dim cmd As New SqlCommand("Get FinancialData", cn) Dim adpt As New SqlDataAdapter( cmd) Dim returnTable As New DataTable Dim stbFinancialDat a As New StringBuilder Dim dr As DataRow Dim drObject As Object Dim blnFirstPass As Boolean = True Dim prmDate As New SqlParameter
With prmDate .ParameterName = "@SearchDat e" .SqlDbType = SqlDbType.VarCh ar .Size = 50 .Direction = ParameterDirect ion.Input .DbType = DbType.String .Value = "All" End With
adpt.SelectComm and.Parameters. Add(prmDate)
adpt.Fill(retur nTable)
With stbFinancialDat a For Each dr In returnTable.Row s If .Length > 0 Then .Append(",") End If blnFirstPass = True For Each drObject In dr.ItemArray If blnFirstPass = False Then .Append(":") End If .Append(drObjec t) blnFirstPass = False Next Next End With
cn.Dispose() cmd.Dispose() adpt.Dispose()
TextBox1.Text = stbFinancialDat a.ToString
End Sub
I'm new at using stored procedures and was wondering if it was possible to
create a stored procedure and add it to a database in runtime code. For
example, I create a database in Access at my applicaiton's start-up then if
it can't find the database used by the applicaiton, it creates a new one. I
would like to use stored procedures but can't figure out how to create one
and add it to the database. An example would be helpful if there is a way to
do it. Thanks.
"JohnFol" wrote: Hi Rob, t's not clear which line causes the exception, but the following may help narrow down the problem.
1) If you use SQL Profiler, you should be able to see the exact syntax sent to the server. This will allow you to identify if it's SQL or the code causing the exception
2) You can simplify the procedure to be this:
ALTER PROCEDURE GetFinancialDat a @SearchDate VARCHAR(50) AS SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net, e.Value AS Incentives FROM dbo.dimDate d INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX WHERE ((d.[Date] = @SearchDate) OR (@SearchDate = 'ALL')) ORDER BY d.[Date], r.Retailer ASC
Sorry it's not a difinitive answer for you. Regards "Rob" <Ro*@discussion s.microsoft.com > wrote in message news:99******** *************** ***********@mic rosoft.com...I have a problem I can't seem to solve although it should be easy. I've done these many times before. But I keep getting an exception, "Line 1: Incorrect syntax near 'GetFinancialDa ta'."
This query works fine through Query Analyzer or if I do not set SQLParameter for the query. I hope someone spots what I overlooked. Please let me know if I have missed something. Thanks in advance.
Here is the query: ALTER PROCEDURE GetFinancialDat a @SearchDate VARCHAR(50) AS IF @SearchDate = 'All' BEGIN SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net, e.Value AS Incentives FROM dbo.dimDate d INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX ORDER BY d.[Date], r.Retailer ASC END ELSE BEGIN SELECT d.[Date], r.Retailer, i.GrossProfit AS Gross, i.NetProfit AS Net, e.Value AS Incentives FROM dbo.dimDate d INNER JOIN dbo.factIncomes i ON i.DateNDX = d.DateNDX INNER JOIN (SELECT DateNDX, RetailerNDX, SUM(Value) AS Value FROM dbo.factExpense s GROUP BY DateNDX, RetailerNDX) e ON e.DateNDX = d.DateNDX INNER JOIN dbo.dimRetailer r ON r.RetailerNDX = i.RetailerNDX WHERE d.[Date] = @SearchDate ORDER BY d.[Date], r.Retailer ASC END
Here is the code that calls the Stored Procedure:
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button1.Click Dim cn As New SqlConnection(" workstation id=(local);uid= <Removed>;passw ord=<Removed>;i nitial catalog=TestDB" ) Dim cmd As New SqlCommand("Get FinancialData", cn) Dim adpt As New SqlDataAdapter( cmd) Dim returnTable As New DataTable Dim stbFinancialDat a As New StringBuilder Dim dr As DataRow Dim drObject As Object Dim blnFirstPass As Boolean = True Dim prmDate As New SqlParameter
With prmDate .ParameterName = "@SearchDat e" .SqlDbType = SqlDbType.VarCh ar .Size = 50 .Direction = ParameterDirect ion.Input .DbType = DbType.String .Value = "All" End With
adpt.SelectComm and.Parameters. Add(prmDate)
adpt.Fill(retur nTable)
With stbFinancialDat a For Each dr In returnTable.Row s If .Length > 0 Then .Append(",") End If blnFirstPass = True For Each drObject In dr.ItemArray If blnFirstPass = False Then .Append(":") End If .Append(drObjec t) blnFirstPass = False Next Next End With
cn.Dispose() cmd.Dispose() adpt.Dispose()
TextBox1.Text = stbFinancialDat a.ToString
End Sub This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: gary b |
last post by:
Hello
When I use a PreparedStatement (in jdbc) with the following query:
SELECT store_groups_id
FROM store_groups
WHERE store_groups_id IS NOT NULL
AND type = ?
ORDER BY group_name
|
by: deko |
last post by:
I'm trying to open a Recordset based on a parameterized query. I'm kind of
new to parameterized queries, so I'm sure I'm missing something simple.
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryInvoices")
qdf.Parameters("prmInv") = strInvoice
qdf.Parameters("prmCid") = lngCustomerID
Set rst = db.OpenRecordset("qryInvoices")
|
by: deko |
last post by:
Is it possible to build a parameterized query from another parameterized
query?
I've tried two variations of this and can't seem to get it to work (using
DAO). Any suggestions welcome!
I want to delete linked documents - but only if they are not
linked to other entities outside of the category being deleted
|
by: anony |
last post by:
Hello,
I can't figure out why my parameterized query from an ASP.NET page is
dropping "special" characters such as accented quotes & apostrophes, the
registered trademark symbol, etc. These symbols insert without problem from
query analyzer, so that suggests it's something within ASP.NET. I've tried
using .NET textbox web controls as well as html textareas. I have a test
database set up with 4 fields: varchar, nvarchar, text, and...
|
by: Roland Hall |
last post by:
In Access you use "*" + + "*", + can be replaced with &
Calling a parameterized query in Access requires % be used in place of *,
however, all that I have read show dynamic SQL passed to Access:
WHERE LIKE '" & ASPvar & "' % ORDER BY ...
However, my call is similar to:
conn.qMyLookup strVar, rs
| |
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: 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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
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...
| |