Trying To Get Output Parameter From TSQL Query

Apr 10, 2008

I am trying to return an ouput parameter from my query. I have tested the stored proceedure extensivly within the SQL Management Studio, and I know it works fine, so that means the error is somewhere within my code, but for the life of my I cant figure where.

Here is my stored proc:

  set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[tblSiteVisits_FindOfficerName]
@VisitID int,
@OfficerName varchar(100) output
AS
BEGIN
-- Variables
Declare @OfficerID int
--Declare @OfficerName varchar(100)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select @OfficerID = (Select top 1
OfficerID from tblSiteVisitOfficers Where VisitID = @VisitID)

IF (@OfficerID Is Null)
BEGIN -- Get the None Registered Officer
Select @OfficerName = (Select top 1 OfficerOther from dbo.tblSiteVisitOfficers Where VisitID = @VisitID)
print 'Got unregistered Officer ' + @OfficerName
END
ELSE
BEGIN -- Get the Registered Officer
Select @OfficerName = (Select OfficerFName + ' ' + OfficerLname from dbo.tblOfficers Where OfficerID = @OfficerID)
print 'Got Registered Officer ' + @OfficerName
END
END

 

And here is the code I am using to access this proceedure:1 Dim blah As String
2 Dim conn2 As New SqlConnection()
3 Dim cmd2 As New SqlCommand()
4 conn2.ConnectionString = _ConnString
5 cmd2.Connection = conn2
6 cmd2.CommandType = CommandType.StoredProcedure
7 cmd2.CommandText = "dbo.tblSiteVisits_FindOfficerName"
8
9 cmd.Parameters.AddWithValue("@VisitID", Convert.ToInt32(row("VisitID")))
10 cmd.Parameters.Add("@OfficerName", SqlDbType.VarChar, 100)
11 cmd.Parameters("@OfficerName").Direction = ParameterDirection.Output
12 Try
13 conn.Open()
14 cmd.ExecuteNonQuery()
15 blah = cmd.Parameters("@OfficerName").Value.ToString()
16
17 Catch ex As Exception
18 Throw ex
19 Finally
20 conn.Close()
21 End Try
22


 

However there I never recieve the output value, and because of the way my database is structures, there is no possible way, that there is no output value.
If anyone can help, that would be great, kind regards.

View 2 Replies


ADVERTISEMENT

How To Display Return Value From Stored Procedure Output Parameter In Query Analyzer

Jul 20, 2004

I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:

CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS

SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO

If I run the query in Query Analyzer directly, I can display @UserName:

DECLARE @UserName as nvarchar(100)

SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password

Select @UserName

But how can I display @UserName if I call stored procedure:

exec UserLogin 'email', 'password', ''

I believed it return int, right?

Thanks in advance for help.

View 2 Replies View Related

Tsql Output To .csv

Jan 4, 2007

What is the best way to output a query to a .csv file for a Microsoft SQL Server Analyzer using tsql?

View 2 Replies View Related

TSQL Text File Output

Aug 24, 2006

How do I output a table as a txt file using tsql?

View 9 Replies View Related

The Formal Parameter @ReportingId Was Not Declared As An OUTPUT Parameter, But...what Is This?

Apr 17, 2008

After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...

An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".

I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.

The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...




Code Snippet
select @ReportingId = max(ReportingId)
from [dbo].[GuidToReportingid]
where Guid = @UniqueIdentifier and
EffectiveEndDate is null

if @ReportingId is null
BEGIN
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
END
ELSE
BEGIN
select @rowcount = count(*) from [dbo].[someTable]
where ReportingId = @ReportingId and...lots of other conditions






...later as part of an else it does the following...




Code Snippet
if @rowcount > 0 and @joinsMatch = 1
begin
set @insertFlag = 0
end
else
begin
update [dbo].[GuidToReportingId]
set EffectiveEndDate = getdate()
where ReportingId = @ReportingId
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
end






...and before the return it's value is inserted to different tables.

View 5 Replies View Related

My Output Parameter Is Being Treated As An Input Parameter...why

Sep 25, 2006

I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal.  I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening?  The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){  if (e.Exception == null)    {   string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString();    ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int

Set @stoptrans = 'NO'

/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )

IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN

IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END

IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END

IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END

ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum

SET @NewRetVal = 0
---Successful deletion
END
GO
 --------------------------------------------------------  When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.

View 2 Replies View Related

Execute SQL Task : Input And Output Parameters In Tsql Stataments With ADO.NET Connection Type

Jan 2, 2007

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks



 

 

View 11 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!



But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

Getting Value From Output Parameter

Aug 2, 2006

I have an SQL INSERT statement with an output parameter called @CusRef. I have been trying to store this in a session variable, however all i am able to store is the reference to the parameter object. (The returned value stored in the parameter is an Integer)Session("CustomerReference") = DataConn.InsertParameters.Item("CusRef")I cant seem to find a value field or a method to get the value from a parameter. I have also tried using CType(DataConn.InsertParameters.Item("CusRef"), Integer) but it cant convert the Parameter type to an Integer.Please help,ThanksGareth

View 1 Replies View Related

Output Parameter?

Aug 25, 2006

A SQL Server 2005 DB table named "Users" has the following columns:
ID - int (IDENTITY)FirstName - varchar(50)LastName - varchar(50)UserID - varchar(20)Password - varchar(20)
Before inserting a new record in the DB table, ASP.NET first checks whether the UserID supplied by the new record already exists or not. If it exists, the new record shouldn't be inserted in the DB table & the user should be shown a message saying UserID already exists. To do this, ASP.NET uses a stored procedure. If the value returned by the stored procedure is 1, it means that the UserID already exists. If the value returned by the stored procedure is 0, then the new record should be inserted in the DB table. This is how I have framed the stored procedure:
CREATE PROCEDURE RegUsers        @FName varchar(50),        @LName varchar(50),        @UserID varchar(50),        @Password varchar(50),        @return_value int OUTPUTAS        IF EXISTS(SELECT UserID FROM Users WHERE UserID=@UserID)        BEGIN                SET @return_value=1        END        ELSE        BEGIN                SET @return_value=0                INSERT INTO Users VALUES (@FName,@LName,@UserID,@Password)        END
& this is how I am invoking the stored procedure from the ASPX page:
<script runat="server">    Sub btnSubmit(ByVal obj As Object, ByVal ea As EventArgs)        Dim sqlCmd As SqlCommand        Dim sqlConn As SqlConnection
        sqlConn = New SqlConnection("Data Source=MyDBSQLEXPRESS;Initial Catalog=DB;Integrated Security=True")        sqlCmd = New SqlCommand("RegUsers", sqlConn)        sqlCmd.CommandType = CommandType.StoredProcedure
        With sqlCmd            Parameters.Add("@return_value", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue            Parameters.AddWithValue("@FName", txtFName.Text)            Parameters.AddWithValue("@LName", txtLName.Text)            Parameters.AddWithValue("@UserID", txtUserID.Text)            Parameters.AddWithValue("@Password", txtPassword.Text)        End With
        sqlConn.Open()        sqlCmd.ExecuteNonQuery()
        If (sqlCmd.Parameters(0).Value = 1) Then            lblMessage.Text = "UserID Already Exists!"        ElseIf (sqlCmd.Parameters(0).Value = 0) Then            lblMessage.Text = "Thanks For Registering!"        End If
        sqlConn.Close()    End Sub</script><form runat="server"><%-- the 4 TextBoxes come here --></form>
When I execute the above ASPX code, if the UserID I entered already exists in the DB table, then ASPX generates the following error:
Procedure or Function 'RegisterUsers' expects parameter '@return_value', which was not supplied.
pointing to the line
sqlCmd.ExecuteNonQuery()
Can someone please point out where am I going wrong?

View 1 Replies View Related

How Can I Get And Use An OUTPUT Parameter

Jan 31, 2007

Here is what I have so far, I can get a number added to the table running my sproc from management studio. But how do I get it out as it is being intserted and then use it in my code?ALTER PROCEDURE [dbo].[NumberCounter]
-- Add the parameters for the stored procedure here
@InsertDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO tblNumberCounter (InsertDate) Values (@InsertDate);Select IDENT_CURRENT('tblNumberCounter')
END

Public Sub SubmitAdd_Click(ByVal Sender As System.Object, ByVal E As System.EventArgs)

Dim Con As SqlConnection
Dim StrInsert As String
Dim cmdInsert As SqlCommand
Dim myNewReceiptNumber As Integer
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

'Add row to receipt table, then get it for ReceiptNumberText field.
cmdInsert = New SqlCommand
cmdInsert.CommandText = "NumberCounter"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)

cmdInsert.Parameters.AddWithValue("@InsertDate", System.DateTime.Today.ToShortDateString())
Try
Con.Open()
myNewReceiptNumber = cmdInsert.ExecuteScalar()
'Response.Write(myNewReceiptNumber)
Catch objException As SqlException
Dim objError As SqlError
For Each objError In objException.Errors
Response.Write(objError.Message)
Next
Finally
Con.Close()
End Try

End Sub 

View 6 Replies View Related

Why Am I Not Getting My OUTPUT Parameter

Jan 7, 2008

Can anyone see in this stored procedure code and my post sub why the stored procedure is not getting the @ReceiptNumber?
@ReceiptNumber int OUTPUTASBEGININSERT INTO tblNumberCounter (InsertDate)Values (GETDATE())SET @ReceiptNumber=SCOPE_IDENTITY()INSERT INTO tblReceipts (pl_ID,client_ID,PaymentDate,Fund_ID,TenderTypeID,AmountPaid,ReceiptNumber,DateEntered,EnteredBy) SELECT     PL.Pl_ID, RS.client_id, PL.pmtDate, RS.rec_fund_id, RS.rec_tendertypeid, RS.rec_amount,                       @ReceiptNumber, RS.DateEntered, RS.EnteredByFROM         tblRec_setup AS RS INNER JOIN                      tblPayments AS PL ON RS.rec_id = PL.rec_id    Sub Post()        Dim cmdInsert1 As SqlCommand        Dim tr As SqlTransaction = Nothing        Dim ConnectStr As String = _        ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString        Dim conn As New SqlConnection(ConnectStr)        cmdInsert1 = New SqlCommand        cmdInsert1.CommandType = CommandType.StoredProcedure        cmdInsert1.CommandText = "BatchMonthly"        'Get a new receipt number and add it.        Dim InsertedIntegerRN As New SqlParameter("@ReceiptNumber", SqlDbType.Int)        InsertedIntegerRN.Direction = ParameterDirection.Output        cmdInsert1.Parameters.Add(InsertedIntegerRN)        'Try        conn.Open()        tr = conn.BeginTransaction()        cmdInsert1.Transaction = tr        cmdInsert1.Connection = conn        cmdInsert1.ExecuteScalar()         tr.Commit()        'Catch objException As SqlException        tr.Rollback()        'Dim ex As SqlError        'Response.Write(ex.Message)        'Finally        conn.Close()        'End Try    End Sub 

View 7 Replies View Related

Can This Be Done With An Output Parameter?

Nov 4, 2003

Hi I want to make a Function in my User class that adds new members into the db. What I want to do is add the users details in using a stored procedure and input parameters and then return a parameter indicating their userid value and set it to a variable.

My userid column in the my db is auto incrementing with a seed of 1 and a step value of 1.

How could I create an output parameter that would return their new user id and then how would i set it to an integer variable.

Thanks

View 6 Replies View Related

Output Parameter

Jun 21, 2004

I keep getting an error stating my stored proc expects "@ret_value" and it's not declared. Even once it is declared, I'm not sure if my proc is going to return the value properly...been working on this half a day.


params put on new line for readability...

Private Sub update_DB()
Dim intResult, ret_value As Integer
ret_value = 0
intResult = SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("connString"), CommandType.StoredProcedure, "Media_Tracking_add_MarketingID",
New System.Data.SqlClient.SqlParameter("@strMarketingID", txtMarketingID.Text),
New System.Data.SqlClient.SqlParameter("@strAdCampaignName", txtAdCampaignName.Text),
New System.Data.SqlClient.SqlParameter("@strCompany", ddlCompany.SelectedItem.Value),
New System.Data.SqlClient.SqlParameter("@strCampaignType", ddlCampaignType.SelectedItem.Value),

New System.Data.SqlClient.SqlParameter("@ret_value", ret_value, ParameterDirection.Output))


lblSuccess.Text = "RETURN = " & ret_value
End Sub


S Proc

CREATE PROCEDURE [dbo].[media_tracking_add_MarketingID]
@strMarketingID nvarchar(50),
@strAdCampaignName nvarchar(50),
@strCompany nvarchar(3),
@strCampaignType nvarchar(50),
@ret_value int OUTPUT
AS
INSERT INTO media_tracking_Marketing_IDs(MarketingID,AdCampaignName,company,status,CampaignType)
values (@strMarketingID,@strAdCampaignName,@strCompany,'1',@strCampaignType);
set @ret_value = scope_identity();
return @ret_value;
GO

View 4 Replies View Related

Output Parameter

Nov 17, 2005

Hello!Can anybody tell me how can I retreive the value of an output parameter from an stored procedure using an SqlDataSource control?Thank you

View 1 Replies View Related

Parameter Output

Apr 28, 2008

Can any body help me
CREATE PROCEDURE getRowCount
@where NVARCHAR(500)
,@totalRows INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL = ' SELECT ' + cast(@totalRows as NVARCHAR) + ' = COUNT(*)
FROM Employee E'

IF LEN(@where ) > 0
BEGIN
SET @SQL= @SQL + @Where
END

EXEC (@SQL)
END

It doesn't return a totalRows value.
Thanks in advance.

View 9 Replies View Related

How Do I Run This SP To Get Output Parameter?

Dec 7, 2007

I've tried using:
Declare @answer bit
Execute procUserVer username answer output

Create PROCEDURE procUserVer
@User char(15),
@Validate bit output
AS
Declare @UserFound int
Select @UserFound = count(*)
From usernames
where networklogin = @user
select @userFound

if @userFound >0
set @validate = 1
else
set @validate = 0

View 6 Replies View Related

Exec Sp With Output Parameter

Feb 28, 2007

I have the following sp:ALTER PROCEDURE myspPrepareTextForHTML @MessageContent nvarchar(1400), @returnval nvarchar(1400) outputASBEGINSET NOCOUNT ON;SET @returnval='something'RETURN @returnvalENDWhen I try this:
EXEC myspPrepareTextForHTML @MessageContent='dfgsdfgdf', @returnval OUTPUT
print @returnval
I get the error:Must declare the scalar variable "@returnval".
How can I get this to work?

View 7 Replies View Related

Need Help Processing SP Output Parameter

May 7, 2007

Stored procedure works:
PROCEDURE dbo.VerifyZipCode@CZip nvarchar(5),@CZipVerified nvarchar(5) outputAS IF (EXISTS(SELECT ZIPCode FROM ZipCensus11202006 WHERE ZIPCode = @CZip)) SET @CZipVerified = 'Yes' ELSE SET @CZipVerified = 'Not Valid Zip'RETURN
 Need help calling and processing sp information:
 
protected void C_Search_btn_Click(object sender, EventArgs e){        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);        SqlCommand cmd = new SqlCommand("VerifyZipCode", con);        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.AddWithValue("@CZip", UserZipCode_tbx.Text);
             . . . how do you set up output parameter, call the SP and capture output. . . ?        if (@CZipVerified == "Not Valid Zip")               {                    TextBox5.Text = "Zip code not valid please re-enter zip";               }                    else               {                    Continue processing page               }
}

View 5 Replies View Related

Output Parameter Not Return The Value

Nov 7, 2007

 In my SP I have an output parameter,
 
the SP work good,
but in the command parameter I alwase get null value/
here is my code:
 
and in the profiler I see that the query work and return the value, here the query that I copy from the profiler:
 thanks! 

View 3 Replies View Related

Output Parameter In SP Not Working?

Feb 28, 2008

Hi everyone,
   This is the sp i have written which works fine in the sql server management studio when i run it but when i call it from the vb.net web application the output parameters are generated as nothing but the sp return the data from the select statement, so here is the sp followed by vb.net code to access the sp. 
This is the Store ProcedureCREATE PROCEDURE [dbo].StaffAuthenticate
@Staff_ID nvarchar(50),
@Password nvarchar(15),
@IsAuthenticated int OUTPUT,
@P_message_code int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
Select * From Staff;
SET @IsAuthenticated = 0;
SET @P_message_code = 100;
RETURN END
GOThis is the VB.NET code.  Dim ConStr As String = GenericDataAccess.GetConnection(IConnectionStrNames.OAAStaffAuthStr)
Dim Conn As SqlConnection = New SqlConnection(ConStr)
Dim Command As SqlCommand = Nothing
Conn.Open()
Command = New SqlCommand()
Command.Connection = Conn
Command.CommandText = "StaffAuthenticate"
Command.CommandType = CommandType.StoredProcedure

Command.Parameters.Add("@Staff_ID", SqlDbType.NVarChar, 50).Value = UserId
Command.Parameters.Add("@Password", SqlDbType.NVarChar, 15).Value = Password
Command.Parameters.Add("@IsAuthenticated", SqlDbType.Int).Direction = ParameterDirection.Output
Command.Parameters.Add("@P_message_code", SqlDbType.Int).Direction = ParameterDirection.Output

Dim myDataReader As SqlDataReader = Command.ExecuteReader()
Dim Res As New StringBuilder

While (myDataReader.Read())
For i As Integer = 0 To myDataReader.FieldCount - 1
Res.Append(myDataReader(i).ToString())
Res.Append(" ; ")
Next
Res.AppendLine()
End While

MsgBox(Command.Parameters("@IsAuthenticated").Value)
MsgBox(Command.Parameters("@P_message_code").Value)
MsgBox(Res.ToString, MsgBoxStyle.Information, "DAL : StaffSqlAuthenticate")  Thanks for all the help, Really could use one. Kabir 
 

View 5 Replies View Related

Output Parameter Question

Mar 25, 2004

Hi,

I have stored some HTML in a table using an attribute defined as a "text" datatype. I use a stored procedure to insert the data and it works fine - I can see the formattede HTML via direct table access uising enterprise manager.

Now I need to call back this data into my vb.net program. So I have created another stored procedure and have tried to out put the field via an output paramater. The output parameter is defined as a text datatype without a field size and the sql assigns the data in a format select @field = dbfield ....

I get a message saying that data of type text cannot be output to the parameter? I will only ever return 1 record and setting up an adapter etc seems overkill. Anyone got a workaround for this?

Thanks,
Rob.

View 2 Replies View Related

How Do I Get A Procedure OUTPUT-parameter...

Apr 25, 2004

In my ASP.NET page I use a stored procedure that have a parameter declared as OUTPUT...
however...I do not know how to get this OUTPUT to be stored in a ASP.NET-variable...

this is the sp:

CREATE PROCEDURE spInsertNews
@uidArticleId uniqueidentifier OUTPUT,
@strHeading nvarchar(300),
@strAbstract nvarchar(600),
@strText nvarchar(4000),
@dtDate datetime,
@dtDateStart datetime,
@dtDateStop datetime,
@strAuthor nvarchar(200),
@strAuthorEmail nvarchar(200),
@strKeywords nvarchar(400)
AS
SET @uidArticleId = newid()
INSERT INTO tblArticles
VALUES(@uidArticleId ,@strHeading,@strAbstract,@strText,@dtDate,@dtDateStart,@dtDateStop,@strAuthor,@strAuthorEmail,@strKeywords)



my asp code is something like this:

...
SqlCommand sqlcmdInsertNewsArticle = new SqlCommand(insertCmd, sqlconCon);

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strHeading", SqlDbType.NVarChar, 300));
sqlcmdInsertNewsArticle.Parameters["@strHeading"].Value = strHeading.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strAbstract", SqlDbType.NVarChar, 600));
sqlcmdInsertNewsArticle.Parameters["@strAbstract"].Value = strAbstract.Text;

sqlcmdInsertNewsArticle.Parameters.Add(new SqlParameter("@strText", SqlDbType.NVarChar, 4000));
sqlcmdInsertNewsArticle.Parameters["@strText"].Value = strText.Text;

...

sqlcmdInsertNewsArticle.Connection.Open();
sqlcmdInsertNewsArticle.ExecuteNonQuery();
sqlcmdInsertNewsArticle.Connection.Close();


How do I do if I want to catch the OUTPUT-parameter (@uidArticleId)?

anyone?

View 2 Replies View Related

Output Parameter Or Return Value

May 2, 2005

I'm having a terrible time getting a return value from my stored procedure. The sp sets the value  and returns it correctly in the query analyzer, but I can't get the calling code - using VB in VS2003 - to read the value.  I've tried putting it into an integer, and using a Return, and also putting it into a string, and using an output parameter.  Either way:  nada.  What am I doing wrong????
Here's the sp:
CREATE PROCEDURE sp_HOUSRoomAudits @Result VarChar(1) OUTPUT, @Dorm VarChar(12), @Room VarChar(5), @StuID VarChar(14)  AS
DECLARE @Capacity IntDECLARE @Assigned Int--DECLARE @Result VarChar(1)DECLARE @BD BIT
SET @BD=(SELECT Boarding FROM HOUS_StudentMaster WHERE StudentID=@StuID)
SET @Capacity=(SELECT [Student Capacity] FROM HOUS_Rooms WHERE Bldg=@Dorm AND [Room #]=@Room)
SET @Assigned=(SELECT COUNT(PSID) FROM HOUS_StudentMaster a JOIN HOUS_Dorms b ON a.Dormatory=b.Dormitory  WHERE b.Bldg=@Dorm AND a.Status='Active'  AND a.Room=@Room)
IF (@BD=1 AND @Room='N/A') OR (@BD=0 AND @Room<>'N/A')
 SET @Result='3'ELSE IF @Assigned<@Capacity   SET @Result='1' ELSE    SET @Result='2'
print @Result--RETURN @ResultGO
Here's the calling code; I'm leaving out all the dim's etc down to the output parameter:
Dim Parm4 As SqlParameter = scmdReturn.Parameters.Add("@Result", SqlDbType.VarChar)
Parm4.Direction = ParameterDirection.Output
Parm4.Value = "0"
Try
scmdReturn.ExecuteNonQuery()
strOut = Parm4.Value
Catch ex As Exception
Me.lblProblem.Text = "Failed to execute command, err = " & ex.Message
Me.lblProblem.Visible = True
Finally
scnnReturn.Close()
End Try
Hoping you can help, and thanks!
Ka
 

View 11 Replies View Related

Output Parameter Poking My Eye Out

Nov 3, 2005

I'm having a blast.I've been trying to get a sproc to work with 3 imputs and 1 output parameter, first I thought it was something wrong with my asp.net (v2.0) code but after some high pursuit code searching I finally tried out the sproc in QA, still didn't work. can you see what's wrong with this sproc?CREATE PROCEDURE gogogod.sp_user_add@username varchar(24),@password varchar(64),@email varchar(64),@errCode varchar(128) OUTPUTAS    IF NOT EXISTS(SELECT username, email FROM tblUser WHERE username = @username OR email = @email )    BEGIN        DECLARE @uid uniqueidentifier        SET @uid = newID()        INSERT INTO tblUser (uid, username, password, email) VALUES (@uid, @username, @password, @email)        SELECT @uid AS uID        SET @errCode = 'user created'    END    ELSE        SET @errcode = 'User already exists'    RETURNfor some reason the sproc asks for a 4th input parameter, I tried just adding " , '' " as a fourth and it 'worked' (i think) I couldn't check if there was an output for the @errcode.any ideas?

View 7 Replies View Related

Output Parameter Problem

Dec 13, 2005

i have the following problem.  I have a sproc that takes 2 input
parameters used for the where clause and outputs another 3 output
parameters to be displayed in my aspx page.  Below is my sproc as
well as my code of how i call the sproc from asp.net2
---------
SPROC
---------
ALTER   Procedure [dbo].[sp_Ret_Users]
@AccountStatus varchar(50),
@LoginName varchar(200),
@Address varchar (50) OUTPUT,
@FName varchar(100) OUTPUT,
@LName varchar (100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

Select
    @Address = address,
    @Fname = fname,
    @Lname = Lname
From tb_Users
Where
    LoginName = @LoginName AND AccountStatus = @AccountStatus
END;
------------------------------------------------------------------------------

Here is my vb code that i use to call from my asp.net2 site
-------------------
        Dim connString As String =
ConfigurationManager.ConnectionStrings("UsersConnectionString").ConnectionString
        Using myConnection As New SqlConnection(connString)
            Dim cmd As New SqlCommand
            Dim Command As SqlCommand = New SqlCommand()

            Command.Connection = myConnection
            Command.CommandText = "sp_Ret_Users"
            Command.CommandType = CommandType.StoredProcedure

            'Input Parameters
           
Command.Parameters.Add(New SqlParameter("@AccountStatus",
Data.SqlDbType.VarChar, 50))
           
Command.Parameters("@AccountStatus").Value =
ddlAccountStatus.SelectedValue
           
Command.Parameters.Add(New SqlParameter("@LoginName",
Data.SqlDbType.VarChar, 200))
           
Command.Parameters("@LoginName").Value = ddlLoginName.SelectedValue

            'Output Parameters
            Dim
AddressParam As New SqlParameter("@Address", SqlDbType.VarChar, 50)
            Dim
FNameParam As New SqlParameter("@FName", SqlDbType.VarChar, 100)
            Dim
LNameParam As New SqlParameter("@LName", SqlDbType.VarChar, 100)

            AddressParam.Direction = ParameterDirection.Output
            FNameParam.Direction = ParameterDirection.Output
            LNameParam.Direction = ParameterDirection.Output

            Command.Parameters.Add(AddressParam)
            Command.Parameters.Add(FNameParam)
            Command.Parameters.Add(LNameParam)

            myConnection.Open()
            Dim reader As SqlDataReader = Command.ExecuteReader()

            Dim AddressOut As String = AddressParam.Value
            Dim FNameOut As String = FNameParam.Value
            Dim LNameOut As String = LNameParam.Value

            myConnection.Close()
        End Using

The last 3 variables AddressOut, FnameOut, LNameOUT do not return any values.

I am sure that my problem is somewhere in my vb code as if i run the sproc in QA it runs ok and outputs the variables.

View 3 Replies View Related

Output Parameter Vs (variable) = ?

Jan 18, 2006

Is there any benefit to using Output parameters:eg.[query set-up and execution goes here]

View 2 Replies View Related

Problem With Output Parameter In SP

Jul 21, 2004

I am having problems returning the value of a parameter I have set in my stored procedure. Basically this is an authentication to check username and password for my login page. However, I am receiving the error:

Procedure 'DBAuthenticate' expects parameter '@@ID', which was not supplied.

This stored procedure is supposed to return a -1 if the username is not found, -2 if the password does not match, or the @ID parameter, which is the user ID, if it is successful. How do i go about fixing this SP so that I am returning this output for @ID?


CREATE PROCEDURE DBAuthenticate

(

@UserName nVarChar (20),
@Password nVarChar (20),
@@ID varchar(4) OUTPUT

)

AS
Declare @ActualPassword nVarchar (20)

Select

@@ID = RegionID,

@ActualPassword =regpassword

From dbo.Regions

Where Region = @Username

If @@ID is not null
Begin
if @Password =@actualpassword

Select @@ID
Else

Select -2
End
Else

Select -1
GO

View 2 Replies View Related

Output Parameter's To ADO Recordset

Jan 8, 2007

Hi guys,
I know this might be the wrong place to post this, but I thought I would try since all of you are very smart here :).
I have a huge problem with an app that I inherited. It is a VB6 app that used RDO.
I am trying to convert it to use ADO, but the problem I am running into is we have allot of stored procedures with OUTPUT parameter's, so instead of the procedure having a SELECT to pass back the value, it is passed back via the OUTPUT parameter.
With RDO this is done easily with just passing back the parameter to a variable.
I am not sure how to do this with ADO. Please shine some light for me.

Here is an example:
RDO code:
Private rqAddRecord As RDO.rdoQuery
Private rs As RDO.rdoResultset
Set rqAddRecord = SQLServerDB.CreateQuery("", MakeSP("sp_UpdCourier", 11))

With rqAddRecord
.rdoParameters(0) = "I"
.rdoParameters(1) = m.nCourierDeliveryID
.rdoParameters(2) = m.dDeliveryDate
.rdoParameters(3) = m.nCourierServiceID
.rdoParameters(4) = m.nCourierID
.rdoParameters(5) = m.sCourierDepartment
.rdoParameters(6) = m.dTimeStart
.rdoParameters(7) = m.dTimeComplete
.rdoParameters(8) = g.sDatabaseUserName
.rdoParameters(9) = m.dInvoiceDate
.rdoParameters(10) = m.sInvoiceNumber

.Execute

m.nCourierDeliveryID = .rdoParameters(1)
End With

Stored Procedure:


CREATE PROCEDURE sp_UpdCourier
(
@ActionCode char(1),-- (I)nsert, (U)pdate, (D)elete
@CourierDeliveryID intOUTPUT,
@DeliveryDate datetime,
@CourierServiceID tinyint,
@CourierID tinyint,
@CourierDepartment char(5),
@TimeStart datetime,
@TimeComplete datetime,
@ChangedUserID char(8),
@InvoiceDate smalldatetime,
@InvoiceNumber char(15)
)
AS

/************************************************** *********************
* Name:sp_UpdCourier
* Author:Markus Waite
* Date: 03/02/04
*-----------------------------------------------------------------------
* Desc:tblCourier Maintenance
*
*-----------------------------------------------------------------------
* $Revision: 4 $
*
************************************************** *********************/

SET NOCOUNT ON

DECLARE@ExistingDeliveryDatedatetime,
@NbrDaysint

IF @ActionCode IN ('I','U')
AND @CourierDepartment = ''
BEGIN
RAISERROR (50075, 16, 1)
RETURN
END

-- Insert
IF @ActionCode = 'I'
BEGIN
-- Validate Existance
IF EXISTS (
SELECT*
FROMtblCourier
WHEREDeliveryDate= @DeliveryDate
ANDCourierServiceID= @CourierServiceID
ANDCourierID= @CourierID
ANDCourierDepartment= @CourierDepartment
ANDTimeStart= @TimeStart
ANDTimeComplete= @TimeComplete )
BEGIN
RAISERROR (50002, 16, 1)
RETURN
END

/*
** Verify if entering another shift that that Times do not overlap
*/
IF EXISTS (
SELECT*
FROMtblCourier
WHEREDeliveryDate= @DeliveryDate
ANDCourierServiceID= @CourierServiceID
ANDCourierID= @CourierID
ANDCourierDepartment= @CourierDepartment
ANDNOT ( (@TimeStart< TimeStart
AND @TimeComplete < TimeStart)
OR(@TimeStart> TimeComplete
AND @TimeComplete > TimeComplete) ) )
BEGIN
RAISERROR (50044, 16, 1)
RETURN
END

BEGIN TRANSACTION

SELECT@CourierDeliveryID = ISNULL(MAX(CourierDeliveryID), 0) + 1
FROMtblCourier (HOLDLOCK)

INSERTtblCourier
(
CourierDeliveryID,
DeliveryDate,
CourierServiceID,
CourierID,
CourierDepartment,
TimeStart,
TimeComplete,
ChangedUserID,
ChangedDate,
InvoiceDate,
InvoiceNumber
)
VALUES
(
@CourierDeliveryID,
@DeliveryDate,
@CourierServiceID,
@CourierID,
@CourierDepartment,
@TimeStart,
@TimeComplete,
@ChangedUserID,
GETDATE(),
@InvoiceDate,
@InvoiceNumber
)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Failed inserting Courier values', 16, 1)
RETURN
END

COMMIT TRANSACTION

--SELECT @CourierDeliveryID

RETURN
END

-- Update
IF @ActionCode = 'U'
BEGIN
/*
** Verify if entering another shift that that Times do not overlap
*/
IF EXISTS (
SELECT*
FROMtblCourier
WHEREDeliveryDate= @DeliveryDate
ANDCourierServiceID= @CourierServiceID
ANDCourierID= @CourierID
ANDCourierDepartment= @CourierDepartment
ANDNOT ( (@TimeStart< TimeStart
AND @TimeComplete < TimeStart)
OR(@TimeStart> TimeComplete
AND @TimeComplete > TimeComplete) )
ANDCourierDeliveryID!= @CourierDeliveryID )
BEGIN
RAISERROR (50044, 16, 1)
RETURN
END

IF EXISTS (
SELECT*
FROMtblCourierDetail d
WHERECourierDeliveryID= @CourierDeliveryID
ANDCONVERT(CHAR,DeliveryTime,14) NOT BETWEEN CONVERT(CHAR,@TimeStart,14) AND CONVERT(CHAR,@TimeComplete,14) )
BEGIN
RAISERROR (50059, 16, 1)
RETURN
END

SELECT@ExistingDeliveryDate = DeliveryDate
FROMtblCourier
WHERECourierDeliveryID= @CourierDeliveryID

IF @@ROWCOUNT <> 1
BEGIN
RAISERROR ('Could not locate current delivery record', 16, 1)
RETURN
END

BEGIN TRANSACTION

UPDATEtblCourier
SETDeliveryDate= @DeliveryDate,
CourierServiceID= @CourierServiceID,
CourierID= @CourierID,
CourierDepartment= @CourierDepartment,
TimeStart= @TimeStart,
TimeComplete= @TimeComplete,
ChangedUserID= @ChangedUserID,
ChangedDate = GETDATE(),
InvoiceDate= @InvoiceDate,
InvoiceNumber = @InvoiceNumber
WHERECourierDeliveryID= @CourierDeliveryID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Failed updating Courier values', 16, 1)
RETURN
END

-- If date changed, then update the Delivery Times
IF CONVERT(CHAR, @DeliveryDate, 101) <> CONVERT(CHAR, @ExistingDeliveryDate, 101)
BEGIN
SELECT@NbrDays = DATEDIFF(dd, @ExistingDeliveryDate, @DeliveryDate)

UPDATEtblCourierDetail
SETDeliveryTime= DATEADD(dd, @NbrDays, DeliveryTime)
WHERECourierDeliveryID= @CourierDeliveryID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Failed updating Courier Detail values', 16, 1)
RETURN
END
END

COMMIT TRANSACTION

--SELECT @CourierDeliveryID

RETURN
END

-- Delete
IF @ActionCode = 'D'
BEGIN
BEGIN TRANSACTION

DELETEtblCourierDetail
WHERECourierDeliveryID= @CourierDeliveryID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Failed deleting Courier Detail values', 16, 1)
RETURN
END

DELETEtblCourier
WHERECourierDeliveryID= @CourierDeliveryID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Failed deleting Courier values', 16, 1)
RETURN
END

COMMIT TRANSACTION

--SELECT @CourierDeliveryID

RETURN
END

-- else

RAISERROR ('Invalid Action Code passed to sp_UpdCourier', 16, 1)
RETURN
GO

View 4 Replies View Related

Want Sp Output Parameter To Be Displayed

Oct 4, 2007

Hi,
I have a sp like this:

Create PROCEDURE sp_child2 @inp int, @out int output
AS
SELECT @out = @inp * 10
GO

In the report screen i want the following:

A text box which will take the @inp(this will come automatically)
When i click the "view report" the output value should be displayed in the textbox i have in the report.

But the problem i am facing is with the above sp is its creating two text boxes automatically asking for @inp and @out.If i dont give anything in @out text box and press the view report an error is thrown asing me to fill the output box.

Isnt it supposed to be a output value?Am i doing something wrong?

please help...

View 1 Replies View Related

Why Is My Output Parameter Not Working?

Jul 23, 2005

Hi,I am currently creating an ASP page that returns a recordset of searchresult based on multiple keywords. The where string is dynamicallybuilt on the server page and that part work quite well. However I wantto also return the number of records in the recordset and I can notmanage to get any output parameter working. Even if I assign SELECT@mycount=100 (or SET @mycount=100) in the SP the only value set inmycount is always NULL. I tested various theories (e.g. early exit,order & naming of parameters etc. but I can not make the SP set theoutput parameters - has it anything to do with the execute?). @mycountalso returns NULL if I test it in SQL QA.What's wrong with this SP (as regards mycount):CREATE PROCEDURE dbo.spFindProducts@mycount integer OUTPUT,@whereString varchar (1000)AS--SET NOCOUNT ON--Set a Default value for the Wherestring which will return all recordsif the Wherestring is blankIF @whereString is NullSELECT @whereString = 'AND TblProduct.ProductID is not null'--Declare a variable to hold the concatenated SQL stringDECLARE @SQL varchar(2500)-- AND (((UPPER([TblProduct].[ProductName] + [ProductGroupCode] +[AttributeValue1] +-- [SearchWords] + [tblSupplier].[SupplierCode] + [SupplierDesc]))Like '%screw%'))SELECT @SQL = 'SELECT TblProduct.ProductID, TblProduct.ProductName,TblProduct.ChapterCode, tblProduct.ProductGroupCode' +' FROM (TblProduct LEFT JOIN TblStockItem ON TblProduct.ProductID =TblStockItem.ProductID) ' +' LEFT JOIN tblSupplier ON TblProduct.SupplierCode =tblSupplier.SupplierCode' +' WHERE 1=1 ' + @whereString +' GROUP BY TblProduct.ProductID, TblProduct.ProductName,TblProduct.ChapterCode, TblProduct.ProductGroupCode'SELECT @mycount = 200; -- testexecute (@SQL);-- next line seems to be ignored ?SELECT @mycount = @@rowcount;GOtiaAxel

View 11 Replies View Related

Getting OUTPUT Parameter In An OLE DB Command

Jul 12, 2007

Is it possible to use stored procedure with output parameter and retrieves the values of that output parameter, in an OLE DB Command within a Data Flow?

What I wanted to do is to get the newly created identity of a row so that I can insert it to the main data set in data flow. I'm not even sure if there is even a much better design to achieve this. I've rummaged the internet but everything I got were all about Execute SQL Task.

View 5 Replies View Related







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