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.
|||
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.
No comments:
Post a Comment