Return SUM That Is Not 0?

Oct 15, 2013

I 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.

View 3 Replies


ADVERTISEMENT

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.

View 2 Replies View Related

How To Return SqlDataReader And Return Value (page Count) From SPROC

Jan 2, 2006

This 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

View 3 Replies View Related

'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,

View 5 Replies View Related

Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

I 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

View 5 Replies View Related

Why SP Return SQLException When No Data Return ?

Jul 24, 2006

hi, 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

View 1 Replies View Related

Transact SQL :: Return Set Of Values From SELECT As One Of Return Values From Stored Procedure

Aug 19, 2015

I 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?

View 17 Replies View Related

Return Value From SP

Dec 6, 2006

I 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?!?!

View 1 Replies View Related

Return Value From SQL

Jun 14, 2008

I 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 Related

Return

May 18, 2004

whats 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

View 8 Replies View Related

If...Else Return Value

Oct 11, 2006

Hi,

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

View 3 Replies View Related

How Could I Return The Next Row(Or 8th Row For Example)

Sep 25, 2005

Hi,
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

View 8 Replies View Related

Getting The Return Value

Aug 15, 2007

Hello 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

View 2 Replies View Related

No Return Value

Mar 6, 2007

Hi 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-

View 14 Replies View Related

Return The Last Row Only

Jul 5, 2007

Hello!

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

View 20 Replies View Related

How To Get A SP Return Value On Another SP?

Sep 12, 2006

I 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 Related

Return New Id

Jul 20, 2005

Dear 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 Related

Return One Row

Jul 20, 2005

HelloI 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 Related

How 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?

View 17 Replies View Related

SP Return Value

Mar 14, 2008

in 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

View 1 Replies View Related

Return One Row Per ID

Oct 22, 2007

I 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 Related

Return More Than One Row

Mar 15, 2008

Select 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

View 4 Replies View Related

StoredProcedure Return Value

Jun 27, 2006

I 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 ?
 

View 3 Replies View Related

How To Use Return Value From SqlDataSource

Nov 3, 2006

I 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.
 

View 4 Replies View Related

Return Single Value

Dec 3, 2006

Hello, 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 Related

Return 0 Rather Than Null

Feb 2, 2007

i 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 Related

How To Return A Static Value?

Apr 25, 2007

I 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>  
 

View 4 Replies View Related

Need To Know How To Return Row Count From SP

May 16, 2007

How 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");

View 1 Replies View Related

How Can I Return Everything While Using A Paramater

Jun 26, 2007

I 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.
 
 

View 3 Replies View Related

Return Inserted Row

Jul 23, 2007

how do i insert a record, then return the inserted record back to VS (ASP + VB) to display? maybe just the ID will be enough. then i will do a select 

View 6 Replies View Related

Get A Return Value With EXEC?

Aug 29, 2007

Hi, I have an sql query like this :DECLARE         @TableName varchar(200),    @ColumnName varchar(200),    @EmployeeID varchar(200),    @Result    varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' +  @ColumnName + ' from ' +  @TableName + ' where Recid = ' + @CustomerID + '');Print @Result;   I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks 

View 1 Replies View Related

SQL Return Parameter

Dec 11, 2007

Hi Guys, I can't for the life of me get the output from the stored procedure into my variable in VB.net.   Can you guys help out, I'm on my third hour.  Trying to learn this peice as I go (output parameters).  I have truncated the code to look at the output parameter, everything works except the VB side of the output parameter, my messagebox keeps giving me a value of zero.  I have even tried  .SQLValue instead of .Value and it didn't work...thoughts????Thanks in advance!Tim
SQL:ALTER PROCEDURE dbo.SDC_RisIncidentInsert      @SeqInc int output  ASSelect @SeqInc = Max(Sequence) + 1 From _Smdba_._Telmaste_Select @SeqInc---------------------------------------------------------------------------------------------
VB.NetPrivate Sub InsertIncident()Dim conn As SqlConnectionDim comm As SqlCommandDim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionStringconn = New SqlConnection(connectionstring)comm = New SqlCommand("SDC_RisIncidentInsert", conn)comm.CommandType = System.Data.CommandType.StoredProcedurecomm.Parameters.Add("@SeqInc", SqlDbType.Int)comm.Parameters("@SeqInc").Direction = ParameterDirection.Output
Dim SeqIncident As Integer = comm.Parameters.Item("@SeqInc").ValueMsgBox(SeqIncident)
Try  conn.Open()  comm.ExecuteNonQuery()Catch ex As Exception  dberrorlabel.Text = "Could not Insert Incident Record"
Finally  conn.Close()  Response.Redirect("/RISDEV/SDC_Incident.aspx?Name=" & SeqIncident & "#Incident")End TryEnd Sub
 

View 2 Replies View Related

Select Return Nothing

Dec 18, 2007

 Hi...I have table who contain words in Hebrew (right to left language), and when i try to select:WHERE     (Topic LIKE '%Hebrew word%')I get no rows...Any idea? thanks... 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved