Showing posts with label datasource. Show all posts
Showing posts with label datasource. Show all posts

Friday, March 23, 2012

problems with Publish Reports Script File

How do i make the datasources same path for each report loaded
I have 5 group sets
I end up manually change the datasource for each one
If i can make this script files to do it

what changes do i need to make ?

__
' Publish Reports
' Adapted from Microsoft Publish Reports Sample Script File


Dim parentfolder as string
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = "/" + parentFolder
Dim filePath As String

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim name As String


parentfolder = "DataSources"
parentpath = "/" + parentFolder

'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

CreateSampleDataSource()


'Publish the sample reports

parentfolder = "Polling Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\Polling Reports\"


PublishReport("Polling Interest")

parentfolder = "Mail Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\Mail Reports\"


PublishReport("Code Usage")
PublishReport("Communication History")
PublishReport("Mail Staff Load")
PublishReport("Outstanding Mail Staff")
PublishReport("Outstanding Mail Summary")
PublishReport("Top Code Usage")
PublishReport("Group Mail Summary")

parentfolder = "Casework Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try


filePath = "c:\apps\Corr6\Server\Reporting Services\Casework Reports\"

PublishReport("Casework Closed")
PublishReport("Casework County")
PublishReport("Casework Detail")
PublishReport("Casework Outstanding")
PublishReport("Casework Staff")
PublishReport("Casework Summary")

parentfolder = "WorkFlow Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try


filePath = "c:\apps\Corr6\Server\Reporting Services\workflow reports\"

PublishReport("Staff Productivity Report")
PublishReport("Overdue Tasks")

parentfolder = "System Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\System Reports\"
PublishReport("Interest System Codes")
PublishReport("Letter System Codes")
PublishReport("Personal System Codes")
PublishReport("System Staff")

End Sub

Public Sub CreateSampleDataSource()
Dim name As String = "DataSource"
Dim parent As String = "/" + parentFolder

'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "data source=(local);initial catalog=cap_win"
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False

Try
rs.CreateDataSource(name, parent, False, definition, Nothing)

Catch e As Exception
Console.WriteLine(e.Message)
End Try

End Sub

Public Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()

Catch e As IOException
Console.WriteLine(e.Message)
End Try

Try
warnings = rs.CreateReport(reportName, parentPath, False, definition, Nothing)

If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning

Else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
End If

Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub

Anybody know what to do ?|||

can it be done ?
any reference where i might get the answer ?

problems with Publish Reports Script File

How do i make the datasources same path for each report loaded
I have 5 group sets
I end up manually change the datasource for each one
If i can make this script files to do it

what changes do i need to make ?

__
' Publish Reports
' Adapted from Microsoft Publish Reports Sample Script File


Dim parentfolder as string
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentPath As String = "/" + parentFolder
Dim filePath As String

Public Sub Main()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim name As String


parentfolder = "DataSources"
parentpath = "/" + parentFolder

'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

CreateSampleDataSource()


'Publish the sample reports

parentfolder = "Polling Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\Polling Reports\"


PublishReport("Polling Interest")

parentfolder = "Mail Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\Mail Reports\"


PublishReport("Code Usage")
PublishReport("Communication History")
PublishReport("Mail Staff Load")
PublishReport("Outstanding Mail Staff")
PublishReport("Outstanding Mail Summary")
PublishReport("Top Code Usage")
PublishReport("Group Mail Summary")

parentfolder = "Casework Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try


filePath = "c:\apps\Corr6\Server\Reporting Services\Casework Reports\"

PublishReport("Casework Closed")
PublishReport("Casework County")
PublishReport("Casework Detail")
PublishReport("Casework Outstanding")
PublishReport("Casework Staff")
PublishReport("Casework Summary")

parentfolder = "WorkFlow Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try


filePath = "c:\apps\Corr6\Server\Reporting Services\workflow reports\"

PublishReport("Staff Productivity Report")
PublishReport("Overdue Tasks")

parentfolder = "System Reports"
parentpath = "/" + parentFolder
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try

filePath = "c:\apps\Corr6\Server\Reporting Services\System Reports\"
PublishReport("Interest System Codes")
PublishReport("Letter System Codes")
PublishReport("Personal System Codes")
PublishReport("System Staff")

End Sub

Public Sub CreateSampleDataSource()
Dim name As String = "DataSource"
Dim parent As String = "/" + parentFolder

'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "data source=(local);initial catalog=cap_win"
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False

Try
rs.CreateDataSource(name, parent, False, definition, Nothing)

Catch e As Exception
Console.WriteLine(e.Message)
End Try

End Sub

Public Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()

Catch e As IOException
Console.WriteLine(e.Message)
End Try

Try
warnings = rs.CreateReport(reportName, parentPath, False, definition, Nothing)

If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning

Else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
End If

Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub

Anybody know what to do ?|||

can it be done ?
any reference where i might get the answer ?

Problems with ProcessAdd and secondary DataSource

We have a SSAS 2005 cube in place and have been using ProcessAdd XMLA for some time with success. The current environment has a single datasource called 'Sales' and a single DSV called 'Sales'. The sales datasource is a DB2 database. Everything works great.

In preperation for adding some new data sources, we have built a version in our test environment that is configured with two data sources and a single DSV. 'Master' is the primary DS and it points to an empty DB on SQL Server. 'Sales' is a secondary DS and it points to the original DB2 database. There is a single DSV that is called 'Master'. Everything works well through SSMS, but I can't seem to build the correct XMLA to get ProcessAdd working again.

If I build it with the 'Master' datasource and DSV it gives me a bunch of errors that arent specific enough for me to take action. Things like

Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DIM IBU HIER VER', Name of 'IBU' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile=""

If I build the xmla for ProcessAdd with the 'Sales' datasource and 'Master' DSV and then rename 'Master' to 'Sales' it appears to work and executes successfuly.. However, when you watch the activity on the profiler, it is not using the query from the out-of-line DSV query but instead it goes back to SSAS DB and uses the query stored there.

All of the ProcessAdd examples I have seen seem to deal with a single DSV and DS.

Can there be multiple datasources in the ProcessAdd xmla?

Is it possible to make this scenario work?

Thanks,

Marty

Have you seen Greg's post on ProcessAdd at http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx ?

Apparently the out-of-line dsv needs to have the same ID as the datasource that was used to orginally build the object and the data sources need to be included in the xmla Process element (this is a bug) - see the notes in his process dimension example.

|||Darren, Thanks for the reply. I still couldnt get it to work. Something about the two data sources seemed to throw it off. What I have decided to do instead is use a combination of ALTER DSV and a very simple ProcessAdd XMLA that will tell SSAS to processAdd using the in-line query. In our environment this actually provides a simpler and easier to maintain solution.

Thanks again,
Marty

Problems with ProcessAdd and secondary DataSource

We have a SSAS 2005 cube in place and have been using ProcessAdd XMLA for some time with success. The current environment has a single datasource called 'Sales' and a single DSV called 'Sales'. The sales datasource is a DB2 database. Everything works great.

In preperation for adding some new data sources, we have built a version in our test environment that is configured with two data sources and a single DSV. 'Master' is the primary DS and it points to an empty DB on SQL Server. 'Sales' is a secondary DS and it points to the original DB2 database. There is a single DSV that is called 'Master'. Everything works well through SSMS, but I can't seem to build the correct XMLA to get ProcessAdd working again.

If I build it with the 'Master' datasource and DSV it gives me a bunch of errors that arent specific enough for me to take action. Things like

Error ErrorCode="3240034316" Description="Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DIM IBU HIER VER', Name of 'IBU' was being processed." Source="Microsoft SQL Server 2005 Analysis Services" HelpFile=""

If I build the xmla for ProcessAdd with the 'Sales' datasource and 'Master' DSV and then rename 'Master' to 'Sales' it appears to work and executes successfuly.. However, when you watch the activity on the profiler, it is not using the query from the out-of-line DSV query but instead it goes back to SSAS DB and uses the query stored there.

All of the ProcessAdd examples I have seen seem to deal with a single DSV and DS.

Can there be multiple datasources in the ProcessAdd xmla?

Is it possible to make this scenario work?

Thanks,

Marty

Have you seen Greg's post on ProcessAdd at http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx ?

Apparently the out-of-line dsv needs to have the same ID as the datasource that was used to orginally build the object and the data sources need to be included in the xmla Process element (this is a bug) - see the notes in his process dimension example.

|||Darren, Thanks for the reply. I still couldnt get it to work. Something about the two data sources seemed to throw it off. What I have decided to do instead is use a combination of ALTER DSV and a very simple ProcessAdd XMLA that will tell SSAS to processAdd using the in-line query. In our environment this actually provides a simpler and easier to maintain solution.

Thanks again,
Marty

Monday, March 12, 2012

problems with insert query and primary key

I am trying to run an insert query off of a sql datasource and I am erroring out. My code and stored procedure as of now are listed below. You will notice the section of the stored procedure that is pulling the value for facility_ID (primary key). I have also tried to pull these and pass the parameter from a label, but that does not work, giving an error that the stored procedure expects the parameter @.Facility_ID which was not supplied. One other odd thing is that stepping through the code, I watched the parameter count total 21, but when running the insert command, the insert parameter count shows 20. With the code below (my current project), I get an error that null values can not be entered for facility_ID. Please help.

CODE:

Dim myConnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("FacilitiesBuild").ConnectionString)

'open myconnection

myConnection.Open()

Dim myCommand As New Data.SqlClient.SqlCommand(SqlDataSourceFac.InsertCommand, myConnection)

myCommand.CommandType = Data.CommandType.StoredProcedure

myCommand.Parameters.Add("@.Name", Data.SqlDbType.VarChar, 50).Value = CType(Me.DetailsView1.FindControl("Textbox5"), TextBox).Text
myCommand.Parameters.Add("@.Address1", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox3"), TextBox).Text
myCommand.Parameters.Add("@.Address2", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox4"), TextBox).Text
myCommand.Parameters.Add("@.State", Data.SqlDbType.VarChar, 2).Value = CType(Me.DetailsView1.FindControl("Textbox17"), TextBox).Text
myCommand.Parameters.Add("@.Zip", Data.SqlDbType.VarChar, 10).Value = CType(Me.DetailsView1.FindControl("Textbox6"), TextBox).Text
myCommand.Parameters.Add("@.Phone", Data.SqlDbType.VarChar, 14).Value = CType(Me.DetailsView1.FindControl("Textbox7"), TextBox).Text
myCommand.Parameters.Add("@.Admin_Name", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox8"), TextBox).Text
myCommand.Parameters.Add("@.Comments", Data.SqlDbType.VarChar, 250).Value = CType(Me.DetailsView1.FindControl("Textbox10"), TextBox).Text
myCommand.Parameters.Add("@.Owner", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeOwnerInsert"), Label).Text
myCommand.Parameters.Add("@.Beds", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("BedsInsert"), Label).Text
myCommand.Parameters.Add("@.Population", Data.SqlDbType.NText).Value = CType(Me.DetailsView1.FindControl("PopulationInsert"), Label).Text
myCommand.Parameters.Add("@.Type_Facility", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeFacilityInsert"), Label).Text
myCommand.Parameters.Add("@.Type_Other", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("TypeOtherInsert"), Label).Text
myCommand.Parameters.Add("@.Profit", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ProfitInsert"), Label).Text
myCommand.Parameters.Add("@.Religious", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ReligiousInsert"), Label).Text
myCommand.Parameters.Add("@.Licensed", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("LicensedInsert"), Label).Text
myCommand.Parameters.Add("@.Active", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ActiveInsert"), Label).Text
myCommand.Parameters.Add("@.City_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertCityLabel"), Label).Text
myCommand.Parameters.Add("@.Agency_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertAgencyLabel"), Label).Text
myCommand.Parameters.Add("@.County", Data.SqlDbType.NVarChar, 3).Value = CType(Me.DetailsView1.FindControl("InsertCountyLabel"), Label).Text
myCommand.Parameters.Add("@.Facility_ID", Data.SqlDbType.VarChar, 6).Value = CType(Me.DetailsView1.FindControl("InsertFacilityLabel"), Label).Text

If CType(Me.DetailsView1.FindControl("DropDownList1"), DropDownList).SelectedItem.Text = "Please Select One" Then
MsgBox("You must select an agency")
Else : SqlDataSourceFac.Insert()
End If

STORED PROCEDURE:

ALTER PROCEDURE [dbo].[SP_OMBFacilityAddDOTNET]


@.Name varchar(50),
@.Address1 varchar(40),
@.Address2 varchar(40),
@.State varchar(2),
@.Zip varchar(10),
@.Phone varchar(14),
@.Admin_Name varchar(40),
@.Comments varchar(250),
@.Owner char(1),
@.Beds int,
@.Population numeric(10,0),
@.Type_Facility char(1),
@.Type_Other varchar(40),
@.Profit char(1),
@.Religious char(1),
@.Licensed char(1),
@.Active char(1),
@.City_ID int,
@.Agency_ID int,
@.County nvarchar(3)

AS
BEGIN
DECLARE @.Facility_ID varchar(6)
DECLARE @.nextID varchar(3)

/* get next facilityID */
SELECT @.nextID = MAX(RIGHT(Facility_ID, LEN(Facility_ID)-(CHARINDEX('-', Facility_ID)))) + 1
From OMBFacility
Where Agency_ID = @.Agency_ID

SELECT @.Facility_ID = CAST(@.Agency_ID AS varchar(2)) + '-' + RIGHT('000' + RTRIM(@.nextID), 3)
INSERT INTO [AIMS].[dbo].[OMBFacility]
([Name],
[Address1],
[Address2],
[State],
[Zip],
[Phone],
[Admin_Name],
[Comments],
[Owner],
[Beds],
[Population],
[Type_Facility],
[Type_Other],
[Profit],
[Religious],
[Licensed],
[Active],
[City_ID],
[Agency_ID],
[County],
[Facility_ID])

VALUES
(@.Name
,@.Address1
,@.Address2
,@.State
,@.Zip
,@.Phone
,@.Admin_Name
,@.Comments
,@.Owner
,@.Beds
,@.Population
,@.Type_Facility
,@.Type_Other
,@.Profit
,@.Religious
,@.Licensed
,@.Active
,@.City_ID
,@.Agency_ID
,@.County
,@.Facility_ID)

END

Here you are:Getting the identity of the most recently added record

|||

I don't mean to be a moron, but will that work in a stored procedure as well? Where would I place "SET @.NewId = Scope_Identity()" in the stored procedure? I tried it at the first using both NewId thinking I could declare it later and Facility_ID which is the name of my primary key, but either way it tells me I have to declare the variable. This error comes up whether or not I declare the variable with the main set of variables in the stored procedure.

|||

lukeduke:

I don't mean to be a moron

Sorry - I'm the moron for not reading your post correctly. I saw the subject and had an almost Pavlovian reaction. The article specifically talks about getting the value of autoincrement fields. Your's isn't doing that. I need to study your post a bit more carefully.

|||

That's cool man, wipe the slobber off of your keyboard. Have you got any idea on what's causing the issue, I am pulling hair out trying to find it.

|||

If i am reading this all correctly here might be a solution:

If you want the ID of the newly inserted record(facility_ID), dont pass that value to the stored procedure, but declare that variable in the procedure like you have

declare @.faility_ID int; set @.facility_ID = 0

dont have it in the insert list, so the list should end with County and @.County. After the insert code run this:

INSERT INTO [AIMS].[dbo].[OMBFacility] ([Name], [Address1], [Address2], [State], [Zip], [Phone], [Admin_Name], [Comments], [Owner], [Beds], [Population], [Type_Facility], [Type_Other], [Profit], [Religious], [Licensed], [Active], [City_ID], [Agency_ID], [County])VALUES (@.Name ,@.Address1 ,@.Address2 ,@.State ,@.Zip ,@.Phone ,@.Admin_Name ,@.Comments ,@.Owner ,@.Beds ,@.Population ,@.Type_Facility ,@.Type_Other ,@.Profit ,@.Religious ,@.Licensed ,@.Active ,@.City_ID ,@.Agency_ID ,@.County)
if(@.@.error = 0)begin set @.facility_ID = scope_identity()end

and have a

select @.facility_ID facilityID

at the end to return the newly inserted ID, this will return a value > 0 if success and 0 if failure.

|||

kyle.spitzer:

If i am reading this all correctly here might be a solution:

I think you fell into the same trap as I did. Scope_Identity will only return an Identity column. Lukeduke's constructing his own varchar PK.

@.LukeDuke:

Could you post some more code so I have half a chance of knocking up a sample page to test? The aspx perhaps? Or just the relevant parts?

|||

Let me try a couple of things that were suggested first. If they don't work, I'll try and build a mockup to post the code for. The app I'm building now is a beast with seven datasources pulling from different tables, a gridview and two detailsview that are both using about 15 template fields each. I'll post the mockup this afternoon or tomorrow morning if my other attempts fail. Thanks for your input so far.

|||

One thing that Kyle did point out though - you have @.FacilityID in the parameter list, but whatever value you have set that doesn't appear to be used anywhere in the procedure. You have declared @.FacilityID as a local variable in the proc and are setting its value purely internally. I can't see why you have added it to the datasource control's parameter collection.

|||

That is where everything gets weird. Earlier in the code, I call a stored procedure that fetches the next facility id and assigns it to the text property of a databound label (the one that supplies the parameter). I run the program in VS2005 debug mode using autoS and set a breakpoint when the parameters are being passed. I step through the code and when the paramter collection is completed, autoS shows that the parameter count is 21.

The next step in the code is to make sure the user selected an agency and to run the insert command of the data source. When stepping to that point, autoS shows that the insertparameter count is 20. Each time facility_ID is dropped. I have moved it up in the list of parameters being passed thinking maybe the bottom parameter is being ignored, but it drops the value for facility_ID each time. I have tried to remove the databinding from the label and I get the same error. Each time I get, the stored procedure SP_OMBFacilityAddDOTNET expects the parameter @.Facility_ID which was not supplied.

Does that have anything to do with the Facility_ID being a primary key (and foreign key to other tables)?

There are two options I am going to try now. First, I'm going to make the facility_ID generate in an event that would fire before the button click event that is populating the parameter collection.

The other option I am going to attempt is to call the stored procedure that returns the new facility_ID inside of my the current stored procedure. The tricky part of that is the Facility_ID is in the format of Agency_ID - Facility_ID or 2-125 for example. In my label, I have to combine the two fields to set the text property.

|||

I fixed the problem. I had to get another eye on the code, but the parameter collection was not passing to the sqldatasource. The way I worked it out was to go into the properties of the sqldatasource and declare the parameters on the insert query. I then used session variables to pass the parameters and everything is working fine.

Thanks for all of your help!

|||

Yes, your original code seemed a little odd.

You built up a sqlcommand object using the stored procedure name stored in your SqlDatasource, manually added a bunch of parameters and assigned the values to them, and then you executed the insert of the SqlDatasource (instead of the SqlCommand).

The SqlCommand, and the parameters you built aren't being used at all.

I'm glad you got it finally working, however, you may want to take a look at implementing your code inside the SqlDatasource_Inserting event instead. Inside there, you can assign the values (or modify the default ones). That way you aren't stuffing arbitrary values into the session object that really don't belong in session. Although, I'm a little confused as to why you aren't using two-way databinding since it appears all the data is coming from a detailsview object.