How to return time & number format that has set in the regional setting using stored procedure.
Following is my sp for getting current date format from Sql Server.
Hi,
I have a date field in my report which takes the current date as the value. My default regional settings is en-US. If I change my regional settings to Germany, on executing the report, I get rsReportParameterTypeMismatch for the Date parameter.
Any help in this regard is welcome.
Thanks.
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
Hello,
I have a problem with date parameters in my Visual Studio reports. I have set parameters €œStart date€? and €œend date€? as €œdate time€? fields, and the report works fine on my computer, but as soon as I put it in report server and start opening from other computers in the costumers place, the date stays right only about half of the times, I gues it depends on regional settings of the computer that opens the report. For example date 02/12/2007 changes to 12/02/2007 that in effect changes the month from February to December, and if the day is 13 or higher I do not get any result at all.
How should I set the date parameters that they could be correctly open and wieved from any computer no matter what regional settings are?
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.
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies View RelatedHi
I have many existing tables within my db with the date format mm/dd/yyyy
Is it possible to run a stored procedure in order to convert the EXISTING records to the date format dd/mm/yyyy?
Thanks
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedI am using SQL 2000 with the Server Enterprise and the Query Analyzer programs. Almost everytime I create a new Stored Procedure, I forget to go into Server Enterprise and grant Execute permissions to my users.
Is there any way in a Stored Procedure to set the permissions when the Procedure is created?
I am running a large insert in a stored procedure, and it is timing out after 30 seconds (which I take to be the default). Can anyone tell me how to change the timeout from inside the stored procedure?
Thanks.
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?
Hello,
I have a stored procedure that prompts the user for a year and
a month. Based on the month selected, I need to determine
the number of days in that month.
I have tried declaring variables to use to calculate number
of days in month and a counter, but they are not
working. When I try to run this it says I have to prompt user for
these as well. How do I declare and set a variable inside
a stored procedure.
Sample of my code is below...
REATE PROCEDURE crm_contact_frequency_report
@TheYear varchar(4),
@TheMonth integer,
@MyCount integer,
@NumDays integer
AS
SELECT
/* EMAILS (B) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode = 1)
) AS Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode = 0)
) AS Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode IS NULL)
) AS Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
) AS Total_All_Emails,
/* PHONE CALLS (C) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode = 1)
) AS Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode = 0)
) AS Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode IS NULL)
) AS Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
) AS Total_All_Calls,
/* FAXES (D) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode = 1)
) AS Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode = 0)
) AS Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode IS NULL)
) AS Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
) AS Total_All_Faxes
FROM CampaignResponse A
GO
Hi,
I am using vs .net 2005, .net frameworks v3.0, vc++ 2005, clr, on vista
I am trying to write a c++ app to call a sql stored procedure.
the following code genrates an error trap and I don't know why:
String ^myConnectionString = "Initial Catalog=ncxSQL;Data
Source=ENVISION-MOD;Integrated Security=SSPI;";
SqlConnection ^myConnection;
myConnection->ConnectionString = myConnectionString;
Any words of wisdom?
Hi guys I know this is a really common question, and I have read loads of replies on it but everything I try does not work. I have written a small stored procedure in SQL server to upload images to a table and return the new ID using scope_identity. I have tested it and it works fine. here it is:******* @siteID numeric(18,0), @imgNum numeric(18,0), @title NVarchar(50), @MIMEtype nchar(10), @imageData varbinary(max)ASBEGINSET NOCOUNT ONdeclare @imageID intINSERT INTO [site_images] ([img_siteID], [img_num], [img_title], [img_MIME], [Img_Data]) VALUES (@siteID, @imgNum, @Title, @MIMEType, @ImageData) SET @imageID = SCOPE_IDENTITY()RETURN @imageIDSET NOCOUNT OFF************If I run this in management studio express it runs fine and returns the ID under 'return value'. The problem I have is trying to actually call that return value in VB. If I try using these lines:Dim returnParam As SqlParameterreturnParam = New SqlParameter("@imageID", SqlDbType.UniqueIdentifier)returnParam.Direction = ParameterDirection.OutputcmdTest.Parameters.Add(returnParam)withcnBKTest.Open()cmdTest.ExecuteNonQuery() imageIDparam = returnParam.value.toStringcnBKTest.Close() I get the error "procedure has too many arguments specified"And if I try to access the return value like this: imageIDparam = cmdTest.Parameters("@return_value").ValueI get the error "@return_value is not contained by this sqlparametercollection" What am I doing wrong? Any help would be greatly appreciated. Robsa
View 3 Replies View RelatedI have written this stored procedure but I get no return value (neither 0 nor 1). What I hope is when the transaction successful, return value 1. If fails, return value 0.1 set @TransactionOk = 0
2
3 BEGIN TRAN
4
5 UPDATE WhiteList_IMEI SET WhiteList_IMEI_Used = 1, Whitelist_IMEI_UsedDate = getdate()
6 WHERE WhiteList_IMEI_Code = @IMEICode_New
7
8 IF @@ERROR <> 0
9 BEGIN
10 ROLLBACK TRAN
11
12 PRINT ('Error. Contact Software Engineer.')
13 RETURN
14 END
15
16 COMMIT TRAN
17 set @TransactionOk = 1
Hi,
I have been trying to this this for quite a while with no joy can someone please tell me the error of my ways. I am trying to add a new record by stored procedure, this I can do, but my problem lies with the returnvalue part of the procedure. I cannot get it to work. When I debug it tells me that the "Specified cast is not valid" see C# code as i comment the line where it errors. I enclose a sample stored procedure and its c# code. Please can someone tell me where I am going wrong? as this is annoying me alot
SQL:create procedure SPUAddVehicleInsert
@VehicleDetailsRegistrationNumber varchar(50),
@VehicleDetailsMake varchar(50),
@VehicleDetailsModel varchar(50),
@NID bigint =null
as
insert into tblvehicledetails
(
VehicleDetailsRegistrationNumber,
VehicleDetailsMake,
VehicleDetailsModel,
)
values
(
@VehicleDetailsRegistrationNumber,
@VehicleDetailsMake,
@VehicleDetailsModel,
);
select @NID = scope_identity();c# code on sqldatasource: protected void dsAddVehicle_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
Int64 VID = (Int64)e.Command.Parameters["NID"].Value; //errors with specified cast is invalid
Response.Redirect("details.aspx?VID=" + VID.ToString());
}
protected void dsAddVehicle_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
SqlParameter p = new SqlParameter("NID", SqlDbType.BigInt);
p.Direction= ParameterDirection.ReturnValue;
e.Command.Parameters.Add(p);
}
sql datasource:<asp:SqlDataSource ID="dsAddVehicle" runat="server" ConnectionString="<%$ ConnectionStrings:National %>"
InsertCommand="SPUAddVehicleInsert" InsertCommandType="StoredProcedure" SelectCommand="SPUAddVehicleSelect"
SelectCommandType="StoredProcedure" OnInserted="dsAddVehicle_Inserted" OnInserting="dsAddVehicle_Inserting">
<InsertParameters>
<asp:Parameter Name="VehicleDetailsRegistrationNumber" Type="String" />
<asp:Parameter Name="VehicleDetailsMake" Type="String" />
<asp:Parameter Name="VehicleDetailsModel" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
I have a stored procedure that returns an integer value, declared as:Public Function MyProc(..) As Int32 . . Return <integer>End FunctionI return an integer value, and can do this and get the value returned from the method, as such:declare @rc intexec @rc = dbo.MyProc <params>select @rcThis returns the value; how do I return the value to code and get the value; I've been debugging and that is my problem, I can't get the value to return.Thanks a lot.
View 2 Replies View RelatedHello all of members, I have written a Stored Procedure.that creates a new account and then returns a value witch displays a result to me.if result is 1 "Username already exists" or 2 "E-Mail already exists".I did it with "Return" instruction.But, I don't know how can I get the returned value in ASP.NET(VB.NET)? Please help me. Thanks in advance
View 3 Replies View RelatedHi all,Is there anyway to get a returned value from a called Stored Procedure from within a piece of SQL? For example, I have the following code...DECLARE @testval AS INTSET @testval = EXEC u_checknew_dwi_limits '163'IF (@testval = 0)BEGIN PRINT '0 Returned'ENDELSEBEGIN PRINT '1 Returned'END...which
as you can see calls a SP called 'u_checknew_dwi_limits'. This SP
(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want to
assign that value to the '@testval' variable (as you can see in my
code) - but Query Analyser is throwing an error at me. Is this the
correct way to do this?ThanksTryst
I have a Stored procedure (sql 2000), that inserts data into a table. Then, I add this, at the end: Return Scope_Identity()
I have the parameters for the sProc defined and added to the Command, but I'm having a really lousy time trying to figure out how to get the return value of the Stored PRocedure. BTW - I'm using OleDB instead of SQL due to using a UDL for the connection string.
I have intReturn defined as an integer
I've tried :Dim retValParam As OleDbParameter = cmd.Parameters.Add("@RETURN_VALUE", OleDbType.Integer)retValParam.Direction = ParameterDirection.ReturnValueintReturn=cmd.Parameters("@RETURN_VALUE").Value
whenever I add this section - I get an error that there are too many arguments for the sProc.
I've tried:intreturn=cmd.ExecuteNonquery - tried adding a DataReader - using ExecuteScalar - I've tried so many things and gotten so many errors - I've forgotten which formations go with which errors.
What is the best way to do this in the code part (VB.Net)?
Thanks ahead of time
I don't know whey this code does not return the values when I run it in sql server 2005 manager.set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_usr_Add]
@FName nvarchar(30),
@LName nvarchar(30),
@UserName nvarchar(20),
@Password nvarchar(20),
@Email nvarchar(50),
@Country nvarchar(2),
@AIM nvarchar(10)
AS
SET NOCOUNT ON
BEGIN
DECLARE @usrID int
DECLARE @usrEmail int
SELECT @usrID = NULL
SELECT @usrEmail = NULL
SELECT @usrID = usrID FROM usr WHERE usrName = @UserName
IF (@usrID IS NOT NULL )
BEGIN
RETURN 1
END
SELECT @usrEmail = usrID FROM usr WHERE usrEmail = @Email
IF (@usrEmail IS NOT NULL)
BEGIN
RETURN 2
END
INSERT INTO usr (usrFName, usrLName, usrName, usrPassword, usrEmail, usrCountry, usrAIM, usrJoinDt)
VALUES (@FName, @LName, @UserName, @Password, @Email, @Country, @AIM, GetDate())
RETURN 0
END
When i run this code i executes fine except when the two conditions become true they do not return thier values, nor does it return 0 when it inserts a row.
Hi
I have the following stored procedure which when exectuted within the Query Analyzer adds a record and returns the @SeqRunNo value. However when I call
it in the from ado the record is added but the value is empty.
I enter the parameter as:
.Parameters.Append .CreateParameter("@SeqRunNo", adVarChar, adParamOutput, 4)
and try and access it as:
SeqRunNo = oCommand.Parameters("@SeqRunNo").Value
Any ideas?
Thanks.
john
CREATE PROCEDURE up_mix_Set_Seq_Run_No
@IID int,
@TrackingID int,
@ADP_Code varchar(6),
@ClientID varchar(20),
@SeqRunNo varchar(4) OUTPUT
AS
DECLARE @Max_Seq_No int,
@Seq_No varchar(4),
@LastUpdate datetime
SELECT @Seq_No = QTR_SEQ_RUN_NO FROM Transmission_Quarter
WHERE IID = @IID
AND Tracking_ID = @TrackingID
AND ADP_Code = @ADP_Code
IF ISNULL(@Seq_No,0) = 0
BEGIN
SET @LastUpdate = GETDATE()
SELECT @Max_Seq_No = CAST(MAX(QTR_SEQ_RUN_NO) AS int)
FROM Transmission_Quarter
WHERE IID = @IID
AND ADP_Code = @ADP_Code
IF ISNULL(@Max_Seq_No,0) = 0
BEGIN
SET @SeqRunNo = '0001'
END
ELSE
BEGIN
IF @Max_Seq_No = 9999
BEGIN
SELECT @SeqRunNo = '0001'
END
ELSE
BEGIN
SELECT @SeqRunNo = REPLICATE('0', 4 - DATALENGTH(CAST(@Max_Seq_No AS VARCHAR))) + CAST((@Max_Seq_No + 1) AS VARCHAR)
END
END
INSERT INTO Transmission_Quarter
(IID, Tracking_ID, QTR_SEQ_RUN_NO, ADP_Code, Last_Updated_By, Last_Updated_Date)
VALUES
(@IID, @TrackingID, @SeqRunNo, @ADP_Code, @ClientID, @LastUpdate)
END
ELSE
BEGIN
SELECT @SeqRunNo = @Seq_No
END
GO
How do I get then return value from an insert stored procedure?
for example:
CREATE PROCEDURE sp_ReceiptsInsertc
@Branch_ID int,
@Source varchar(1),
@BatchNo varchar(8),
@Amount money ,
@Iden int OUTPUT
AS
INSERT INTO Receipts(Branch_ID, Source, BatchNo, Amount)
VALUES (@Branch_ID, @Source, @BatchNo, @Amount)
SELECT @Iden=@@Identity
GO
Thanks
How do I get then return value from an insert stored procedure?
for example:
CREATE PROCEDURE sp_ReceiptsInsertc
@Branch_ID int,
@Source varchar(1),
@BatchNo varchar(8),
@Amount money ,
@Iden int OUTPUT
AS
INSERT INTO Receipts(Branch_ID, Source, BatchNo, Amount)
VALUES (@Branch_ID, @Source, @BatchNo, @Amount)
SELECT @Iden=@@Identity
GO
Thanks
Hello Newsgroup !My Tools are:Windows 2000, VBA(Access 2000) and MS SQL Server 7.0I wrote in an *.adp project (Access 2000) a Stored Procedure "xyz"with parameters a,bIn my VBA Code i wrote:Dim par As New ADODB.ParameterCmd.CommandType = adCmdStoredProcCmd.CommandText = "[prcSucheUNRWIAEStichtag]"Set par = Cmd.CreateParameter("@a", adInteger)Cmd.Parameters.Append parSet par = Cmd.CreateParameter("@b", adVarChar, adParamInput, 5)Cmd.Parameters.Append par........Cmd.Parameters(0) = aCmd.Parameters(1) = bSet rsTemp = Cmd.ExecuteNow my Problem is the following:there is an error in the stored procedure and i want to use something likethis:if @idontknow = '000000000'Beginreturn(1) -- Something <>0endHow can i use this return value in my VBA code ? Maybe i should ask thisquestion in an other Newsgroup. Pleaselet me know in which oneGreetingsFrank
View 2 Replies View RelatedIn one of my SSIS jobs I have an 'Execute SQL Task' which is calling a stored procedure. This SP returns 0 or 1, if the SP returns 1 the package needs to stop executing. Is there a way to get the value that is returned from the SP and either continue or stop the job from continuing to run?
so if value is 0 it goes to the next step, if its 1 it stops running
Greetings All,
I am a newbie to SSIS and need some help. I need to get the return value from a stored procedure into a SSIS variable. I'm assuming I would use an OLE DB Command but I havn't a clue on how to capture the return value. Can someone get me started on how I can do this?
Note, the return value is actually an identity of the inserted value. I need this value in my data flow for further processing.
Thanks is advance!
I am have stored procedure that accepts one parameter and returns a single value
The sp works fine.
My question is how do I set the c# webpage to return the single value.
I need a specific example of how to code.
this is how the code should work.
A blank form is loaded, when the user clicks submit I need the code to pull the Login name from the text box and check to see if it exist. I have the sp working the way I need it to. But I can figure how to pull the single value into a variable in the code behind.
I am using dataset elsewhere on the site, so I created a ds the calls the sp. Can it work this way?
thanks,
Hi AllHere is my SPALTER PROCEDURE dbo.InsertPagerDays @ReportEndDate datetime, @PagerDays int,@UserID varchar(25)ASIF EXISTS(-- you cannot add a pager days more than once per report dateSELECT ReportEndDate, UserId from ReportPagerDays where ReportEndDate = @ReportEndDate and UserId = @UserID)Return 1 elseSET NOCOUNT OFF;INSERT INTO [ReportPagerDays] ([ReportEndDate], [PagerDays], [UserID]) VALUES (@ReportEndDate, @PagerDays, @UserID)RETURNMy Question is, this SP will not let you enter in a value more than once (which is what i want) but how do I write my code to inform the user? Here is my VB code becuase the SP does not error out (becuase it works it acts as if the record updates)How can I catch the Return 1'set parameters for SPDim cmdcommand = New SqlCommand("InsertPagerDays", conn)cmdcommand.commandtype = CommandType.StoredProcedurecmdcommand.parameters.add("@ReportEndDate", rpEndDate)cmdcommand.parameters.add("@PagerDays", PagerDays)cmdcommand.parameters.add("@UserId", strUserName)Try'open connection hereconn.Open()'Execute stored proccmdcommand.ExecuteNonQuery()Catch ex As Exceptionerrstr = ""'An exception occured during processing.'Print message to log file.errstr = "Exception: " & ex.Messagelblstatus.ForeColor = Drawing.Color.Redlblstatus.Text = "Exception: " & ex.Message'MsgBox(errstr, MsgBoxStyle.Information, "Set User Report Dates")FinallyIf errstr = "" Thenlblstatus.ForeColor = Drawing.Color.Whitelblstatus.Text = "Pager Days Successfully Added!"End If'close the connection immediatelyconn.Close()End Try
View 1 Replies View RelatedI have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset. What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath
View 5 Replies View RelatedThis is my SP:SELECT CAST(id AS varchar(10)) + ' - ' + UserName AS 'User List', Scanned, Scripts AS 'Total Scripts', Processed,CAST((Processed/ Scripts)* 100.0 as int) AS 'DONE (%)'FROM tblworkQueueProcessed and Scripts are both DataTypes of intMy DONE(%) column comes back with 0Any idea where I am going wrong?
View 2 Replies View RelatedI'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