I am using Asp.net 2.0 and I am trying to retrieve the value from an output parameter in a sql server 2005 stored procedure. The following is my code:
Public Function GetUserProfile(ByVal UserID As String, ByVal Name As String) As String
Dim Value As StringDim ValueParam As SqlParameter = New SqlParameter("@Value", SqlDbType.VarChar, 50, ParameterDirection.Output)Dim command As New SqlCommand("sp_GetUserProfile", con)
If con.State <> ConnectionState.Closed Then con.Close()Return Value
'Catch ex As Exception
'If con.State <> ConnectionState.Closed Then con.Close()
'Return ""
'End Try
End Function
ALTER PROCEDURE dbo.sp_GetUserProfile
(@UserID varchar(36),
@Name varchar(25),
@Value varchar(50) OUTPUT
)
AS
--SET NOCOUNT ON
SELECT @Value = [Value]
FROM UserProfileWHERE [UserID] = @UserID
AND [Name] = @Name
The error I am recieving is : Procedure or function 'sp_GetUserProfile' expects parameter '@Value', which was not supplied. Can someone tell me what I am doing wrong? Thanks!
I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.The problem is that cmd.Parameters["@UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.Here is my code:using (SqlConnection cn = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("mys_ExistsPersonWithUserName", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName; cmd.Parameters.Add("@UserExists", SqlDbType.Int); cmd.Parameters["@UserExists"].Direction = ParameterDirection.Output; cn.Open(); int x = (int)cmd.Parameters["@UserExists"].Value; cn.Close(); return (x>1); } And the corresponding stored procedure: ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName ( @UserName varchar(16), @UserExists int OUTPUT ) AS SET NOCOUNT ON SELECT @UserExists = count(*) FROM mys_Profiles WHERE UserName = @UserName
Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?
Hi, I am designing a simple SSIS package that tests database connectivity. To use the same connection for each database, I am changing the "InitialCatalog" property of the Connection Manager object to test each database in a server.
When the connection fails, I can capture a generic error message from the exception in a Try...Catch block, but it doesn't tell me why a connection attempt failed. I have tried Package Logging as well and that was actually less helpful.
I would have stopped there except the Output Window and Progress/Execution Results tabs BOTH show that the reason for the error was a login failure. I am trying to capture the message that appears in either of these windows as they are the most helpful.
Has anyone been able to programmatically capture these messages?
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.
Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.
Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.
I am learning SSIS, and am trying to save the XML output from a SQL task executing a stored procedure. I need to either save it to a .xml file, then FTP the .xml file to an external server, or do the reverse (FTP, then save file.) I can't get past the SQL task so that it works with the File System Task, or the FTP task. Here is the stored procedure Create Procedure [dbo].[HRGCSpace] (@HRGSpace nvarchar(max) = ''Output) as Begin Set NOCOUNT On set @HRGSpace = (select CustomerNumber 'CustomerNumber/@id',StartDate 'StartDate/@id', EndDate 'EndDate/@id',Action 'Action/@id', WorkZoneNumber 'WorkZoneNumber/@id',BuildingName 'BuildingName/@id',BuildingFloorPlan 'BuildingFloorPlan/@id', FloorName 'FloorName/@id',FloorFloorPlan 'FloorFloorPlan/@id',SpaceName 'SpaceName/@id',VacantUnit 'VacantUnit/@id', SpaceFloorPlan 'SpaceFloorPlan/@id',Address1 'Street/@id',Address2 'Street2/@id',City 'City/@id', StateProv 'StateProv/@id',ZIPPost 'ZipPosCode/@id',MoveOutDate 'MoveOutDate/@id' from HRGCvCorrigoUnits order by BuildingName, SpaceName For XML Path ('Space'), Root('HRGSpaces')) End -------------------------------------- Here is the execute from the SQL Task DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HRGSpace Output
If I define the SSIS variable User::HRGSpaces as string, Direction = Output, Parameter Name = 0, Parameter Size = 0 , then use in the SQLTask General ResultSet = None, Bypass Prepare = True, Connection Type = OLE DB, IsQuery Stored Procedure = False, Parameter Mapping for User::HRGSpaces,Direction = Output, Parameter Name = 0, Parameter Size = 0 the Progress is "Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete Task Execute SQL Task - select the XML Output failed Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
If I change it so that ResultSet = XML, the Progress is " [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".
If I add ResultName = 0, and VariableName= HRGSpaces, I'm back to "Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete Task Execute SQL Task - select the XML Output failed Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property."
Leaving the ResultSet = XML, and changing the SSIS variable as Object, the Progress is " [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".
Changing the ResultSet back to None, but leaving the variable as Object, the SQL task will successfully execute, but the File System Task with IsSourcePathVariable = True and SourceVariable = User::HRGSpaces when executed errors out with "[File System Task] Error: Variable "HRGSpaces" is not a string. Error: There were errors during task validation. Validation is completed. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
So, how to I resolve the Object vs String definition. Any help in getting beyond this is appreciated!
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.
Is there a way to retrieve the parameter list for a given stored procedure?
I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.
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.
I'm creating a report with a Datetime parameter. There's no available values, so the calendar control comes out automatically when I run/preview the report. Now, when I select the date in my calendar control, say for example I select 2/5/07 (2nd of May) and run the report, it gives me data from 5th of Feb. Can anyone help me with this date format problem ???
Not sure if it's related to my problem, I did edit the language tag in the XML source code of my report to have en-AU so that the dates field is showing in the correct dd/mm/yyyy format.
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
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?
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
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
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.
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
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.
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
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?
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 } }
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
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
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?
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...
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
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?
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()
'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)
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?
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))
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
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
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)
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?
