Parameterized Query

[Rob]

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 'GetFinancialData'."

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 GetFinancialData @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.factExpenses 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.factExpenses 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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("workstation
id=(local);uid=<Removed>;password=<Removed>;initia l catalog=TestDB")
Dim cmd As New SqlCommand("GetFinancialData", cn)
Dim adpt As New SqlDataAdapter(cmd)
Dim returnTable As New DataTable
Dim stbFinancialData As New StringBuilder
Dim dr As DataRow
Dim drObject As Object
Dim blnFirstPass As Boolean = True
Dim prmDate As New SqlParameter

With prmDate
.ParameterName = "@SearchDate"
.SqlDbType = SqlDbType.VarChar
.Size = 50
.Direction = ParameterDirection.Input
.DbType = DbType.String
.Value = "All"
End With

adpt.SelectCommand.Parameters.Add(prmDate)

adpt.Fill(returnTable)

With stbFinancialData
For Each dr In returnTable.Rows
If .Length > 0 Then
.Append(",")
End If
blnFirstPass = True
For Each drObject In dr.ItemArray
If blnFirstPass = False Then
.Append(":")
End If
.Append(drObject)
blnFirstPass = False
Next
Next
End With

cn.Dispose()
cmd.Dispose()
adpt.Dispose()

TextBox1.Text = stbFinancialData.ToString

End Sub

[JohnFol]

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 GetFinancialData @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.factExpenses 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*@discussions.microsoft.com> wrote in message
news:99**********************************@microsof t.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 'GetFinancialData'."

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 GetFinancialData @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.factExpenses 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.factExpenses 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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("workstation
id=(local);uid=<Removed>;password=<Removed>;initia l catalog=TestDB")
Dim cmd As New SqlCommand("GetFinancialData", cn)
Dim adpt As New SqlDataAdapter(cmd)
Dim returnTable As New DataTable
Dim stbFinancialData As New StringBuilder
Dim dr As DataRow
Dim drObject As Object
Dim blnFirstPass As Boolean = True
Dim prmDate As New SqlParameter

With prmDate
.ParameterName = "@SearchDate"
.SqlDbType = SqlDbType.VarChar
.Size = 50
.Direction = ParameterDirection.Input
.DbType = DbType.String
.Value = "All"
End With

adpt.SelectCommand.Parameters.Add(prmDate)

adpt.Fill(returnTable)

With stbFinancialData
For Each dr In returnTable.Rows
If .Length > 0 Then
.Append(",")
End If
blnFirstPass = True
For Each drObject In dr.ItemArray
If blnFirstPass = False Then
.Append(":")
End If
.Append(drObject)
blnFirstPass = False
Next
Next
End With

cn.Dispose()
cmd.Dispose()
adpt.Dispose()

TextBox1.Text = stbFinancialData.ToString

End Sub

[Michael C#]

Try

Dim cmd As New SqlCommand("GetFinancialData @SearchDate", cn)

You set the parameter later, but you never specified the parameter in your
SqlCommand.

"Rob" <Ro*@discussions.microsoft.com> wrote in message
news:99**********************************@microsof t.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 'GetFinancialData'."

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 GetFinancialData @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.factExpenses 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.factExpenses 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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("workstation
id=(local);uid=<Removed>;password=<Removed>;initia l catalog=TestDB")
Dim cmd As New SqlCommand("GetFinancialData", cn)
Dim adpt As New SqlDataAdapter(cmd)
Dim returnTable As New DataTable
Dim stbFinancialData As New StringBuilder
Dim dr As DataRow
Dim drObject As Object
Dim blnFirstPass As Boolean = True
Dim prmDate As New SqlParameter

With prmDate
.ParameterName = "@SearchDate"
.SqlDbType = SqlDbType.VarChar
.Size = 50
.Direction = ParameterDirection.Input
.DbType = DbType.String
.Value = "All"
End With

adpt.SelectCommand.Parameters.Add(prmDate)

adpt.Fill(returnTable)

With stbFinancialData
For Each dr In returnTable.Rows
If .Length > 0 Then
.Append(",")
End If
blnFirstPass = True
For Each drObject In dr.ItemArray
If blnFirstPass = False Then
.Append(":")
End If
.Append(drObject)
blnFirstPass = False
Next
Next
End With

cn.Dispose()
cmd.Dispose()
adpt.Dispose()

TextBox1.Text = stbFinancialData.ToString

End Sub

[Dennis]

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 GetFinancialData @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.factExpenses 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*@discussions.microsoft.com> wrote in message
news:99**********************************@microsof t.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 'GetFinancialData'."

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 GetFinancialData @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.factExpenses 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.factExpenses 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(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim cn As New SqlConnection("workstation
id=(local);uid=<Removed>;password=<Removed>;initia l catalog=TestDB")
Dim cmd As New SqlCommand("GetFinancialData", cn)
Dim adpt As New SqlDataAdapter(cmd)
Dim returnTable As New DataTable
Dim stbFinancialData As New StringBuilder
Dim dr As DataRow
Dim drObject As Object
Dim blnFirstPass As Boolean = True
Dim prmDate As New SqlParameter

With prmDate
.ParameterName = "@SearchDate"
.SqlDbType = SqlDbType.VarChar
.Size = 50
.Direction = ParameterDirection.Input
.DbType = DbType.String
.Value = "All"
End With

adpt.SelectCommand.Parameters.Add(prmDate)

adpt.Fill(returnTable)

With stbFinancialData
For Each dr In returnTable.Rows
If .Length > 0 Then
.Append(",")
End If
blnFirstPass = True
For Each drObject In dr.ItemArray
If blnFirstPass = False Then
.Append(":")
End If
.Append(drObject)
blnFirstPass = False
Next
Next
End With

cn.Dispose()
cmd.Dispose()
adpt.Dispose()

TextBox1.Text = stbFinancialData.ToString

End Sub