Running Total Count In Stored Procedure

Jul 20, 2005

in my procedure, I want to count the number of rows that have errored
during an insert statement - each row is evaluated using a cursor, so
I am processing one row at a time for the insert. My total count to
be displayed is inside the cursor, but after the last fetch is called.
Wouldn't this display the last count? The problem is that the count is
always 1. Can anyone help?

here is my code,

.... cursor fetch
begin ... cursor
if error then:
begin

INSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,
CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,
STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,
PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,
USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,
CUSTINDICATOR, DT_LOADED)
VALUES(@ERRORNUM, @ERRORDESC,
@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,
@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,
@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,
@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,
@CUSTINDICATOR, @DTLOADED)

SET @ERRORCNT = @ERRORCNT + 1

END --error

--
FETCH NEXT FROM CERNO_US INTO
@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,
@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,
@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,
@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED
--
IF @ERRORCNT > 0
INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,
STATUS)
VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILED
INSERT/UPDATE')

END -- cursor
CLOSE CERNO_US
DEALLOCATE CERNO_US

View 1 Replies


ADVERTISEMENT

Transact SQL :: Stored Procedure For Total Count For New Users

Jul 14, 2015

I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."

But now we have changed the concept to get the ISID (name of the user)  and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .

After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.

if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.

Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).

Here is my sample  code:

I need to sum it up to get the total user's from both the table but I should get 1 instead of 2 

SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*)  from dbo.test1
where invited_by ='sam'

View 8 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

Conditional Total In Stored Procedure

Aug 11, 2006

Is there a simple way to do conditional totaling?

I have a table that has incremental values, so the maximum value is the current total, however on occassion the value is reset and the increment begins anew. What I need to do is get the total the maximum value each time it is reset.

ie.

Value Date
----- -----
12 1-1
14 1-2
100 1-8
35 1-10
50 1-12
5 1-15

the total would be 155 (100 + 50 + 5)

View 4 Replies View Related

Stored Procedure + Return Total Row Affected

Jul 7, 2004

Hi..

I have a stored procedure which is used to remove record from database.
Is there anyway to get the total row deleted for each table?


CREATE PROCEDURE dbo.sp_company_delete
(
@Company_Id int,
@Sched int Output,
@Users int Output,
@Asset int Output,
@Fleet int Output

)
AS
SET NOCOUNT OFF;
Begin Transaction delete_company
Select @Sched = COUNT(*) from Scheduler Where Company_id=@Company_Id
Delete From Scheduler Where Company_id=@Company_Id

Select @Users = COUNT(*) From Users Where Company_Id=@Company_Id
Delete From Users Where Company_Id=@Company_Id

Select @Asset = COUNT(*) From Asset Where Company_Id=@Company_Id
Delete From Asset Where Company_Id=@Company_Id
Delete From Fleet Where Company_Id=@Company_Id
Delete From Company Where Company_Id=@Company_Id
Commit Transaction delete_company
GO


Basically I want to get the total row deleted for each table and put it inside output parameters.

I am doing a double job here, first I get the count, put into output parameter, then only I delete the record.

Is there any other way which is more efficient to do this?

I tried using
SET @Sched = Delete From Scheduler Where Company_id=@Company_Id
SET @Users = Delete From Users Where Company_Id=@Company_Id
But doesn't work.

Any suggestion is welcomed.
Thank you in advanced.

View 2 Replies View Related

Create Stored Procedure To Get Total Last 7 Day Sales For Each Day

Mar 13, 2015

Table name: ONIV
Columns:
Date: DocDate
Sales: DocTotal

I want to populate a line graph which would show the 7 days of the week on X-Axis and the sale on the Y-Axis.

View 2 Replies View Related

Sql Count Using Stored Procedure Withing Stored Procedure

Apr 29, 2008

I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks

View 10 Replies View Related

Using Count(*) In A Stored Procedure

Jul 6, 2007

Hi,
I would like to add custom paging to my ASP.Net pages. How can I use Count(*) in a query such as below to retrieve a record count? 
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO
ALTER PROCEDURE [dbo].[view_icon_photos]    @Filenumber int = '0' , @Location nvarchar(50)= '-1' , @Rubric nvarchar(MAX)= '-1' , @Author nvarchar(75)= '-1' , @startRowIndex int= '-1' , @maximumRows int= '0'
As
IF @Filenumber = '0' AND @location > '-1' AND @Rubric = '-1' AND @Author = '-1' AND @startRowIndex > '-1' AND @maximumRows >'0'Select Icon, Filenumber, RowRankFrom(Select Icon, Filenumber,  Row_Number () Over ( Order By Filenumber ASC ) AS RowRankFrom dbo.photosWhere Location = @Location)As IconRank Where RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)
 

View 2 Replies View Related

How Can I Use COUNT In My Stored Procedure?

Jul 29, 2007

hello,
i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums
How can i use the COUNT function in my stored procedure to take only first n Categories?
I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...
here is my stored procedure (i use it for two nested Repeaters)ALTER PROCEDURE SubCategoriiInCategorii
CategoryID int)
AS
SELECT CategoryID, Name, Description FROM Categories WHERE CategoryID = @CategoryIDORDER BY Name
 
SELECT p.SubCategoryID, p.Name,p.CategoryID FROM SubCategorii p
ORDER BY p.Name
i hope you understand what i mean, thank you

View 4 Replies View Related

Using Two COUNT(*) In A Stored Procedure

Apr 19, 2005

what's wrong with the following code? COUNT for completed and exempted doesn't return correct values. they return same values. but when i remove one count it's returning right COUNT. i need to return the COUNT from two tables with a single query. how can i accomplish that??? is there anything wrong with my code below?
<code>
CREATE PROCEDURE dbo.Test AsSELECT s.StudentIdNum,  c.[Name], COUNT(*) AS Completed, COUNT(*) AS exempted FROM StudentEnrolled AS s JOIN StudentCourse AS sc ON s.StudentKey = sc.StudentKey JOIN Courses AS c ON sc.CourseID = c.CourseIDLEFT JOIN ModuleEnrolment AS m ON sc.StudentCourseID = m.StudentCourseIDLEFT JOIN ModuleExemption AS e ON sc.StudentCourseID = e.StudentCourseIDWHERE m.Status = "Completed"GROUP BY s.StudentIdNum, c.[Name]
</code>

View 5 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:


--Test Count/Count subquery

declare @Date datetime

set @date = '8/15/2007'


select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@Date) and @Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc


Thanks. Dan

View 1 Replies View Related

Running Dts From Stored Procedure

Mar 21, 2004

Hi

I am trying to upload an excel file into a sql server database. I uploading the spreadsheet from an asp.net page and then running the dts froma stored procedure. But it doesn't work, I am totally lost on what I am doing wrong.
Any help would be greatly appreciated.

Asp.net code;

Dim oCmd As SqlCommand

oCmd = New SqlCommand("exportData", rtConn)
oCmd.CommandType = CommandType.StoredProcedure
rtConn.Open()

With oCmd
.CommandType = CommandType.StoredProcedure
Response.write("CommandType.StoredProcedure")
End With

Try
oCmd.ExecuteNonQuery()
Response.write("ExecuteNonQuery")
Finally
rtConn.Close()
End Try

StoredProcedure;

CREATE PROCEDURE exportData AS
Exec master..xp_cmdshell
'DTSRUN /local/DTS_ExamResults'
GO

Thanks
Rachel

View 4 Replies View Related

Running A DB2 Stored Procedure

Nov 3, 2006

I've set up a linked server between my SQL 2005 server and my AS400 DB2 server. I can query data successfully.

How do i call a DB2 stored procedure?

View 1 Replies View Related

TSQL Stored Procedure - Get Count Where....

Feb 23, 2007

I have the following for sql server 2000...
  Select b.courseName, a.courseId, count(a.courseId) as [count],  avg(convert(INT, a.fldScore)) as [average], count(fldPass) as [passed], count(fldPass) as [failed] From tblTest a  inner join tblTest2 b on a.courseId = b.courseId Group by a.courseId, b.courseName
Problem is the [passed] and [failed]
As it is, it's counting all of them.
I need to adjust it so passed will only read where fldPass = 'yes'
and fldPass = 'no' for the passed and failed.
Suggestions?
Thanks,
Zath

View 4 Replies View Related

Dynamic Sql For Count In Stored Procedure

Apr 29, 2008

Hi all,
I'm using sql 2005. Can some one please tell me how to write dynamic sql for count. What i want is that i want to count the number of employees existing for the given department names and get the counted values as output into my vb.net 2.0 project.  If any one know who to write this please send the code.. Please help me.. I want the below code to change to dynamic sql:
 Alter proc GetCountforemp
@DestName varchar(200)=null,
@total int output
as
begin
SELECT @total = Count(distinct Employee.EmployeeID) FROM Employee
INNER JOIN Dest R on R.DestID=Employee.DestID
WHERE R.DestName in ('''+@DestName+''')
end 

View 1 Replies View Related

Stored Procedure For Each ProductID Count

Aug 5, 2004

Would like to get a total count from Quantity Column for (Each individual ProductID # entered) from the ProductID Column. And insert (the New total Value count into the QtySold Column.

Does anyone have an idea how to write a stored Procedure for this?

The tables name is OrderItems which contains the following columns:

uid, OrderID, ProductID, Quantity, QtySold, ProductName, Price,

Many Thanks

View 5 Replies View Related

Sum Or Count Value Of Field In Stored Procedure

Apr 19, 2005

How can i create a stored procedure that count or sum value of field
e.g.

               
f1      f2    
f3    f4    f5
 record     1      
1      2     
3     1
 
and get answer  like this   1=4  - 2=1 -  3=1

 

View 1 Replies View Related

Stored Procedure - Return Count

Jun 16, 2005

Having a little trouble with this sp... just need to return the count.....CREATE  PROCEDURE dbo.getTotalObjectives @courseId   VARCHAR(20) ASBEGIN DECLARE @errCode     INT
  SELECT count(*)  FROM   objstructure  WHERE  courseId  = @courseId
 SET @errCode = 0  RETURN @errCode Can only return one thing, @errCode, but can return others in the select statement....I did it before like.....SELECT @lessonLocation = lessonLocation  FROM   cmiDataModel  WHERE  studentId = @studentIdand got the lessonLocationAnd, in code behind, I know this may be off as well....sObjNum = command.Parameters[ "@courseId" ].Value.ToString();The @courseId should be the count????Thanks all,Zath

View 4 Replies View Related

Column Count From Stored Procedure

Jul 20, 2005

I am trying to determine the number of columns that are returned froma stored procedure using TSQL. I have a situation where users will becreating their own procedures of which I need to call and place thoseresults in a temp table. I will not be able to modify those usersprocedures. I figure if I have the number of columns I can dynamicallycreate a temp table with the same number of columns, at which point Ican then perform an INSERT INTO #TempTableCreatedDynamically EXEC@UserProcCalled. With that said, does anyone have any idea how todetermine the number of rows that an SP will return in TSQL?Thanks!

View 2 Replies View Related

Problem Running Stored Procedure

Jan 3, 2005

Hi Guys & Gals

I'm having problems running a stored procedure, I'm getting an error that I don't understand. My procedure is this:

ALTER PROC sp_get_allowed_growers
@GrowerList varchar(500)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL =
'SELECT nu_code, nu_description, nu_master
FROM nursery WHERE nu_master IN (' + @GrowerList + ') ORDER BY nu_code ASC'

EXEC(@SQL)
END
GO


and the code I'm using to execute the procedure is this:


public DataSet GetGrowers(string Username)
{
System.Text.StringBuilder UserRoles = new System.Text.StringBuilder();
UsersDB ps = new UsersDB();
SqlDataReader dr = ps.GetRolesByUser(Username);
while(dr.Read())
{
UserRoles.Append(dr["RoleName"]+",");
}
UserRoles.Remove(UserRoles.Length-1,1);
//Create instance of Connection and Command objects
SqlConnection transloadConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionStringTARPS"]);
SqlDataAdapter transloadCommand = new SqlDataAdapter("sp_get_allowed_growers",transloadConnection);
//Create and fill the DataSet
SqlParameter paramList = new SqlParameter("@GrowerList",SqlDbType.VarChar);
paramList.Value = UserRoles.ToString();
transloadCommand.SelectCommand.Parameters.Add(paramList);
DataSet dsGrowers = new DataSet();
transloadCommand.Fill(dsGrowers);
return dsGrowers;

}



The UserRoles stringbuilder has an appropriate value when it is passed to the stored procedure. When I run the stored procedure in query analyser it runs just fine. However, when I step through the code above, I get the following error:


Line 1: Incorrect syntax near 'sp_get_allowed_growers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'sp_get_allowed_growers'.



Anyone with any ideas would be very helpful...

View 6 Replies View Related

Stored Procedure Only Part Running

Dec 10, 2001

I am calling a SQL Server 6.5 Stored Procedure from Access 2000 with the following code :-

Public Function CheckDigitCalc()

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command

On Error GoTo TryAgain

conn.Open "DSN=WEB;uid=sa;pwd=;DATABASE=WEB;"
Set cmd.ActiveConnection = conn

cmd.CommandText = "SPtest2"
cmd.CommandType = adCmdStoredProc
cmd.Execute

MsgBox "Numbers created OK.", vbOKOnly

Exit Function

TryAgain:

MsgBox "Error occurred, see details below :-" & vbCrLf & vbCrLf & _
Err & vbCrLf & vbCrLf & _
Error & vbCrLf & vbCrLf, 48, "Error"

End Function

The MsgBox pops up indicating that the Stored Procedure has run, and there are no errors produced by either SQL Server or Access. However, when I inspect the results of the Stored Procedure, it has not processed all the records it should have. It appears to stop processing after between 6 and 11 records out of a total of 50. The wierd thing is that if I execute the procedure on the server manually, it works perfectly. HELP ME IF U CAN ! THANKS.

View 2 Replies View Related

Error While Running Stored Procedure.

Aug 2, 2007

hi,
i'm using SQL server 2000. i'm getting the below error when i run a store procedure.
"Specified column precision 500 is greater than the maximum precision of 38."
I have created a temporary table inside the stored procedure inserting the values by selecting the fields from other table. mostly i have given the column type as varchar(50) and some fields are numeric(50).

View 2 Replies View Related

Findout If A Stored Procedure Is Running?

Feb 5, 2008

How can I find out if a stored procedure is currently being executed?

sp_who2 and sys.sysprocesses, Command, Cmd fields just gives me parts of the sql inside the stored procedure.

View 5 Replies View Related

Stored Procedure Running Slow In ADO.NET

Jan 9, 2008

We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks

View 22 Replies View Related

Running A Stored Procedure In Code

Apr 14, 2008

Hi,

I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.

I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)

I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.

Here is my SP


ALTER PROCEDURE uspSelectBarItemID2

(

@BarTabID INT,

@DrinkID INT,

@ReturnBarItemID INT OUTPUT

)

AS

BEGIN

SELECT @ReturnBarItemID = barItemID

FROM [Bar Items]

WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)

END

In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.

What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.

Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.

Cheers.

View 11 Replies View Related

Calling A Stored Procedure Which Uses A Return @count

Aug 21, 2007

Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:-----------  ALTER PROCEDURE dbo.CountTEstAS   SET NOCOUNT ON    DECLARE @sql nvarchar(100);    DECLARE @recCount int;      DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*)            FROM Pool 'SET @parms = '@recCount1 int OUTPUT'             exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4  My code that calls this stored proc is:         Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True")        connect.Open()        Dim cmd As New SqlCommand("CountTEst", connect)        cmd.CommandType = CommandType.StoredProcedure        Dim MyCount As Int32        MyCount = cmd.ExecuteScalar        connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and  then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks, 

View 5 Replies View Related

SQL Problem Involving Count(*) In Stored Procedure

Jun 4, 2008

I seem to have a little problem with my SQL.  I'm attempting to use a an .NET grid-view to page a list of search results using an ObjectDataSource (ODS).  It's a very simple search setup.  The ODS retrieves the Search keyword from the query-string sending it to the BLL's GetResult method which then utilises the DAL's version and retrieves the result.  Not all of them of course because I'm paging so it only retrieves the first page (Lets say the first 10 items).  This part of the SQL works fine as the same SQL method is used else where on the site and it's well tested.  But to to use the Paging function I'm using the SelectCountMethod attribute of the ODS.  Again this gets the Search term from the QueryString and sends it too the BLL which retrieves the information from the DAL.  Now this is where it starts playing up.  The Sql count method is as follows and is run as a Stored Procedure: 1 ALTER PROCEDURE dbo.StoreName_Store_GetProductSearchCount
2 (
3 @SearchQuery nvarchar
4 )
5 AS
6 SET NOCOUNT ON
7
8 SELECT COUNT(*)
9 FROM StoreName_Products
10 WHERE Title LIKE '%' + @SearchQuery + '%'
Nice and Easy.  Not a Problem... Isn't it? When I run this query by selecting the database in the Server Explorer in VS2005  and running a 'New Query', it retrieves the correct results.  (I only run this query from the Start of the SELECT Query in this mode, don't add the stuff above it)When I run this query through the website as intended and retrieves the int from the Stored Procedure, it always returns the entire count of the number of products in the table.At first I thought It might be a simple error with my caching system.  Nope, it's not as after I purged it, it still returns the same value. I thought, well maybe just maybe the BLL or the DAL are some point converting it to this number but I'm quite sure it wasn't... and nope it wasn't that either because the ExecuteScalar method was found to be returning this number.  So I ran the Stored Procedure by opening it up and simply right clicking the SQL Query and Executing it adding a value for @SearchQuery when it requested.  Bingo.  It's returning the full number of the products.So my question is... What's the deal?  How can the same query return two completely different results?  and most of all... How do I overcome this?  Is this because it's in a stored procedure and I should be executing it as a SqlCommand from my DAL?  I'm a bit confused.  Is there a problem running this sort of query in a stored procedure that I don't know about and probably really should?Thankful for any answers,Regards,Luke 

View 11 Replies View Related

Count Records When RecordSource Is A Stored Procedure

Jul 20, 2005

I have a stored procedure named mySP that looks basically like this:Select Field1, Field2From tblMyTableWhere Field 3 = 'xyz'What I do is to populate an Access form:DoCmd.Openform "frmMyFormName"Forms!myFormName.RecordSource = "mySP"What I want to do in VBA is to open frmContinuous(a datasheet form) ifmySP returns more than one record or open frmDetail if mySP returnsonly one record.I'm stumped as to how to accomplish this, without running mySP twice:once to count it and once to use it as a recordsource.Thanks,lq

View 1 Replies View Related

Running A Stored Procedure Without Passing Parameters

Feb 14, 2007

HI all, I'd like to run a simple stored procedure on the Event of a button click,  for which I don't need to pass any parameters, I am aware how to run a Stored Procedure with parameters, but I don't know how without, any help would be appreciated please.thanks. 

View 6 Replies View Related

Running Stored Procedure Multiple Times

Jun 25, 2007

I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?
newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure
Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam)
ownrParam.Value = OwnerID
colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close()
colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close()
and so on for 9 columns…

View 7 Replies View Related

Timeout Exception When Running Stored Procedure

Feb 4, 2008

Hi,
 I'm running a CLR stored procedure through my web using table adapters as follows:
res = BLL.contractRateAdviceAdapter.AutoGenCRA()    'with BLL being the business logic layer that hooks into the DAL containing the table adapters.
 The AutoGen stored procedure runs fine when executed directly from within Management Studio, but times out after 30 seconds when run from my application. It's quite a complex stored procedure and will often take longer than 30 seconds to complete.
The stored procedure contains a number of queries and updates which all run as a single transaction. The transaction is defined as follows:
----------------------------------------------------------------------------------------------------------------------
options.IsolationLevel = Transactions.IsolationLevel.ReadUncommittedoptions.Timeout = New TimeSpan(1, 0, 0)
Using scope As New TransactionScope(TransactionScopeOption.Required, options)
'Once we've opened this connection, we need to pass it through to just about every
'function so it can be used throughout. Opening and closing the same connection doesn't seem to work
'within a single transactionUsing conn As New SqlConnection("Context Connection=true")
conn.Open()
ProcessEffectedCRAs(dtTableInfo, arDateList, conn)
scope.Complete()
End Using
End Using
----------------------------------------------------------------------------------------------------------------------
As I said, the code encompassed within this transaction performs a number of database table operations, using the one connection. Each of these operations uses it's own instance of SQLCommand. For example:
----------------------------------------------------------------------------------------------------------------------Dim dt As DataTable
Dim strSQL As StringDim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 0Dim rdr As SqlDataReaderstrSQL = "SELECT * FROM " & Table
cmd.CommandText = strSQL
rdr = cmd.ExecuteReader
SqlContext.Pipe.Send(rdr)
rdr.Close()
----------------------------------------------------------------------------------------------------------------------
Each instance of SQLCommand throughout the stored procedure specifies cmd.CommandTimeout = 0, which is supposed to be endless. And the fact that the stored procedure is successful when run directly from Management studio indicates to me that the stored procedure itself is fine. I also know from output messages that there is no issues with the database connection.
I've set the ASP.Net configuration properties in IIS accordingly.
Are there any other settings that I need to change?
Can I set a timeout property when I'm calling the stored procedure in the first place?
Any advice would be appreciated.
 
Thanks

View 2 Replies View Related

Running A MS SQL Stored Procedure On Button Click

Mar 14, 2008

I am new to ASP.NET so please excuse what may seem like a dumb question.
I have a stored procedure that I need to run when the user clicks on our submit button.  I am using Visual Studio 2005 and thought I could use the SqlDataSOurce Control.  IS it possible to us the control or do I need to create a connection and call the stored procedure in the the button_click sub?
Thanks in advance
MF

View 6 Replies View Related

Error When Running A Stored Procedure From My Code

May 9, 2008

 Hi all,      I wonder if you can help me with this. Basically, Visual Web Developer doesn't like this part of my code despite the fact that the stored procedure has been created in MS SQL. It just won't accept that bold line in the code below and even when I comment it just to cheat, it still gives me an error about the Stored Procedure. Here's the line of code:          // Define data objects        SqlConnection conn;        SqlCommand comm;        // Initialize connection        string connectionString =            ConfigurationManager.ConnectionStrings[            "pay"].ConnectionString;        // Initialize connection        conn = new SqlConnection(connectionString);        // Create command         comm = new SqlCommand("UpdatePaymentDetails", conn);        //comm.CommandType = CommandType.StoredProcedure;        // Add command parameters        comm.Parameters.Add("PaymentID", System.Data.SqlDbType.Int);        comm.Parameters["PaymentID"].Value = paymentID;        comm.Parameters.Add("NewPayment", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewPayment"].Value = newPayment;        comm.Parameters.Add("NewInvoice", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewInvoice"].Value = newInvoice;        comm.Parameters.Add("NewAmount", System.Data.SqlDbType.Money);        comm.Parameters["NewAmount"].Value = newAmount;        comm.Parameters.Add("NewMargin", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewMargin"].Value = newMargin;        comm.Parameters.Add("NewProfit", System.Data.SqlDbType.Money);        comm.Parameters["NewProfit"].Value = newProfit;        comm.Parameters.Add("NewEditDate", System.Data.SqlDbType.DateTime);        comm.Parameters["NewEditDate"].Value = newEditDate;        comm.Parameters.Add("NewQStatus", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewQStatus"].Value = newQStatus;        comm.Parameters.Add("NewStatus", System.Data.SqlDbType.VarChar, 50);        comm.Parameters["NewStatus"].Value = newStatus;        // Enclose database code in Try-Catch-Finally        try        {            conn.Open();            comm.ExecuteNonQuery();        } 

View 7 Replies View Related







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