Accessing Stored Procedure In ASP.Net - What Is Wrong?

Jul 25, 2006

I have the following stored procedure




drop procedure ce_selectCity;



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
create PROCEDURE ce_selectCity
    @recordCount int output
    -- Add the parameters for the stored procedure here
    

    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>
AS

        declare @errNo         int
        
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.

        SET NOCOUNT ON;

        
            -- Insert statements for procedure here

        select ciId,name from ce_city order by name
        
        select @recordCount = @@ROWCOUNT
        select @errNo = @@ERROR

        if @errNo <> 0 GOTO HANDLE_ERROR

        return @errNo
HANDLE_ERROR:
    Rollback transaction
    return @errNo


Go


and i was just testing it like

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        db.connect()
        

        Dim reader As SqlDataReader

        Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection)

        Dim recordCountParam As New SqlParameter("@recordCount", SqlDbType.Int)
        Dim errNoParam As New SqlParameter("@errNo", SqlDbType.Int)

        recordCountParam.Direction = ParameterDirection.Output
        errNoParam.Direction = ParameterDirection.ReturnValue

        sqlCommand.Parameters.Add(recordCountParam)
        sqlCommand.Parameters.Add(errNoParam)

        reader = db.runStoredProcedureGetReader(sqlCommand)

        If (db.isError = False And reader.HasRows) Then
            Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />")
            While (reader.Read())
                Response.Write(reader("ciId") & "::" & reader("name") & "<br />")
            End While

        End If
        db.close()
    End Sub


It returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).

BUT, its returning 0.

What am i doing wrong??

EDIT:
Oh, and this is the function i use to execute stored procedure and get the reader

Public Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader
            sqlCommand.CommandType = CommandType.StoredProcedure
            Return sqlCommand.ExecuteReader
        End Function

View 5 Replies


ADVERTISEMENT

What's Wrong With This Stored Procedure?

Apr 6, 2004

please take a look at this strored proxedure and tell what is wrong with it?


CREATE PROCEDURE sp_Pictures_CreateTextPost
@UserID Int,
@Country varchar (2),
@Description varchar (1000),
@PostID int output
AS
BEGIN TRANSACTION
INSERT INTO Pictures_TextPosts
(UserID, Country, Description)
VALUES
(@UserID, @Country, @Description)

IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
BEGIN
COMMIT TRANSACTION
UPDATE Accounts_Users SET Count_InsertText = Count_InsertText + 1 Where UserID = @UserID
SET @PostID = @@IDENTITY
RETURN 1
END


i run this sp on MS SQL server on my local machine and it works fine. now i move the same sp to the host server and it no longer works. it is the problem with this sp as i run sql query to test it. i use the ASP.NET Enterprise Manager to insert this sp. can someone please help?

one hint: on the host server, if i add 'GO' to the end of the sp, error occured.

View 6 Replies View Related

What's Wrong With My Stored Procedure?

Apr 8, 2004

I have written an ASP.net app that iterates through the controls on my page if the are checkboxlist or radiobuttonlist controls, the id and value are serialized into an xml file which is sent to a SQL 2000 stored procedure where the xml data is inserted into a couple of tables.

I captured the xml that is being sent:

<?xml version="1.0"?>
<Schema7Ihsurveyresponse xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" surveyid="1" Userid="14">
<ihsresponsedetail question="Q1" response="0" result="1" />
<ihsresponsedetail question="Q2" response="0" result="1" />
<ihsresponsedetail question="Q3" response="0" result="1" />
<ihsresponsedetail question="Q5" response="0" result="1" />
<ihsresponsedetail question="Q6" response="0" result="1" />
<ihsresponsedetail question="Q7" response="0" result="1" />
<ihsresponsedetail question="Q8" response="0" result="1" />
<ihsresponsedetail question="Q9" response="0" result="1" />
<ihsresponsedetail question="Q10" response="0" result="1" />
<ihsresponsedetail question="Q11" response="0" result="1" />
<ihsresponsedetail question="Q12" response="0" result="1" />
<ihsresponsedetail question="Q13" response="0" result="1" />
<ihsresponsedetail question="Q14" response="0" result="1" />
<ihsresponsedetail question="Q15" response="0" result="1" />
<ihsresponsedetail question="Q17" response="0" result="1" />
<ihsresponsedetail question="Q18" response="0" result="1" />
<ihsresponsedetail question="Q19" response="0" result="1" />
<ihsresponsedetail question="Q20" response="0" result="1" />
<ihsresponsedetail question="Q21" response="0" result="1" />
<ihsresponsedetail question="Q23" response="0" result="1" />
<ihsresponsedetail question="Q24" response="0" result="1" />
<ihsresponsedetail question="Q25" response="0" result="1" />
<ihsresponsedetail question="Q26" response="0" result="1" />
<ihsresponsedetail question="Q27" response="0" result="1" />
<ihsresponsedetail question="Q28" response="0" result="1" />
<ihsresponsedetail question="Q29" response="0" result="1" />
<ihsresponsedetail question="Q30" response="0" result="1" />
<ihsresponsedetail question="Q31" response="0" result="1" />
<ihsresponsedetail question="Q32" response="0" result="1" />
<ihsresponsedetail question="Q33" response="0" result="1" />
<ihsresponsedetail question="Q34" response="0" result="1" />
<ihsresponsedetail question="Q35" response="0" result="1" />
<ihsresponsedetail question="Q36" response="0" result="1" />
<ihsresponsedetail question="Q37" response="0" result="1" />
<ihsresponsedetail question="Q38" response="0" result="1" />
<ihsresponsedetail question="Q39" response="0" result="1" />
<ihsresponsedetail question="Q40" response="0" result="1" />
</Schema7Ihsurveyresponse>


and here is my stored procedure:

CREATE PROCEDURE Insertresponsedetails
@XML TEXT

AS
SET NOCOUNT ON
SET XACT_ABORT ON
-- Access is not restricted.

insert into tempxml ([xml]) values (@xml)

DECLARE @idoc INT, @responseid int
EXECUTE sp_xml_preparedocument @idoc OUTPUT, @XML, '<ns xmlns:a="http://interhealthusa.com/surveyresponse.xsd"/>'

if @@error<>0
begin
select(-1)
return
end

BEGIN TRANSACTION
insert into ihsurveyresponse (userid, Surveyid, Responsedate)
select userid, surveyid, getdate()
from openxml(@idoc, '/schema7ihsurveyresponse', 1)
with (userid int, surveyid int)

if @@error<>0
begin
rollback transaction
select 2
return
end


set @responseid = scope_identity()

INSERT INTO IHSResponsedetail (Question,Result, Response)
SELECT Question, Result, @responseid
FROM OPENXML (@idoc, '/schema7ihSurveyResponse/ihsresponsedetail')
WITH ( [Question] char(10) '@Question', Result int '@Result')
EXECUTE sp_xml_removedocument @idoc

if @@error<>0
begin
rollback transaction
select 3
return
end


COMMIT TRANSACTION
select @responseid
GO


If anyone has any insight I would really appreciate the assistance.

View 1 Replies View Related

What&#39;s Wrong With This Stored Procedure?

Sep 20, 2001

Hiya, all!
I've got this stored procedure:
CREATE Procedure CheckReOccurences
@PatID INT, @CPT CHAR(7),@DOS DATETIME,@DRecur SMALLINT
AS
DECLARE @DaysElapsed INT
SET @DaysElapsed = (SELECT DATEDIFF(day,ISNULL((SELECT MAX(ServiceDate) FROM VouchersDetail WHERE PatID = @PatID AND ProcCode = @CPT),01/01/1900),@DOS))
IF @DaysElapsed < @DRecur --Patient has had procedure done too recently
BEGIN
PRINT 'Too Recent-Only ' + str(@dayselapsed) + ' days elapsed'
RETURN 0
END
ELSE --More time has elapsed since procedure last performed than is necessary
BEGIN
PRINT 'Not Too Recent- ' + str(@dayselapsed) + ' days elapsed'
RETURN 1
END
And when I run it with the exec command in Query Analyzer, filling in all parameters with proper values, I get this error message:
Server: Msg 8114, Level 16, State 4, Procedure CheckReOccurences, Line 0
Error converting data type nvarchar to datetime.

Please note Line says 0, when no such line exists. I've tried running every single part separately in QA, and all runs well (the functions work, returns expected code, etc.). What's the problem?

Thanks,
Sarah

View 4 Replies View Related

What's Wrong With This Stored Procedure?

Jun 11, 2004

I am having trouble getting this procedure to properly enter the data into a table. It works when I do not add the @ToName info and only ask to insert the AccountID and FromName data. When i add the @ToNamevariable, no data is inserted. I think it might have to do with the select statement. I have two TerritoryID, RegionID, DivisionID, and EmpID because I want this information to be entered for both the FromName and the ToName selected. Does anyone know what my problem is? Id appreciate any help.

CREATE PROCEDURE InsertAllThree
@AccountID char(10),
@FromName nvarchar(50),
@ToName nvarchar(50)
AS

insert into accounttransferstestmike
(AccountID, FromTerritoryID, FromRegionID, FromDivisionID, FromEmpID, ToTerritoryID, ToRegionID, ToDivisionID, ToEmpID)
select Accounts.AccountID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID
From Accounts, EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID
Where (Accounts.AccountID = @AccountID) and (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromN ame) AND (EndoscopySqlUser.Territories.TerritoryName=@ToNam e)
GO

View 4 Replies View Related

What's Wrong With This Stored Procedure

Oct 31, 2006

When I try to create the stored procedure below, I receive the following error. Can someone please let me know what is wrong with the code? The problem seems to be with the default parameter of getdate(). This is because when I comment the default parameter, it works fine. Please note that I need the @BeginDate parameter to be varchar(10). I cannot have it as datetime.

Msg 156, Level 15, State 1, Procedure test, Line 4
Incorrect syntax near the keyword 'convert'.
Msg 137, Level 15, State 2, Procedure test, Line 8
Must declare the scalar variable "@BeginDate".


/* Stored Procedure*/
Create PROCEDURE test --test
@BeginDate varchar(10) = convert(varchar(10),getdate(),101)
as
select convert(datetime,@BeginDate)



Thanks!

View 9 Replies View Related

Help - I've Done Something Wrong On My Stored Procedure,....

Jan 24, 2008



Hi All,

Appreciate any help that you can offer, I have three tables that need data entered when the user completes a web form, in short the tables are

dbo.JBAClient
Which has
JBCID as PK and other data

dbo.JBAEmployee
Which has
JBEID as PK and JBEClientID as a Foreign Key and other data

dbo.JBAdvert
Which has
JBAID as PK, JBAEmployeeID as Foreign Key , JBAClientID as Foreign Key and other data,

The stored procedure that I've tried to write is supposed to create a new client record and enter all of the relevant data into the client table (store the JBCID) then Create a new Employee Record, insert all relevant data including (JBCID into JBEClientID) and (Store both JBCID & JBEID). Finally create a new advert record and store all of the relevant data including (JBCID as JBAClientID and JBEID as JBAEmployeeID)

The stored procedure that I have written works fine until the last section, where instead of inserting JBCID into JBAClientID and JBEID into JBAEmployeeID, it inserts JBEID into JBAClientID and JBEID into JBAEmployeeID

So inserting JBEID twice and ignoring JBEClientID........

the code is below,---

CREATE PROCEDURE PayPalCLient
@siteid int,
@companyname nvarchar(50),
@address nvarchar(500),
@phone nvarchar(50),
@fax nvarchar(50),
@email nvarchar(225),
@url nvarchar(225),
@companytype nvarchar(50),
@billingcontact nvarchar(50),
@name nvarchar(50),
@AccountType nvarchar(50),
@PASSWORD nvarchar(50),
@AccountLive nvarchar(50),
@EmployeeLevel nvarchar(50),
@adverttitle nvarchar(50),
@description nvarchar(500),
@category nvarchar(50),
@location nvarchar(50),
@payrate nvarchar(50),
@employmenttype nvarchar(50),
@reference nvarchar(50),
@startdate nvarchar(50),
@postfor INT,
@invoiced nvarchar(50),
@notified nvarchar(50),
@ppclient nvarchar(50),
@pppaid nvarchar(50)
AS
Declare @NewID INT
INSERT INTO dbo.JBClient(JBCLSiteID, JBCLName, JBCLAddress, JBCLPhone, JBCLFax, JBCLEmail, JBCLCompanyType, JBCLURL, JBCLAccountType, JBCLAccountlive, JBCLBillingContact)
VALUES (@siteid, @companyname, @address, @phone, @fax, @email, @companytype, @url, @AccountType, @AccountLive, @billingcontact)
DECLARE @NewID2 INT
SELECT @NewID = SCOPE_IDENTITY()
INSERT INTO dbo.JBEmployee(JBEClientID, JBESiteID, JBEName, JBELevel, JBEUsername, JBEPassword, JBEAddress, JBEPhone)
VALUES (@NewID, @siteid, @name, @EmployeeLevel, @email, @PASSWORD, @address, @phone)
SELECT @NewID = SCOPE_IDENTITY()
SELECT @NewID2 = SCOPE_IDENTITY()
INSERT INTO dbo.JBAdvert(JBAClientID, JBAEmployeeID, JBASiteID, JBATitle, JBADescription, JBACategory, JBALocation, JBAPayRate, JBAEmplymentType, JBAReference, JBAStartDate, JBAPostFor, JBAInvoiced, JBANotified, JBAPPClient, JBAPPPaid)
VALUES (@NewID, @NewID2, @siteid, @adverttitle, @description, @category, @location, @payrate, @employmenttype, @reference, @startdate, @postfor, @invoiced, @notified, @ppclient, @pppaid)

Appreciate any help that you can offer, I think the problem has something to do with --

SELECT @NewID = SCOPE_IDENTITY()
SELECT @NewID2 = SCOPE_IDENTITY()


Thanks again

View 3 Replies View Related

What Wrong With This Variable In Stored Procedure?

Jun 23, 2004

Hi all.

1. I have a stored procedure, the procedure need create new category for forums, each category have their own place (what going first , what going second etc...), it's importent for me that all categories will be in property for them place, so i must read next place from table, and if not then i set it = 0 . But it's seems not working, i getting error.
Can somebody tell me please what wrong ?


ALTER PROCEDURE dbo.CreateCategory
(
@category_name varchar(100)
)

AS
declare @place int

/* setting place to zero */
SET @place = 0

/* trying to get last place and set value into @place */
SET @place = (Select max(category_place) FROM fo_categories)

/* if got nothing from table then setting value of first category */
IF (@place = 0)
set @place = 1


INSERT fo_categories (category_name, category_place)
VALUES(@category_name, @place)

RETURN


Running dbo."CreateCategory" ( @category_name = Public forums ).
Cannot insert the value NULL into column 'category_place', table 'mg_forum.dbo.fo_categories'; column does not allow nulls. INSERT fails.


2. I also would ask what SET NOCOUNT ON/OFF mean and for what i can use it ?

View 2 Replies View Related

Accessing Stored Procedure Parameters Through XSD

Dec 9, 2006

Hi All,
 I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD.
Thanks,
 
Long Live Microsoft ;)
 

View 4 Replies View Related

Accessing Web.config From Stored Procedure

Feb 27, 2007

I want to access a key from appSettings  section of web.config. 
I have the number of days allowed for a user to activate his/her account  as a key in appSettings.
I have a maintenance procedure to delete all accounts that are not activated before that many days.
In this context, i have to access web.config from stored procedure.  The procedure will be scheduled as a JOB in sql server.
Thanks.
 

View 3 Replies View Related

Accessing A 'View' From Within A Stored Procedure

Jun 7, 2004

Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.

The syntax I'm using is as follows :

SELECT TOP 1 @MyJobN = IJobN FROM MyView

I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.

SELECT TOP 1 @MyJobN = IJobN FROM LimsLive.dbo.MyView

But alas to no avail!


Any offers kind people??

View 12 Replies View Related

Error In Accessing Stored Procedure

Oct 10, 2006

i'm trying to insert into db sql 2000 through a stored procedure .. i got this error " Procedure or function newuser has too many arguments specified "

this is the procedure :



ALTER PROCEDURE newuser

(@username_1 [nvarchar](80),

@email_2 [nvarchar](50),

@password_3 [nvarchar](256),

@Country_ID_4 [int],

@city_id_5 [nvarchar](10),

@gender_6 [nvarchar](50),

@age_7 [int],

@fname_8 [nvarchar](50),

@lname_9 [nvarchar](50),

@birthdate_10 [datetime])

AS INSERT INTO [Brazers].[dbo].[users]

( [username],

[email],

[password],

[Country.ID],

[city.id],

[gender],

[age],

[fname],

[lname],

[birthdate])


VALUES

( @username_1,

@email_2,

@password_3,

@Country_ID_4,

@city_id_5,

@gender_6,

@age_7,

@fname_8,

@lname_9,

@birthdate_10)







& that 's the code in asp page :



Dim param As New SqlClient.SqlParameter

SqlConnection1.Open()

param.ParameterName = "@username_1"

param.Value = TextBox1.Text

param.Direction = ParameterDirection.Input

SqlCommand1.Parameters.Add(param)

SqlCommand1.ExecuteNonQuery()







plz .. waiting any solve for this problem immediately

View 2 Replies View Related

Accessing Another Database In A Stored Procedure

Aug 10, 2006

hi

i have stored procedure and i need to access another database in my stored procedure

I'm going to query a table which is located in another database



as you know it is impossible to use the USE database keyword in stored procedures

so what should I do?

thanks.

View 2 Replies View Related

Accessing SELECT Results Within A Stored Procedure

May 18, 2004

Hi,
Can anyone tell me how to access the results from a select statement within the stored procedure?

This is to implement auditting functionality, before updating a record i want select the original record and compare the contents of each field with the varibles that are passed in, if the fields contents has changed i want to write an audit entry.

So i'd like to store the results of the select statement in a variable and access it like a dataset. e.g

declare selectResult

set selectResult = Select field1,field2,field3 from table1

if selectResult.field1 <> @field1
begin
exec writeAudit @var1,@var2,var3
end


Many thanks.

View 4 Replies View Related

Error While Accessing COM Component From .NET Stored Procedure..

May 7, 2006

I have a COM object that is written using Visual Basic 6. This is referenced in a .NET Stored Procedure. But when I execute the stored procedure I get an error:

Msg 6522, Level 16, State 1, Procedure prCalculator_ExecCalc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'prCalculator_ExecCalc':

System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {844E8165-ABC1-432B-9490-51B1A6D91E71} failed due to the following error: 80040154.

System.Runtime.InteropServices.COMException:



Here is what I do:

1. Compile the VB DLL.

2. Convert into a .NET assembly by importing to a Visual Studio 2005 project. Sign the interop assembly.

3. Register it as an assembly in SQL 2005 server.

4. Create a .NET stored procedure and reference the above assembly. Compile this assembly again in SQL 2005 and create a stored procedure using the assembly. This assembly is also signed.

Please Note:

1. All the assemblies are registered with UNSAFE permissions.

2. They are compiled with COM Visible flag in Visual Studio 2005.

3. This works perfectly on my local SQL Express where I have Visual Basic/VisualStudio 2005 installed.

4. I get this error, when trying on Test Server. I tried to install the VB Runtime on this machine and still does not work.

So, what am I missing? Thanks for your help.





















.

View 4 Replies View Related

Problem With Accessing Stored Procedure In The Report

Mar 14, 2007

Hi,

I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:

An error has occurred during report processing. (rsProcessingAborted)

Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)

Could not find stored procedure 'stpBranch'.

But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.
Input string was not in a correct format.

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.FormatException: Input string was not in a correct format.

Source Error:





An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:





[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102
Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64


I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.

I am not sure why I am getting this error and I greatly appreciate any help.

Thanks



View 2 Replies View Related

All I Want To Do Is Feed A Varchar Into A Stored Procedure And Get An Id Back... What Am I Doing Wrong?

Dec 28, 2006

So I have been building stored procedures and things were working fine until I hit something that I am sure is incredibly simple to do; however, i have having a hell of a time with it.  Here is the code:#############################################ALTER PROCEDURE dbo.GetUserIdForUser  @username NVARCHARASBEGINDECLARE @postedbyid UNIQUEIDENTIFIERSET @postedbyid = (SELECT UserIdFROM aspnet_UsersWHERE (UserName = @username))END###Which returns this### Running [dbo].[GetUserIdForUser] ( @username = jason ).No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser].############################################# This is part of a much larger stored procedure, but this is the point of failure in the stored procedure I am trying to build.  If anyone can tell me what I am doing wrong I would appreciate it.  I have tried a few things that have resulted in different failures.  If I remove any references to variables (delete SET @postedbyid = and replace @username with 'jason') I can get it to return a result.  If I put @username in though it doesn't work.  Here are the examples of those:ALTER PROCEDURE dbo.GetUserIdForUser  @username NVARCHARASBEGINSELECT UserIdFROM aspnet_UsersWHERE (UserName = @username)END###Which returns this###Running [dbo].[GetUserIdForUser] ( @username = jason ).UserId                                 -------------------------------------- No rows affected.(0 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser].  Here is a sanity check to show that the data is in fact in the database:ALTER PROCEDURE dbo.GetUserIdForUserASBEGINSELECT UserIdFROM aspnet_UsersWHERE (UserName = 'jason')END Running [dbo].[GetUserIdForUser].UserId                                 -------------------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[GetUserIdForUser]. Anyone have any ideas on what I am doing wrong?   

View 13 Replies View Related

Accessing Multiple Databases From Stored Procedure In SQL 2000

May 16, 2006

Hi ,

I have around 5 databases with same structure used to store data for different locations and services.

I have created an intermediate database which will have all the stored procedures to access data from above 5 databases.

My queries are common to all the databases. so,I would like to pass database name as an argument to my stored proc and execure query on specified database.

I tried this doing using "USE Databasename " Command. but it says ...We can not use "USE command " in stored proc or triggers. so ..what i did is ..

--------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE TestDB_access(@dbname varchar(100))
AS

BEGIN

DECLARE @localDB varchar(100)

Set @LocalDB=@dbname

if @LocalDB='XYZ'

EXEC ('USE XYZ ')
elseif @LocalDB='ABC'

EXEC ('USE ABC')


Select * from Mytable

END

---------------------------------------------------------------------------------------------------------------

When I run this from my database , it gives me an error "unable to find table "mytable".

What is the best way to make my queries work with all the databases.

Thanks in advance

Venu Yankarla

View 4 Replies View Related

Trouble Accessing SQL Server 2005 Stored Procedure Parameters

Jun 14, 2007

I created a stored procedure (see snippet below) and the owner is "dbo".
I created a data connection (slcbathena.SLCPrint.AdamsK) using Windows authentication.
I added a new datasource to my application in VS 2005 which created a dataset (slcprintDataSet.xsd).
I opened up the dataset in Designer so I could get to the table adapter.
I selected the table adapter and went to the properties panel.
I changed the DeleteCommand as follows: CommandType = StoredProcedure; CommandText = usp_OpConDeleteDirectory. When I clicked on the Parameters Collection to pull up the Parameters Collection Editor, there was no parameters listed to edit even though there is one defined in the stored procedure. Why?

If I create the stored procedure as "AdamsK.usp_OpConDeleteDirectory", the parameters show up correctly in the Parameters Collection Editor. Is there a relationship between the owner name of the stored procedure and the data connection name? If so, how can I create a data connection that uses "dbo" instead of "AdamsK" so I can use the stored procedure under owner "dbo"?



Any help will be greatly appreciated!



Code SnippetCREATE PROCEDURE dbo.usp_OpConDeleteDirectory
(
@DirectoryID int
)
AS
SET NOCOUNT ON
DELETE FROM OpConDirectories WHERE (DirectoryID = @DirectoryID)

View 1 Replies View Related

What Is Wrong With This Code? Selecting Data With Stored Procedure With Multiple Parameters

Sep 22, 2005

oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent)  'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.

View 2 Replies View Related

Accessing A Stored Procedure From ADO.NET 2.0-VB 2005 Express:How To Define/add 1 Output &&amp; 2 Input Parameters In Param. Coll.?

Feb 23, 2008

Hi all,

In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":

CREATE PROC [dbo].[spInvTotal3]

@InvTotal money OUTPUT,

@DateVar smalldatetime = NULL,

@VendorVar varchar(40) = '%'



This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:


Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub printMyInvTotal()

Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"

Dim conn As SqlConnection = New SqlConnection(connectionString)

Try

conn.Open()

Dim cmd As New SqlCommand

cmd.Connection = conn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "[dbo].[spInvTotal3]"

Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)

param.Direction = ParameterDirection.Output

cmd.Parameters.Add(param)

cmd.ExecuteNonQuery()

'Print out the InvTotal in TextBox1

TextBox1.Text = param.Value

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

conn.Close()

End Try

End Sub

End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?

Please help and advise.

Thanks in advance,
Scott Chang



View 7 Replies View Related

Data Access :: Users Get Wrong Data Records - Cross Coupling When Accessing Files From MVC App

Aug 7, 2015

I have an MVC asp.net application that stores many records in a table on sql server, in its own system.  used the system for 2 months, worked fine accessing, changing data.

Now that other users are logging in? there is cross coupling going on.  one user gets the data from another users sql search.

In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous.  no effect;  the user makes their own login id but that does nt matter either.

View 8 Replies View Related

What Is Wrong With This Procedure?

Jul 23, 2005

I get the following error for this procedure:Server: Msg 156, Level 15, State 1, Procedure Trig_Ins_TrackingNumbers,Line 6Incorrect syntax near the keyword 'IF'.CREATE TRIGGER Trig_Ins_TrackingNumbers ON TrackingNumbersINSTEAD OF INSERTASBEGINIF (SELECT ISNUMERIC(InvoiceNumber) FROM inserted)IF(EXISTS (SELECT I.InvoiceNumberFROM Invoices I, inserted InsWHERE I.InvoiceNumber=CAST(Ins.InvoiceNumber AS INT)))INSERT INTO TrackingNumbers(InvoiceNum,InvoiceNumber,TrackingNumber)SELECT CAST(InvoiceNumber AS INT), InvoiceNumber, TrackingNumberFROM insertedEND

View 4 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Procedure Returns Wrong @@Identity

Jan 15, 2004

Hi,

I have a procedure that inserts a record to a table and return the @@Identity as an output parameter.

This table has a trigger that is triggered when a record is inserted and add a new record to other table.

The problem is that the @@Identity returned is totally wrong.

Any ideas?

Thanx in advance.

View 3 Replies View Related

If / Else Stored Proc -- What Am I Doing Wrong?

Mar 29, 2005

Bellow is the stored procedure.  I wanted to do an If / Else statement.  I'm getting a syntax error that something is wrong around my Begin / Else statements.  If anyone knows what is wrong I would greatly appriciate it.
 
Thanks in advance as always.
RB
<code>
Create Proc UpdateFundsAndTotals ASIF (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPledges.fundID AND FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)Begin Update FundsAndTotals Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledgesENDELSE  (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPayments.fundID AND FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)Begin Update FundsAndTotals Set FundsAndTotals.TotalPayments = TotalPayments.TotalPaymentsENDElseEND
</code>

View 7 Replies View Related

What's Wrong With This Stored Proc

Mar 8, 2005

Can somebody please debug what is wrong with this stored proc.
When I execute this procedure I get an error
Invalid column name 'abcd'.



CREATE PROCEDURE SearchTest

@LName varchar (25) = 'abcd',
@ID varchar(9) = null

AS

Declare @query varchar(100)
Set @query = 'Select * From Document '

IF ( @LName IS NOT NULL)
set @query = @query + ' Where LName = ' + @LName

exec (@query)

GO

Thanks :)

View 4 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Whatz Wrong With This Stored Proc

Aug 16, 2004

Hi,
I'm getting an error ERROR 137: Must declare the variable '@' when I click the check sysntax button on my store proc window. Any ideas on how to resolve this??


CREATE PROCEDURE CabsSchedule_GetPrevYearMonth
AS
DECLARE @prvYearMonth int
DECLARE @prvYear int
DECLARE @prvMonth int

BEGIN
SET @prvYearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
IF ((SELECT COUNT(*) FROM CabsSchedule WHERE YearMonth = @ prvYearMonth) > 15)
ELSE
BEGIN
IF (SUBSTRING(CAST(@prvYearMonth AS VarChar(6)), 5, 2) = '01')
BEGIN
SET @prvMonth = 12
SET @prvYear = CAST(SUBSTRING(CAST(@prvYearMonth AS VARCHAR(6)),1,4) AS INT) - 1
SET @prvYearMonth = CAST(CAST(@prvYear AS VARCHAR(4)) + CAST(@prvMonth AS VARCHAR(2)) AS INT)
END
END
END
GO



Thanks,

View 1 Replies View Related

Accessing Views From Stored Procedures

Sep 7, 2007

Hi,I was just wondering if it's possible to access views from stored procedures? I know it doesn't make much sense, but would it be possible? If so, can you also give me some code example?Thanks. 

View 4 Replies View Related

Accessing .NET Dlls From Stored Procedures

Oct 28, 2004

Can anyone tell me is it possible to register and access .NET dlls from Stored procedures
and how to call those methods in those dlls from Stored procedures

View 1 Replies View Related

Need Help In Accessing A Variable Without Stored Procs

Apr 17, 2008

Hi

I have a sql task in my control flow, which is like
Resultset = Single Row, SQL SourceType = Direct Input, SQL Stmt = EXEC StoreProcA ?
Result Name VariableName
StartDate UserStartDate
EndDate User:EndDate
Above SQL task is working fine... My question is I have an another SQL task immediately below that one.SQL Stmt is


SELECT CreditDebitDiff

FROM (SELECT C.BalanceNbr - D.BalanceNbr As CreditDebitDiff

FROM dbo.BalanceLog C, dbo.BalanceLog D

JOIN Work.CycleControl cc

ON cc.CycleControlId = D.CycleControlId

WHERE C.BalanceItemId = '81041'

AND D.BalanceItemId = '81031'

AND cc.CycleDate = ?) AS D

Parameter0 is mapped to User:EndDate and ResultName mapped to User:CreditDebitDiff (Datatype: Double)
SQL Task fails. I know I am making a mistake but dunno how to correct it. I can do it with a store proc but don't want to.
Any help would be greatly appreciated...

View 4 Replies View Related

Wrong Values Stored For Datatype Float

Aug 3, 2006

In SQL Server for a field of datatype float(8),
if i insert a value 2.62 , it saves it as 2.6200000000000001
like so for other values also.
But in frontend i can see the right values.

Could any of you help me in fixing up this issue?

Thanks in advance.

~ chaitanya

View 1 Replies View Related







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