Hi
I'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@.@.rowcount) which have been inserted into the database and display it in my aspx page. I am using a stored procedure.
thanks
Hello Mattock,
Have a look at the following article about using stored procedure to update data:http://msdn2.microsoft.com/en-us/library/59x02y99(VS.80).aspx
Jeroen Molenaar.
sql
An example will be very handy..
Thxsql
Code Example
Declare @.table varchar(25)
Declare @.somevalue varchar(3)
Set @.table = 'sometable'
Set @.somevalue = 'somevalue'Declare @.sqlBuild varchar(2000)
Set @.sqlBuild = 'DECLARE @.return varchar(3); ' +
' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
exec (@.sqlBuild)
i want to be able to extract the value of @.return for later use. this procdure works fine but I need to grab that value somehow.
Any suggestions?put a the end of your stored procedure select @.return ...
Use a ExecuteScalar in your code and it will be returned ...|||i'm not quite sure what you mean...
can you give me an example?|||You will need to use sp_executesql in order to capture the OUTPUT parameter from a dynamic SQL statement. Something like this (note that your @.sqlBuild needs to be of type nvarchar):
DECLARE @.table varchar(25), @.somevalue varchar(3), @.return integer, @.sqlBuild nvarchar(4000)SELECT @.table = 'sometable', @.somevalue = 'somevalue'
SELECT @.sqlBuild = ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
EXEC sp_executesql @.sqlBuild, N'@.return integer OUTPUT', @.return OUTPUT
Terrisql
Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.
Thanks,
Garick
I want to do something similar.
I want the value of the amount of records retrieved in the sub report.
The table row that the sub report is in needs to be hidden if the value is not greater than 1.
Can it be done?
|||Jabuka
I think that there's a simple way to do what you want with creating the same dataset that you have in your subreport in the parent one. Then you can evalute the field in your visibility expression.
Hope it helps you
|||Another way is to create a simple assembly (any language in .Net) and have a static (or shared) variable in it. Set the value of this variable in your subreport and refer to that in your main report.
The only problem with this approach is concurrency as you are using a static variable.
Shyam
I am using an example from ‘SQL Server 2005 Books Online’, which explain how to retrieve User-Defined Member Properties.
Using the PROPERTIES Keyword to Retrieve User-Defined Member Properties:
DIMENSION PROPERTIES [Dimension.]Level.<Custom_Member_Property>
The PROPERTIES keyword appears after the set expression of the axis specification. For example, the following MDX query the PROPERTIES keyword retrieves the List Price and Dealer Price user-defined member properties and appears after the set expression that identifies the products sold in January:
SELECT
CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members,
[Measures].[Sales Amount]) ON COLUMNS,
NON EMPTY Product.Product.MEMBERS
DIMENSION PROPERTIES
Product.Product.[List Price],
Product.Product.[Dealer Price]ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Month of Year].[January])
After running the above MDX query, I don’t see any [List Price] or [Dealer Price] and the result is exactly like running the following MDX query:
SELECT
CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members,
[Measures].[Sales Amount]) ON COLUMNS,
NON EMPTY Product.Product.MEMBERS ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Month of Year].[January])
How can I retrieve User-Defined Member Properties?
Thanks,
Yones
I found the problem, which is related to the way data is returned after execution of an MDX query. It is returned differently in Analysis Services 2000 and 2005. For example using an XMLReader, elements names are returned as follow:
AS 2000:
clXmlReader.Name:"List Price"
clXmlReader.value:"List Price value"
clXmlReader.Name:"Dealer Price"
clXmlReader.value:"Dealer Price value"
AS 2005:
clXmlReader.Name:"_x005B_ Product _x005D_._x005B_Product_x005D_._x005B_Product_x005D_._x005B_ List Price _x005D_"
clXmlReader.value:"List Price value"
clXmlReader.Name:"_x005B_ Product _x005D_._x005B_ Product _x005D_._x005B_ Product _x005D_._x005B_ Dealer Price _x005D_"
clXmlReader.value:"Dealer Price value"
How can I efficiently retrieve the top x managers within the top y regions within the top z states within the top q countries?
The only way I have been able to do this is to first find the top q countries, and store the list in an IN clause. Then for each of those, find the top z states and create another IN clause that contains the country+state concatenated. Then for each of those, find the top y regions and concatenate country+state+region. Then finally find the top x managers within this IN clause.
This works fine for a few hundred records, but once it reaches the thousands, it takes much too long. The final IN clause contains thousands of entries.
Isn't there a simpler way to approach this, especially with SQL Server 2005?
Instead of an IN clause containing a comma-delimited list, you can write a query inside the IN clause parentheses.
That might be a bit simpler. Just nest your four queries.
Now, I want to make sure I understand your example.
Let's say there are 100 countries we do business in, and there are 1000 salesmen in each country.
I sell five times more than any other salesman in the world.
However, the other 999 salesmen in my country are slack-jawed buffoons who have sold almost nothing.
So, my country has the lowest overall sales of any other country.
If your list was for top salesmen instead of top managers, would I be on it?
If you wanted the top 5 salesmen, would 4 of my slack-jawed colleagues also be on the list, since together we are the top 5 salesmen in our country?
How I might suggest to proceed would be influenced by your answers!
|||
You've nailed the complexity of this problem.
If you were the top salesman in the lowest-sales country, and we were looking at the top 50 countries, then no, you would not be selected.
If we were looking at all 100 countries and 5 salesmen in each, then you and 4 others would be selected.
We're looking for the top x in EACH of the top y ...
Because of this, I cannot get the sub-query to work, since it just ends up with the top x overall. Is there any way in SQL Server to ask for the Top x FOR EACH y?
|||give me some create table statements and insert statements for sample data, and I'll see what I can do!
hi,
I want to retrieve all instances in the network .
I am trying to use smoaplication.EnumAvailableSqlServers(true)
but the problem is i am using on sql cluster ..i am getting not only cluster instance but also instances present on the two nodes(these instances should be part of cluster and cannot be accessed)..so can u please tell me how do i just retieve only actual instances
i am using sql SMO|||Hi,
you could hide the instances from the network using the network utility, but I am not sure if the cluster things are supporting this (but probably they will)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this... I tried several alternatives that I found on the internet but none seem to work...
Thanks!
Dim objConn3As SqlConnection
Dim mySettings3AsNew NameValueCollection
mySettings3 = AppSettings
Dim strConn3AsString
strConn3 = mySettings3("connString")
objConn3 =New SqlConnection(strConn3)
Dim strInsertPatientAsString
Dim cmdInsertAs SqlCommand
Dim strddlSexAsString
Dim strddlPatientStateAsString
Dim rowsAffectedAsInteger
strddlSex = ddlSex.SelectedItem.Text
strddlPatientState = ddlPatientState.SelectedItem.Text
strInsertPatient ="Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _
"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _
"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _
"Today_Date_Year) Values (@.UserID, @.Accession, @.FirstName, @.MI, @.LastName, @.MedRecord, " & _
"'" & strddlSex &"', @.DOB, @.Address1, @.Address2, @.City, @.Suite , '" & strddlPatientState &"', " & _
"@.ZIP, @.HomeTelephone, @.OutsideNYC, @.ClinicalImpression, @.Today_Date_Month, @.Today_Date_Day, " & _
"@.Today_Date_Year)SELECT @.@.IDENTITY AS NewID SET NOCOUNT OFF"
cmdInsert =New SqlCommand(strInsertPatient, objConn3)
cmdInsert.Parameters.Add("@.UserID","Joe For Now")
cmdInsert.Parameters.Add("@.Accession", Accession.Text)
cmdInsert.Parameters.Add("@.LastName", LastName.Text)
cmdInsert.Parameters.Add("@.MI", MI.Text)
cmdInsert.Parameters.Add("@.FirstName", FirstName.Text)
cmdInsert.Parameters.Add("@.MedRecord", MedRecord.Text)
cmdInsert.Parameters.Add("@.ddlSex", strddlSex)
cmdInsert.Parameters.Add("@.DOB", DOB.Text)
cmdInsert.Parameters.Add("@.Address1", Address1.Text)
cmdInsert.Parameters.Add("@.Address2", Address2.Text)
cmdInsert.Parameters.Add("@.City", City.Text)
cmdInsert.Parameters.Add("@.Suite", Suite.Text)
cmdInsert.Parameters.Add("@.strddlPatientState", strddlPatientState)
cmdInsert.Parameters.Add("@.ZIP", zip.Text)
cmdInsert.Parameters.Add("@.HomeTelephone", Phone.Text)
cmdInsert.Parameters.Add("@.OutsideNYC", OutsideNYC.Text)
cmdInsert.Parameters.Add("@.ClinicalImpression", ClinicalImpression.Text)
cmdInsert.Parameters.Add("@.Today_Date_Month", Today_Date_Month.Text)
cmdInsert.Parameters.Add("@.Today_Date_Day", Today_Date_Day.Text)
cmdInsert.Parameters.Add("@.Today_Date_Year", Today_Date_Year.Text)
objConn3.Open()
cmdInsert.ExecuteNonQuery()
objConn3.Close()
Try this - a zillion ways to get Scope_Identity back:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54
