index fragmentation
database statictics
disk i/o
hope this helps,
Derek
sqlindex fragmentation
database statictics
disk i/o
hope this helps,
Derek
sqlThere are only two parameters passed to the SP. The problem is slightly complicated to explain fully but essentially what's happening is that the second and third IF statements do not return any values and I've no idea why, as the SELECT statement is almost identical in all four.
ALTER PROCEDURE developers.fort_GetSearchResults
(
@.propertyType int,
@.county int
)
AS
IF ((@.propertyType = 0) AND (@.county = 0))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps,
Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
ORDER BY NEWID()
ELSE
IF (@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)
ELSE
IF (@.county = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode,
Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax,
Experimental_ContactDetails.email, Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps,
Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_ContactDetails.county = @.county)
ORDER BY NEWID()
ELSE
IF ((@.propertyType != 0) AND (@.county != 0))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription, Experimental_Accommodation.description,
Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading,
Experimental_Images.filename, Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)
AND (Experimental_ContactDetails.county = @.county)
ORDER BY NEWID()
RETURN
Condition 2 and 3 may have some no inclusives here:
1. ((@.propertyType = 0) AND (@.county = 0))
2. ((@.propertyType = 0) AND (@.county != 0))
3. ((@.propertyType != 0) AND (@.county = 0))
4. ((@.propertyType != 0) AND (@.county != 0))
Could you try these conditions and check SQL statement with hard coded value whether they are returning results/
From your script, my guess is:
1. ((@.propertyType = 0) AND (@.county = 0))
you don't use your parameters' value in your sql
2. ((@.propertyType = 0) AND (@.county != 0))
you may need (@.county value in your sql instead of @.propertyType
3. ((@.propertyType != 0) AND (@.county = 0))
you may need (@.propretyType value in your sql instead of @.county
4. ((@.propertyType != 0) AND (@.county != 0))
you use both parameters' values here.
Let us know if you get this work.
Limno
|||
IF(@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)
Do you have any propertyTypes that are 0?
Motley wrote:
IF(@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)Do you have any propertyTypes that are 0?
In any case, if there wasn't a propertyType of 0, the IF ((@.propertyType = 0) AND (@.county = 0)) line wouldn't work either, and it does. It's strange that the only SELECT statements that don't work are the ones with only a single parameter checked by the IF statement.
Probably easy enough to determine.
sp_helptext sp_helpdb
And they can read the text for themselves.
I don't see anything limiting to 24 characters at a quick pass. The user
defined datatype of sysname should be limited to 128 characters.
Perhaps someone changed it?|||Eyal Goren (eyal_goren@.bmc.com) writes:
> One of our customers claims that the sp_helpdb truncates database name
> larger than 24 characters, the release is 7.00.1094, did any one
> encounter such a problem ?
Yes, I was able to repeat this problem on an SQL7 server. It turns
out that in the final SELECT, there is an explicit substring():
select name = substring(d.name, 1, 24),
db_size = str(sum(convert(dec(15),v.size))*
(select low from master.dbo.spt_values
where type = 'E' and number = 1)
/ 1048576,10,2)+ ' MB',
owner = substring(suser_sname(d.sid), 1, 24),
dbid = d.dbid,
created = convert(char(11), d.crdate),
status = s.dbdesc
from master.dbo.sysdatabases d, #spfiledesc v, #spdbdesc s
where d.dbid = s.dbid
and s.dbid = v.dbid
group by d.name,d.sid,d.dbid,d.crdate,s.dbdesc
order by d.name
If you are really venturesome you could change the procedure yourself,
but note that this is definitely not supported.
This problem does not appear in SQL2000.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
I have facing this problem, and i don't know what is going wrong.
The SP when tested in the Query Analyser works fine.
the resultant is either a 0 or PID where 0 is returned when the insert doesn't happen
I might have some extra declaration, which might be causing this error , or not defining the right thing.
Any suggestion is welcomed and greatly appreciated.
Thanks
Is PID a return value, or an output parameter? You should use an output parameter to return an ident from a sproc.
Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim sSQLConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim daInsertSku As SqlDataAdapter = New SqlDataAdapter()
Try
sSQLConnection.Open()
Dim cmdInsert As New SqlCommand("sp_request", sSQLConnection)
cmdInsert.CommandType = CommandType.StoredProcedurecmdInsert.Parameters.Add("@.Username", SqlDbType.Int, 4)
cmdInsert.Parameters("@.Username").Value = "0"Dim PID As SqlParameter = New SqlParameter("@.identity", SqlDbType.Int)
PID.Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(PID)daInsertSku.InsertCommand = cmdInsert
cmdInsert.ExecuteNonQuery()
Response.Write(PID.Value)Catch Exc As Exception
' error handling
Finally
' close connection
End Try
End Sub
And what is wrong? Are you getting an error?|||I did get it working, i changed to Output Parameter instead or Return
thank yousql
I am using c# in Visual Studio 2005 to run a stored procedure in a sql 2000 databse. this is the stored procedure
CREATE procedure dbo.returnVendor4
@.vendorID varchar(10),
@.name nvarchar output
as
select @.name = vendor_Name
from vendors
where Code = @.vendorID
I steped through the c# code, it gives me an error when the procedure is ran (at the ExecuteNonQuery() part). this is the error message
"String[0]: the Size property has an invalid size of 0."
I have a similar procedure that returns an int, and it works okay. My c# code that calls the procedure is below
string retVal ;
SqlConnection con = new SqlConnection(
"server = lissadell; database = s00048271; uid = s00048271; pwd = 'chocolate'");
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "returnVendor4";
cmd.Parameters.Add("name", SqlDbType.NVarChar);
cmd.Parameters.Add("vendorID", SqlDbType.VarChar);
cmd.Parameters["name"].Direction = ParameterDirection.Output;
cmd.Parameters["vendorID"].Value = "G/03/I";
cmd.ExecuteNonQuery();
retVal = (string)(cmd.Parameters["name"].Value);
textBox1.Text = retVal.ToString() ;
just an idea - have you tried switching the order of your Parameters.Add statements? I think SQL will want the input parameter first. No idea if this will make a difference, just something off the top of my head.
Another guess - can you specify the size of the parameters when you add them? i.e. varchar(10)?
|||i tried both ideas, they didn't work|||IN your Stored Procedure you need to add a length to your @.name parameter
@.name nvarchar(50) output.
Also, I think your C# Parameter.Add syntax may be a little off (Been a while since I coded in C#), try
cmd.Parameters.Add("@.name", SqlDbType.NVarChar,50);
cmd.Parameters.Add("@.vendorID", SqlDbType.VarChar,10);
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.
Hi,
I have this stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_insertpositionsheader]
@.settlementDate DATETIME,
@.description VARCHAR(50),
@.calculatedUsed INT = 0,
@.refRecId INT OUTPUT
AS
SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions
WHERE SettlementDate = @.settlementDate
IF (@.refRecId = 0)
INSERT INTO ARSNettedPositions (SettlementDate, Description, CalculatedUsed) VALUES (@.settlementDate, @.description, @.calculatedUsed) SET @.refRecId = SCOPE_IDENTITY()
RETURN @.refRecId
I call it from my code, like this:
sqlCommand = new SqlCommand();
sqlCommand.Connection = dbConnection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "sp_insertpositionsheader";
sqlCommand.Parameters.AddWithValue("@.settlementDate", settlementDate);
sqlCommand.Parameters.AddWithValue("@.description", "MAN Settlement report");
sqlCommand.Parameters.AddWithValue("@.calculatedUsed", isCalculated);
SqlParameter refRecIdParameter = new SqlParameter("@.refRecId", SqlDbType.Int);
refRecIdParameter.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add(refRecIdParameter);
sqlCommand.ExecuteScalar();
refRecId = (int)refRecIdParameter.Value;
On the last line, I get "specified cast is not valid" error. I don't know why. And when I delete IF in the stored procedure, everything runs ok (but I get wrong number ).
Could you help me explain it?
Change the top to:
ALTER PROCEDURE [dbo].[sp_insertpositionsheader]
@.settlementDate DATETIME,
@.description VARCHAR(50),
@.calculatedUsed INT = 0,
@.refRecId INT OUTPUT
AS
SET NOCOUNT ON
SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions
.
.Helps not.
Situation is still the same.
|||OHHHHH. Now that I look at it, you are missing BEGIN/END on your IF. Change it to:SET NOCOUNT ON
SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions
WHERE SettlementDate = @.settlementDate
IF (@.refRecId = 0)
BEGIN
INSERT INTO ARSNettedPositions (SettlementDate, Description, CalculatedUsed) VALUES (@.settlementDate, @.description, @.calculatedUsed)
SET @.refRecId = SCOPE_IDENTITY()
END
RETURN @.refRecId
You are running 2 statements under your IF which requires BEGIN/END. I have a rule of ALWAY using BEGIN/END after IF, even when technically unnecessary because you never know when you will want 2 statements.
Thanks,
Bcs you are doing integer division.
use the following sampel to fix yours..
Code Snippet
Select
1/Count(*) [1/Count(*)], --Wrong, Integer Division
1.0/Count(*) [1.0/Count(*)], --Partially Correct
1.0/Cast(Count(*) as Float) [1.0/Cast(Count(*) as Float)], --Perfect Expression
Log10(1.0/Cast(Count(*) as Float)) [Your Expression] --This is your answer
From
Sysobjects
|||Thanks a lot. It helped.Thanks,
Bcs you are doing integer division.
use the following sampel to fix yours..
Code Snippet
Select
1/Count(*) [1/Count(*)], --Wrong, Integer Division
1.0/Count(*) [1.0/Count(*)], --Partially Correct
1.0/Cast(Count(*) as Float) [1.0/Cast(Count(*) as Float)], --Perfect Expression
Log10(1.0/Cast(Count(*) as Float)) [Your Expression] --This is your answer
From
Sysobjects
|||Thanks a lot. It helped.hi
im using a stored procedure to insert float numbers in a data base...the parameter i send to the stored procedure is a float but every time i use it, it inserts a number with a lot of extra numbers i mean if i try to insert 5.3 it inserts 5.30000019073486 any idea why this is happening?
thak you
Himarcelocr2,
Have you done any sort of calculation before add/update or during you retrieve the float value?
Anyway, try this before you update your float value:
Math.Round(yourFloatValue, numberOfDecimal)
Hope this is help
|||What is the datatype of the column in the table that will have these values? If you only need 3 digits after the decimal use the decimal datatype and set the precision to 3. You can do this in the column properties under design view. Enterprise Mgr -> Right click on the table -> Design View.
|||hi
thank you to both of you
i tried with the math.round, but it converts the numbers to integers, and i need the numbers to be float numbers...
i cant change the type in the sqlserver because i need it to be a float number
is there a way to show only 3 or 4 numbers after the ","?
thank you
|||
Hello,
You should let is save like that only and should use round(SQL function-round(fieldname,4)) in Stored Procedures where you retrive data.
|||
Hi,
The Float datatype in both SQL Server and .NET framework is a floating point value. It is not accurate but an approximate value. This is defined by way that float is stored in memory.
However, if you need an accurate value, try to use Decimal instead in both SQL Server and .NET framework.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!