Hey All,
I am hoping that I will get a speedy response to this problem. I am working
in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
multivalue. They work fine, with the following exception:
I'm trying to set an expression in a text box on the report, to display back
to the users which parameters they selected prior to viewing the report.
This works fine for non-multivalue parameters. But, with the multivalue
parameters, I keep getting an error about the '&' not being valid. The
report builds fine, it only errors when I try to preview the report.
Here's the expression in my textbox:
="Report Parameters Entered:" & vbCrLf & "Retailer: " &
Parameters!Retailer.Value & vbCrLf & "Month/Year: " & Parameters!Month.Value
& "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
Parameters!Managed.Value
If I change the parameters in this to .IsMultiValue (for the ones that are
multivalue), the report runs fine. But, the actual values selected are not
returned. What shows up in the report is 'True' for each of the multivalue
parameters (because they are multivalue and I'm using the IsMultiValue?).
Anyway, if anyone knows of a work around for this, or if I am just way off
base...PLEASE let me know as soon as possible.
Thanks so much for your response!
SandiMaybe this tip helps?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481987&SiteID=1
Kaisa M. Lindahl Lervik
"SandiB" <SandiB@.discussions.microsoft.com> wrote in message
news:C87FE985-C985-4C3F-B047-53338EFF7A41@.microsoft.com...
> Hey All,
> I am hoping that I will get a speedy response to this problem. I am
> working
> in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
> multivalue. They work fine, with the following exception:
> I'm trying to set an expression in a text box on the report, to display
> back
> to the users which parameters they selected prior to viewing the report.
> This works fine for non-multivalue parameters. But, with the multivalue
> parameters, I keep getting an error about the '&' not being valid. The
> report builds fine, it only errors when I try to preview the report.
> Here's the expression in my textbox:
> ="Report Parameters Entered:" & vbCrLf & "Retailer: " &
> Parameters!Retailer.Value & vbCrLf & "Month/Year: " &
> Parameters!Month.Value
> & "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
> Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
> Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
> Parameters!Managed.Value
> If I change the parameters in this to .IsMultiValue (for the ones that are
> multivalue), the report runs fine. But, the actual values selected are
> not
> returned. What shows up in the report is 'True' for each of the
> multivalue
> parameters (because they are multivalue and I'm using the IsMultiValue?).
> Anyway, if anyone knows of a work around for this, or if I am just way off
> base...PLEASE let me know as soon as possible.
> Thanks so much for your response!
> Sandi|||Thanks so much! This worked perfectly!
"Kaisa M. Lindahl Lervik" wrote:
> Maybe this tip helps?
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481987&SiteID=1
> Kaisa M. Lindahl Lervik
> "SandiB" <SandiB@.discussions.microsoft.com> wrote in message
> news:C87FE985-C985-4C3F-B047-53338EFF7A41@.microsoft.com...
> > Hey All,
> >
> > I am hoping that I will get a speedy response to this problem. I am
> > working
> > in RS in SQL 2005. I have a report that has 6 parameters, 3 of these are
> > multivalue. They work fine, with the following exception:
> >
> > I'm trying to set an expression in a text box on the report, to display
> > back
> > to the users which parameters they selected prior to viewing the report.
> > This works fine for non-multivalue parameters. But, with the multivalue
> > parameters, I keep getting an error about the '&' not being valid. The
> > report builds fine, it only errors when I try to preview the report.
> >
> > Here's the expression in my textbox:
> >
> > ="Report Parameters Entered:" & vbCrLf & "Retailer: " &
> > Parameters!Retailer.Value & vbCrLf & "Month/Year: " &
> > Parameters!Month.Value
> > & "/" & Parameters!Year.Value & vbCrLf & "Store(s): " &
> > Parameters!StoreID.Value & vbCrLf & "Super Categories: " &
> > Parameters!SuperCategory.Value & vbCrLf & "Managed Indicator: " &
> > Parameters!Managed.Value
> >
> > If I change the parameters in this to .IsMultiValue (for the ones that are
> > multivalue), the report runs fine. But, the actual values selected are
> > not
> > returned. What shows up in the report is 'True' for each of the
> > multivalue
> > parameters (because they are multivalue and I'm using the IsMultiValue?).
> >
> > Anyway, if anyone knows of a work around for this, or if I am just way off
> > base...PLEASE let me know as soon as possible.
> >
> > Thanks so much for your response!
> >
> > Sandi
>
>|||'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.Sync.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\ISOJC111\ISOJC\bin\Debug\ISOJC.vshost.exe', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.CrystalReports.Engine\10.2.3600.0__692fbea5521e1304\CrystalDecisions.CrystalReports.Engine.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC\CrystalDecisions.Enterprise.Framework\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Enterprise.Framework.dll', No symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC\CrystalDecisions.Enterprise.InfoStore\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Enterprise.InfoStore.dll', No symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.ReportSource\10.2.3600.0__692fbea5521e1304\CrystalDecisions.ReportSource.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Shared\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Shared.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\CrystalDecisions.Windows.Forms\10.2.3600.0__692fbea5521e1304\CrystalDecisions.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.Common\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.Common.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.WinForms\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.WinForms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualBasic.PowerPacks\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.PowerPacks.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Deployment\2.0.0.0__b03f5f7f11d50a3a\System.Deployment.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Design\2.0.0.0__b03f5f7f11d50a3a\System.Design.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The thread 0x12ac has exited with code 0 (0x0).
The thread 0x1650 has exited with code 0 (0x0).
The thread 0xdc has exited with code 0 (0x0).
'ISOJC.vshost.exe' (Managed): Loaded 'C:\ISOJC111\ISOJC\bin\Debug\ISOJC.exe', Symbols loaded.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Runtime.Remoting\2.0.0.0__b77a5c561934e089\System.Runtime.Remoting.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.ProcessingObjectModel\8.0.0.0__b03f5f7f11d50a3a\Microsoft.ReportViewer.ProcessingObjectModel.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'ISOJC.vshost.exe' (Managed): Loaded 'expression_host_10e7fe2ca2834f48ab7700ebecf77268', No symbols loaded.
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0x1730 has exited with code 0 (0x0).
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0x380 has exited with code 0 (0x0).
Warning: The Value expression for the textbox ?textbox27? contains an error: Input string was not in a correct format. (rsRuntimeErrorInExpression)
The thread 0xd24 has exited with code 0 (0x0).
The thread 0x17ec has exited with code 0 (0x0).
The thread 0x133c has exited with code 0 (0x0).
The program '[5820] ISOJC.vshost.exe: Managed' has exited with code 0 (0x0).
From http://www.google.co.in/search?q=rsRuntimeErrorInExpression&hl=en&start=10&sa=N
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts
Sunday, March 11, 2012
Saturday, February 25, 2012
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2
Heres my code:
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddLinks 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("links.mdb")
'Create an ADO recordset object
Set rsAddLinks = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tblLinks.URL, tblLinks.Description FROM tblLinks;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddLinks.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddLinks.LockType = 3
'Open the recordset with the SQL query
rsAddLinks.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddLinks.AddNew
'Add a new record to the recordset
rsAddLinks.Fields("URL") = Request.Form("url")
rsAddLinks.Fields("Description") = Request.Form("description")
'Write the updated recordset to the database
rsAddLinks.Update
'Reset server objects
rsAddLinks.Close
Set rsAddLinks = Nothing
Set adoCon = Nothing
'Redirect to the guestbook.asp page
Response.Redirect "links.asp"
%>
When i run the code I get: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2
Can someone please help?
--NickTry replacing % in place of *.|||Ok, thanks, but now it says
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Can you help me on this?
--Nick
PS: If your wondering, im reletively new at asp thats why I havent been able to figure this out myself.|||By all means refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/3/77.asp&NoWebContent=1) to get rid of the issues and follwup.|||That really didn't help me much.|||thanks for all your help. I figured it out! i was linking to the page directly when i should have been using the page b4 it that links to it...
if that makes any scence
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddLinks 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("links.mdb")
'Create an ADO recordset object
Set rsAddLinks = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tblLinks.URL, tblLinks.Description FROM tblLinks;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddLinks.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddLinks.LockType = 3
'Open the recordset with the SQL query
rsAddLinks.Open strSQL, adoCon
'Tell the recordset we are adding a new record to it
rsAddLinks.AddNew
'Add a new record to the recordset
rsAddLinks.Fields("URL") = Request.Form("url")
rsAddLinks.Fields("Description") = Request.Form("description")
'Write the updated recordset to the database
rsAddLinks.Update
'Reset server objects
rsAddLinks.Close
Set rsAddLinks = Nothing
Set adoCon = Nothing
'Redirect to the guestbook.asp page
Response.Redirect "links.asp"
%>
When i run the code I get: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2
Can someone please help?
--NickTry replacing % in place of *.|||Ok, thanks, but now it says
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Can you help me on this?
--Nick
PS: If your wondering, im reletively new at asp thats why I havent been able to figure this out myself.|||By all means refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/3/77.asp&NoWebContent=1) to get rid of the issues and follwup.|||That really didn't help me much.|||thanks for all your help. I figured it out! i was linking to the page directly when i should have been using the page b4 it that links to it...
if that makes any scence
Friday, February 24, 2012
[Feature Request] Jump to Report (Choice of report to the user)
I am trying to build a report with a "jump to report" (with all the
applicable parameters) effect in a table.
But based on user's choice - I somehow want to let them choose which
report they can jump on.
How may I present them the choices?
One idea I had was to create a dummy report that stores all the
parameters, and then provide different report options...You might want to use jump to url instead. Here is an idea: You have a
report that shows the values you want to have the user select from. Just
show it in a table. Have one of the fields (could be an extra field that
says, Detail ... or something like that. Make it blue and underlined. Then
set the advanced properties and use the jump to URL setting the appropriate
parameters. Depending on how complicated it is you might want to create code
behind that assembles the URL string for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Harsh" <harsh.vb@.gmail.com> wrote in message
news:1e70ecdd.0411130826.3d172549@.posting.google.com...
>I am trying to build a report with a "jump to report" (with all the
> applicable parameters) effect in a table.
> But based on user's choice - I somehow want to let them choose which
> report they can jump on.
> How may I present them the choices?
>
> One idea I had was to create a dummy report that stores all the
> parameters, and then provide different report options...|||In Jump to dialog box, where it asked the name of the report to jump to, do
an
IIF(Fields!xxx.Value = "A", "AReport", IIF(Fields!xxx.Value = "B",
"BReport", "")
"Harsh" wrote:
> I am trying to build a report with a "jump to report" (with all the
> applicable parameters) effect in a table.
> But based on user's choice - I somehow want to let them choose which
> report they can jump on.
> How may I present them the choices?
>
> One idea I had was to create a dummy report that stores all the
> parameters, and then provide different report options...
>
applicable parameters) effect in a table.
But based on user's choice - I somehow want to let them choose which
report they can jump on.
How may I present them the choices?
One idea I had was to create a dummy report that stores all the
parameters, and then provide different report options...You might want to use jump to url instead. Here is an idea: You have a
report that shows the values you want to have the user select from. Just
show it in a table. Have one of the fields (could be an extra field that
says, Detail ... or something like that. Make it blue and underlined. Then
set the advanced properties and use the jump to URL setting the appropriate
parameters. Depending on how complicated it is you might want to create code
behind that assembles the URL string for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Harsh" <harsh.vb@.gmail.com> wrote in message
news:1e70ecdd.0411130826.3d172549@.posting.google.com...
>I am trying to build a report with a "jump to report" (with all the
> applicable parameters) effect in a table.
> But based on user's choice - I somehow want to let them choose which
> report they can jump on.
> How may I present them the choices?
>
> One idea I had was to create a dummy report that stores all the
> parameters, and then provide different report options...|||In Jump to dialog box, where it asked the name of the report to jump to, do
an
IIF(Fields!xxx.Value = "A", "AReport", IIF(Fields!xxx.Value = "B",
"BReport", "")
"Harsh" wrote:
> I am trying to build a report with a "jump to report" (with all the
> applicable parameters) effect in a table.
> But based on user's choice - I somehow want to let them choose which
> report they can jump on.
> How may I present them the choices?
>
> One idea I had was to create a dummy report that stores all the
> parameters, and then provide different report options...
>
Thursday, February 16, 2012
[database_name . [schema_name ] . | schema_name . ] table_name
Hi,
I try to create 2 schema as the syntax display's as below, but I get a error
of only use 1. Do I have to change any parameters in SQL 2005?
And will I get any other problems in the future to use 2 schema?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a
> error
> of only use 1.
Can you show the ACTUAL syntax you tried, and the EXACT error message?
The syntax you show demonstrates two options:
CREATE TABLE master.schema1.table
CREATE TABLE schema1.table
It sounds like you maybe misinterpreted it as:
CREATE TABLE master.schema1.schema2.table
?
> Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name|||The schema concept is not hierarchical. An object is contained in a schema,
not a level of schemas.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RBC" <RBC@.discussions.microsoft.com> wrote in message
news:DD3B310F-B41C-45E5-AEC9-C5F2DD1A12C4@.microsoft.com...
> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a err
or
> of only use 1. Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name
I try to create 2 schema as the syntax display's as below, but I get a error
of only use 1. Do I have to change any parameters in SQL 2005?
And will I get any other problems in the future to use 2 schema?
Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a
> error
> of only use 1.
Can you show the ACTUAL syntax you tried, and the EXACT error message?
The syntax you show demonstrates two options:
CREATE TABLE master.schema1.table
CREATE TABLE schema1.table
It sounds like you maybe misinterpreted it as:
CREATE TABLE master.schema1.schema2.table
?
> Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name|||The schema concept is not hierarchical. An object is contained in a schema,
not a level of schemas.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RBC" <RBC@.discussions.microsoft.com> wrote in message
news:DD3B310F-B41C-45E5-AEC9-C5F2DD1A12C4@.microsoft.com...
> Hi,
> I try to create 2 schema as the syntax display's as below, but I get a err
or
> of only use 1. Do I have to change any parameters in SQL 2005?
> And will I get any other problems in the future to use 2 schema?
> Syntax
> CREATE TABLE
> [ database_name . [ schema_name ] . | schema_name . ] table_name
[bug?] Hidden parameters
Hello,
I have troubles with hidden parameters. What am I doing:
1) define a report parameter based on a dataset's field (a dataset is
"Stored proc." type, the procedure returns the only string value -- if it is
important)
2) Build & deploy a solution, run the report from Report manager -- it's ok
3) run the report from Report manager, click on "Properties" tab, choose
"parameters":
Has Default - leave checked on
Default Value - (Query Based)
Null - (none)
Prompt User - leave checked on
Prompt String - I cleared this textbox
4) run the report again:
Reporting Services Error
Default value or value provided for the report parameter 'MyParam' is not a
valid value. (rsInvalidReportParameter) Get Online Help
Refresh button on RM or the IE's same button don't change this error message
5) Properties -> Parameters -> Prompt String: type a former prompt string
6) run the report: it works ok, however the aforementioned parameter acts
like it doesn't have Default Value anymore.
What have I do to hide param properly?
(MS RS sp1)
Microsoft SQL Server Reporting Services Version 8.00.878.00
Thanks,
Lucy.Does it have list of available values?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have troubles with hidden parameters. What am I doing:
> 1) define a report parameter based on a dataset's field (a dataset is
> "Stored proc." type, the procedure returns the only string value -- if it
> is
> important)
> 2) Build & deploy a solution, run the report from Report manager -- it's
> ok
> 3) run the report from Report manager, click on "Properties" tab, choose
> "parameters":
> Has Default - leave checked on
> Default Value - (Query Based)
> Null - (none)
> Prompt User - leave checked on
> Prompt String - I cleared this textbox
> 4) run the report again:
> Reporting Services Error
> Default value or value provided for the report parameter 'MyParam' is not
> a
> valid value. (rsInvalidReportParameter) Get Online Help
> Refresh button on RM or the IE's same button don't change this error
> message
> 5) Properties -> Parameters -> Prompt String: type a former prompt string
> 6) run the report: it works ok, however the aforementioned parameter acts
> like it doesn't have Default Value anymore.
> What have I do to hide param properly?
> (MS RS sp1)
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> Thanks,
> Lucy.
>|||Yes. From query. Same to "default".
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Does it have list of available values?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I have troubles with hidden parameters. What am I doing:
> >
> > 1) define a report parameter based on a dataset's field (a dataset is
> > "Stored proc." type, the procedure returns the only string value -- if
it
> > is
> > important)
> > 2) Build & deploy a solution, run the report from Report manager -- it's
> > ok
> > 3) run the report from Report manager, click on "Properties" tab, choose
> > "parameters":
> > Has Default - leave checked on
> > Default Value - (Query Based)
> > Null - (none)
> > Prompt User - leave checked on
> > Prompt String - I cleared this textbox
> > 4) run the report again:
> >
> > Reporting Services Error
> > Default value or value provided for the report parameter 'MyParam' is
not
> > a
> > valid value. (rsInvalidReportParameter) Get Online Help
> >
> > Refresh button on RM or the IE's same button don't change this error
> > message
> >
> > 5) Properties -> Parameters -> Prompt String: type a former prompt
string
> > 6) run the report: it works ok, however the aforementioned parameter
acts
> > like it doesn't have Default Value anymore.
> >
> > What have I do to hide param properly?
> >
> > (MS RS sp1)
> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >
> > Thanks,
> > Lucy.
> >
> >
>|||To summarize, error rsInvalidReportParameter happened because same stored
procedure was used for available values and default value, and this stored
procedure returned unique result for every call.
Report Server was executing this stored procedure twice (1st time for
available values and 2nd time for default value) and was unable to match
default value with available value.
Solution is not to set available values.
Also because you just wanted to have read-only parameter, it is enough to
leave prompt blank, and Report Desinger would set parameter properties
during Deploy procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Yes. From query. Same to "default".
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
>> Does it have list of available values?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lusy Crown" <evesq@.uk2.net> wrote in message
>> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
>> > Hello,
>> >
>> > I have troubles with hidden parameters. What am I doing:
>> >
>> > 1) define a report parameter based on a dataset's field (a dataset is
>> > "Stored proc." type, the procedure returns the only string value -- if
> it
>> > is
>> > important)
>> > 2) Build & deploy a solution, run the report from Report manager --
>> > it's
>> > ok
>> > 3) run the report from Report manager, click on "Properties" tab,
>> > choose
>> > "parameters":
>> > Has Default - leave checked on
>> > Default Value - (Query Based)
>> > Null - (none)
>> > Prompt User - leave checked on
>> > Prompt String - I cleared this textbox
>> > 4) run the report again:
>> >
>> > Reporting Services Error
>> > Default value or value provided for the report parameter 'MyParam' is
> not
>> > a
>> > valid value. (rsInvalidReportParameter) Get Online Help
>> >
>> > Refresh button on RM or the IE's same button don't change this error
>> > message
>> >
>> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> string
>> > 6) run the report: it works ok, however the aforementioned parameter
> acts
>> > like it doesn't have Default Value anymore.
>> >
>> > What have I do to hide param properly?
>> >
>> > (MS RS sp1)
>> > Microsoft SQL Server Reporting Services Version 8.00.878.00
>> >
>> > Thanks,
>> > Lucy.
>> >
>> >
>>
>|||How do you not set available values...in report designer report params
dialog, my parameter has a name, 'tick', has the non-queried available values
radio button clicked and the list to the right is completely blank, as I want
it to be. This parameter is unique everytime a report is executed and cannot
be selected from a list of existing values...but I'm still getting the
following error when I call the web service render method...
+ System.SystemException {"The value provided for the report parameter
'tick' is not valid for its type. --> The value provided for the report
parameter 'tick' is not valid for its type. --> The value provided for the
report parameter 'tick' is not valid for its type."} System.SystemException
"Lev Semenets [MSFT]" wrote:
> To summarize, error rsInvalidReportParameter happened because same stored
> procedure was used for available values and default value, and this stored
> procedure returned unique result for every call.
> Report Server was executing this stored procedure twice (1st time for
> available values and 2nd time for default value) and was unable to match
> default value with available value.
> Solution is not to set available values.
> Also because you just wanted to have read-only parameter, it is enough to
> leave prompt blank, and Report Desinger would set parameter properties
> during Deploy procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Yes. From query. Same to "default".
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> >> Does it have list of available values?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Lusy Crown" <evesq@.uk2.net> wrote in message
> >> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> >> > Hello,
> >> >
> >> > I have troubles with hidden parameters. What am I doing:
> >> >
> >> > 1) define a report parameter based on a dataset's field (a dataset is
> >> > "Stored proc." type, the procedure returns the only string value -- if
> > it
> >> > is
> >> > important)
> >> > 2) Build & deploy a solution, run the report from Report manager --
> >> > it's
> >> > ok
> >> > 3) run the report from Report manager, click on "Properties" tab,
> >> > choose
> >> > "parameters":
> >> > Has Default - leave checked on
> >> > Default Value - (Query Based)
> >> > Null - (none)
> >> > Prompt User - leave checked on
> >> > Prompt String - I cleared this textbox
> >> > 4) run the report again:
> >> >
> >> > Reporting Services Error
> >> > Default value or value provided for the report parameter 'MyParam' is
> > not
> >> > a
> >> > valid value. (rsInvalidReportParameter) Get Online Help
> >> >
> >> > Refresh button on RM or the IE's same button don't change this error
> >> > message
> >> >
> >> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> > string
> >> > 6) run the report: it works ok, however the aforementioned parameter
> > acts
> >> > like it doesn't have Default Value anymore.
> >> >
> >> > What have I do to hide param properly?
> >> >
> >> > (MS RS sp1)
> >> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >> >
> >> > Thanks,
> >> > Lucy.
> >> >
> >> >
> >>
> >>
> >
> >
>
>
I have troubles with hidden parameters. What am I doing:
1) define a report parameter based on a dataset's field (a dataset is
"Stored proc." type, the procedure returns the only string value -- if it is
important)
2) Build & deploy a solution, run the report from Report manager -- it's ok
3) run the report from Report manager, click on "Properties" tab, choose
"parameters":
Has Default - leave checked on
Default Value - (Query Based)
Null - (none)
Prompt User - leave checked on
Prompt String - I cleared this textbox
4) run the report again:
Reporting Services Error
Default value or value provided for the report parameter 'MyParam' is not a
valid value. (rsInvalidReportParameter) Get Online Help
Refresh button on RM or the IE's same button don't change this error message
5) Properties -> Parameters -> Prompt String: type a former prompt string
6) run the report: it works ok, however the aforementioned parameter acts
like it doesn't have Default Value anymore.
What have I do to hide param properly?
(MS RS sp1)
Microsoft SQL Server Reporting Services Version 8.00.878.00
Thanks,
Lucy.Does it have list of available values?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have troubles with hidden parameters. What am I doing:
> 1) define a report parameter based on a dataset's field (a dataset is
> "Stored proc." type, the procedure returns the only string value -- if it
> is
> important)
> 2) Build & deploy a solution, run the report from Report manager -- it's
> ok
> 3) run the report from Report manager, click on "Properties" tab, choose
> "parameters":
> Has Default - leave checked on
> Default Value - (Query Based)
> Null - (none)
> Prompt User - leave checked on
> Prompt String - I cleared this textbox
> 4) run the report again:
> Reporting Services Error
> Default value or value provided for the report parameter 'MyParam' is not
> a
> valid value. (rsInvalidReportParameter) Get Online Help
> Refresh button on RM or the IE's same button don't change this error
> message
> 5) Properties -> Parameters -> Prompt String: type a former prompt string
> 6) run the report: it works ok, however the aforementioned parameter acts
> like it doesn't have Default Value anymore.
> What have I do to hide param properly?
> (MS RS sp1)
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> Thanks,
> Lucy.
>|||Yes. From query. Same to "default".
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> Does it have list of available values?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I have troubles with hidden parameters. What am I doing:
> >
> > 1) define a report parameter based on a dataset's field (a dataset is
> > "Stored proc." type, the procedure returns the only string value -- if
it
> > is
> > important)
> > 2) Build & deploy a solution, run the report from Report manager -- it's
> > ok
> > 3) run the report from Report manager, click on "Properties" tab, choose
> > "parameters":
> > Has Default - leave checked on
> > Default Value - (Query Based)
> > Null - (none)
> > Prompt User - leave checked on
> > Prompt String - I cleared this textbox
> > 4) run the report again:
> >
> > Reporting Services Error
> > Default value or value provided for the report parameter 'MyParam' is
not
> > a
> > valid value. (rsInvalidReportParameter) Get Online Help
> >
> > Refresh button on RM or the IE's same button don't change this error
> > message
> >
> > 5) Properties -> Parameters -> Prompt String: type a former prompt
string
> > 6) run the report: it works ok, however the aforementioned parameter
acts
> > like it doesn't have Default Value anymore.
> >
> > What have I do to hide param properly?
> >
> > (MS RS sp1)
> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >
> > Thanks,
> > Lucy.
> >
> >
>|||To summarize, error rsInvalidReportParameter happened because same stored
procedure was used for available values and default value, and this stored
procedure returned unique result for every call.
Report Server was executing this stored procedure twice (1st time for
available values and 2nd time for default value) and was unable to match
default value with available value.
Solution is not to set available values.
Also because you just wanted to have read-only parameter, it is enough to
leave prompt blank, and Report Desinger would set parameter properties
during Deploy procedure.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Yes. From query. Same to "default".
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
>> Does it have list of available values?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lusy Crown" <evesq@.uk2.net> wrote in message
>> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
>> > Hello,
>> >
>> > I have troubles with hidden parameters. What am I doing:
>> >
>> > 1) define a report parameter based on a dataset's field (a dataset is
>> > "Stored proc." type, the procedure returns the only string value -- if
> it
>> > is
>> > important)
>> > 2) Build & deploy a solution, run the report from Report manager --
>> > it's
>> > ok
>> > 3) run the report from Report manager, click on "Properties" tab,
>> > choose
>> > "parameters":
>> > Has Default - leave checked on
>> > Default Value - (Query Based)
>> > Null - (none)
>> > Prompt User - leave checked on
>> > Prompt String - I cleared this textbox
>> > 4) run the report again:
>> >
>> > Reporting Services Error
>> > Default value or value provided for the report parameter 'MyParam' is
> not
>> > a
>> > valid value. (rsInvalidReportParameter) Get Online Help
>> >
>> > Refresh button on RM or the IE's same button don't change this error
>> > message
>> >
>> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> string
>> > 6) run the report: it works ok, however the aforementioned parameter
> acts
>> > like it doesn't have Default Value anymore.
>> >
>> > What have I do to hide param properly?
>> >
>> > (MS RS sp1)
>> > Microsoft SQL Server Reporting Services Version 8.00.878.00
>> >
>> > Thanks,
>> > Lucy.
>> >
>> >
>>
>|||How do you not set available values...in report designer report params
dialog, my parameter has a name, 'tick', has the non-queried available values
radio button clicked and the list to the right is completely blank, as I want
it to be. This parameter is unique everytime a report is executed and cannot
be selected from a list of existing values...but I'm still getting the
following error when I call the web service render method...
+ System.SystemException {"The value provided for the report parameter
'tick' is not valid for its type. --> The value provided for the report
parameter 'tick' is not valid for its type. --> The value provided for the
report parameter 'tick' is not valid for its type."} System.SystemException
"Lev Semenets [MSFT]" wrote:
> To summarize, error rsInvalidReportParameter happened because same stored
> procedure was used for available values and default value, and this stored
> procedure returned unique result for every call.
> Report Server was executing this stored procedure twice (1st time for
> available values and 2nd time for default value) and was unable to match
> default value with available value.
> Solution is not to set available values.
> Also because you just wanted to have read-only parameter, it is enough to
> leave prompt blank, and Report Desinger would set parameter properties
> during Deploy procedure.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:OAYt06paEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Yes. From query. Same to "default".
> >
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%2300zztpaEHA.3352@.TK2MSFTNGP12.phx.gbl...
> >> Does it have list of available values?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Lusy Crown" <evesq@.uk2.net> wrote in message
> >> news:OBEtp5laEHA.3892@.TK2MSFTNGP10.phx.gbl...
> >> > Hello,
> >> >
> >> > I have troubles with hidden parameters. What am I doing:
> >> >
> >> > 1) define a report parameter based on a dataset's field (a dataset is
> >> > "Stored proc." type, the procedure returns the only string value -- if
> > it
> >> > is
> >> > important)
> >> > 2) Build & deploy a solution, run the report from Report manager --
> >> > it's
> >> > ok
> >> > 3) run the report from Report manager, click on "Properties" tab,
> >> > choose
> >> > "parameters":
> >> > Has Default - leave checked on
> >> > Default Value - (Query Based)
> >> > Null - (none)
> >> > Prompt User - leave checked on
> >> > Prompt String - I cleared this textbox
> >> > 4) run the report again:
> >> >
> >> > Reporting Services Error
> >> > Default value or value provided for the report parameter 'MyParam' is
> > not
> >> > a
> >> > valid value. (rsInvalidReportParameter) Get Online Help
> >> >
> >> > Refresh button on RM or the IE's same button don't change this error
> >> > message
> >> >
> >> > 5) Properties -> Parameters -> Prompt String: type a former prompt
> > string
> >> > 6) run the report: it works ok, however the aforementioned parameter
> > acts
> >> > like it doesn't have Default Value anymore.
> >> >
> >> > What have I do to hide param properly?
> >> >
> >> > (MS RS sp1)
> >> > Microsoft SQL Server Reporting Services Version 8.00.878.00
> >> >
> >> > Thanks,
> >> > Lucy.
> >> >
> >> >
> >>
> >>
> >
> >
>
>
Saturday, February 11, 2012
@original and stored procedures
When using @.original_{0}, how does this work if you are using a stored procedure instead of inline SQL? For example, if my update parameters are like this :
<
UpdateParameters><asp:ParameterType="String"Name="ProductType"/><asp:ParameterType="String"Name="OpportunityType"/><asp:ParameterType="Double"Name="Value"/><asp:ParameterType="String"Name="Probability"/><asp:ParameterType="Int32"Name="OpportunityID"/></UpdateParameters>
And my stored procedure like this :
CREATE procedure dbo.UpdateOpportunity
(
@.ProductType int,
@.OpportunityType int,
@.Value money,
@.Probability int,
@.OpportunityID int
)
as
update Opportunity
set ProductType = @.ProductType, OpportunityType = @.OpportunityType,
[Value] = @.Value, Probability = @.Probability
where OpportunityID = @.OpportunityID
Do I need to change both cases of @.OpportunityID in my stored procedure to @.original_OpportunityID for it to work?
Most likely. It also depends on what you have set for the conflictdetection property. If it's compareallvalues, then you'll need to accept @.original_ for each of the columns that you selected via your select statement.@NumericVal IS NOT NULL seems not to return a boolean.
I'm trying to ensure that only of the parameters is passed to my stored procedure.
BOL says that the IS [NOT] NULL operator (language construct?) will return a boolean. An IF statement takes an expression which results in a boolean so I was surprised to find that the below code doesn't parse.
CREATE PROC sp_OneParm(
@.NumericVal float = null,
@.StringVal nvarchar(200) = null,
@.DateVal datetime = null,
@.BitVal bit = null)
AS
DECLARE @.ValCount tinyint
SET @.ValCount = 0
-- Ensure we've only got one update value specified
IF @.NumericVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.StringVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.DateVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.BitVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.ValCount > 1 RAISERROR ('Only one @.*Val paramater may be specified when calling sp_OneParm()', 16, 1)
-- Other Stuff
GO
Am I missing something simple or do I need to restructure my code to achieve the logic I want?Sure :)
IF @.NumericVal IS NOT NULL SET @.ValCount = @.ValCount + 1|||Bugger. :o
Thank-you roac.
BOL says that the IS [NOT] NULL operator (language construct?) will return a boolean. An IF statement takes an expression which results in a boolean so I was surprised to find that the below code doesn't parse.
CREATE PROC sp_OneParm(
@.NumericVal float = null,
@.StringVal nvarchar(200) = null,
@.DateVal datetime = null,
@.BitVal bit = null)
AS
DECLARE @.ValCount tinyint
SET @.ValCount = 0
-- Ensure we've only got one update value specified
IF @.NumericVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.StringVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.DateVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.BitVal IS NOT NULL @.ValCount = @.ValCount + 1
IF @.ValCount > 1 RAISERROR ('Only one @.*Val paramater may be specified when calling sp_OneParm()', 16, 1)
-- Other Stuff
GO
Am I missing something simple or do I need to restructure my code to achieve the logic I want?Sure :)
IF @.NumericVal IS NOT NULL SET @.ValCount = @.ValCount + 1|||Bugger. :o
Thank-you roac.
Thursday, February 9, 2012
@@Rowcount or output parameters error
I have the following stored procedure working with an Access 2000 front end. The output parameters returned to Access are both Null when the record is successfully updated (ie when @.@.Rowcount = 1), but the correct parameters are returned when the update fails. I'm a bit new to using output parameters, but I have them working perfectly with an insert sproc, and they look basically the same. What bonehead error have I made here? The fact that the record is updated indicates to me that the Commit Trans line is being executed, so why aren't the 2 output parameters set?
TIA
EDIT: Solved, sort of. I found that dropping the "@.ResNum +" from "@.ResNum + ' Updated'" resolved the problem (@.ResNum is an input parameter). This implies that the variable lost its value between the SQL statement and the If/Then, since the SQL correctly updates only the appropriate record from the WHERE clause. Is this supposed to happen? I looked in BOL, and if it's addressed there I missed it.
CREATE PROCEDURE [procResUpdate]
Various input parameters here,
@.RetCode as int Output, @.RetResNum as nvarchar(15) Output
AS
Declare @.RowCounter int
Begin Tran
UPDATE tblReservations
SET Various set statements here, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified
SELECT @.RowCounter = @.@.ROWCOUNT
If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = @.ResNum + ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = 'Update Failed'
End
GOPost the complete DDL for the proc (including @.ResNum) and a sample of calling it that produces the problem you describe.
Regards,
hmscott|||Here's the complete sproc (I added back the offending part in red):
CREATE PROCEDURE [procResUpdate]
@.ReqDate as datetime, @.PassName as nvarchar(25), @.DispDate as datetime, @.PassPhone as nvarchar(25),
@.PassQuant as smallint, @.AuthBy as nvarchar(25), @.AcctID as smallint, @.PuLandmark as smallint, @.PuStreet as nvarchar(50),
@.PuCity as smallint, @.PuXStreet as nvarchar(50), @.PuPoint as nvarchar(50), @.DestLandmark as smallint, @.DestStreet as nvarchar(50),
@.DestCity as smallint, @.DestXStreet as nvarchar(50), @.DestPoint as nvarchar(50), @.Operator as smallint, @.Comments as nvarchar(250),
@.DriverReq as nvarchar(25), @.CarType as smallint, @.EstHours as money, @.EstPrice as money, @.CCType as nvarchar(25), @.CCnum as nvarchar(25),
@.CCAuthNum as nvarchar(25), @.CarQuant as tinyint, @.ResNum as int, @.Status as smallint, @.LastModified as tinyint,
@.RetCode as int = Null Output, @.RetResNum as nvarchar(15) = Null Output
AS
Declare @.RowCounter int
Begin Tran
UPDATE tblReservations
SET ReqDateTime = @.ReqDate, PassName = @.PassName, DispDateTime = @.DispDate, PassPhone = @.PassPhone, PassQuant = @.PassQuant, AuthBy = @.AuthBy, AcctID = @.AcctID,
PuLandmarkID = @.PuLandmark, PuStreet = @.PuStreet, PuCityID = @.PuCity, PuXStreet = @.PuXStreet, PuPoint = @.PuPoint, DestLandmarkID = @.DestLandmark, DestStreet = @.DestStreet,
DestCityID = @.DestCity, DestXStreet = @.DestXStreet, DestPoint = @.DestPoint, OperatorID = @.Operator, Comments = @.Comments, DriverReq = @.DriverReq, CarType = @.CarType,
EstHours = @.EstHours, EstPrice = @.EstPrice, CCType = @.CCType, CCNum = @.CCnum, CCAuthNum = @.CCAuthNum, CarQuant = @.CarQuant, Status = @.Status, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified
SELECT @.RowCounter = @.@.ROWCOUNT
If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = @.ResNum + ' Update Failed'
End
GO
and the VBA that calls it. The 2 highlighted lines will be null if the "@.ResNum + " is included. Otherwise, it runs fine.
Public Sub cmdUpdate_Click()
Dim strSQL As String
Dim strField As String
Dim fOK As Integer
Dim intCount As Integer
Dim x As Integer
Dim strMsg As String
Dim cmd As ADODB.Command
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim dteReqDate As Date
Dim dteDispDate As Date
On Error GoTo ErrorHandler
dteReqDate = Me.txtReqDate & " " & Me.txtReqTime
dteDispDate = DateAdd("n", -Nz(Me.txtMinutesAhead, 0), dteReqDate)
Call EstablishConnection
Set db = CurrentDb()
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
Set tdf = db.TableDefs("tblReservations_Local")
rst.ActiveConnection = objConn
With cmd
.ActiveConnection = objConn
.CommandText = "procResUpdate"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ReqDate", adDBTimeStamp, adParamInput, , dteReqDate)
.Parameters.Append .CreateParameter("@.PassName", adVarChar, adParamInput, 25, Me.txtPassName)
.Parameters.Append .CreateParameter("@.DispDate", adDBTimeStamp, adParamInput, , dteDispDate)
.Parameters.Append .CreateParameter("@.PassPhone", adVarChar, adParamInput, 25, Me.txtPassPhone)
.Parameters.Append .CreateParameter("@.PassQuant", adInteger, adParamInput, , Me.txtPassQuant)
.Parameters.Append .CreateParameter("@.AuthBy", adVarChar, adParamInput, 25, Me.txtAuthBy)
.Parameters.Append .CreateParameter("@.AcctID", adInteger, adParamInput, , Me.cboAcctNum)
.Parameters.Append .CreateParameter("@.PuLandmark", adInteger, adParamInput, , Me.cboLandmark)
.Parameters.Append .CreateParameter("@.PuStreet", adVarChar, adParamInput, 50, Me.txtPuAddress)
.Parameters.Append .CreateParameter("@.PuCity", adInteger, adParamInput, , Me.cboPuCity)
.Parameters.Append .CreateParameter("@.PuXStreet", adVarChar, adParamInput, 50, Me.txtPuXStreet)
.Parameters.Append .CreateParameter("@.PuPoint", adVarChar, adParamInput, 50, Me.txtPuPoint)
.Parameters.Append .CreateParameter("@.DestLandmark", adInteger, adParamInput, , Me.cboDestLandmark)
.Parameters.Append .CreateParameter("@.DestStreet", adVarChar, adParamInput, 50, Me.txtDestAddress)
.Parameters.Append .CreateParameter("@.DestCity", adInteger, adParamInput, , Me.cboDestCity)
.Parameters.Append .CreateParameter("@.DestXStreet", adVarChar, adParamInput, 50, Me.txtDestXStreet)
.Parameters.Append .CreateParameter("@.DestPoint", adVarChar, adParamInput, 50, Me.txtDestPoint)
.Parameters.Append .CreateParameter("@.Operator", adInteger, adParamInput, , Me.txtOperator)
.Parameters.Append .CreateParameter("@.Comments", adVarChar, adParamInput, 250, Me.txtComments)
.Parameters.Append .CreateParameter("@.DriverReq", adVarChar, adParamInput, 25, Me.txtDriverReq)
.Parameters.Append .CreateParameter("@.CarType", adInteger, adParamInput, , Me.cboCarType)
.Parameters.Append .CreateParameter("@.EstHours", adCurrency, adParamInput, , Me.txtEstHours)
.Parameters.Append .CreateParameter("@.EstPrice", adCurrency, adParamInput, , Me.txtEstPrice)
.Parameters.Append .CreateParameter("@.CCType", adVarChar, adParamInput, 25, Me.cboCCType)
.Parameters.Append .CreateParameter("@.CCnum", adVarChar, adParamInput, 25, Me.txtCCNum)
.Parameters.Append .CreateParameter("@.CCAuthNum", adVarChar, adParamInput, 25, Me.txtCCAuthNum)
.Parameters.Append .CreateParameter("@.CarQuant", adInteger, adParamInput, , Me.txtCarQuant)
.Parameters.Append .CreateParameter("@.ResNum", adInteger, adParamInput, , Me.txtResNum)
.Parameters.Append .CreateParameter("@.Status", adInteger, adParamInput, , Me.cboStatus)
.Parameters.Append .CreateParameter("@.LastModified", adInteger, adParamInput, , Me.txtLastModified)
.Parameters.Append .CreateParameter("@.RetCode", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@.RetResNum", adVarChar, adParamOutput, 15)
.Execute
End With
fOK = cmd.Parameters("@.RetCode")
strMsg = cmd.Parameters("@.RetResNum")|||Update for anyone interested. It seems that there was a type mismatch occurring. @.ResNum is a numeric value (data type int), and apparently in this line that caused a problem:
Select @.RetResNum = @.ResNum + ' Update Failed'
I assumed it would concatenate the two values. If I create a new varchar variable and populate it with the value of @.ResNum (converted to varchar) and use that, everything works fine. What threw me was that not only was that return parameter null, but the other one was too.
TIA
EDIT: Solved, sort of. I found that dropping the "@.ResNum +" from "@.ResNum + ' Updated'" resolved the problem (@.ResNum is an input parameter). This implies that the variable lost its value between the SQL statement and the If/Then, since the SQL correctly updates only the appropriate record from the WHERE clause. Is this supposed to happen? I looked in BOL, and if it's addressed there I missed it.
CREATE PROCEDURE [procResUpdate]
Various input parameters here,
@.RetCode as int Output, @.RetResNum as nvarchar(15) Output
AS
Declare @.RowCounter int
Begin Tran
UPDATE tblReservations
SET Various set statements here, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified
SELECT @.RowCounter = @.@.ROWCOUNT
If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = @.ResNum + ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = 'Update Failed'
End
GOPost the complete DDL for the proc (including @.ResNum) and a sample of calling it that produces the problem you describe.
Regards,
hmscott|||Here's the complete sproc (I added back the offending part in red):
CREATE PROCEDURE [procResUpdate]
@.ReqDate as datetime, @.PassName as nvarchar(25), @.DispDate as datetime, @.PassPhone as nvarchar(25),
@.PassQuant as smallint, @.AuthBy as nvarchar(25), @.AcctID as smallint, @.PuLandmark as smallint, @.PuStreet as nvarchar(50),
@.PuCity as smallint, @.PuXStreet as nvarchar(50), @.PuPoint as nvarchar(50), @.DestLandmark as smallint, @.DestStreet as nvarchar(50),
@.DestCity as smallint, @.DestXStreet as nvarchar(50), @.DestPoint as nvarchar(50), @.Operator as smallint, @.Comments as nvarchar(250),
@.DriverReq as nvarchar(25), @.CarType as smallint, @.EstHours as money, @.EstPrice as money, @.CCType as nvarchar(25), @.CCnum as nvarchar(25),
@.CCAuthNum as nvarchar(25), @.CarQuant as tinyint, @.ResNum as int, @.Status as smallint, @.LastModified as tinyint,
@.RetCode as int = Null Output, @.RetResNum as nvarchar(15) = Null Output
AS
Declare @.RowCounter int
Begin Tran
UPDATE tblReservations
SET ReqDateTime = @.ReqDate, PassName = @.PassName, DispDateTime = @.DispDate, PassPhone = @.PassPhone, PassQuant = @.PassQuant, AuthBy = @.AuthBy, AcctID = @.AcctID,
PuLandmarkID = @.PuLandmark, PuStreet = @.PuStreet, PuCityID = @.PuCity, PuXStreet = @.PuXStreet, PuPoint = @.PuPoint, DestLandmarkID = @.DestLandmark, DestStreet = @.DestStreet,
DestCityID = @.DestCity, DestXStreet = @.DestXStreet, DestPoint = @.DestPoint, OperatorID = @.Operator, Comments = @.Comments, DriverReq = @.DriverReq, CarType = @.CarType,
EstHours = @.EstHours, EstPrice = @.EstPrice, CCType = @.CCType, CCNum = @.CCnum, CCAuthNum = @.CCAuthNum, CarQuant = @.CarQuant, Status = @.Status, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified
SELECT @.RowCounter = @.@.ROWCOUNT
If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = @.ResNum + ' Update Failed'
End
GO
and the VBA that calls it. The 2 highlighted lines will be null if the "@.ResNum + " is included. Otherwise, it runs fine.
Public Sub cmdUpdate_Click()
Dim strSQL As String
Dim strField As String
Dim fOK As Integer
Dim intCount As Integer
Dim x As Integer
Dim strMsg As String
Dim cmd As ADODB.Command
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim dteReqDate As Date
Dim dteDispDate As Date
On Error GoTo ErrorHandler
dteReqDate = Me.txtReqDate & " " & Me.txtReqTime
dteDispDate = DateAdd("n", -Nz(Me.txtMinutesAhead, 0), dteReqDate)
Call EstablishConnection
Set db = CurrentDb()
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
Set tdf = db.TableDefs("tblReservations_Local")
rst.ActiveConnection = objConn
With cmd
.ActiveConnection = objConn
.CommandText = "procResUpdate"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.ReqDate", adDBTimeStamp, adParamInput, , dteReqDate)
.Parameters.Append .CreateParameter("@.PassName", adVarChar, adParamInput, 25, Me.txtPassName)
.Parameters.Append .CreateParameter("@.DispDate", adDBTimeStamp, adParamInput, , dteDispDate)
.Parameters.Append .CreateParameter("@.PassPhone", adVarChar, adParamInput, 25, Me.txtPassPhone)
.Parameters.Append .CreateParameter("@.PassQuant", adInteger, adParamInput, , Me.txtPassQuant)
.Parameters.Append .CreateParameter("@.AuthBy", adVarChar, adParamInput, 25, Me.txtAuthBy)
.Parameters.Append .CreateParameter("@.AcctID", adInteger, adParamInput, , Me.cboAcctNum)
.Parameters.Append .CreateParameter("@.PuLandmark", adInteger, adParamInput, , Me.cboLandmark)
.Parameters.Append .CreateParameter("@.PuStreet", adVarChar, adParamInput, 50, Me.txtPuAddress)
.Parameters.Append .CreateParameter("@.PuCity", adInteger, adParamInput, , Me.cboPuCity)
.Parameters.Append .CreateParameter("@.PuXStreet", adVarChar, adParamInput, 50, Me.txtPuXStreet)
.Parameters.Append .CreateParameter("@.PuPoint", adVarChar, adParamInput, 50, Me.txtPuPoint)
.Parameters.Append .CreateParameter("@.DestLandmark", adInteger, adParamInput, , Me.cboDestLandmark)
.Parameters.Append .CreateParameter("@.DestStreet", adVarChar, adParamInput, 50, Me.txtDestAddress)
.Parameters.Append .CreateParameter("@.DestCity", adInteger, adParamInput, , Me.cboDestCity)
.Parameters.Append .CreateParameter("@.DestXStreet", adVarChar, adParamInput, 50, Me.txtDestXStreet)
.Parameters.Append .CreateParameter("@.DestPoint", adVarChar, adParamInput, 50, Me.txtDestPoint)
.Parameters.Append .CreateParameter("@.Operator", adInteger, adParamInput, , Me.txtOperator)
.Parameters.Append .CreateParameter("@.Comments", adVarChar, adParamInput, 250, Me.txtComments)
.Parameters.Append .CreateParameter("@.DriverReq", adVarChar, adParamInput, 25, Me.txtDriverReq)
.Parameters.Append .CreateParameter("@.CarType", adInteger, adParamInput, , Me.cboCarType)
.Parameters.Append .CreateParameter("@.EstHours", adCurrency, adParamInput, , Me.txtEstHours)
.Parameters.Append .CreateParameter("@.EstPrice", adCurrency, adParamInput, , Me.txtEstPrice)
.Parameters.Append .CreateParameter("@.CCType", adVarChar, adParamInput, 25, Me.cboCCType)
.Parameters.Append .CreateParameter("@.CCnum", adVarChar, adParamInput, 25, Me.txtCCNum)
.Parameters.Append .CreateParameter("@.CCAuthNum", adVarChar, adParamInput, 25, Me.txtCCAuthNum)
.Parameters.Append .CreateParameter("@.CarQuant", adInteger, adParamInput, , Me.txtCarQuant)
.Parameters.Append .CreateParameter("@.ResNum", adInteger, adParamInput, , Me.txtResNum)
.Parameters.Append .CreateParameter("@.Status", adInteger, adParamInput, , Me.cboStatus)
.Parameters.Append .CreateParameter("@.LastModified", adInteger, adParamInput, , Me.txtLastModified)
.Parameters.Append .CreateParameter("@.RetCode", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@.RetResNum", adVarChar, adParamOutput, 15)
.Execute
End With
fOK = cmd.Parameters("@.RetCode")
strMsg = cmd.Parameters("@.RetResNum")|||Update for anyone interested. It seems that there was a type mismatch occurring. @.ResNum is a numeric value (data type int), and apparently in this line that caused a problem:
Select @.RetResNum = @.ResNum + ' Update Failed'
I assumed it would concatenate the two values. If I create a new varchar variable and populate it with the value of @.ResNum (converted to varchar) and use that, everything works fine. What threw me was that not only was that return parameter null, but the other one was too.
Subscribe to:
Posts (Atom)