I get a execption when i run my code i dont know how to debug sql statements so ya could any one give me adive heres the code public static int CreateMember(string username, string aspApplicationName) { int returnvalue = 0; DateTime dateCreated = DateTime.Now;
// All users are added to users role upon registration. Roles.AddUserToRole(username, "Users");
} finally { if (command != null) command.Dispose(); if (conn != null) conn.Dispose(); } return returnvalue; } i get a exception at command.ExecuteNonQuery(); and if i dont do int returnvalue = 0; it says i cant use it cause it hasnt be initialized or something like that ALTER PROCEDURE [dbo].[InsertMember] @AspNetUsername nvarchar(256), @AspNetApplicationName nvarchar(256), @DateCreated smalldatetime = getdateASDECLARE @Id int;SET NOCOUNT ON;INSERTINTO [Members] ([AspNetUsername], [AspNetApplicationName],[DateCreated]) VALUES (@AspNetUsername, @AspNetApplicationName,@DateCreated);SET @Id = @@IDENTITYSELECT @Id AS [Id] theres my stored proc any ideas?
I'm using SQL Server 2000 as our back end. I'm finding it bit difficult to write StoredProcs manually to be called from my front end. Is there any good Stored Proc generator tool available?
I have to execute stored procedures containing xp_cmdshell and certain system storedprocedures in msdb and master with a user who is not SA. (i.e iam able to execute stored procedures when i log as sa, but any other user cannot run them)
i have a serach page which have 4 textboxes. passing this textboxes as parameters to storedproc iam searching the value. filling atleast one textbox should fetch the value.
i have stored proc for searching it using normal column values but i want it do using wildcard search also.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[search1] (@val1 varchar(225), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) AS BEGIN
DECLARE @MyTable table (CNo varchar(255))
INSERT @MyTable
Select CNo From customer where ((@val1 IS NULL) or (CNo = @val1)) AND ((@val2 IS NULL) or(LastName = @val2)) AND ((@val3 IS NULL) or(FirstName = @val3)) AND ((@val4 IS NULL) or(PhoneNumber = @val4))
--Now do your two selects
SELECT c.* FROM customer c INNER JOIN @MyTable T ON c.CNo = T.CNo Select r.* From refunds r INNER JOIN @MyTable t ON r.CNo = t.CNo END
I WANT THE SEARCH TO BE DONE FOR WILD CARD CHARACTERS ALSO.
if the user enters lastname s*
using same storedproc can i insert wildcard search.
I am working on the login portion of my app and am using my own setup for the moment so that I can learn more about how things work. I have 1 user setup in the db and am using a stored procedure to do the checking for me, here is the stored procedure code:ALTER PROCEDURE dbo.MemberLogin(@MemberName nchar(20),@MemberPassword nchar(15),@BoolLogin bit OUTPUT)ASselect MemberPassword from members where membername = @MemberName and memberpassword = @MemberPassword if @@Rowcount = 0beginselect BoolLogin = 0returnendselect BoolLogin=1/* SET NOCOUNT ON */ RETURNWhen I run my app, I continue to get login failed but no error messages. Can anybody help? Here is my vb code:Dim MemberName As StringDim MemberPassword As StringDim BoolLogin As BooleanDim DBConnection As New Data.SqlClient.SqlConnection(MyCONNECTIONSTRING)Dim SelectMembers As New Data.SqlClient.SqlCommand("MemberLogin", DBConnection)SelectMembers.CommandType = Data.CommandType.StoredProcedureMemberName = txtLogin.TextMemberPassword = txtPassword.TextDim SelectMembersParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameter'NameSelectMembersParameter.ParameterName = "@MemberName"SelectMembersParameter.Value = MemberNameSelectMembers.Parameters.Add(SelectMembersParameter)'PasswordDim SelectPasswordParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameterSelectPasswordParameter.ParameterName = "@MemberPassword"SelectPasswordParameter.Value = MemberPasswordSelectMembers.Parameters.Add(SelectPasswordParameter)Dim SelectReturnParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameterSelectReturnParameter.ParameterName = "@BoolLogin"SelectReturnParameter.Value = BoolLoginSelectReturnParameter.Direction = Data.ParameterDirection.OutputSelectMembers.Parameters.Add(SelectReturnParameter)If BoolLogin = False ThenMsgBox("Login Failed")ElseIf BoolLogin = True ThenMsgBox("Login Successful")End IfEnd SubThank you!!!
I have three tables that are important here, a 'Plant' table a 'Spindle' table and a 'PlantSpindle' table. The 'PlantSpindle' is comprised of a PlantID and a SpindleID acting as the Primary Key for the table with no other fields.
I have an aspx page that captures the appropriate data to create an entry in the Spindle table. Depending on the user, I will know which plantID they are associated with via a querystring. In my storedproc I insert the data from the webform into the Spindle table but get stuck when I try to also insert the record into the PlantSpindle table with the PlantID I have retrieved via the querystring and the SpindleID of the spindle record the user just created. Basically, I am having trouble retrieving that SpindleID.
Here is what I have in my storedProc (truncated for brevity).
AS SET NOCOUNT ON INSERT INTO Spindle (plantHWG, spindleNumber, spindleDateInstalled, spindleDateRemoved, spindleDurationMonths, spindleBearingDesignNumber, spindleArbor, spindleFrontSealDesign, spindleFrontBearing, spindleRearBearing, spindleRearSealDesign, spindleNotes) VALUES (@plantHWG, @spindleNumber, @spindleDateInstalled, @spindleDateRemoved, @spindleDurationMonths, @spindleBearingDesignNumber, @spindleArbor, @spindleFrontSealDesign, @spindleFrontBearing, @spindleRearBearing, @spindleRearSealDesign, @spindleNotes)
SET @spindleID = (SELECT @@Identity FROM Spindle)
INSERT INTO PlantSpindle (plantID, SpindleID)
VALUES (@plantID, @SpindleID)
I have guessed at a few different solutions but still come up with Procedure 'InsertSpindle' expects parameter '@spindleID', which was not supplied when I execute the procedure.
When I attempt to update using a stored procedure I get the error 'Incorrect syntax near sp_upd_Track_1'. The stored procedure looks like the following when modified in SQLServer: ALTER PROCEDURE [dbo].[sp_upd_CDTrack_1] (@CDTrackName nvarchar(50), @CDArtistKey smallint, @CDTitleKey smallint, @CDTrackKey smallint) AS BEGIN
SET NOCOUNT ON; UPDATE [Demo1].[dbo].[CDTrack] SET [CDTrack].[CDTrackName] = @CDTrackName WHERE [CDTrack].[CDArtistKey] = @CDArtistKey AND [CDTrack].[CDTitleKey] = @CDTitleKey AND [CDTrack].[CDTrackKey] = @CDTrackKey END But when I use the following SQL coded in the gridview updatecommand it works: "UPDATE [Demo1].[dbo].[CDTrack] SET [CDTrack].[CDTrackName] = @CDTrackName WHERE [CDTrack].[CDArtistKey] = @CDArtistKey AND [CDTrack].[CDTitleKey] = @CDTitleKey AND [CDTrack].[CDTrackKey] = @CDTrackKey" Whats the difference? The storedproc executes ok in sql server and I guess that as the SQL version works all of my databinds are correct. Any ideas, thanks, James.
The problem that I'm dealing with is that I can't get recordset from SP, where I first create a temporary table, then fill this table and return recordset from this temporary table. My StoredProcedure looks like:
CREATE PROCEDURE MySP AS CREATE TABLE #TABLE_TEMP ([BLA] [char] (50) NOT NULL) INSERT INTO #TABLE_TEMP SELECT bla FROM …… SELECT * FROM #TABLE_TEMP
When I call this SP from my ASP page, the recordset is CLOSED (!!!!) after I open it using the below statements:
Set rs = Server.CreateObject("ADODB.Recordset") rs.Open "MySP", Conn, , ,adCmdStoredProc
if rs.State = adStateClosed then response.Write "RecordSet is closed !!!! " ‘I ALLWAY GET THIS !!!! else if not(rs.EOF) then rs.MoveFirst while not(rs.EOF) Response.Write rs ("BLA") & " 1 <br>" rs.MoveNext wend end if
end if
Conn.Close
Do you have any idea how to keep this recordset from closing? Thanks Igor
I am testing another similar stored proc and am getting this error:
Server: Msg 8152, Level 16, State 9, Procedure usp_Patient_Info_INSERT, Line 24 String or binary data would be truncated. The statement has been terminated.
(Line 24 performs an insert to a GUID)
Pertient code portions below. Can anybody shed any light. I am essentially doing nearly identical things to another Stored Proc which works just fine.
Code below fails with above error, but is virtually identical in how it treats all GUID fields to another which does work fine.
------------------------------------------------- CREATE PROCEDURE [usp_Patient_Info_INSERT] @PatientGUID varchar(40),--uniqueidentifier, @PersonGUIDvarchar(40),--uniqueidentifier , @CaseNumberdecimal(10,0), << and so forth >>
AS IF @PatientGUID Is Null SET @PatientGUID =cast( (newid()) as varchar(40))
INSERT INTO [Patient_Info] ( PatientGUID, PersonGUID, CaseNumber, << and so forth >>
Values ( cast( @PatientGUID as uniqueidentifier), cast( @PersonGUID as uniqueidentifier), @CaseNumber,
I want to e-mail a user when a Stored Proc fails, what is the best way to do this? I was going to create a DTS package or is this too complicated?
Also, the Stored Proc inserts data from one table to another, I would like to use Transactions so that if this fails it rolls back to where it was, I'm not sure of the best way to go about this. Could anyone possibly point me in the right direction? Here's a copy of some of the stored procedure to give an idea of what I am doing:
-- insert data into proper tables with extract date added INSERT INTO tbl_Surgery SELECT SurgeryKey, GETDATE(), ClinicianCode, StartTime, SessionGroup, [Description], SurgeryName, Deleted, PremisesKey, @practiceCode--SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5) FROM tbl_SurgeryIn
INSERT INTO tbl_SurgerySlot SELECT SurgerySlotKey, GETDATE(), SurgeryKey, Length, Deleted, StartTime, RestrictionDays, Label, IsRestricted, @practiceCode FROM tbl_SurgerySlotIn
INSERT INTO tbl_Appointment SELECT AppointmentKey, GETDATE(), SurgerySlotKey, PatientKey, Cancelled, Continuation, Deleted, Reason, DateMade FROM tbl_AppointmentIn
-- empty input tables DELETE FROM tbl_SurgeryIn DELETE FROM tbl_SurgerySlotIn DELETE FROM tbl_AppointmentIn
I have a search page which contains 4 fields.Giving input to anyone of the field should display the result in Parent Gridview.Parent Gridview has button in it .when i click on the button child Gridview should display related refund details of customer in parent Gridview.
let us think i have two tables like Customer and refunddetails.
Parent Gridview should display Customer details,Child should display corresponding customers refund details.
I need two storedprocs for binding to both Gridviews.
i have first stored proc for Gridview1
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[MyProc] (@val1 varchar(255), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) --@out smallint OUTPUT AS select * from customer where ((@val1 IS NULL) or (name = @val1)) AND ((@val2 IS NULL) or(ssn = @val2)) AND ((@val3 IS NULL) or(accountnumber = @val3)) AND ((@val4 IS NULL) or(phonenumber = @val4))
now i need to capture the @val1 from storedproc1 and using that value retrieve the remaining values in refund table. name is common in both the tables.
i need this because user can search the value using ssn or accountnumber or phonenumber or name.it is not required that user serches using name.Name textbox can be null.
Hi can anyone tell meHow to bind messages in storedproc to lable control in the front end.I have a stored proc which updates the data table.in certain condition update should not take place and a message should be generated that update did not take place.Can anyone tell me how that message can be shown in front endmy taught was to bind it using lable control. But how the messages can come from storedproc to front endcan we do it using dataset binding.Is there any other way please lemme know immediately .Thankyousiri
I've been doing some LOCAL reports on my current application until recently there's has been a case that I really need to do SERVER reports.
Usually when I design my local reports, I create a XSD file, so I usually have one dataset with multiple tables in it. I just pass the dataset to report with a single procedure call that returns multiple result sets or data table.
From what I understood server reports are binded to database objects only, like stored procedures. Now I used the same stored procedure that I used in my local report to my server report. But the thing is only the first result set in the stored procedure is recognized. Are there anyway that I can bind the server report to a single stored procedure that return multiple result sets?
I'm using SQL RS 2005 and have a report where we want the report to run a different stored procedure depending on if a condition is true. I've set my 'command type' to stored proc and can type in the name of a stored procedure. If I type in just one stored procedure's name, it runs fine. But if I try to use a =IIF(check condition, if true run stored proc 1, if false run storedproc 2) then the exclamation (run) button is greyed out. Does anyone know how I can do this? Thanks.
id beg for a hint if our idea of a general dynamic CATCH handler for SPs is possible somehow. We search for a way to dynamically figure out which input parameters where set to which value to be used in a catch block within a SP, so that in an error case we could buld a logging statement that nicely creates a sql statement that executes the SP in the same way it was called in the error case. Problem is that we currently cant do that dynamically.
What we currently do is that after a SP is finished, a piece of C# code scans the SP and adds a general TRY/CATCH bloack around it. This script scans the currently defined input parameters of the SP and generates the logging statement accordingly. This works fine, but the problem is that if the SP is altered the general TRY/CATCH block has to be rebuildt as well, which could lead to inconstencies if not done carefully all the time. As well, if anyone modifies an input param somewhere in the SP we wouldnt get the original value, so to get it right we would have to scan the code and if a input param gets altered within the SP we would have to save it at the very beginning.
So the nicer solution would be if we could sniff the input param values dynamically on run time somehow, but i havent found a hint to do the trick.....
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.
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.
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
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 :
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.
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 ?
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)
I have the following SP SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE spGetResettedPassword@Email nvarchar(50)ASBEGIN SET NOCOUNT ON; declare @newid uniqueidentifier set @newid=newid() declare @newPass nvarchar(10) set @newPass=convert(nvarchar(100),@newid) set @newPass=substring(@newPass,1,7) UPDATE aspnet_Membership SET Password=@newPass WHERE Email=@Email return @newPassENDGO When calling it from code like this: MyConnection.Open() cmd = New SqlCommand("spGetResettedPassword", MyConnection) cmd.CommandType = Data.CommandType.StoredProcedure myTrans = MyConnection.BeginTransaction() cmd.Transaction = myTrans fBeginTransCalled = True bSuccess = False cmd.Parameters.Add(New SqlParameter("@Email", Email)) Dim NewPassword As Object = cmd.ExecuteScalar If NewPassword <> Nothing And CType(NewPassword, String) <> "" _ And TypeOf (NewPassword) Is String Then Return CStr(NewPassword) Else Return "ERROR, CONTACT US ON THE SITE" End If bSuccess = True fBeginTransCalled = False
I get the following error: Conversion failed when converting the nvarchar value '40DC5F3' to data type int. What is happening here?!?!
I am trying to get the following to work, here is the stored procedureSTORED PROCEDURE (@varA varchar(25),@varb varchar(25))AS IF EXISTS (SELECT column1 FROM table1 WHERE nameA=@NameA)RETURN 01IF EXISTS (SELECT column2 FROM table1 WHERE nameB=@nameB)RETURN 02ELSE BEGIN INSERT INTO table1 (nameA, NameB) VALUES (@nameA, @nameB) RETURN 03 END **************************************************************************** dim cn as New sqlConnection(connectionstring) dim cm as sqlcommand cn.Open() With Cm .CommandType = CommandType.StoredProcedure .CommandText = "STOREDPROCEDURE" .Connection = cn End With 'INSERT Names Cm.Parameters.Add("@nameA", SqlDbType.VarChar, 25).Value = nameA.text Cm.Parameters.Add("@nameB", SqlDbType.VarChar, 25).Value = nameB.text param_returnValue = Cm.Parameters.Add("RETURN_VALUE", SqlDbType.Int) param_returnValue.Direction = ParameterDirection.ReturnValue*******************************************************************************************This is where I get stuck. I need to display certain error messages depending on the return value.******************************************************************************************* dim insertStatus as integer insertStatus = Cm.ExecuteScalar() If insertStatus = 01 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 02 Then warningLabel.Text = "NameA AlreadyExists." If insertStatus = 9999 Then Response.Redirect("success.aspx") End If End If End If I am pretty sure the IF statements arent the way to go especially because it is not working but i am curious to how I am to make this work.
whats wrong with this SP? I want @id to contain the row identity of the newly created row as a return value.ALTER PROCEDURE setCountry ( @name varchar( 50 ) = NULL, @alt varchar( 24 ) = NULL, @code varchar( 3 ) = NULL, @id int = null OUT ) AS SET NOCOUNT ON INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @name, @alt, @code ) @id = @@identity RETURN
Quote: CREATE PROCEDURE dbo.usp_ProfQuals_Add_AwardingBody_nr ( @awardbody varchar(100) ) AS BEGIN declare @message int
if EXISTS(SELECT vchr_awardingbody from tbl_ProfQuals_AwardingBody where vchr_awardingbody = @awardbody) set @message = 1 else set @message = 2 insert into tbl_ProfQuals_AwardingBody (vchr_AwardingBody) values (@awardbody)
END return @message
The problem I am having is that it returns the correct value if there is a record that exists or indeed if it doesn't BUT ... even when a record exists it still carries out the insert!
Hi, I want to return the next row in a select ... order by ... cursor. I don't want to loop,just SQL,Do you know any solution? For example: in Oracle we use rownum,is there any equivalent in SQL Server? -Thanks