Parameter Error When Previewing (New To SRS)
Mar 10, 2008
I am new to SRS and have yet to preview a report successfully. Here's my situation. I have a fairly complex set of logic that will generate the data that I use as the dataset for the report, this is housed in a stored proc that takes three parameters (2 DateTime and one varchar). The last few steps select the data into a temp table which I then issue a Select * on to get the data back for the report. This all works fine if I run it from the Data tab using the ! button, but when I try to preview I get an error saying the following:
An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for data set 'dsMarketPrices'.
Procedure or function 'spr_Recon_SelectMarketPrices' expects parameter '@DateStart', which was not supplied.
Do I have to manually add the parameters to the report? I assumed not since it knows to prompt me for them when I run the data using the ! button...
Mike Devenney
View 8 Replies
ADVERTISEMENT
Jun 15, 2007
Anyone know why I am getting this?===================================
There was an error displaying the preview. (Microsoft Visual Studio)
===================================
Too many fields defined. (Microsoft JET Database Engine)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.ExecuteStatement(Int32 resultType, Boolean isStoredProc, UInt32 dwTimeOut)
at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection90 externalColumns)
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)
View 5 Replies
View Related
May 12, 2008
I am creating a report in VS 2005 SP1 (all updates applied) on a Windows 2003 Server with 8GB of RAM. When I preview the report (about 120,000 records) it locks up my machine for about 5 minutes and then gives an error:
An error occurred during local report processing.
An error has occurred during report processing.
Exception of type 'System.OutOfMemoryException' was thrown.
This report has a somewhat complex query, but when I deploy the report to the server (Reporting Services 2005) it runs fine. I just hate to deploy the report each time I want to preview it. Is there a setting for VS that I can change to increase memory? Or is there something else I need to check?
Thank you for any assistance!
Bill
View 1 Replies
View Related
Nov 12, 2007
Okay, when i go and preview a report that has columns (my report is designed for printing lables, i'm just pulling names and addresses and placing them in a list text box and 3 columns), the columns do not show. Keep in mind this is in "preview" cause html cannot handle columns.
On the first page, the middle column is blank, then the second page has blank first and last columns, and this repeats throughout the preview. So column 2 is blank, then 1 & 3 are blank, and so on.
I'm wondering if this is a SP2-related thing cause i know SP2 caused the black-preview-renderings (and i fixed that).
I know it's working properly cause i can export to PDF and it's perfect.
So, this is merely a rendering issue (at least so far). Does anyone have any suggestions or any advice to a fix on this? I CAN'T BE THE ONLY ONE? It'd be very nice to be able to preview my reports before they're deployed to the intranet website.
Thanks in advance!
View 11 Replies
View Related
Apr 15, 2008
I would like to break a large table into multiple pages when previewing in the designer. Any ideas?
View 10 Replies
View Related
Nov 28, 2001
HELP! im running this sql and get the following error - If i manually start the dts package it is fine....also if i use DTSrun from my pc it also works ok..
USE master
EXEC xp_cmdshell 'DTSrun /S BTN_AH_TWB02NEW /U pkent /P clarke05 /N pbk'
output
DTSRun: Loading...
Error: -2147024809 (80070057); Provider Error: 0 (0)
Error string: The parameter is incorrect.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 713
(9 row(s) affected)
View 2 Replies
View Related
Jan 16, 2005
I am trying to extract a value out of a cookie and then use that as a parameter to a SQL Select...Where function but I am getting the Argument not specified for parameter error. I assume the value from the cookie is not in the variable that I created but I could be wrong.
What is the proper method for populating a variable from a cookie and using that value as part of a SQL Select....Where function?
After I get a value out of the Select...Where function, I need to use the dataset results in a DropDownList as well as perform the DataBind().
here's some of the code:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
if Not Page.IsPostBack then
dim Code as String
Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
label1.Text = Code
GetPropertyCodes(Code)
ddlPropertyCode.DataBind()
end if
End Sub
Function GetPropertyCodes(ByVal Code As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Property_Details_db].[Prop_Code] FROM [Property_Details_db] WHERE ([Property_Details_db].[Code] = @Code)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_code.ParameterName = "@Code"
dbParam_code.Value = Code
dbParam_code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_code)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Thanks for your help.
Chris
View 13 Replies
View Related
May 14, 2001
Hi,
I'm having a problem with my SQL 7.0 (SP2) server running on NT4 (SP6a).
When I use Enterprise Manager on my NT4 workstation (SP6) and I try to double click a local DTS package to go into the design view I get the following error message:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: The parameter is incorrect
If I log on to the actual server and run Enteprise Manager everything is o.k
It is not a permissioning problem as I'm logging in with domain Admin in both cases. Any ideas ?
Regards,
Tim
View 2 Replies
View Related
Aug 11, 2000
SQL7, NT workstation with SQL installed locally:
A programmer creates a new DTS pkg from his NT workstation (SQl client installed - using EM). He saves it to a SQL server on an NT server on the network. Then when he "sites" the server using PCDUO he is unable to open the DTS pkg he just created. He tried creating the pkg logged on as "sa", but still is unable to open it. We are unable to edit the DTS?
*******
Error source: Microsoft Data Transformation Services (DTS) Package
Error Description: The parameter is incorrect.
*******
He is saving it to "local packages".
Thanks,
View 1 Replies
View Related
Apr 30, 2001
I am running SQL 7 Svc Pack 2 on TN 4.0 Svc Pack 6a.
I am getting the following error when opening up a DTS package on my SQL Server from EM on my workstation. If I open it up on the server by using pcanywhere the package opens fine. Ther error is a dialogue box stating:
Package Error
Error Source:Microsoft Data Transformation Services (DTS) Package
Error Description: The Parameter is incorrect.
Has anyone seen this. I get nothing in the event log or SQL logs. I can't seem to figure this one out.
View 3 Replies
View Related
Jan 25, 2007
Hi
I am getting error while passing parameter in SSIS package
I am writing following query in sql command
select * from Mytable where empid in (select empid from testtable where empid = ?)
Error:
Parameter cannot be extracted from the SQL Command. The Provider might not help to parse parameter information from the command. In that case, use the "SQL Command from variable" access mode, in which the entire SQL command is stored in a variable.
can any body help me out
Thanks
View 1 Replies
View Related
Oct 1, 2007
In SQL Server 2005 reporing services, I am using the syntax in the documentation for a multivalue parameter.
AND c.Track IN (@Track)
It works fine when I just select one value, but if I select 2 or more, I get an error about the commas being wrong. Is the syntax correct? Is there a way I can see the SQL generated by the report so I can see what is wrong?
Thanks,
Linda
View 17 Replies
View Related
Mar 12, 2008
I'm getting this invalid expression error (#error) when I try to return the following in a text box. Is there syntax error?
=Parameters!CampaignSiteName.Label
View 5 Replies
View Related
May 15, 2008
i have an rdl file that takes a dataset which has a SP with 2 parametes. when i am able to see the data filled in the data tab, where as i am not able to preview the report in the preview tab. it show an error " specified argument was out of range of range of valid value. Parameter name : format " .....can someone help out with some solution....help would be greatly appriciated....thanks
reply me at sudhakar.x.govindarajan@verizon.com
Sudhakar G
View 4 Replies
View Related
Dec 26, 2006
Hi all, i am a newbie to reporting services. Hope someone can help me with this date problem.
I have a parameter date which i declared as type string in the report manager. when i entered the date as eg) "31/12/2003" and clicked on the 'preview' tab in the report manager, it gives me error saying that 'Cast from string "31/12/2003" to type 'Date' is not valid.'
So i use the cultureinfo function to set the date to 'en-US' format.... something like the one below:
CStr( Format(DateTime.Parse( Parameters!strDateTo.Value , New System.Globalization.CultureInfo("en-US")), "dd-MMM-yyyy"))
This works perfectly in my preview tab. But when i deployed to the server and when viewed on IE, it gives me an error msg -> "#Error" instead. Why is that so??
I have also checked my pc regional settings . It's set to United States and the date format is also "dd/MM/yyyy". Have also checked the settings in the server and it's also set to the same format. But i am still getting this error.
By the way... i am using windows 2003 with SRS 2000 SP2.
Any help will be appreciated. Thks !
View 1 Replies
View Related
Mar 27, 2007
New to report services and trying out my first report. I have a report with a simple parameter lookup. When I preview it in Visual Studio it works fine. I can select the parameter value and it returns back the correct information.
When trying to preview the deployed version of the report I get the following error after selecting the parameter value and clicking preview. I am not sure where I need to declare the variable @owners.
Thanks
Reporting Services Error
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Query execution failed for data set 'ANDON'. (rsErrorExecutingCommand) Get Online Help
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@Owners'.
Microsoft Reporting Services
View 8 Replies
View Related
Aug 15, 2006
I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.
I'm trying to dynamically set a Data Source for an Excel connected, using a query.
Query:
DECLARE @directory VARCHAR(1000)
SET @directory = 'C:'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @directory
The Store Procedure:
CREATE PROCEDURE spr_GetNewestExcel
(
@directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @cmdParam VARCHAR(8000)
--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)
--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)
--Sets the command parameter
SET @cmdParam = 'dir "' + @directory + '"'
--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @cmdParam
--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'
--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@directory, LEN(@directory), 1) = '' THEN @directory + fileName
ELSE @directory + '' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC
DROP TABLE #output
DROP TABLE #files
END
The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.
When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".
Any help would be much appreciated, this has had me stumped for quite some time.
peace,
CR
View 7 Replies
View Related
Oct 26, 2007
When im testing in local machine... my app works fine... but when i publish it on server... the sql DateTime parameter get an error:
ERROR ON SERVER : String was not recognized as a valid DateTime
my code:SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StringDeConexao"].ConnectionString);
SqlCommand oCmd = new SqlCommand("spcCadLjItem", con);oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Connection.Open();SqlTransaction trans = con.BeginTransaction();
oCmd.Transaction = trans;
oCmd.Parameters.Add(new SqlParameter("@dt_exclusao", SqlDbType.DateTime));
oCmd.Parameters["@dt_exclusao"].Value = DBNull.Value;
oCmd.ExecuteNonQuery();
When I inspect the type of parameter on my machine its ok.. SqlDbType its DateTime... and runs perfectly...Why it doesnt work when i publish on server???????
tks!!
View 1 Replies
View Related
Mar 21, 2006
Can anyone help me out with using parameters in my SqlDataSource?
I have my UpdateCommand set as follows:
UpdateCommand="UPDATE Custom_ContainerRequest_ESTWEIGHTS SET WEIGHTRANGES = @WEIGHTRANGES WHERE ESTWEIGHTS_ID = @original_ESTWEIGHTS_ID"
<UpdateParameters> <asp:Parameter Name="WEIGHTRANGES" Type="String" /> <asp:Parameter Name="original_ESTWEIGHTS_ID" Type="Int32" /></UpdateParameters>
And the application is just hanging when I run it. (No error message.)
If, however, I hardcode the values instead of using parameters, it works just fine everytime.
UpdateCommand="UPDATE Custom_ContainerRequest_ESTWEIGHTS SET WEIGHTRANGES = 'test' WHERE ESTWEIGHTS_ID = 1"
So I don't know if it's a single-quote issue, or if there's some other syntax I need, or what. One last note is that this is a DB2 database I'm using. But I'm sure that my connection is valid because I can read and update data...only not using parameters.
Any help is greatly appreciated.
Thanks,Billy
View 2 Replies
View Related
Aug 7, 2000
Hi all Thanks in advance ..
I have some DTS Shedules on SQL 7.0 SP1 . All shcedule was running properly . When I made some changes in an SQL Task and saved it the shedule starts to fail. It gives me an error "DTS RUN LOAding Error- 2147024809(80070057)Ptovider Error (0) Error String : The parameter is incorrect etc)
But All my DTS is running smoothly without error Only the schedule fails.
Please help me.
View 6 Replies
View Related
Aug 28, 2000
I am getting an error stating the parameter is incorrect when I open a package on my laptap. If I do it on the server it works fine. I even tried recreating my ODBC connection on my laptop thinking that might be it. Any suggestions would be appreciated.
View 2 Replies
View Related
Feb 7, 2005
Hi everyone,
I want to be able to pass an "ORDER BY" parameter into my stored procedure but I am receiving errors when I do. For example:
CREATE PROCEDURE GetFromTable
(
@SortOn varchar(20)
)
AS
SELECT *
FROM Table
ORDER BY @SortOn
GO
This is the error I get: Variables are only allowed when ordering by an expression referencing a column name. Any suggestions on what I can do to make this work?
Thanks in advance,
Piet
View 9 Replies
View Related
Sep 17, 2006
Hello.
It is the first time that Im using IMAGE field type..
I created a stored procedure that should return an IMAGE field back to a
c-sharp program .
(@OutImg IMAGE declared as an output parameter of the procedure.)
(Select @OutImg = ImgFld from table)
Well,I can compile it , but when execute i get this error :
Msg 2739, Level 16, State 1, Line 14
The text, ntext, and image data types are invalid for local variable.
Is there something I can do ,OR should I use any cast/convert func to solve it ..?
Many thanks.:o
View 1 Replies
View Related
Nov 11, 2006
I'm generarintg a mail from a trigger, using xp_sendmail.
I use the query parameter to generate the body of the mail, the query is a procedure call with some parameters, one of this is a long string.
when the length is less that 128 work fine, but, when this parameter is longer than 128 charaters, return me the error 103 telling me that it has exceed 128 characteres, and do not send the mail.
I find this quite frustrating, the parameter is limited only to 128 characteres, what could I do.
Thanks in advance
Cheers.
Ale.
View 1 Replies
View Related
Nov 11, 2014
While trying to execute the below query I get an error "Invalid Paramter Binding.
SELECT RELEASE_CYCLES.RCYC_NAME+' : '+ CYCL_FOLD.CF_ITEM_NAME /*Test Set Folder (CYCLE).Name*/ as "Cycle Name : Test Component",
SUM(case when t3.status='Passed' then 1 else 0 end) "Passed",
SUM(case when t3.status='Failed' then 1 else 0 end) "Failed",
SUM(case when t3.status='Not Completed' then 1 else 0 end) "Not Completed",
SUM(case when t3.status='No Run' then 1 else 0 end) "No Run",
SUM(case when t3.status in ('Passed','Failed','Not Completed','No Run') then 1 else 0 end) "Total",
[code]....
View 5 Replies
View Related
Jul 1, 2007
Hey all,
I ran into a little snag when writing a VBScript app for work that parses a text file and places that data into a DB2 V8.1 database via OLE DB ADO calls and a stored procedure with parameters.
I have found that I can call a stored procedure w/o parameters but as soon as I one that takes some I get the following error.
"arguments are of the wrong type, are out of acceptable range"
To try to troubleshoot it, I tried applying each parameter property individually. I noticed that when I didn't provide a direction, I received a different error (Don't have the exact one but it had something to do with an IBM OLE DB property error).
Because the VBScript error was on the line (mycmd.commandtype = adCmdStoredProc) I replaced the global constant with a literal (IE: 4). No fix.
I'm going to try to replace all of the global constants with their literal values but I also found this on the IBM site...
1: SQLProcedureColumns is used by many applications to
determine the direction of stored procedure parameters. The
ODBC driver is returning an ordinal of 1 for all the
parameters.
This causes problems with applications that use this
information. For example a stored procedure call using
Microsoft ActiveX Data Objects (ADO) and the adCmdStoredProc
syntax will return one of the following error messages:
3265 Item cannot be found in the collection corresponding to the
requested name or ordinal.
3001 Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another.
SQLProcedureColumns is also returning a "BUFFER_LENGTH" value
for numeric data types that is 1 byte too large. The default C
data type is character so this value should be display size
-without an extra byte for null.
I'm guessing this is the solution to my issue but I figured I'd put this out there just in case I was missing something.
If you have any other suggestions, please chime in.
View 5 Replies
View Related
Nov 14, 2007
I have been working on parameterised reports for quite a while and have a problem with a report that I cannot fathom despite days spent fiddling. I am hoping someone will be able to spot a silly mistake...
Basically, I am developing my report in VS2005 and it works perfectly. When I publish it to the reporting server I get an error: The 'pSite' parameter is missing a value.
I cannot understand why the report should fail when published.
Before anyone says it, yes I have deleted the published report and re-deployed so there should be no chance of any 'old' deployments messing things up!
Any obvious suggestions before I start posting code?!
Thanks all
Will
View 3 Replies
View Related
Apr 22, 2008
I have set up a parameter driven subscription and am having strange results. This subscription is being set up as Web-archive, including the report and link.
At first when setting up the report, I didn't have a default value set in the report, but would choose it in the subscription and would see an error saying the parameter value provided is not valid. After seeing the error I would edit the subscription and see the parameter value was blank. This would happen if the subscription was set to start after at least a few hours. This didn't matter if I chose the subscription schedule or using a shared schedule.
When I set up a subscription the same way, but then had it delivered within the next few minutes, the report would work fine, using the given parameter.
Since one of reports is being sent to a manager, I put in a default parameter value and set up a few subscriptions. I was hoping at least if the subscription "didn't remember" the parameter value, the default would be used and delivered. I was right, the report was delivered using only the default... but will only be delivered using the default.
There have been quite a few people posting similar questions, but none of them are answered.
Any help is greatly appreciated.
Thanks in advance,
Eric
Ps - here is some of the log file relating to one of the failed subscriptions:
From: ReportServerService*.log
ReportingServicesService!library!10!4/19/2008-02:00:05:: i INFO: Schedule 93453b16-89d3-48d1-a470-d7a09fc163b6 executed at 4/19/2008 2:00:05 AM.
ReportingServicesService!library!10!4/19/2008-02:00:05:: i INFO: Schedule 93453b16-89d3-48d1-a470-d7a09fc163b6 execution completed at 4/19/2008 2:00:05 AM.
ReportingServicesService!dbpolling!10!4/19/2008-02:00:05:: i INFO: EventPolling finished processing item e40d5803-bb69-4d20-bd55-797074969348
ReportingServicesService!dbpolling!12!4/19/2008-02:00:05:: i INFO: EventPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!f!4/19/2008-02:00:05:: i INFO: EventPolling processing item fe04012c-6376-4599-b980-6aa1c77036fd
ReportingServicesService!library!f!4/19/2008-02:00:05:: i INFO: Schedule 93453b16-89d3-48d1-a470-d7a09fc163b6 executed at 4/19/2008 2:00:05 AM.
ReportingServicesService!schedule!f!4/19/2008-02:00:05:: Creating Time based subscription notification for subscription: f2551ca1-f6b8-4394-8036-7fa8a9be1070
ReportingServicesService!library!f!4/19/2008-02:00:05:: i INFO: Schedule 93453b16-89d3-48d1-a470-d7a09fc163b6 execution completed at 4/19/2008 2:00:05 AM.
ReportingServicesService!dbpolling!f!4/19/2008-02:00:05:: i INFO: EventPolling finished processing item fe04012c-6376-4599-b980-6aa1c77036fd
ReportingServicesService!dbpolling!12!4/19/2008-02:00:05:: i INFO: NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!f!4/19/2008-02:00:05:: i INFO: NotificationPolling processing item d1e8f484-659e-48a1-a5da-747108ce63af
ReportingServicesService!library!f!04/19/2008-02:00:06:: i INFO: Initializing EnableIntegratedSecurity to 'True' as specified in Server system properties.
ReportingServicesService!library!f!04/19/2008-02:00:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'day' is not a valid value., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'day' is not a valid value.
ReportingServicesService!notification!f!04/19/2008-02:00:06:: Notification d1e8f484-659e-48a1-a5da-747108ce63af completed. Success: False, Status: , DeliveryExtension: Report Server Email, Report: Daily RTS time, Attempt 0
ReportingServicesService!dbpolling!f!04/19/2008-02:00:06:: i INFO: NotificationPolling finished processing item d1e8f484-659e-48a1-a5da-747108ce63af
View 14 Replies
View Related
Sep 7, 2006
Report X passes all it's parameters to Report Y via a "jump to report". Report Y then populates a select box based on the parameters passed to it. The user selects one of these values and clicks "View Report". This works perfectly in the development environment. But when I deploy it to the server I keep getting an error when I click the link that jumps to Report Y.
Here's the error: "The 'B' parameter is missing a value"
The problem is.. I don't want to pass the B parameter to Report Y. I want the user to select this from a list. I'm very confused as to why this would work in the development envionment but not when deployed.
Any Ideas?
View 3 Replies
View Related
Jun 18, 2007
I have a stored proc which starts like this:
CREATE PROCEDURE dbo.AddAttachmentListItem
@ListID uniqueidentifier,
@AttachmentPath varchar (260),
@DeleteAttachmentAfterSend bit = 0
I have a Script task which generates a GUID and stores it in the variable @[User::AttachmentListId], which is of type System.Object since Guid wasn't an option. Following this is an Execute SQL task (with an OLE DB connection to an SQL Server 2000 database) whose SQL statement is
EXEC AddAttachmentListItem ?, ?, 0
My parameter mapping looks like this (variable name, direction, data type, parameter name, parameter size):
@[User::AttachmentListId], Input, GUID, 0, -1
@[User::AFilePath], Input, VARCHAR, 1, 260
When I execute my package I get the following error:
[Execute SQL Task] Error: Executing the query "EXEC AddAttachmentListItem ?, ?, 0" failed with the following error: "The type is not supported.DBTYPE_GUID". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This worked earlier when the first parameter was an int and not a uniqueidentifier, but I reworked my design because the GUID was a better choice for what I was doing. Well, at least 'til I got to this point....
Any ideas?
View 6 Replies
View Related
May 15, 2007
Hi,I'm trying to create a page where a user can search the database according to some criteria and get back the result in the form of a GridView. Also, the user has the option of saving the criteria to another table in the database by assigning it a name so that it can be retrieved easily in the future.I have the search and display part working, however, saving the criteria to the database is giving problems for some reason.Given below is my stored procedure to add the info to the db. SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddToReport]
(@ReportName varchar(100), @ProjID varchar(300), @DeptCode varchar(20), @ProjType varchar(20), @ProjectTitle varchar(300),
@ProjectManagerID int, @DateRequested datetime, @DueDate datetime, @ProjectStatusID int)
AS
SET NOCOUNT ON
DECLARE @Dept varchar(50)
DECLARE @err int
BEGIN TRANSACTION
IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN
IF @DeptCode IS NOT NULL
BEGIN
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
END
SET @err = @@ERROR
INSERT INTO dbo.tbl_Report (ReportName, ProjID, DeptCode, ProjType, ProjectTitle, ProjectManagerID, DateRequested, DueDate, ProjectStatusID)
VALUES (@ReportName, @ProjID, @Dept, @ProjType, @ProjectTitle, @ProjectManagerID, @DateRequested, @DueDate, @ProjectStatusID);
IF @err<>0
BEGIN
ROLLBACK TRANSACTION
RETURN @err
END
END
COMMIT TRANSACTION Given below is the relevant codebehind. This is how the values are initialized: Dim newManager As New ListItem
newManager.Text = "Choose a Manager"
newManager.Value = 0
projectManagerDDL.Items.Add(newManager)
Dim newDept As New ListItem
newDept.Text = "Choose a Department"
newDept.Value = ""
deptCodeDDL.Items.Add(newDept)
Dim newID As New ListItem
newID.Text = "Choose a Project"
newID.Value = ""
projIDDDL.Items.Add(newID)
Dim newStatus As New ListItem
newStatus.Text = "Choose a Status"
newStatus.Value = 0
projectStatusDDL.Items.Add(newStatus)
Dim newDateRequestedMonth As New ListItem
newDateRequestedMonth.Text = "Month"
newDateRequestedMonth.Value = 0
dateRequestedMonthDDL.Items.Add(newDateRequestedMonth)
Dim newDateRequestedDay As New ListItem
newDateRequestedDay.Text = "Day"
newDateRequestedDay.Value = 0
dateRequestedDayDDL.Items.Add(newDateRequestedDay)
Dim newDateRequestedYear As New ListItem
newDateRequestedYear.Text = "Year"
newDateRequestedYear.Value = 0
dateRequestedYearDDL.Items.Add(newDateRequestedYear)
Dim newDueDateMonth As New ListItem
newDueDateMonth.Text = "Month"
newDueDateMonth.Value = 0
dueDateMonthDDL.Items.Add(newDueDateMonth)
Dim newDueDateDay As New ListItem
newDueDateDay.Text = "Day"
newDueDateDay.Value = 0
dueDateDayDDL.Items.Add(newDueDateDay)
Dim newDueDateYear As New ListItem
newDueDateYear.Text = "Year"
newDueDateYear.Value = 0
dueDateYearDDL.Items.Add(newDueDateYear) This is the submit code: Protected Sub saveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim connString As String
Dim con As SqlConnection
Try
connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
con = New SqlConnection(connString)
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = con
If (([String].IsNullOrEmpty(reportNameTextBox.Text) = False) Or reportNameTextBox.Text <> "Enter Report Name") Then
cmd.Parameters.Add("ReportName", SqlDbType.VarChar, 300).Value = reportNameTextBox.Text
End If
If ([String].IsNullOrEmpty(projIDDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("ProjID", SqlDbType.VarChar, 30).Value = projIDDDL.SelectedItem.Value
End If
If ([String].IsNullOrEmpty(deptCodeDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("DeptCode", SqlDbType.VarChar, 20).Value = deptCodeDDL.SelectedItem.Value
End If
If (typeRBL.SelectedItem.Value <> "All") Then
cmd.Parameters.Add("ProjType", SqlDbType.VarChar, 20).Value = typeRBL.SelectedItem.Value
End If
If ([String].IsNullOrEmpty(projectTitleTextBox.Text)) = False Then
cmd.Parameters.Add("ProjectTitle", SqlDbType.VarChar, 300).Value = projectTitleTextBox.Text
End If
If CInt(projectManagerDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectManagerID", SqlDbType.Int).Value = CInt(projectManagerDDL.SelectedItem.Value)
End If
If (dateRequestedDayDDL.SelectedItem.Value = 0 Or dateRequestedMonthDDL.SelectedItem.Value = 0 Or dateRequestedYearDDL.SelectedItem.Value = 0) Then
Dim dateRequested As New DateTime
dateRequested = Nothing
Else
Dim dateRequested As New DateTime(dateRequestedYearDDL.SelectedValue, dateRequestedMonthDDL.SelectedValue, dateRequestedDayDDL.SelectedValue)
If (dateRequested) <> Nothing Then
cmd.Parameters.Add("DateRequested", SqlDbType.DateTime).Value = dateRequested
End If
End If
If (dueDateDayDDL.SelectedItem.Value = 0 Or dueDateMonthDDL.SelectedItem.Value = 0 Or dueDateYearDDL.SelectedItem.Value = 0) Then
Dim dueDate As New DateTime
dueDate = Nothing
Else
Dim dueDate As New DateTime(dueDateYearDDL.SelectedValue, dueDateMonthDDL.SelectedValue, dueDateDayDDL.SelectedValue)
If (dueDate) <> Nothing Then
cmd.Parameters.Add("DueDate", SqlDbType.DateTime).Value = dueDate
End If
End If
If (projectStatusDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectStatusID", SqlDbType.Int).Value = CInt(projectStatusDDL.SelectedItem.Value)
End If
cmd.CommandText = "dbo.AddToReport"
cmd.CommandType = CommandType.StoredProcedure
Try
con.Open()
cmd.ExecuteNonQuery()
Response.Write("Report Saved")
Catch ex As Exception
Response.Write(ex)
Finally
con.Close()
con.Dispose()
End Try
Catch ex As ApplicationException
Response.Write("Could not load the database")
End Try
End Sub The only absolute requirement when saving to the table is the ReportName. All the other criteria can be NULL. If I don't select and values and try to save the values, I get an error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@ProjID', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError... etc If I choose the ProjID (thus giving it a value), I get the following error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@DeptCode', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError... etc and so forth. I'm guessing it's a problem of NULLs somewhere, but I'm not sure. Thanks.
View 6 Replies
View Related
Jun 1, 2004
Hi there,
I have a search page that redirects to another page transfering the search terms in a querystring. In the page to display the search results I use SQL parameters to show the results. Everything runs fine until I do a page refresh or use paging to bring up another set of results.
Everytime I try to refresh/page etc. I get the following error:
Item has already been added. Key in dictionary: "@CityTown" Key being added: "@CityTown"
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: Item has already been added. Key in dictionary: "@CityTown" Key being added: "@CityTown"
Source Error:
Line 20:
Line 21: 'Creates parameters for the WHERE statement taking the querystring contents from previous page
Line 22: dsPropertyListing.Parameters.Add("@CityTown", CityTown)
Line 23: dsPropertyListing.Parameters.Add("@Suburb", Suburb)
Line 24: dsPropertyListing.Parameters.Add("@Rooms", Rooms)
Source File: K:detailsdetailspropertylisting.aspx Line: 22
Stack Trace:
[ArgumentException: Item has already been added. Key in dictionary: "@CityTown" Key being added: "@CityTown"]
System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add) +931
System.Collections.Hashtable.Add(Object key, Object value) +11
ASP.detailspropertylisting_aspx.Page_Load(Object sender, EventArgs e) in K:detailsdetailspropertylisting.aspx:22
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e) +55
System.Web.UI.Control.LoadRecursive() +27
System.Web.UI.Page.ProcessRequestMain() +731
Any sugestions that anyone could provide would be greatly appreciated. This error obviously renders the search features useless.
Thanks in anticipation,
TCM
View 3 Replies
View Related
Jul 1, 2004
Hi,
I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@dtmWarrantyStart', which was not supplied" even though I supplied it in the code
which is
Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@dtmWarrantyStart", SqlDbType.datetime, 8)
ParamdtmWarrantyStart.Value = dtmWarrantyStart
myCommand.Parameters.Add(ParamdtmWarrantyStart)
below is the stored procedure.
create Proc sp_AddBoard(@BrandID int,
@strPcName varchar(50),
@bitAccounted bit,
@dtmAccounted datetime,
@dtmWarrantyStart datetime,
@dtmWarrantyEnd datetime,
-- @strDescription varchar(500),
@intStatus int,
@ModelNo varchar(50),
@intMemorySlots int,
@intMemSlotTaken int,
@intAgpSlots int,
@intPCI int,
@bitWSound bit,
@bitWLan bit,
@bitWVideo bit,
@dtmAcquired datetime,
@stat bit output,
@intFSB int) as
if not exists(select strPcName from tblBoards where strPcName=@strPcName)
begin
insert into tblBoards
(BrandID, strPcName, bitAccounted,
dtmAccounted, dtmWarrantyStart,
dtmWarrantyEnd, --strDescription,
intStatus,
ModelNo, intMemorySlots, intMemSlotTaken,
intAgpSlots, intPCI, bitWLan,
bitWVideo, dtmAcquired,intFSB,bitWSound)
values
(@BrandID,@strPcName,@bitAccounted,
@dtmAccounted,@dtmWarrantyStart,
@dtmWarrantyEnd,--@strDescription,
@intStatus,
@ModelNo,@intMemorySlots,@intMemSlotTaken,
@intAgpSlots,@intPCI,@bitWLan,
@bitWVideo,@dtmAcquired,@intFSB,@bitWSound)
end
else
begin
set @stat=1
end
The table is also designed to accept nulls on that field but still same error occured.
Please help
View 1 Replies
View Related
