Parameter Queries in Access Projects

Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:

SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]

How can we do this in Access projects for views or stored procedure or user defined functions?

ThanksYou are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.

Regards,

Hugh Scott

Originally posted by Sia
Hi, I am migrating an Access db to SQL Server.
I have a problem with my parameter queries.
In my access db, I have a lot of parameter quereis which I pass the parameters from forms, for example:

SELECT ... FROM... WHERE PID=[Forms]![frmPersonContact]![PersonID]

How can we do this in Access projects for views or stored procedure or user defined functions?

Thanks|||I know that its not easy but do you know of any sample that I can look at?

Thanks

Originally posted by hmscott
You are probably better off replacing them with stored procedures. It won't be entirely the same; you can't simply link the value from a form to the parameter in the query. There will likely be some code involved.

Regards,

Hugh Scott|||Consider this:

SQL Stored Procedure:

CREATE PROC spDoThis
@.Parm1 as Varchar(255),
@.Parm2 as Int

AS

UPDATE MyTable
SET Column1 = @.Parm1
WHERE ID = @.Parm2

ADO Code (in your form):

Private Function Update_Click()

Dim oConn, oComm

' Instantiate ActiveX objects
Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")

' Set connection string
oConn.ConnectionString="Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security=False;" & _
"Catalogue=MyDatabase;" & _
"Server=MyServer"

oConn.Open

' Set the ADO Command to the name of the stored proc
Set oComm.ActiveConnection = oConn

oComm.CommandText = "spDoThis"
oComm.Parameters.Refresh

' Assign the values to the stored proc parameters from your form
oComm.Parameters("Parm1") = [MyForm].[MyText].[Value]
oComm.Parameters("Parm2") = [MyForm].[MyRecordID].[Value]
oComm.Execute

' Clean up ActiveX objects
Set oComm = Nothing
oConn.Close
Set oConn = Nothing

End Function

Notes:
1. This was written from the top of my head in a hurry (ie, don't nag me about syntax).
2. I have not tested this.
3. Your mileage may vary.

Hope this helps.

Regards,

Hugh Scott
Originally posted by Sia
I know that its not easy but do you know of any sample that I can look at?

Thankssql

Parameter Queries

I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank you
See "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:

> I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how to
> do it in Query Designer. Is there a way to save this query with the set up
> parameters into a view so that I can have other users re-use it?
> thank you
|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank you

Parameter Queries

Is there a way in Transact-SQL to do a "runtime" parameter query within a MS
Access adp frontend? This is easily done in a pure MS Access mdb using the
square brackets, e.g. [ENTER Customer Number].Hi
SQL Server will not allow this, but you may want to ask in an access
newsgroup to find the best solution.
John
"Peter Marshall at OCC" wrote:

> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>
>|||If you call a SQL Server stored proc that takes parameters without passing a
ny MSAccess will contrive to prompt you for values. The
prompt will be the name of the parameter, with the window title of : "Enter
Parameter Value".
Good enough?
If you want to replace the prompt, you'll have to code
"Peter Marshall at OCC" <peter.marshall@.ohiocoatingscompany.com> wrote in message news:ubCn
sarvFHA.596@.TK2MSFTNGP12.phx.gbl...
> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>

Parameter Queries

I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank youSee "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:
> I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how to
> do it in Query Designer. Is there a way to save this query with the set up
> parameters into a view so that I can have other users re-use it?
> thank you|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank you

Parameter Queries

I am migrating a database from Access to SQL Server 2000. In Access, I had
several Parameter Queries set up so that users could specify a parameter
based on their need. I combed through Books on Line and figured out how to
do it in Query Designer. Is there a way to save this query with the set up
parameters into a view so that I can have other users re-use it?
thank youSee "Inline User-Defined Functions" in BOL.
AMB
"gmead7" wrote:

> I am migrating a database from Access to SQL Server 2000. In Access, I ha
d
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how t
o
> do it in Query Designer. Is there a way to save this query with the set u
p
> parameters into a view so that I can have other users re-use it?
> thank you|||A view acts like a table in SQL Server. E.G. "Select * from Table_View"
works if "Table_View" is a table or a view. There are no "parameters" that
you can specify for a view. If you want to have a query change based on
user input, I'd have to point you to stored procedures.
Scott
"gmead7" <gmead7@.discussions.microsoft.com> wrote in message
news:E6793343-5F16-4194-89D3-3A78F346971C@.microsoft.com...
>I am migrating a database from Access to SQL Server 2000. In Access, I had
> several Parameter Queries set up so that users could specify a parameter
> based on their need. I combed through Books on Line and figured out how
> to
> do it in Query Designer. Is there a way to save this query with the set
> up
> parameters into a view so that I can have other users re-use it?
> thank yousql

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 Entry on a report

I'm trying to graduate from Access to SQL and am finding that Access is still meeting my needs better than SQL.

So, I figure I'm doing something wrong.

In Access, I have queries that contain parameters. So far, I've gotten SQL to accept parameters, using =@.prompt. But, this seems to be a limited parameter capability. Number one, I can't use a phrase for a prompt. Number 2, I can't do something like Access' Between [upper] And [lower]; I've not found away to use parameters as bounds in SQL.

Any help?

In your SQL query for

Between [upper] And [lower]

You need to do something like

SELECT MYPARAMETER FROM MYTABLE WHERE STARTDATE BETWEEN @.StartDate AND @.EndDate

What do you mean by

Number one, I can't use a phrase for a prompt

If you mean that you can't enter text into a parameter, you need to declare the parameter to be varchar.

|||

Hmm.

I have to enter it as SQL code, not in the builder like I used to for Access?

I am a major novice at SQL, having used Access' query builder forever.

On the "phrase for a prompt" part, I meant something like, =@.Enter Start Date:.

|||

I think you can use query builder, but I've never used it. I just write the SQL query.

|||Perhaps you've already looked at these but here is a good basic good tutorial about parameters:

http://msdn2.microsoft.com/en-us/library/aa337432.aspx

|||Thanx, mrtrombone! I think that will help a lot.

Pagination question - retrieve only records for each page

I want to provide paged access to data. Rather than displaying the
entire contents of a report or query to an end user, I want to show
only a subset of records per web page, with controls for moving from
page to page. I have a large result set and only want to selectively
retrieve only those records that should be shown for the particular
page. I know how to do this in ASP.NET but I'm new to Report Server.
Can anyone point me in the right direction?
ThanksThis is default Reporting Services behavior.
RS will give you the functions to move from page to page.
You have to design the report to use parameters, and then add formatting to
the report to make it break over several pages.
If you have the SQL Server 2005 setup available, install the Report Designer
on your workstation and play around with it. (Or download a trial version of
SS 2005) There are lots of good articles to get you going. A good place to
start is with William Pearson's articles about Reporting Services at
http://www.databasejournal.com/article.php/1459531 (scroll down to find
them).
Kaisa M. Lindahl Lervik
"fparc" <fparc@.aol.com> wrote in message
news:1165461353.254431.13270@.j72g2000cwa.googlegroups.com...
>I want to provide paged access to data. Rather than displaying the
> entire contents of a report or query to an end user, I want to show
> only a subset of records per web page, with controls for moving from
> page to page. I have a large result set and only want to selectively
> retrieve only those records that should be shown for the particular
> page. I know how to do this in ASP.NET but I'm new to Report Server.
> Can anyone point me in the right direction?
> Thanks
>|||Thanks for the link. What a great resource. I was thinking of
pagination in terms of record retrieval instead of page breaks. I want
to query 50 records at a time so I don't tax the database. After
looking at it a bit I think I will try to use the TimeStamp field as
the index. So I put a text box that holds the Last timestamp of the
group of TOP 50. I make the textbox a 'Jump to URL' and pass the last
timestamp on the end of the URL to open the new report page. One
problem; I can't get the parameterize URL to work in Report Server. I'm
using the wrox Professional SQL Server 2005 Reporting Services book but
the URL format they give is wrong(a pre-release book no doubt). So now
I need to find a good simple test to see if I can get this working
first. Know of any good tutorials on passing params in URL for Report
Server?|||I got the parameterize URL to work in Report Server. I was wrong about
the wrox Professional SQL Server 2005 Reporting Services book being
wrong. I was using "http://server/SQLReports/Pages/Folder.aspx?"
instead of "http://server/ReportServer?/folder/file" in my Jump URL.
Now I have 12 parameters including the TimeStamp the user can choose
from. By choosing the Allow Blank Value property from the Report
Parameters Dialog Box, the user can fill in all or none of the
parameter text boxes for various searches - except the Timestamp which
defaults to =Now.
My SQL pulls TOP 50 records. When the user clicks on the 'Jump to URL'
text box the timestamp param of the 50th record is passed in the URL to
pull the next 50.
Now I have a type text column filled with XML in which there are 2
things we need to pull - an email and/or an event_code. My next step is
to change the table column from text to the XML type to speed up the
searches on that column. Here's what I've found so far.
http://www.developer.com/db/article.php/3531196
http://www.15seconds.com/issue/050803.htm
http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx
http://www.ftponline.com/vsm/2005_06/magazine/features/rjennings/

Pagination does not work after Table Visiblity false.? Help : Urgent ? Please

Hi,
You know I was trying to create new report and found that works fine with
Pagining- URL Access.
But I want to hide report when there is not data return. In that case my
pagining does not work.
For hiding my table when no data return i do following steps:-
Table -> Properties-> Visiblity Tag-> Expression ->
IIF(count(Fields!FormName.Value)=0,True,False)
Which hide my table when no data return, but paginition also does not work.
Please Help me. What shall i proceed with these things..
Waiting for Response.
Thanks
Labhesh S
Bangalore

In This.

1. I want to hide my details table which return data , when there is no data returned.

2. I want pagination should work when there is data. But is it not happening. ?

3. Pagination is not happening because i have written code in expression for Hiding the table when there is no data.

But i need Pagination also.

Please help..

Labhesh Shrimali

Bangalore

|||

HTML renderer and Preview (which are soft page break renderers) will ignore page breaks of conditionally hidden items and their children.

PrintPreview , Image and PDF renderers (which are physical page break renderers) will respect these page breaks.

If there is no data for the table, than we will render:

NoRowsMessage property if you defined one

or

table header and footer

What is the case for you? What are trying to achieve by hidden the table?

If you are in case 2 and you don't want the header and footer, you can set their visibility instead of the table.

Thank you,

Nico