SQL Return Parameter

Dec 11, 2007

Hi Guys, I can't for the life of me get the output from the stored procedure into my variable in VB.net.   Can you guys help out, I'm on my third hour.  Trying to learn this peice as I go (output parameters).  I have truncated the code to look at the output parameter, everything works except the VB side of the output parameter, my messagebox keeps giving me a value of zero.  I have even tried  .SQLValue instead of .Value and it didn't work...thoughts????

Thanks in advance!
Tim

SQL:

ALTER PROCEDURE dbo.SDC_RisIncidentInsert
      @SeqInc int output
  
AS
Select @SeqInc = Max(Sequence) + 1 From _Smdba_._Telmaste_
Select @SeqInc

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

VB.NetPrivate Sub InsertIncident()
Dim conn As SqlConnection
Dim comm As SqlCommand
Dim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionString
conn = New SqlConnection(connectionstring)
comm = New SqlCommand("SDC_RisIncidentInsert", conn)
comm.CommandType = System.Data.CommandType.StoredProcedure

comm.Parameters.Add("@SeqInc", SqlDbType.Int)
comm.Parameters("@SeqInc").Direction = ParameterDirection.Output

Dim SeqIncident As Integer = comm.Parameters.Item("@SeqInc").Value
MsgBox(SeqIncident)

Try
  conn.Open()
  comm.ExecuteNonQuery()

Catch ex As Exception
  dberrorlabel.Text = "Could not Insert Incident Record"

Finally
  conn.Close()
  Response.Redirect("/RISDEV/SDC_Incident.aspx?Name=" & SeqIncident & "#Incident")
End Try
End Sub

 

View 2 Replies


ADVERTISEMENT

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

Help With Sp_executesql And Return Parameter

Nov 10, 2003

I am trying to use dynamic sql with a return parameter, but with limited success. I am using WebMatrix, vb.net and MSDE to perform this routine. Can someone please clue me in. I have read two fine articles by <a href='http://www.algonet.se/~sommar/dyn-search.html>Erland Sommarskog</a> on dynamic sql using sp_executesql, as well as the somewhat opaque article by Microsoft (262499) on the subject.

While there may be other ways to accomplish this task, I am interested in making it work with dynamic SQL. In production, there will be over 20 parameters coming from the vb.net to the SQL, being driven from user input. Then those same variables will be used to actually retrieve the records to a datagrid.

So with a tip of the cap to Rod Serling, I submit this small code and SQL for your consideration from my Twilight Zone:

Public Function totalrecordsbysql(list as arraylist) as integer
dim RetVal as new integer
dim querystring as string

Dim cn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("Indiafriend"))
Dim cmd As SqlCommand = New SqlCommand("SimpleDynProfileCount", cn)
cmd.commandtype = commandtype.storedprocedure

dim mydr as SqlDataReader

cmd.Parameters.add("@TotalRecords",SqlDbType.int).direction=ParameterDirection.Output
cmd.Parameters.add("@age",sqldbtype.int).value = 18

cn.Open()

try
mydr=cmd.executereader()
catch e as sqlexception
dim err as sqlerror
dim strErrorString as string

for each err in e.Errors
strErrorString += "SqlError: #" & err.Number.ToString () & vbCRLF + err.Message
trace.write("sqlexception",strErrorString)
Next

finally

RetVal = cmd.parameters("@TotalRecords").value

end try


Return RetVal
cn.close()
End Function

Now here is the stored procedure:

CREATE PROCEDURE SimpleDynProfileCount

@age int,
@TotalRecords int output

AS

Declare @sql nvarchar(4000),
@paramlist nvarchar(4000)

select @sql = 'select @xTotalRecords = count(*) from profile where 1 = 1 '

// RAISERROR(@sql, 16, 1)

IF @age > 0
Select @sql = @sql + ' AND age > @xage '

Select @paramlist = '@xage int, @xTotalRecords int output'

Execute sp_executesql @sql,@paramlist,@age,@xTotalRecords = @TotalRecords output

select @TotalRecords
GO

Please note the commented RAISERROR statement. If I uncomment this statement, I will get a return value of 11 records. If I leave it out, I get zero records.
The data is the database should return 11 records, based on the criteria of age > 11

View 5 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

Return Stored Procedure Parameter

Apr 27, 2007

I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e)
{
/*variables need for the update of the old staff record
and the creation of the new staff record.
*/
BMSUser bmsUser = (BMSUser)Session["bmsUser"];
int staffid = Convert.ToInt32(bmsUser.CmsStaffID);
int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]);
string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text);
int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value);


SqlConnection conn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_BMS_MoveStaffMember";
cmd.Parameters.Add("@OldStaffProfileID", SqlDbType.Int);
cmd.Parameters["@OldStaffProfileID"].Precision = 9;
cmd.Parameters["@OldStaffProfileID"].Scale = 0;
cmd.Parameters["@OldStaffProfileID"].Value = oldStaffProfileID;
cmd.Parameters.Add("@NewManagerProfileID", SqlDbType.Int);
cmd.Parameters["@NewManagerProfileID"].Precision = 9;
cmd.Parameters["@NewManagerProfileID"].Scale = 0;
cmd.Parameters["@NewManagerProfileID"].Value = newMangerProfileID;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int);
cmd.Parameters["@LastModifiedBy"].Precision = 9;
cmd.Parameters["@LastModifiedBy"].Scale = 0;
cmd.Parameters["@LastModifiedBy"].Value = staffid;

SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);

lbl_ERROR.Text = Convert.ToString(sp);

conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
conn.Close();
 My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null.
 Here is the parts of my stored proc returning the value...
DECLARE @OLDManagerReturn numeric(9)
SELECT @OLDManagerReturn =  ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear
RETURN @OLDManagerReturn

View 5 Replies View Related

Execute String With Return Parameter

Jun 26, 2001

I'm trying to execute a parameterized SQL string but need a return parameter (a multiplier) to include in a later SELECT statement.

What i'm looking for is equivalent to this paraphrased statement:
EXEC('SELECT @val = from @column where Value = @Value')

Where I would later use @val something like:
Select Value * @val as Total Value

Is there an easy way to do this..i've read and read to no avail.

TIA,
Charles

View 5 Replies View Related

Transact SQL :: Return Value 1 For Output Parameter

Oct 7, 2015

I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.

Here is my code:
  
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)

[Code] ....

View 4 Replies View Related

Update Failing Due To Carriage Return In Parameter Value

Dec 15, 2006

When I make a call to a stored procedure to update a record, the update does not happen.  No error is thrown, there are just no rows updated.  When I look at SQL Server Profiler, this is what I see:
exec TTN_Update_RecurringIssueVehicle @RecurringIssueVehicleID='00962233-6EC3-42CE-ABBD-1851F1176D63',@RecurringIssueID='66FE821A-9881-4368-B975-5B04975C5E16',@RecurringIssueProblemID='ABED20CD-CB37-4491-903F-553555EEE47C',@MarketID=1,@VehicleNumber=268,@AddedDate=N'12/14/2006 3:30:00 PM',@AddedBy=12,@ResolvedDate=N'12/15/2006 10:19:45 AM',@ResolvedBy=12
 
It seems to be putting a carriage return in the middle of one of my date parameter values, and also seems to be putting those values in double quotes.  If I try to run this in SQL Management Studio, it throws an error.  If I take out the carriage return, and fix the quotes, it succeeds.  Here is my VB.Net code:
 
<DataObjectMethod(DataObjectMethodType.Update)> _
Public Function Update_RecurringIssueVehicle( _
ByVal RecurringIssueVehicleID As Guid, _
ByVal RecurringIssueID As Guid, _
ByVal RecurringIssueProblemID As Guid, _
ByVal MarketID As Integer, _
ByVal VehicleNumber As Integer, _
ByVal AddedDate As DateTime, _
ByVal AddedBy As Integer, _
ByVal ResolvedDate As DateTime, _
ByVal ResolvedBy As Integer) As Integer
dbCmd.CommandText = "TTN_Update_RecurringIssueVehicle"
dbCmd.Parameters.Clear()
dbCmd.Parameters.AddWithValue("@RecurringIssueVehicleID", RecurringIssueVehicleID)
dbCmd.Parameters.AddWithValue("@RecurringIssueID", RecurringIssueID)
If RecurringIssueProblemID <> Guid.Empty Then dbCmd.Parameters.AddWithValue("@RecurringIssueProblemID", RecurringIssueProblemID)
dbCmd.Parameters.AddWithValue("@MarketID", MarketID)
dbCmd.Parameters.AddWithValue("@VehicleNumber", VehicleNumber)
dbCmd.Parameters.AddWithValue("@AddedDate", AddedDate.ToString())
dbCmd.Parameters.AddWithValue("@AddedBy", AddedBy)
If ResolvedDate <> DateTime.MinValue Then dbCmd.Parameters.AddWithValue("@ResolvedDate", ResolvedDate.ToString())
If ResolvedBy <> -1 Then dbCmd.Parameters.AddWithValue("@ResolvedBy", ResolvedBy)
Dim retVal As New SqlParameter("@RetVal", SqlDbType.Int)
retVal.Direction = ParameterDirection.ReturnValue
dbConn.Open()
dbCmd.ExecuteNonQuery()
dbConn.Close()
Return CInt(retVal.Value)
End Function
 
Any ideas are greatly appreciated - thanks

View 4 Replies View Related

Return Output Parameter From Stored Procedure?

Feb 6, 2008

I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable?
Below is the source code that I have:1 ' Insert question_text into Question table
2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 Dim QuestionID As Integer
5
6 ' Set connection to DB
7 cmd.Connection = conn
8
9 cmd.CommandText = "usp_QuestionResponse_ins"
10 cmd.CommandType = Data.CommandType.StoredProcedure
11
12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output
13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID))
14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID))
15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn))
16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp))
17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn))
18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder))
19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3))
20 QuestionID = sp1.Value
21 cmd.Connection.Open()
22 cmd.ExecuteNonQuery()
23
24 conn.Close()
25 cmd.Dispose()
26

Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins
2 (
3 @question_id int = null output,
4 @assessment_id int = null,
5 @domain_id int = null,
6 @question_text_en nvarchar(1000) = null,
7 @question_text_sp nvarchar(1000) = null,
8 @category_en nvarchar(500) = null,
9 @display_order smallint = null,
10 @question_template_id int = null
11 )
12 As
13 BEGIN
14 DECLARE @Err Int
15
16 DECLARE @QuestionID INT
17 DECLARE @ReturnValue INT
18 SET @ReturnValue = 0
19
20 DECLARE @CategoryId int
21 SELECT @CategoryId = NULL
22
23 -- Check to see if an answer has already been inserted (i.e. revisiting)
24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0)
25 IF ( @CategoryId IS NOT NULL )
26 BEGIN
27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0
28 SET @QuestionID = @ReturnValue
29 END
30
31 IF ( @QuestionID IS NOT NULL )
32 BEGIN
33 DECLARE @question_locale_id int
34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0
35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0
36
37 END
38
39 RETURN @QuestionID
40 End

What am I doing wrong?  How do I properly capture the QuestionID from the SP?  Running the SP from command line works fine.
Thanks

View 2 Replies View Related

Return Resultset As Output Parameter To VB Application

Nov 27, 2014

I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.

I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:

Code:
while (@I <= @count)
begin
BEGIN TRY
-- delete all previous rows inserted in @customerRow for previous counts @I
delete from @customerRow
-- this is inserting the current row that we want to save in database
insert into @customerRow

[Code] .....

This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:

Code:
begin catch
IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE();

[Code] ....

Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.

View 3 Replies View Related

Stored Procedure Multi-value Parameter And Return Value

Feb 12, 2008

What is the best way to design this stored procedure for speed?

I want to pass in a list of IDs and have a return value of a table that contains the ID and its associated value. Is there a way to pass in as table and come out as table?

input parameter
ID
0
1
2

return value
ID, CurrentDue, PastDue
0, 100, 0
1, 100, 100
2, 123, 0

View 11 Replies View Related

How To Return All Records When Date Filter Parameter Is Missing

Mar 28, 2007

I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView.  I want to be able to show all the records, or let the user to select only those records that expire in a certain month.  The expire field is of type date I'm used to all records being returned when a parameter is missing.  If I have Select * from table where last=@last, only the records where the last name is 'Smith' will be returned if @last = 'Smith', but all records are returned is @last = "".  But that's not how it's working with the date. I'm passing an integer from 1 to 12 in a querystring.  I have the equivalent of select * from table where (MONTH([AD ENDS]) = @month)MONTH(datefield) always returns an integer from 1 to 12.  If @month is empty, I want all the records to be displayed, but nothing is.  If @month is an int form 1 to 12, it works fine.  How can I get all the records if no month is selected?  Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not?  Diane 

View 5 Replies View Related

Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures

Oct 30, 2005

Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. 
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _                                    "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function

View 7 Replies View Related

How Do I Return Results Using An Entire Column As Part Of The Search Parameter

Nov 10, 2007

Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.

eg.
CREATE TABLE [dbo].[tPopupKeywords](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]


INSERT INTO dbo.tPopupKeywords(title, description)
SELECT 'check', 'desc' UNION ALL
SELECT 'for', 'desc' UNION ALL
SELECT 'keywords', 'desc'


select dbo.tpopupkeywords.title
where 'This is an example of a passed in string to check if any keywords are returned.'
LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??

expected results.....:

check
keywords

View 1 Replies View Related

ADO - Cannot Access The Return Parameter Of A Stored Procedure On SQL Server 2005

Apr 18, 2007

Hello,



I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.



To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.



try{

pCmd.CreateInstance((__uuidof(Command)));

pCmd->ActiveConnection = m_pConnection;

pCmd->CommandType = adCmdStoredProc;

pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");



............................ code here ........................................



pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("AircraftID"),adChar,adParamInput,7,vAcId));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureAerodome"),adChar,adParamInput,4,vDepAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DestinationAerodome"),adChar,adParamInput,4,vDestAero));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureHour"),adInteger,adParamInput,2,vDepHour));

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("DepartureMin"),adInteger,adParamInput,2,vDepMin));



VARIANT returnVal;

returnVal.vt = VT_I2;

returnVal.intVal = NULL;

pCmd->Parameters->Append(pCmd->CreateParameter(_bstr_t("RETURNVALUE"),adInteger,adParamReturnValue,sizeof(_variant_t),returnVal));

//Get Return value by executing the command

//The return value should be the DB unique ID.



pCmd->Execute(NULL, NULL, adCmdStoredProc);

int uniqueId = returnVal.intVal;

//pRst = pCmd->Execute(NULL, NULL, adCmdStoredProc);

//GetFieldValue(0,pRst,uniqueId);



printf("The DB unique ID is: %i",uniqueId);

return uniqueId;

}



Cheers,

Seth

View 1 Replies View Related

Return An Unique Identifier To An ASP.NET Page To Send It As A Parameter Into Another Stored Procedure

May 1, 2007

Hi !
I have a problem with the unique identifier and  don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
Dim cmd As New SqlCommand
cmd.CommandText = "Insertar_Contacto"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
 Thanks!!
 

View 2 Replies View Related

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

Transact SQL :: Retrieve Return Result With Chronological Order Based On Parameter

Jun 23, 2015

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.

Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?

Information: I'm using SQL server 2012

create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values

[Code] .....

View 2 Replies View Related

Reporting Services :: Modify Report To Return Events In Progress Over Arrival Date Parameter?

Aug 6, 2015

I have a report which queries events occurring during the reporting time frame the user selects.

A user selects arrivalDateStart as 8/1/2015 and arrivalDateEnd as 12/31/2015 and the results are returned.

The issue is that there is an event that began 7/31/2015 and ends 8/2/2015. This does not appear on the report. The user does not want to have to pick an earlier arrival date as the parameter of his report to pick up anything that was already in progress prior to the beginning of the month.

I am trying to figure out how to add an expression that will also return events that are in progress over the arrival date chosen for the report.

View 9 Replies View Related

How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?

Apr 7, 2008

Dear All

I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.

Here is my query sample for yours to understand.



SET NOCOUNT ON

DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)

Set @qstring = 'select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm'

PRINT '-------- ITEM Products Report --------'

DECLARE ITEM_cursor CURSOR FOR

execute (@qstring)

OPEN ITEM_cursor

FETCH NEXT FROM ITEM_cursor

INTO @ITEMCODE

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' '

SELECT @message = '----- Products From ITEM: ' +

@ITEMNAME

PRINT @message

-- Get the next ITEM.

FETCH NEXT FROM ITEM_cursor

INTO @ITEMcode

END

CLOSE ITEM_cursor

DEALLOCATE ITEM_cursor


Why i use @qstring? It is because the query will be changed by different critiera.

Regards
Edmund

View 6 Replies View Related

Return Error Code (return Value) From A Stored Procedure Using A Sql Task

Feb 12, 2008


I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.

I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT)
The sql task rowset is set to NONE. It is a OLEB connection.

I have two parameters mapped:

tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO.
returnvalue output long 1

I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.

The first part of the sp is below and I set the value @i and return.


CREATE procedure [dbo].[Marketing_extract_history_load_TEST]

@table_name varchar(200),

@i int output

as

Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.

View 2 Replies View Related

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

Jan 2, 2006

This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0

    Function get_all_events() As SqlDataReader
        Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
        Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
        parameterState.Value = Request.Params("State")
        myCommand.Parameters.Add(parameterState)

        Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
        parameterPagesize.Value = 20
        myCommand.Parameters.Add(parameterPagesize)

        Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
        parameterPagenum.Value = pn1.SelectedPage
        myCommand.Parameters.Add(parameterPagenum)

        Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
        parameterPageCount.Direction = ParameterDirection.ReturnValue
        myCommand.Parameters.Add(parameterPageCount)

        myConnection.Open()
        'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
        Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

Variable Pages is global integer.

This is what i am calling
        DataList1.DataSource = get_all_events()
        DataList1.DataBind()

How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck

View 3 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

Using Output Clause And Return Timestamp And Return Value

Oct 28, 2006

I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)

I have used the old goto method for trapping errors committing or rolling back the transaction.

Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?

I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).

Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?

So how to return timestamp and a return value and how to check for concurrency all in the try/catch.

by the way I read that you could not return an identity in the output clause but I had no problem.

Thanks for help on this

SM haig

View 5 Replies View Related

Why SP Return SQLException When No Data Return ?

Jul 24, 2006

hi, good day,

i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(

anyone know how to handle the problem when SP return no data ?

thanks in advance

View 1 Replies View Related

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

Aug 19, 2015

I have a stored procedure that selects the unique Name of an item from one table. 

SELECT DISTINCT ChainName from Chains

For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.

SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName

Each row of the result set returned by the stored procedure would contain:

ChainName, Array of StoreNames (or comma separated strings or whatever)

How can I code a stored procedure to do this?

View 17 Replies View Related

RS 2005: Stored Procedure With Parameter It Runs In The Data Tab But The Report Parameter Is Not Passed To It

Feb 19, 2007

Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.

I have to display the result of a parameterized query and I created a SP that based in the parameter does something:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''

AS
BEGIN

declare @slqSelectQuery nvarchar(MAX);

SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end

Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.

Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried

the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!

What is wrong? What I forgot??

Thankx for any help!
Marina B.





View 3 Replies View Related

Reporting Services :: Preview Has One Extra Parameter Than Parameter List - SSRS

Aug 21, 2015

I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter. 

How do I know , when this parameter is coming in the preview ?

View 5 Replies View Related

Reporting Services :: Multi-value Parameter Not Passing In Subreport Parameter List

Jul 29, 2015

I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.

Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..

=join(parameter!user_id!value,",") which pass selected value to chart 

And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .

View 2 Replies View Related

Default Parameter Value Is No Longer Part Of Cascading Parameter In SSRS 2005?

Jan 30, 2007

Hi,

I need "conditional" cascading parameters: In Report Manager when one changes parameter 1, parameter 2 get changed based on parameter 1. Optionally, one can also enter values to parameter 2 directly.

I was able to achieve this in SSRS 2000 (SP2) with the following setups. SSRS 2005 and SP1 no longer works - Parameter 2 always shows its default value regardless whether one select a value in Parameter 1 or not.

Parameter 1
available values: from query
default values: non query (specify a value "<None>")
Parameter 2
available values: Non query (no value specified)
default values: from query (based on Parameter 1)

It seems to me that the default value in SSRS 2000 is considered as cascading parameter. But it is no longer the case in SSRS 2005.

Is this a SSRS 2005 bug? is there any other work arounds or suggestions?

Thanks.

Kong

View 6 Replies View Related

How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters

Oct 15, 2007



Hi,

I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.

My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?



Other question :

Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.


Thanks !

JF

View 7 Replies View Related

Reporting Services :: Share Point List Data Set Parameter Allow Null In Multivalue Drop Down Parameter

Aug 28, 2015

my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you  can not select null in multivalue parameter.How can i do it. i am using share point list.

View 3 Replies View Related

OLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter

Apr 17, 2008

Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?


I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent

View 2 Replies View Related







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