How Do I Get The Returned Id From The Store Proceedure In Asp C#?

Mar 12, 2007

my store proceedure gets the id:

 
 
CREATE PROCEDURE createpost(
 @userID integer,
 @categoryID integer,
 @title varchar(100),
 @newsdate datetime,
 @story varchar(250),
 @wordcount int
)
 as
DECLARE @newNewsID integer
Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)
SELECT @newNewsID = @@IDENTITY  

 
then im calling it in the asp:
 
  con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
  con.Open();  
      
  cmdselect = new SqlCommand("createpost", con);
  cmdselect.CommandType = CommandType.StoredProcedure;
  
  
  cmdselect.Parameters.Add("@userID", userID);
  cmdselect.Parameters.Add("@categoryID", categoryID  ); 
  cmdselect.Parameters.Add("@title", title.Text ); 
  cmdselect.Parameters.Add("@newsdate", newsdate.Text ); 
  cmdselect.Parameters.Add("@story", story.Text );  
  cmdselect.Parameters.Add("@wordcount", "1" );
  
  
  int valueinserted = cmdselect.ExecuteNonQuery();
  
  
  Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted); 
  
  con.Close();
 
as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?
 

 

View 2 Replies


ADVERTISEMENT

Return The New Id From A Store Proceedure

Mar 12, 2007

i have a store procedure which i need to get the returned id from how do i do this??

the sp:
CREATE PROCEDURE createpost(
@userID integer,
@categoryID integer,
@title varchar(100),
@newsdate datetime,
@story varchar(250),
@wordcount int
)
as

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)

im calling it from asp, maybe i need to do something here as well im a bit lost really.. any help be great!

the asp:
con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;



cmdselect.Parameters.Add("@userID", userID);
cmdselect.Parameters.Add("@categoryID", categoryID );
cmdselect.Parameters.Add("@title", title.Text );
cmdselect.Parameters.Add("@newsdate", newsdate.Text );
cmdselect.Parameters.Add("@story", story.Text );
cmdselect.Parameters.Add("@wordcount", "1" );


int userinserted = cmdselect.ExecuteNonQuery();


Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id=1");

View 1 Replies View Related

MSOLAP_NODE_SCORE Values Returned By Store Proc DTGetNodeGraph

Oct 19, 2006

Hello--

For extracting the link structure of a dependency network with a large number of nodes (for problems having a large number of variables), we have been using the stored procedure:

System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph('{model-name}', value)

The stored procedure returns a resultset with columns: [Node_type], [Node_unique_name_1], [Node_unique_name_2], and [MSOLAP_NODE_SCORE]

Are there any pointers, references or descriptions of the values of [MSOLAP_NODE_SCORE]?

Thanks,

- Paul

View 5 Replies View Related

Data Returned From .WriteXML Is Different Than What Is Returned In Query Analyzer.

Jun 20, 2006

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3.   string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE  a.RegionCode = b.RegionCode"; 5.   DataSet dsMarketList = new DataSet();6.   SqlConnection sqlConn = new SqlConnection(intranetConnStr);   7.   SqlCommand cmd = new SqlCommand(sql,sqlConn);8.  sqlConn.Open();9.   SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10.   try11.   {12.   adapter.Fill(dsMarketList);
 13.  String bling = adapter.SelectCommand.CommandText;//BRG 14.   dsMarketList.DataSetName="RegionMarket"; 15.  dsMarketList.Tables[0].TableName = "MarketList"; 16.    dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to  17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18.           // from the SQL query 19.  } 20.  catch(Exception e) 21. {  22. // Handle the exception (Code not shown)

View 2 Replies View Related

Stored Proceedure

Feb 21, 2007

Hi people,
 I'm using the following SP to return the rank of users but I really want it to return just a single Row column and also a Count() of the numer of Users there are....at the moment it sending me the whole table of ranked users.
 Any ideas?
 
ALTER PROCEDURE dbo.RankUsersOnScore
(
@UserID INT
)
AS SET NOCOUNT ON;
SELECT Row, Score, UserID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Score DESC)
AS Row, Score, UserID FROM dbo.tblUserStats )
AS tblUsersRanked

View 1 Replies View Related

Stored Proceedure

Mar 2, 2004

I was using a proceedure i created in code but because of the way i am using it i decided that a stored proceedure will work better

i have a few add proceedures that work for insert and update but when i tried a select command, no matter what the data entered it returns the first record in the table when I fill the dataset does any one have a clue as to why it would do this
is there something you have to return through the stored proceedure
like you do when you use @@Identity

this is what i currently have,

CREATE PROCEDURE Location_Select


@p1 char(15)
AS
SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)
go

do i need this? and if so what can i return i tried LocationID and it yelled at me saying that is an invalid column name


CREATE PROCEDURE Location_Select


@p1 char(15),
@retval int output

AS
SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)

SET @retval =LocationID
GO



thankyou

View 2 Replies View Related

DTS / Stored Proceedure

Apr 16, 2004

How can you run a DST package from a stored proceedure.
I am using sql server 2000
i cant find the syntax anywhere
it is a DTS that takes a file and imports it into a table in the db

View 17 Replies View Related

Help With Stored Proceedure Please

Jul 20, 2005

Hope someone can help.I am trying to write a stored proceedure to display sales activity by monthand then sum all the columbs.The problem is that our sales year starts in April and end in March.So far I have been able to get the sales info my using to sp's, one that saymonth >3 and the other says <4. I pass in a year parameter, that for thisyears figures would be 2003 for sp1 and 2004 for sp4.I am sure there is a better way.Below is a copy of one of my sp's.Hope you are able to help.JohnALTER PROCEDURE dbo.sp_SalesAnalFigures_P1(@Year nvarchar(50),@CCode varchar(50),@SCode varchar(50),@OType varchar(50))AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) ASMonth, SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS Sales,SUM(dbo.InvoiceItemsCostQry.TotalCost) AS Cost,SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet -dbo.InvoiceItemsCostQry.TotalCost) AS Margin,COUNT(dbo.InvoiceHeaderTbl.InvoiceNo) AS NoOfInvoices,AVG(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS AverageValueFROM dbo.InvoiceHeaderTbl INNER JOINdbo.InvoiceItemsCostQry ON dbo.InvoiceHeaderTbl.InvoiceNo =dbo.InvoiceItemsCostQry.InvoiceNoWHERE (DATEPART(yyyy, dbo.InvoiceHeaderTbl.InvoiceDate) = @Year) AND(dbo.InvoiceHeaderTbl.CompanyCode LIKE @CCode) AND(dbo.InvoiceHeaderTbl.SalesManCode LIKE @SCode) AND(dbo.InvoiceHeaderTbl.OrderType LIKE @OType)GROUP BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)HAVING (DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) > 3)ORDER BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)

View 6 Replies View Related

Increment With Stored Proceedure

Jan 4, 2007

Howdy team,
 How would I increment a field of type 'int' with a stored proceedure with sql2005
Thanks.

View 5 Replies View Related

Sql String In Stored Proceedure

Jan 28, 2004

I've got a stored procedure which contains a fast_forward cursor. I was wondering whether it is possible to pass in an sql query string into this stored procedure for the cursor, i.e:

Create Procedure (@sqlString as text)
as
DECLARE aCursor CURSOR
FAST_FORWARD
FOR @sqlString

View 4 Replies View Related

Stored Proceedure Problem

Mar 8, 2004

CREATE PROCEDURE BatchID_Select

@Que bigint,
@retval varchar(10) OUTPUT

AS

SELECT BatchID FROM t_Que WHERE (QueID = @Que)

SET @retval =BatchID
GO


This stored proceedure will not let me save is there any reason why
if i take out the set line it says the syntax works
if i leave this line in however it says that batchid is not a valid column name
can you only return @@ variables?
i just cant figure out what its problem is

View 2 Replies View Related

Calling One Stored Proceedure From Another

Oct 16, 2007

yet another question unfortunately

I have now created a stored proceedure that has a return parameter, not i am unsure how to call it from another proceedure,

ie
say i have select projectid, project name from projects into temp from projects.

how can i then loop around all the rows in temp, to call my stored proceedure for each record?

in vb i would have created a function like my stored proceedure, then picked up a recordset, looped around it and picked up the return value for each row.

can this be done for sql?

I am trying to do something like

for each record in #temp (projectid, project name) find the stored sprceedure value

so my end result will look like

projectid, project name, @storedproceedure return value
lprojectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value

any help appreciated

View 1 Replies View Related

View Or Stored Proceedure?

Jul 20, 2005

Hope you can give me some advise.I am wanting to build a databse driven website. I am using Access toconnect to an SQL 2000 server to create tables etc.I am using ASP/ASP.Net to build my site.The question I have is on best method to retrive data. Lets say I havea Table of Products, and one of those fileds is CurrentProduct and itsTrue or False. On my web page I want to retreive and list all productsthat are marked as CurrentProduct True.I could do this as part of the SQL statement in the web page ie"Select * From Products Where CurrentProduct=True", I could create aView that only shows Current Products and use Select * FromCurrentProductsView or I could create a stored prodceedure, that looksvery much like a view.I know a bit about Access and Queries which is why I am using accessto manage Sql, but very liitle if anything about Stored Proceedures.When should I use what? And what are the advantages / dis-advantagesof each approach?Many thanks for any help you are able to provide.

View 1 Replies View Related

Stored Proceedure Question - Looping? Or Is There A Better Way?

Jan 17, 2008

I am writing a bit of code for our intranet using ASP.NET C# and SQL2005. We have a program called Aboutface that is a web based firm directory. We are using the SQL database it has in order to pull data out of it and integrate it into our intranet as we dont really care for its original interface.
I am most interested in setting up relationships.
An attorney only has one secretary that supports them, but a secretary can have many attorneys they support. This is the nature of my problem. The code below is my stored procedure. I am passing in an int which is the ID, and my goal is to generate the  ID of and the name of the person who supports/is supported by. With 1 to 1 relationships, it works fine.. With more than that, it blows up because its finding multuple names (of attorneys being supported by a secretary)
 I was told I might want to use a FOR EACH loop in the SP. I assume I would want to also generate a COUNT variable (to be used in the procedure and to also build the rows of my table outside of it.) and  a NAME variable for each name it finds to populate the table....   but from there I am not sure if I am on the right lines of thinking or where to begin.
Any thoughts/suggestions would be greatly appreciated.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[GetAttySectyData] (@id as int)
AS
BEGIN
DECLARE @First as varchar(200)
DECLARE @Middle as varchar(50)
DECLARE @Last as varchar(200)
 SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id ) SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
Select ISNULL(@First, '') + ' ' + ISNULL(@Middle, '') + ' ' + ISNULL(@Last,'') AS FullName
END
 

View 1 Replies View Related

Stored Proceedure Merge Data

Mar 1, 2004

I have some data that I am inputing and if the record already exists i would like to add data to the fields that are not populated ONLY if they are not populated
for example


Already in the table
address= "123 this place"
city= ""
State="MI"
Zip="48462"
FirstName=""
LastName=""

being added
address= "123 this place"
city= "Ontate"
State=""
Zip="48462"
FirstName="Person"
LastName="Guy"


once i find that this record already exists because of the address and zip (which i already have complete)
I would like it to update the City first name and last name in the data that is already in the table. thank you for your help

View 4 Replies View Related

Automatic Execution Of A Stored Proceedure

Jul 23, 2005

How do you set a stored proceedure for automatic execution?--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Hewlp Needed Creating A Stored Proceedure

Feb 21, 2006

Can someone please help me....I have created a DNN module that works on
the test site but when I upload the module zip to a new site I get an
error on creating my stored proceedure as follows:



StartJob
Begin Sql execution

Info
Executing 01.00.00.SqlDataProvider

StartJob
Start Sql execution: 01.00.00.SqlDataProvider file

Failure
SQL Execution resulted in following Exceptions:
System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'.
Line 51: Incorrect syntax near '@Str_Title'. at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText) at
DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean
UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder
tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF
ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID],
[PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner],
[Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like
COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text
'%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN
@SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either
field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text],
[DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID =
@PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like
COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN
DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC

EndJob
End Sql execution: 01.00.00.SqlDataProvider file


The SP looks like this:

/* -------------------------------------------------------------------------------------
/   ListTAS_Journal
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
    @PortalID int,
    @SortOrder tinyint = NULL,
    @Str_Title varchar(100) = '',
    @Str_Text varchar(100) = ''
AS

IF ISNULL(@Str_Title, '') = '' or   ISNULL(@Str_Text, '') = ''

SELECT
    [EntryID],
    [PortalID],
    [ModuleID],
    [Title],
    [Text],
    [DateAdded],
    [DateMod],
    [Owner],
    [Access]
FROM
    TAS_Journal
WHERE
    PortalID = @PortalID AND
    (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND
    Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))


ORDER BY
    (CASE
    WHEN     @SortOrder = 1 THEN DateAdded
    WHEN     @SortOrder = 0 THEN DateMod
    END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
    [EntryID],
    [PortalID],
    [ModuleID],
    [Title],
    [Text],
    [DateAdded],
    [DateMod],
    [Owner],
    [Access]
FROM
    TAS_Journal
WHERE
    PortalID = @PortalID AND
    (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR
    Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))


ORDER BY
    (CASE
    WHEN     @SortOrder = 1 THEN DateAdded
    WHEN     @SortOrder = 0 THEN DateMod
    END) DESC, EntryID DESC
GO


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This SP works on the test site.
Any help would be creatly apreciated

Mark

View 2 Replies View Related

Create New Date Table With A Stored Proceedure

Sep 21, 2007



Hi,

I have a table from which I need to create a report via MSRS2005, however the data in the table is awful in its construction and I was hoping to be able to use a stored proceedure to create a new table in which I can manupulate the data, but my T-SQL programming skills aren't that clever, so if anyone can offer any advice I'd be most grateful:

In the existing table there are two columns; StartDate and EndDate which is pretty self explanitory - what I would like to do is create a new table with only one date column and if there is more than one day between StartDate and EndDate I would like it to fill in every date in between.

For example, if the StartDate is 01/06/2007 and the EndDate 10/06/2007 I'd like the new table to list dates 01/06/2007 through 10/06/2007 inclusive in one column.

Is this possible? All suggestions welcome.

Thanks in advance,

Paul

View 1 Replies View Related

How To Return Event Description From Stored Proceedure?

Nov 15, 2006



I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..



can the stored proceedure send the text?

View 1 Replies View Related

Stored Proceedure Question, How To Pull An Autogenerate Field Value?

Feb 6, 2008

I have a stored proceedure that is adding a record to a database table. When the record is added using an insert statement, the ID field is autogenerated.
I have a second insert statement that inserts into a second table, however, I want/need? to use that ID field in order to link this additional information to the proper record in the initial table.
 Is there an easy way to do a select or just pull the ID?  I was thinking I could do a select before the final insert using 2 or 3 required fields of which used in a select altogether would be unique, but before I did that, I wanted to see if I was missing a better way. I posted the code below....
 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[Add_Employee_Data]
(
@LastName as varchar(100),@FirstName as varchar(100),
@MiddleName as varchar(100),@Position as varchar(100),
@Department as varchar(100),
@DirectDial as varchar(100),
@Ext as varchar(100),
@Fax as varchar(100),
@HomePhone as varchar(100),
@CellPhone as varchar(100),
@Partner as varchar(100),
@TimeKeeper as varchar(100),
@Notary as varchar(100),
@Practice as varchar(100),
@SummerAddress as varchar(250),
@SummerPhone as varchar(100),
@LegalNonLegal as varchar(100),
@Bar as varchar(100),
@OtherEmail as varchar(100),
@HomeAddressComplete as varchar(100),
@HomeAddress as varchar(100),
@HomeCity as varchar(100),
@HomeState as varchar(100),
@HomeZip as varchar(100),
@School as varchar(100),
@Degree as varchar(100),
@Status as varchar(100),
@Floor as varchar(100)) AS
DECLARE @Code as varchar(100)
 
INSERT Into tblMain2(LastName,FirstName,MI,Position,Dept,Extension,DirectDial,[FAX DID],HomePhone,CellularPhone,SpousePartner,
TimeKeeper,Notary,PrGroup,SummerAddress,SummerPhone,LegalNonLegal,Bar,OtherEmail,HomeAddressComplete,HomeAddress,HomeCity,
HomeState,HomeZip,Floor,Active)
Values(@LastName,@FirstName,@MiddleName,@Position,@Department,@Ext,@DirectDial,@Fax,@HomePhone,@CellPhone,@Partner,@TimeKeeper,
@Notary,@Practice,@SummerAddress,@SummerPhone,@LegalNonLegal,@Bar,@OtherEmail,@HomeAddressComplete,@HomeAddress,
@HomeCity,@HomeState,@HomeZip,@Floor,@Status)
<<<<< Put select statement here to pull in @Code where LastName = @LastName  and Extension =@Ext ??
INSERT Into Education(Code,CollegeSchool,DegreeCert) Values(@Code,@School,@Degree)
 
 
 

View 6 Replies View Related

Store Procedure Not Store

Nov 20, 2013

My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure

exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'

I am getting below error :

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.

View 13 Replies View Related

One Row Returned But E.AffectedRows ==0?

Jan 9, 2007

Stored Procedure:CREATE PROCEDURE [dbo].[GetBanner]     @BannerPage nvarchar(50),    @MagazineID intASBEGIN    SET NOCOUNT ON;    DECLARE @BannerID int     SELECT TOP 1    @BannerID = BannerID    FROM Banners    WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID    AND StartDate <= GetDate() AND EndDate >=GetDate()    ORDER BY Views    SELECT     BannerID,     BannerFileName,     AltText,     URL,     Views     FROM Banners     WHERE BannerID = @BannerID           UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID     UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerIDEND OnSelected method: protected void dsBanner_Selected(object sender, SqlDataSourceStatusEventArgs e)    {        int RecordCount = e.AffectedRows;            Response.Write(RecordCount);    }  This ALWAYS gives me 0 - despite the fact that the correct banner is pulled from the database, and updated etc.  Also, when I execute the procedure (for valid parameter values) in SSMS, I get "1 row(s) Affected".  Can anyone tell me what I need to change to get the proper value? Thanks   

View 6 Replies View Related

Name Column Returned

Sep 27, 2007

 I have the following sql:SELECT courseModuleCode FROM courses WHERE courseCode like '%' + @prefixText + '%'UNION
 SELECT courseName FROM courses WHERE courseName like '%' + @prefixText + '%'
UNIONSELECT TeacherName FROM courses WHERE TeacherName like '%' + @prefixText + '%' it returns one column, i want to name tha column as "words"how do i do it?thanks  

View 3 Replies View Related

No Record Set Returned

Sep 10, 2004

I have a table that COULD be linked to 3 other tables, but if one of the link fields is zero I get nothing back at all. Here is my query:
SELECTEvents.*, Agencies.A_NAME AS A_NAME,
(Families.F_FNAME + ' ' + Families.F_LNAME) AS F_NAME,
(Candidates.C_FNAME + ' ' + Candidates.C_LNAME) AS C_NAME
FROM Events
INNER JOIN Agencies ON Events.E_AGENCY_ID = Agencies.A_ID
INNER JOIN Families ON Events.E_FAMILY_ID = Families.F_ID
INNER JOIN Candidates ON Events.E_CANDIDATE_ID = Candidates.C_ID
WHEREE_ID = 89

Even if all the inner join id's are zero, I would at least like to get back the event information.
Could anyone help me out here, or do I have to do 4 queries?

Thanks in advance.

View 2 Replies View Related

Once Again, No Records Returned....

Dec 21, 2004

The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?



----------------------------------------------------------------------------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@unitid int
AS

SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled

FROM tbl_units as A

INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid

WHERE A.UnitID = @unitID
GO
-----------------------------------------------------------------------------------------------------

View 3 Replies View Related

Subquery Returned More Than 1 Value...

Mar 9, 2006

can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

update purchaseorders set orderstatusID = 4 where purchaseorderid in (
select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null
)

View 1 Replies View Related

Different No. Of Rows Returned In SEM Vs QA

Nov 19, 2004

Hi,

Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?

Thanks,

Alph

View 3 Replies View Related

Subquery Returned More Than 1 Value

Dec 7, 2011

I am getting the below error while trying to run sql query

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.

SELECT substring(sc.container_id,0,14) container_id,
imc.item_class,
sh.ship_to, sh.shipment_id,
cust.ADDRESS1,
sh.carrier,

[code]...

View 3 Replies View Related

Exclude If Returned Value Is 0

Jan 27, 2012

I am running a query that works just fine however, I would like it to exclude value that are equal to zero.

Basically my query looks at the commission that clients pay over a number of periods.

So it goes like this

T.Client_Code as Client
,SUM(CASE t.Transaction_Date WHEN DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)THEN (ABS (t.transaction_commission) /((fx.Exchange_Bid + fx.Exchange_Ask)/2 )) ELSE 0 END)as Commission_Day

FROM TABLE T
JOINING FX TABLE

WHERE
fx.Currency = 'USD'

And T.Salesman_Name in ('X''Y'Z)

Group BY
T.Client_Code

It works perfectly fine however, we dont transact with our clients everyday so therefore this list will return all of our clients in the database and many will have generate zero commission. I want to keep the query along those lines I just need to insert something that says "ONLY SHOW WHEN Commission is not ZERO.

View 2 Replies View Related

Subquery Returned More Than 1 Value?

Mar 25, 2015

i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role

My code is:

Code:
select top 100
username,
(Select b.name
from application b
inner join userroleapplication c
on b.applicationID = c.applicationID ) Application,
(select name from role d, userroleapplication gr
where gr.roleID = d.roleID and gr.userID = a.userID) role
from users a
where username in
(
'username'
)

i know where is the problem but i cant figure how to fix this

What i need as a output

Username | Application | Role

"user1" | "App1" | "Role1"
"user1" | "App1" | "Role2"
"user1" | "App2" | "Role1"
"user1" | "App3" | "Role1"
"user2" | "App1" | "Role1"
"user2" | "App3" | "Role1"
etc.

View 2 Replies View Related

Subquery Returned More Than 1 Value?

Apr 15, 2008

Hi

I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query

The table with data is like this,one file maybe belong to many group.

Filename FilegroupID

file1 1
file2 2
file1 3
file1 4
file3 1
file2 5


I need write a query to get the result like this

file1 1,3,4
file2 2,5
file3 1

I try to write a query with subquery but I get error message

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

I use SQL Server 2005.but I need the subquery return data like "1,3,4".

Please help me.

Thanks

Mark

View 7 Replies View Related

Why Are Nulls Not Being Returned?

Apr 29, 2008

Select * from table where field <> 'This'


Why does that not return nulls? 'This' does not equal null. Why does sql server work this way?

View 2 Replies View Related

Subquery Returned More Than 1 Value

Dec 18, 2013

I have a simple update query that looks like this:

Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID

I am receiving the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."Yes, it is all contained in the same table.

View 5 Replies View Related







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