Parameter problem..help is needed please

Can someone please help identify where the problem exist in this code. A
connection with an Access database (in this case)is established through the
GUI. The snippet of code is aimed to implement two parameters: an input
parameter and an output parameter. The user inputs a value in textbox1 to
serve as input for parameter @.BizName. The output parameter is @.BizAddress.
It’s value is captured in variable i and placed in textbox2.
Why do I get this error message:
Server Error in '/WebApplicationAccessParam' Application.
Parameter 1: '@.BizAddress' of type: String, the property Size has an invalid
size: 0
This is the code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim i As String
Dim dsResult As New DataSet
Dim strSQL As String
strSQL = "SELECT @.BizAddress=Address FROM testTable WHERE Name =
@.BizName"
Dim oleAdapter As New OleDbDataAdapter(strSQL, OleDbConnection1)
oleAdapter.SelectCommand.Parameters.Add("@.BizName", OleDbType.VarChar)
oleAdapter.SelectCommand.Parameters("@.BizName").Direction =
ParameterDirection.Input
oleAdapter.SelectCommand.Parameters("@.BizName").Value = TextBox1.Text
oleAdapter.SelectCommand.Parameters.Add("@.BizAddress",
OleDbType.VarChar)
oleAdapter.SelectCommand.Parameters("@.BizAddress").Direction =
ParameterDirection.Output
i = oleAdapter.SelectCommand.Parameters("@.BizAddress").Value
TextBox2.Text = i
oleAdapter.Fill(dsResult, "testTable")
DataGrid1.DataSource = dsResult
DataGrid1.DataMember = "testTable"
DataGrid1.DataBind()
OleDbConnection1.Close()
Many thanks in advance.I think you need to add a size to your .Add() function, like this:
oleAdapter.SelectCommand.Parameters.Add("@.BizName", OleDbType.VarChar, 50)
I used a default of 50, because that's what Access defaults to for those
type fields, but you should change it to match the size of the field in your
database.
Same thing with the @.BizAddress parameter.
Thx,
Mike C.
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:51A57F18-B570-4311-859C-3A0B7357FC6C@.microsoft.com...
> Can someone please help identify where the problem exist in this code. A
> connection with an Access database (in this case)is established through
> the
> GUI. The snippet of code is aimed to implement two parameters: an input
> parameter and an output parameter. The user inputs a value in textbox1 to
> serve as input for parameter @.BizName. The output parameter is
> @.BizAddress.
> It's value is captured in variable i and placed in textbox2.
> Why do I get this error message:
> Server Error in '/WebApplicationAccessParam' Application.
> Parameter 1: '@.BizAddress' of type: String, the property Size has an
> invalid
> size: 0
> This is the code:
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Dim i As String
> Dim dsResult As New DataSet
> Dim strSQL As String
> strSQL = "SELECT @.BizAddress=Address FROM testTable WHERE Name =
> @.BizName"
> Dim oleAdapter As New OleDbDataAdapter(strSQL, OleDbConnection1)
> oleAdapter.SelectCommand.Parameters.Add("@.BizName",
> OleDbType.VarChar)
> oleAdapter.SelectCommand.Parameters("@.BizName").Direction =
> ParameterDirection.Input
> oleAdapter.SelectCommand.Parameters("@.BizName").Value =
> TextBox1.Text
> oleAdapter.SelectCommand.Parameters.Add("@.BizAddress",
> OleDbType.VarChar)
> oleAdapter.SelectCommand.Parameters("@.BizAddress").Direction =
> ParameterDirection.Output
> i = oleAdapter.SelectCommand.Parameters("@.BizAddress").Value
> TextBox2.Text = i
>
> oleAdapter.Fill(dsResult, "testTable")
> DataGrid1.DataSource = dsResult
> DataGrid1.DataMember = "testTable"
> DataGrid1.DataBind()
> OleDbConnection1.Close()
>
> Many thanks in advance.
>
>|||I did that. But now i'm getting another server error:
"Multiple-step OLE DB operation generated errors. Check each OLE DB status
value. if available. No work was done."
Any more ideas.
Nab
"Michael C#" wrote:

> I think you need to add a size to your .Add() function, like this:
> oleAdapter.SelectCommand.Parameters.Add("@.BizName", OleDbType.VarChar, 50)
> I used a default of 50, because that's what Access defaults to for those
> type fields, but you should change it to match the size of the field in yo
ur
> database.
> Same thing with the @.BizAddress parameter.
> Thx,
> Mike C.
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:51A57F18-B570-4311-859C-3A0B7357FC6C@.microsoft.com...
>
>|||Which command is it erroring on? Possibly this one?
Why are you trying to get a Parameter Value from an Output parameter when
the Command hasn't been executed yet? I'm not even sure you can use an
OUTPUT parameter with a DataAdapter's .Fill() method. You might want to
look that one up in MSDN Online. To return output parameters, they have to
be specified in your SQL Command by following with the OUTPUT keyword also.
If you'd like to pursue this further, I can help you sort it out, but we
should probably move this thread to the
microsoft.public.dotnet.languages.csharp newsgroup, since we're leaving the
realm of SQL problems and charging into C#.NET + ADO.NET issues.
Thanks,
Mike C.
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:6B22A250-F07C-482B-A7E9-00AED74B8829@.microsoft.com...
>I did that. But now i'm getting another server error:
> "Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value. if available. No work was done."
> Any more ideas.
> Nab
> "Michael C#" wrote:
>|||Thanks Michael. The error is on this line:
oleAdapter.Fill(dsResult, "testTable")
I will appreciate it if you could email to: nabofengland@.hotmail.co.uk
With a suggested solution.
Cheers.
Nab
"Michael C#" wrote:

> Which command is it erroring on? Possibly this one?
>
> Why are you trying to get a Parameter Value from an Output parameter when
> the Command hasn't been executed yet? I'm not even sure you can use an
> OUTPUT parameter with a DataAdapter's .Fill() method. You might want to
> look that one up in MSDN Online. To return output parameters, they have t
o
> be specified in your SQL Command by following with the OUTPUT keyword also
.
> If you'd like to pursue this further, I can help you sort it out, but we
> should probably move this thread to the
> microsoft.public.dotnet.languages.csharp newsgroup, since we're leaving th
e
> realm of SQL problems and charging into C#.NET + ADO.NET issues.
> Thanks,
> Mike C.
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:6B22A250-F07C-482B-A7E9-00AED74B8829@.microsoft.com...
>
>|||Sorry, here are the lines of error with oleAdapter.Fill(dsResult,
"testTable") highlighted in red.
Source Error:
Line 62: i =
oleAdapter.SelectCommand.Parameters("@.BizAddress").Value()
Line 63:
Line 64: oleAdapter.Fill(dsResult, "testTable")
Line 65: DataGrid1.DataSource = dsResult
Line 66: DataGrid1.DataMember = "testTable"
"Michael C#" wrote:

> Which command is it erroring on? Possibly this one?
>
> Why are you trying to get a Parameter Value from an Output parameter when
> the Command hasn't been executed yet? I'm not even sure you can use an
> OUTPUT parameter with a DataAdapter's .Fill() method. You might want to
> look that one up in MSDN Online. To return output parameters, they have t
o
> be specified in your SQL Command by following with the OUTPUT keyword also
.
> If you'd like to pursue this further, I can help you sort it out, but we
> should probably move this thread to the
> microsoft.public.dotnet.languages.csharp newsgroup, since we're leaving th
e
> realm of SQL problems and charging into C#.NET + ADO.NET issues.
> Thanks,
> Mike C.
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:6B22A250-F07C-482B-A7E9-00AED74B8829@.microsoft.com...
>
>|||This should help:
http://support.microsoft.com/kb/308051
-oj
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:58CE1CA5-56D2-492A-A3EA-C15B41AF0D34@.microsoft.com...
> Sorry, here are the lines of error with oleAdapter.Fill(dsResult,
> "testTable") highlighted in red.
> Source Error:
> Line 62: i =
> oleAdapter.SelectCommand.Parameters("@.BizAddress").Value()
> Line 63:
> Line 64: oleAdapter.Fill(dsResult, "testTable")
> Line 65: DataGrid1.DataSource = dsResult
> Line 66: DataGrid1.DataMember = "testTable"
>
> "Michael C#" wrote:
>|||Line 62 looks like it might be an offending instruction, as well as possibly
the manner in which you're trying to use an Output Parameter with your SQL
statement. What's the Output Parameter for anyway? Just wondering... I'm
not sure exactly what you're trying to accomplish with your DataGrid and
OleAdapter here, but basically you could eliminate the Output Parameter
altogether, since the only thing your statement would return to the DataGrid
(without the "@.BizAddress=" part), is the value of Address; presumably a
single "Address" from a single row...
Thanks,
Mike C.
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:58CE1CA5-56D2-492A-A3EA-C15B41AF0D34@.microsoft.com...
> Sorry, here are the lines of error with oleAdapter.Fill(dsResult,
> "testTable") highlighted in red.
> Source Error:
> Line 62: i =
> oleAdapter.SelectCommand.Parameters("@.BizAddress").Value()
> Line 63:
> Line 64: oleAdapter.Fill(dsResult, "testTable")
> Line 65: DataGrid1.DataSource = dsResult
> Line 66: DataGrid1.DataMember = "testTable"
>
> "Michael C#" wrote:
>

Parameter based Report Schedule problem Problem

Here is my Case
+++++++++++++++++++++++++++++
I have a common report object residing on Reporting Service. The report
shows Monthly Activity for a Department. This report is based on a Stored
Procedure and has Department ID as a parameter.
Through an application different departments either can run this report or
create a schedule so that at the end of each month, any department can get
the report related to only its data.
How can I do that?
+++++++++++++++++++++++++
You input highly appreciatble...Hi Sue,
This is more an issue for the developers of the applications calling the
report as they will be passing the DepartmentID.
How can the app know which department it is run from? Is there a setting in
Active Directory for the user? Is there a database table somewhere with who
lives in which department? (very messy to maintain).
I'd be querying AD if possible.
"Sue" wrote:
> Here is my Case
> +++++++++++++++++++++++++++++
> I have a common report object residing on Reporting Service. The report
> shows Monthly Activity for a Department. This report is based on a Stored
> Procedure and has Department ID as a parameter.
> Through an application different departments either can run this report or
> create a schedule so that at the end of each month, any department can get
> the report related to only its data.
> How can I do that?
> +++++++++++++++++++++++++
> You input highly appreciatble...
>|||Hi Mary:
Thank you for your response.
I have been using RS API calls to generate the Report from my Web Application.
While generating a report the system already know the Dapartment ID based on
the person who logged in.
Let me give you more information regarding this problem.
1. Report object already exists in the common folder located in the
Reporting Server.
2. End-User can supply Department ID is supplied throgh the program. How can
I perform generate ad-hoc report?
3. How can the end-user schedule this report?
Your input highly appreciatable.
Thanks
Sue
"Mary Bray [SQL Server MVP]" wrote:
> Hi Sue,
> This is more an issue for the developers of the applications calling the
> report as they will be passing the DepartmentID.
> How can the app know which department it is run from? Is there a setting in
> Active Directory for the user? Is there a database table somewhere with who
> lives in which department? (very messy to maintain).
> I'd be querying AD if possible.
> "Sue" wrote:
> > Here is my Case
> > +++++++++++++++++++++++++++++
> > I have a common report object residing on Reporting Service. The report
> > shows Monthly Activity for a Department. This report is based on a Stored
> > Procedure and has Department ID as a parameter.
> >
> > Through an application different departments either can run this report or
> > create a schedule so that at the end of each month, any department can get
> > the report related to only its data.
> >
> > How can I do that?
> > +++++++++++++++++++++++++
> > You input highly appreciatble...
> >
> >sql

Paramater in Case statement

When I enter the following query in the MSSQL 2005 Server Management Studio,
I get results, but when i enter the same query (Minus the top 2 lines) in RS,
i get an a error:
Title: Microsoft Visual Database Tools
Error: The Parameter is incorrect.
Any ideas what i'm doing wrong? This query is just a simplified example
using the northwind db.
DECLARE @.Region AS NVARCHAR(15)
SELECT @.Region = '1'
SELECT *
FROM Customers
WHERE Region = CASE
WHEN @.Region = '1'
THEN 'SP'
END
--
Lucas DargisHi Lucas,
> DECLARE @.Region AS NVARCHAR(15)
> SELECT @.Region = '1'
You are using a variable (@.Region) in your query and if you leave off the
top 2 lines, you are not declaring the variable and setting its value. The
statement needs this.
HTH!
Kind regards - Fred|||thanks Fred.
I left off the top two lines because in RS you specify the parameters in the
'Report > Report Parameters' window.
i found that this is just a bug in the design view of RS. when i Previewed
the report, it worked just fine.
thanks
--
Lucas Dargis
"Fred Block" wrote:
> Hi Lucas,
> > DECLARE @.Region AS NVARCHAR(15)
> > SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>
>|||This is not true. The way it works, if you have not declared it then RS
knows that it is a query parameter and automatically creates a report
parameter for it. I used the below code against adventureworks and it works:
SELECT *
FROM sales.Customer
WHERE TerritoryID =CASE
WHEN @.Region = '2'
THEN 2
END
Use the generic query designer (the button to switch to generic mode is one
of the buttons to the right of the ...).
Execute the query, you should be prompted for a value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi Lucas,
>> DECLARE @.Region AS NVARCHAR(15)
>> SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>|||Bruce,
that is what i said... just not as well.
i just gave him the 'check' since i couldn't give it to myself.
--
Lucas Dargis
"Bruce L-C [MVP]" wrote:
> This is not true. The way it works, if you have not declared it then RS
> knows that it is a query parameter and automatically creates a report
> parameter for it. I used the below code against adventureworks and it works:
> SELECT *
> FROM sales.Customer
> WHERE TerritoryID => CASE
> WHEN @.Region = '2'
> THEN 2
> END
> Use the generic query designer (the button to switch to generic mode is one
> of the buttons to the right of the ...).
> Execute the query, you should be prompted for a value.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
> news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> > Hi Lucas,
> >
> >> DECLARE @.Region AS NVARCHAR(15)
> >> SELECT @.Region = '1'
> >
> > You are using a variable (@.Region) in your query and if you leave off the
> > top 2 lines, you are not declaring the variable and setting its value. The
> > statement needs this.
> >
> > HTH!
> >
> > Kind regards - Fred
> >
>
>

Paging in Stored Procedure

I got this code on internet for Stored Proc paging. This will work if my final result is the order of eployee id.
In my case my final result is in the order of emplyee name. What i have to change in below code so i can have paging in SQL plus my order is by name. I dont want to create Temp table. Becuase i think Creating temp table is overhead to sql server
and in that case i'll use Datagrid defualt paging and return all rows always.(My total number of rows will not be more than 300 and in 70% cases they are below 100)

CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@.startRowIndex int,
@.maximumRows int
)
AS

DECLARE @.first_id int, @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @.first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

i'm using SQL server 2000|||

If name is unique, then just change it to:

DECLARE @.first_name varchar(100), @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_name = name FROM employees ORDER BY name

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE name >= @.first_name
ORDER BY e.name

SET ROWCOUNT 0

If name is not unique, you might get some overlap, but it won't be a critical issue unless you have a lot of overlap. You could use two columns and include the employeeId for uniqueness, if you wanted to avoid overlapping rows.

|||name will not be Unique|||

This should work:

DECLARE @.first_name varchar(100), @.employeeId int, @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_name = name, @.employeeId = employeeId FROM employees ORDER BY name

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE name > @.first_name
or (name = @.first_name
and employeeId > @.employeeId)
ORDER BY e.name, e.employeeId

SET ROWCOUNT 0

Something like that...