i get the following error message during package validation:
OnError
ErrorCode -1073451000
Source DataFlow
SubComponent DTS.Pipeline
Description The package contains two objects with the duplicate name of "compone
nt "OLE DB Command" (719)" and "component "OLE DB Command" (701)".
I've searched around for a while and can't seem to find out how/where I set the timeout on a bulk insert task programmatically. Here is a snippet of code that works as long as the input file is pretty small....when I attempted to pump 1.3 million rows through the default timeout value was not big enough and a timeout exception occurred.
DTSExecResult result = loadPackage.Execute(); timer.Stop(); Console.WriteLine(string.Concat("Process took(ms): ", timer.ElapsedMilliseconds.ToString())); Console.WriteLine(result.ToString()); if (loadPackage.Errors.Count > 0) { foreach (DtsError error in loadPackage.Errors) { Console.WriteLine(error.Description); } }
Again, the code above works just fine until we hit a timeout. There is no Timeout property on the BulkInsertTask class, nor can I see a way to set it on the Sql Connection. I don't even see them in the Connectionmanager.Properties collection for either the file connection or the database connection.
Hi everyone, I am building a search page and am a bit stuck. The page has an sqldatasource with no select command and i am passing the select command and parameters depending on the optionsm selected. If a tick box is ticked and a drop down selected i need to add a parameter to the datasource and generate the select command. then if 1 and or 2 trext boxes are filled in, add the 1 or 2 select parameters then generate the select command. The select command is correct as far as i can tell , but the parameters are not being passed to the datasource so it doesn't select anything. my code behind is in the page load handler and at the moment only adds the parameters in one place so i could test. Does anyone have any idea what i have done wrong?
Cheers Chris CODE BEHINDProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim SQLstr As String
If cb_Today.Checked = True And dd_Area.SelectedValue.ToString <> "" ThenLocManSearch.SelectParameters.Add("Area", TypeCode.String, dd_Area.SelectedValue.ToString()) SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() & "%') AND ([available] LIKE '%" & Date.Today & "%') AND ([viewable] = 'True')" ElseIf cb_Today.Checked = False And dd_Area.SelectedValue.ToString <> "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() & "%') " ElseIf txt_FName.Text <> "" And txt_LName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([FirstName] LIKE '%" & txt_FName.Text.ToString() & "%') and [LastName] LIKE '%" & txt_LName.Text.ToString() & "%')" ElseIf txt_LName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([LastName] LIKE '%" & txt_LName.Text.ToString() & "%')" ElseIf txt_FName.Text <> "" And cb_Today.Checked = False And dd_Area.SelectedValue.ToString = "" Then SQLstr = "SELECT * FROM [LocMan_CV] WHERE ([FirstName] LIKE '%" & txt_FName.Text.ToString() & "%')" End If Response.Write(SQLstr) LocManSearch.SelectCommand = SQLstr End Sub
I am new to SSIS. I am working in adding SSIS components programmatically. I have added Data flow task, Lookup transformation, OLEDB Source and OLEDB Destination.
Now, i am facing problems in adding Custom Script tranformation component programatically. Please help me out.
I am programmatically creating a package and for executing the package i say "Package.Execute()". Likewise,is there any method exposed which programatically stops the package execution?
I wrote this post others times but the answer did not satisfied so that I'd like to know if really anyone has ever used or experienced with this possibility. No by .Net language rather than Vb 6.0 or ASP or even instanciacing DMO library.
According this link http://support.microsoft.com/?kbid=817248 it seems possible but I haven't idea if possible keeping in mind that it has been made by yourself no for others... So that, I mean, these assemblies comes along with Sql Server 2005 installation.
I created a SSIS Package programatically based on the few threads here in this forum. This package just has a data flow task, during data transfer for every 1000 rows or so I want to update the status in a table in the database.
How do I achieve this?
As of now I just have a source and a destination, no transformations in between the flow. I'm not sure if rowcount will help, when I tried it using a onprogress even handler it always showed up as zero.
Is it possible to create a package through the SQL Server Business Intelligence Studio, and then execute that package through an Excel file (by calling the package through VBA?).
I need some advice on the ways I can execute the package other than via command line utilities. Can you use Visual Basic 6.0? .NET?
I've put together a SSIS package that, once a user uploads an Excel spreadsheet from a webpage, grabs it, does a mess of calculations and spits it out into a datareader (this last part is tricky, but I haven't even gotten to this point yet). In BIDS, the package works fine. Run using the 32-bit version of dtexec, it runs fine. But when I try to call it from the page, I keep getting an error. The errors look familiar enough that I'm thinking it's due to the package trying to run 64-bit, and that not playing nicely with Excel. If that's true, is there an easy way to force the 32-but version to be used? I've already set the project properties to Run64BitRuntime = false, which I'd hoped would help. but no luck.
-1071636471 TestReports_UploadTestReport Connection manager "Excel Connection Manager" Microsoft.SqlServer.Dts.Runtime.DtsError0{8BDFE898-E9D8-4D23-9739-DA807BCDC2AC} SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". -1071611876 DFT - Upload spreadsheet to dataset Excel Source [1] Microsoft.SqlServer.Dts.Runtime.DtsError0dtsmsg.rll{8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. -1073450985 DFT - Upload spreadsheet to dataset DTS.Pipeline Microsoft.SqlServer.Dts.Runtime.DtsError0dtsmsg.rll{8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} component "Excel Source" (1) failed validation and returned error code 0xC020801C. -1073450996 DFT - Upload spreadsheet to dataset DTS.Pipeline Microsoft.SqlServer.Dts.Runtime.DtsError0dtsmsg.rll{8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} One or more component failed validation. -1073594105 DFT - Upload spreadsheet to dataset
hi all, I am developing an application in C# which creates SSIS package programatically.These packages perform various activities like transfering data from one server to another,extract data from table,transfering data from sql to msaccess etc.Here I am using the following code to get the description of the SSIS errorcode.
For example if the error code is 0xC0204018,the application shows its equivalent description as "The "%1!s!" has a precision that is not valid. The precision must be between %2!ld! and %3!ld! "
But the actual error message is "The Output column "fund" has a precision that is not valid. The precision must be between 1 and 18"
My question is how to show more descriptive error message..like how to replace "%1!s!" to the actual field.
I had just installed SQL 2005 dev on my laptop and got an error message when I tried to create a package using the BI IDE. I received the same error using VS2005 IDE. But the project was created regardless without any packages. When I tried to create a new package in the project, I received the same error again, but with an option to view the error details.
Following is the text of the error details:
TITLE: Microsoft Visual Studio ------------------------------
I have a ssis component that begins like below. I recently added a new customproperty to the package and needed a way to detect when an upgrade from an old version was needed. As per microsofts directions I tried to use the currentversion property for this. This seemed to work fine until I noticed that newly added component to the dataflow keep starting at version 0 and going thru an upgrade making an duplicate of the new property. As below the new version has currentversion = 2 as a property. So why is this ignored. Or how do i make a component start out with the proper version so no upgrades happen?
Code Snippet namespace itworkz.ssis.CustomComponents { [DtsPipelineComponent( DisplayName = "ODS Tool v2", Description = "proccess data for the operational data store", ComponentType = ComponentType.Transform, CurrentVersion = 2, UITypeName = "itworkz.ssis.CustomComponents.OdsTool2UI, OdsTool2, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3276bb0510a3c305" )]
I´m wondering how to make a connection to a file in differents machines, FTP, excel file, HTTP, WebService instead of OLDDB, where can I find a reference for the constants that must be used in each case.
I have a big problem and i'm not able to find any hint on the Network.
I have a window2000 pc, VS2005,II5 and SQLServer 2005(dev edition)
I created an SSIS Package (query to DB and the result is loaded into an Excel file) that works fine.
I imported the dtsx file inside my "Stored Packages".
I would like to load and run the package programmatically on a Remote Scenario using the web services.
I created a solution with web service and web page that invoke the web service.
When my code execute: Microsoft.SqlServer.Dts.Runtime.Application.LoadFromDtsServer(packagePath, ".", Nothing)
I got the Error: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
The error message doesn't help so much and there is nothing on the www to give me and advice....
My Server having DTS package even Job failing saying "Error string: The specified DTS Package ('Name = 'ERP_Mater_Encript_Data_Move'; ID.VersionID = {[not specified]}.{T0I37w32-AB51-1SA4-9495-AcE6SSS4a321}') does not exist"
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
While Creating a script task in Control Flow, I am getting "Package Validation Error". Here is the complete message:
Error at Validate File and Load Data: The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated. (Microsoft.DataTransformationServices.VsIntegration)
As mentioned in the message, I opened the script IDE and added the code I need. When I close the VSA IDE, package designer displays the same error message.
The worst part of whole story is that if I close the package designer and reopen it, I find that all the code I wrote in the script task has been deleted by the package designer. This is not at all acceptable as I saved the package the and still lost all my work. I did all the coding from scratch for that task.
Please respond if anyone faced similar problem.
Thanks in advance!
Anand
PS: If any one from Microsoft is reading this, please see what you guys are coding there. Due to the buggy software you deliver, I am loosing my credibility.<P< P>
I am having problems executing a child package from a parent package using the Execute Package Task. I am attempting to run the master package through a SQL Server Agent job.
The SQL Server Agent job is owned by sa. The step that runs the parent package is configured to load the package from the SSIS Package Store on the same server that the job is running.
I have the Execute Package Task configured as follows:
Location: SQL Server ExecuteOutOfProcess: True Connecting as a SQL Server login (let's say TestEtl)
I have added the db_dtsoperator database role to both the TestEtl login and the login that SQL Server Agent connects through. I have also configured the child package's reader role to include db_dtsoperator. Per http://msdn2.microsoft.com/en-US/library/ms141053.aspx, this should allow these logins to run the child package.
I have enabled logging of all events in both the parent and child packages. I see the following in the logs when the Execute Package Task executes (omitted portions unrelated to the execution of the child package task):
450939 OnPreExecute ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450940 OnPreValidate ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450941 OnPostValidate ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450942 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.450943 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.450944 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.450945 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDBInitialize::Initialize succeeded'. The external request has completed.450946 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDBCreateSession::CreateSession'.450947 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDBCreateSession::CreateSession succeeded'. The external request has completed.450948 OnError ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 Error 0x80070005 while preparing to load the package. Access is denied. . 450949 OnError ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 Error 0x80070005 while preparing to load the package. Access is denied. . 450950 OnTaskFailed ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450951 OnPostExecute ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450952 OnWarning ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. 450953 OnPostExecute ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450954 PackageEnd ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 End of package execution.
I am sure that what I am doing is quite common, and I obviously have something misconfigured somewhere - but I'm not sure what my misconfiguration is. Can anyone enlighten me?
I cannot execute a package by using Execute Package task. I supplied sa credentials to connection manager, and it shows the list of Packages on SQL Server but when running the task it says
Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
I am running dts in Sql Server 2005 management studio from Management, Legacy and data Transformation Services.
Once the dts has run, I get this error message "Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error accessing Windows Event Log."
Hi guys, I am facing a confusing problem, whenever i select the add new item tab and choose add sql database, it reads an error stating 'Connections to sql server files require SQL server 2005 to be functioning properly, please verify the installation of the component or download it' The thing is, I have sql server 2005 properly installed on my pc and at the same time, whenever I choose to create a new connection from the server connection or in setting up a connection to the existing databases, I do not face any problem, so why is this issue occuring over here, I have no idea, thanks to anyone willin to resolve!
Hi.. When I try to add new item "SQL Data Base" the following error generate: Failed to generate a user instance of SQL server due to a failure in starting the process for the user instance. The connection will be closed. what is mean? what is its reason? how I can solved? Please help me as soon as possible.. Thanks..
I have inserted a new NT user via the Security Manager applet into a group associated with two databases. Now when I select 'Logins' from the Server Manager window and then double-click the newly added user, I receive this message 'Error 21770: The name '<NT username>' was not found in the Users collection.'
Can anyone please explain the above error message.
When I tried to add a new login to the server and after assigning DB Roles, I am getting the error message that the role has been already added to the user. I think this username has already been in the system earlier. It is in the sysuser table. I tried to delete the roles to which this user has permissions. But still I am getting this error. I am not sure where else it is stored. How can I delete this username completely from all Databases and reassign this userid in the server. Please advise.
I've been getting what I think is a bizarre error message whilst trying to create a foreign key between two tables.
tblDailyComments has a primary key of DiaryID tblDailyOperatingData has a column DiaryID.
Both are Int (4).
Yet when I run the following:
ALTER TABLE tblDailycomments ADD CONSTRAINT FK_tbldailycomments_tblDailyOpData FOREIGN KEY REFERENCES tblDailyOperatingData(DiaryID)
I'm getting the error message:
Server: Msg 8139, Level 16, State 1, Line 1 Number of referencing columns in foreign key differs from number of referenced columns, table 'tblDailycomments'.
I also tried to create the relationship in a database diagram and this brought back errors as well.
The subscriber was able to get the snapshot ok, then synchronise fine
However after adding an article to the publication and recreating the snapshot I get this error now at the subscriber
Microsoft.SqlServer.Replication.ComErrorException (0x80004005): A failure occurred when accessing 'usp_misc_dailyTasks_3.sch' due to an operating system error [2='The system cannot find the file specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Run()
The key properties when synchronising are as follows:
I have a clr assembly that access the internet via Sockets and file access. This require me to set the Assembly permissions level to External.
There are lots of messages on the net regarding this issue. none have worked for me.
Currently I'm trying to install the assembly with "sign the assembly" checked, createing a strong name key file.
When I run the installation I get the following message from the server
"Create failed for SqlAssembly 'xxx'.(Microsoft.sqlServer.Smo)
an exception occured while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
A severe error occured in the current command. The results, if any, should be discarded.
This is the messag I get.
The following link does not display any usefull information.
TITLE: Microsoft SQL Server Management Studio ------------------------------
Create failed for SqlAssembly 'KBTTriggers'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
A severe error occurred on the current command. The results, if any, should be discarded. A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476
I am new to ADO.NET and have a emergency problem to slove.
I am using VS 2005 to open a solution that created by previous VS. After the solution is upgraded by VS, I was gonna add a data souce to the form. The destinated database is a sample called Northwind. But right after I want to add a table called "employee" as the object database in dataset, I got an error "error using the dropdown: Could not get type information for 'Getting_Started.NorthwindDataSet'"
I created a test form and did the same procedure but got no error at all. So I guess there must be something wrong with using VS 2005 to open solution created by previous VS.
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
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
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.