Return NewSequentialID() From SP
Apr 24, 2008Hi All
I am using SQL Server 2005 and creating a insert SP now. How could I return the newsequentialID() after the record inserted successfully?
Regards
Alex
ADVERTISEMENT
Obtain The Last NEWSEQUENTIALID()
Nov 14, 2006I'm using sql server 2005, I created a customer table and set the customer_id as an uniqueidentifier that is a NEWSEQUENTIALID()...when a user is created I want to obtain the last customer_id so that I can insert it in another table that has a one to many relationship with the customer table. Please help...how do i do this?.Thanx
View 3 Replies View RelatedGUIDs Using NEWSEQUENTIALID() On SQL Server Mobile
Feb 28, 2006Hello,
I've been thinking about using GUIDs as primary keys within my application. One problem I have with this approach is that GUIDs generated with NEWID() are not sequential, and this can have a huge impact on insertion performance due to fragmented indexes. It would seem that NEWSEQUENTIALID() solves this issue, however it doesn't seem very practical to use this on SQL Server Mobile. With SQL Server, you can specify an OUTPUT clause for an INSERT statement, and using this, determine what the last inserted GUID was. There doesn't seem to be such an option in SQL Server Mobile, so how can I get the last generated GUID so that I can attach children records to my parent record? It's been suggested that you could use the MAX() function to get the latest GUID, but there is a problem with this approach - NEWSEQUENTIALID() guarantees that the GUIDs generated will be sequential for the machine generating them, but makes no guarantees about how this will fit in with GUIDs generated by other machines. It is entirely possible that GUIDs generated by a different machine will be larger than the latest one generated on the current machine, so using MAX() to determine the latest value doesn't seem like a valid option to me.
Does anybody have any ideas on tackling this problem? Any thoughts on the suitability of using GUIDs as the primary key for SQL Server Mobile devices in general?
Thanks,
Adrien.
SQL 2012 :: NewSequentialID - Error Validating The Default For Column
Oct 8, 2014Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
we are using the default of newsequentialID() as the key on some tables and getting the error
Error validating the default for column xxx
When first saving the table if the value is entered as newsequentialid() the save works but on any subsequent design change I get the error.
If you click Yes to continue it works fine.
The value that is in there once its saved shows up as (newsequentialid()) if you change it back to not have the outer parenthesis it saves without issue.
Return Error Code (return Value) From A Stored Procedure Using A Sql Task
Feb 12, 2008
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
CREATE procedure [dbo].[Marketing_extract_history_load_TEST]
@table_name varchar(200),
@i int output
as
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
How To Return SqlDataReader And Return Value (page Count) From SPROC
Jan 2, 2006This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
'Return' Statement In A Function,Get,or Operator Must Return A Value....Question
Aug 28, 2007
I'm a Reporting Services New-Be.
I'm trying to create a report that's based on a SQL-2005 Stored Procedure.
I added the Report Designer, a Report dataset ( based on a shared datasource).
When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.
I'll only reproduce one instance of the error for the sake of brevity.
[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.
I've searched on this error and it looks like it's a Visual Basic error :
http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx
My guess is that BIDS is creating some VB code behind the scenes for the report.
I can't find any other information that seems to fit this error.
The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.
BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.
Can anyone help ?
Thanks,
Using Output Clause And Return Timestamp And Return Value
Oct 28, 2006I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
Thanks for help on this
SM haig
Why SP Return SQLException When No Data Return ?
Jul 24, 2006hi, good day,
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
thanks in advance
Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure
Aug 19, 2015I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
Return Value From SP
Dec 6, 2006I have the following SP
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE spGetResettedPassword@Email nvarchar(50)ASBEGIN SET NOCOUNT ON; declare @newid uniqueidentifier set @newid=newid() declare @newPass nvarchar(10) set @newPass=convert(nvarchar(100),@newid) set @newPass=substring(@newPass,1,7) UPDATE aspnet_Membership SET Password=@newPass WHERE Email=@Email return @newPassENDGO
When calling it from code like this: MyConnection.Open() cmd = New SqlCommand("spGetResettedPassword", MyConnection) cmd.CommandType = Data.CommandType.StoredProcedure myTrans = MyConnection.BeginTransaction() cmd.Transaction = myTrans fBeginTransCalled = True bSuccess = False cmd.Parameters.Add(New SqlParameter("@Email", Email)) Dim NewPassword As Object = cmd.ExecuteScalar If NewPassword <> Nothing And CType(NewPassword, String) <> "" _ And TypeOf (NewPassword) Is String Then Return CStr(NewPassword) Else Return "ERROR, CONTACT US ON THE SITE" End If bSuccess = True fBeginTransCalled = False
I get the following error:
Conversion failed when converting the nvarchar value '40DC5F3' to data type int.
What is happening here?!?!
Return Value From SQL
Jun 14, 2008I am trying to get the following to work, here is the stored procedureSTORED PROCEDURE (@varA varchar(25),@varb varchar(25))AS IF EXISTS (SELECT column1 FROM table1 WHERE nameA=@NameA)RETURN 01IF EXISTS (SELECT column2 FROM table1 WHERE nameB=@nameB)RETURN 02ELSE BEGIN INSERT INTO table1 (nameA, NameB) VALUES (@nameA, @nameB) RETURN 03 END **************************************************************************** dim cn as New sqlConnection(connectionstring) dim cm as sqlcommand cn.Open() With Cm .CommandType = CommandType.StoredProcedure .CommandText = "STOREDPROCEDURE" .Connection = cn End With 'INSERT Names Cm.Parameters.Add("@nameA", SqlDbType.VarChar, 25).Value = nameA.text Cm.Parameters.Add("@nameB", SqlDbType.VarChar, 25).Value = nameB.text param_returnValue = Cm.Parameters.Add("RETURN_VALUE", SqlDbType.Int) param_returnValue.Direction = ParameterDirection.ReturnValue*******************************************************************************************This is where I get stuck. I need to display certain error messages depending on the return value.******************************************************************************************* dim insertStatus as integer insertStatus = Cm.ExecuteScalar() If insertStatus = 01 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 02 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 9999 Then Response.Redirect("success.aspx") End If End If End If I am pretty sure the IF statements arent the way to go especially because it is not working but i am curious to how I am to make this work.
View 2 Replies View RelatedReturn
May 18, 2004whats wrong with this SP? I want @id to contain the row identity of the newly created row as a return value.ALTER PROCEDURE setCountry
(
@name varchar( 50 ) = NULL,
@alt varchar( 24 ) = NULL,
@code varchar( 3 ) = NULL,
@id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @name, @alt, @code )
@id = @@identity
RETURN
If...Else Return Value
Oct 11, 2006Hi,
Here is my sp:
Quote: CREATE PROCEDURE dbo.usp_ProfQuals_Add_AwardingBody_nr
(
@awardbody varchar(100)
)
AS
BEGIN
declare @message int
if EXISTS(SELECT vchr_awardingbody from tbl_ProfQuals_AwardingBody where vchr_awardingbody = @awardbody)
set @message = 1
else
set @message = 2
insert into tbl_ProfQuals_AwardingBody (vchr_AwardingBody) values (@awardbody)
END
return @message
The problem I am having is that it returns the correct value if there is a record that exists or indeed if it doesn't BUT ... even when a record exists it still carries out the insert!
Any help much appreciated.
Dave
How Could I Return The Next Row(Or 8th Row For Example)
Sep 25, 2005Hi,
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-Thanks
Getting The Return Value
Aug 15, 2007Hello everyone -
Please advise on why the output value is an empty
declare
@DynamicSQL3 NVARCHAR(4000
SET @DynamicSQL3 = 'SELECT Security_Level_ID ' +
'FROM ' + @DatabaseName + '.dbo.Security_Level_Master ' +
'WHERE Security_Level_Name = ''Administrator'' '
exec sp_executesql @DynamicSQL3, N'@i int output', @AdministratorSecurityID output
I run the query and see the grid being populated with the proper
security_level_id in the query analyzer,
but when i execute a print @AdministratorSecurityID
there is a space character in the results window
please advise on what i am doing wrong
thanks
tony
Return SUM That Is Not 0?
Oct 15, 2013I have this table that contains transactions for a specific client and some clients, when I total the transactions, have a zero balance.
I want to be able to filter those clients out, only show the ones that have open balances.
Here's an example:
DECLARE @T1 TABLE( CODE VARCHAR(6), ITEM INTEGER, AMOUNT INTEGER)
INSERT INTO @T1
VALUES
('ABC',4001,110),
('ABC', 4001, -110),
('ABC',4002,-5.5),
[code]....
If you look at the table, client ABC has a lot of transactions but if I SUM them the balance is 0, same with client GHI. The only row that show show on my query is client DEF.
I am trying to do something like this:
select *
from @T1
having Code in ( SELECT code
from @T1
group by code
having SUM(amount) <> 0)
but I get an error.
No Return Value
Mar 6, 2007Hi all. How could i trigger the result in select statement? If the query does not return a value, there must be a prompt message to appear.
thanks
-Ron-
Return The Last Row Only
Jul 5, 2007Hello!
I'm new to SQL and i've searched everywhere for the answer to this question but I can't seem to find the anwer anywhere. My question is.....
If there are multiple rows for a specific field, is there a way in SQL to only return the last row based on that specific field.
For example, I wrote a query that returns a list of patients who have a specific lab type. If a patient has had several labs taken, they show up in the result set several times (for each lab date). I only want to return the most recent lab date.
Here is my query........
SELECT id, resulted_test_desc, result_value, units, update_date
FROM dbo.lab_result LEFT OUTER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
WHERE resulted_test_desc = 'Hemoglobin'
Any help would be greatly appreciated.
Thanks!
Amedeo
How To Get A SP Return Value On Another SP?
Sep 12, 2006I have a SP, which will exec other SP depend on the input.the "other SP" need to return a integer back.How to do this?Thanks for give me a hand.
View 4 Replies View RelatedReturn New Id
Jul 20, 2005Dear All,I try this but it keeps returning zero !Dim newid As LongCurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) & "-" &Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as'newID'"Me.RequeryMsgBox newid >> returns 0Msgbox DMax("ORD_ID","[ORDER]") >> returns the new idMy question stays: is it possible to return the newid into a VB-variabledirectly?Filip
View 1 Replies View RelatedReturn One Row
Jul 20, 2005HelloI was wondering if someone could help me out with something.With the following rows:agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl1 1 1 10-10-2000 01 10-15-2000 021 1 2 10-09-2000 06 051 2 1 10-08-2000 05 10-20-2000 011 2 1 10-05-2000 01 10-15-2000 03What I need is SQL to get one row per agnt/supv with the following:strt_dt, strt_lvl of earliest strt_dttrm_dt, trm_lvl of the latest trm_dt but only if all trm_dts forthe supv arefilled in, otherwise null for bothagnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl1 1 10-09-2000 06 null null1 2 10-05-2000 01 10-20-2000 01If anyone has any ideas on the most efficient way to accomplish this,I'd appreciate a reply.Thanks!
View 5 Replies View RelatedHow To Return Just Any One Row Among So Many?
Sep 19, 2007
I have set of procedures that implement business logic in such a way that I am just interested to return only one row when there could possibly be millions of them. Those procedures are in this form
SELECT Top 1 SGP.GroupID
FROM dbo.SubGroupPerm SGP
INNER JOIN dbo.GroupPermStats GPS on SGP.GroupID = GPS.GroupID
INNER JOIN dbo.IMISProduct IP on GPS.ProductID = IP.ProductID
INNER JOIN dbo.HasMeasureGroup HMG on SGP.GroupID = HMG.GroupID
WHERE SGP.SubscriberID = 40335
AND HMG.MeasureTypeID = 119
AND GPS.IndustryCode = 'CT'
Such tables are properly indexed and most queries return results using clustered index seek/index seek operations. Unfortunately, in presence of large tables I don't get the desired response time because query generates massive IOs. I was wondering if someone could possibly have another clever way to re-write above query where we just have to return one row?
SP Return Value
Mar 14, 2008in my package i am executing a stored procedure from execute sql task which is something like
DECLARE @return_value int
EXEC @return_value = [dbo].[NextDigitalDataLoad]
SELECT 'ReturnValue' = @return_value
GO
Now based on the value returned by this i have to check in a dataflow task whether the file content is correct or not.
please tell me how to do
Return One Row Per ID
Oct 22, 2007I have a table that contains multiple entries per a specific Id similar to the following: 1 09-01-2007 1 09-02-2007 1 09-15-2007 1 09-06-2007 2 09-03-2007 2 06-09-2007 2 07-09-2007 3 01-01-2007 3 02-02-2007 I need to get a single row back for each id which corresponds to the row with the minimum datetime. Using the example I would need to return: 1 09-01-2007 2 06-09-2007 3 01-01-2007 Anyone have any ideas on how to accomplish this with a TSQL query? Thanks.
View 1 Replies View RelatedReturn More Than One Row
Mar 15, 2008Select Model, Kota as [Çıkan Anket] ,
(Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model) as [Verilen Adres] , (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model) as [Donen Adres]
,
(Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model) as [Donen Anket] ,((Select cast(Count(*) as decimal(2,0)) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model ) / (Select top 1 (cast(Kota as decimal(2,0))) From dbo.Model Where Model = tt.Model))*100 as [Oran]
,
(Kota - (Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model)) as [Kalan Anket]
,
((Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model)- (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model)) as [Kalan Adres]
From dbo.Model as tt where tt.Model in('A3')
it's return more than one row :
1. column 2. col..
A3 5 606 0 0 0.000000 5 606
A3 2 606 0 0 0.000000 2 606
A3 2 606 0 0 0.000000 2 606
A3 0 606 0 0 0.000000 0 606
A3 0 606 0 0 0.000000 0 606
A3 0 606 0 0 0.000000 0 606
A3 0 606 0 0 0.000000 0 606
i want it like below
A3 **9*** 606 0 0 0.000000 5 606
PLEASE HELPP
StoredProcedure Return Value
Jun 27, 2006I am using SQL Server 2005 now and I have a table with following columns.
ID, FirstName, LastName, Email
"ID" is the primary key (int) and is set auto generated (1 increment)
I have a StoredProcedure to insert a new record.
CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO
I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
How To Use Return Value From SqlDataSource
Nov 3, 2006I have the following code in my page:
<asp:SqlDataSource ID="SqlHolidayDateRange" runat="server" ConnectionString="(OurConnectionString)"
SelectCommand="CountHolidays" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="WeekEndingDatePicker" Name="EndDate" PropertyName="SelectedDate" />
<asp:Parameter Direction="ReturnValue" Name="RowCount" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
I need to place RowCount, the returned integer value from the stored procedure CountHolidays into a field on the web page. How in the world do I access that data?
Everything I've been able to find works if I'm defining my own command object and writing this thing from scratch. What I - and you - have to work with is what's above.
Return Single Value
Dec 3, 2006Hello, I created a stored procedure which selects a value according to ContentId. I know that will be only one value returned or none. So if a record is found I want to return the string contained in ContentHtml. Else I want to return the string "NotFound" Could somebody help me out with this? Here is my present stored procedure: -- Specifies the SQL-92 equals compliant behavior SET ANSI_NULLS ON GO -- Specifies the SQL-92 quotation mark rules SET QUOTED_IDENTIFIER ON GO -- Alter procedure ALTER PROCEDURE [dbo].[by27_Content_GetContent] -- Define the procedure parameters @ContentName NVARCHAR(100), @ContentCulture NVARCHAR(5) AS -- Prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Declare and define ContentId DECLARE @ContentId UNIQUEIDENTIFIER; SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName = @ContentName -- Check if ContentId is Not Null IF @ContentId IS NOT NULL BEGIN -- Select localized content from by27_ContentLocalized SELECT dbo.by27_ContentLocalized.ContentHtml FROM dbo.by27_Content INNER JOIN dbo.by27_ContentLocalized ON dbo.by27_Content.ContentId = dbo.by27_ContentLocalized.ContentId WHERE (dbo.by27_ContentLocalized.ContentCulture = @ContentCulture AND dbo.by27_Content.ContentName = @ContentName); END -- Create procedure GO Thanks, Miguel
View 1 Replies View RelatedReturn 0 Rather Than Null
Feb 2, 2007i have query which does the following select x from y where t = "House"x is an integer. If no record is found, how do i get it to return 0 rather than null?
View 9 Replies View RelatedHow To Return A Static Value?
Apr 25, 2007I have the following stored procedure...
CREATE Procedure UserGetInfo2 (@UserID int, @SystemTimePeriodID int )As set nocount onSELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccountedFROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @UserID) AND (UserTimePeriod.SystemTimePeriodID = @SystemTimePeriodID)returnGO
The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.
Obviously, I need to wrap this in an IF...EXISTS
IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @SystemTimePeriodID) AND (UserID = @UserID))
(the SELECT above, since that's what needs to come back if the data exists)
ELSE
Do the same select but put in a static value for BudgetCode, like '0000'
GO
How could I do the part where the IF...EXISTS fails?
I'm... not sure I can use RETURNS, since it feeds into this recordset:
rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")
and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>
Need To Know How To Return Row Count From SP
May 16, 2007How can I tell if table "CompanyInfo" has any rows? Below is the partial code behind and the SP I am running to fill CompanyInfo;
PROCEDURE newdawn.AcceptSubmission @CompanyID intASSELECT C_ID, CG_ID, LS_ID, L_Rank, L_Name, L_URL, FROM tblStoreSubmitWHERE C_ID = @CompanyIDRETURN
SqlCommand cmd3 = new SqlCommand("AcceptSubmission", con); cmd3.CommandType = CommandType.StoredProcedure; cmd3.Parameters.AddWithValue("@CompanyID", CompanyID); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd3; DataSet ds = new DataSet(); try { da.Fill(ds, "CompanyInfo");
How Can I Return Everything While Using A Paramater
Jun 26, 2007I guess a pretty basic sql question here. I am using tableadapters. And some of the queries have multiple parameters. But often a parameter is at a default or null - wherein I'd like to be able to pass it in and have it not filter at all - ie., return everything.
Simple example:
Select * from ReportsWhere ReportID = @ReportID
If I have a dropdownlist of reports where the values are ReportIDs - but the topmost unselected value is say "", then the results will be generated by
Select * from ReportsWhere ReportID = ''
Whis not what I want. What I would want is simply
Select * from Reports
or
Select * from ReportsWhere ReportID = ReportID
So how can one setup say an ObjectDataSource or somehow handle this where I don't need to use dynamic sql to expand/contract my where clause?
Really need hlpe on this and hope I've made my question reasonably clear. Thanks.