Hi can anyone tell me
How 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 end
my taught was to bind it using lable control. But how the messages can come from storedproc to front end
can we do it using dataset binding.Is there any other way please lemme know immediately .
Thankyou
siri
how can i display the result of an asp:SqlDataSource into a lable control.the sqldatasource returns the count for some thing ie "select count(*) as total from tbl"please help
Hi,What's the 'new' declarative way to fetch one field from a SQL table and display the result in a Label control?I have a users table with a fullname field. I want to query the table and retrieve the fullname where the usertable.username = Session("loggedinuser")I can make the SqlDataSource ok that has the correct Select query, but not sure how to bind the label to that.thanks,Bruce
Hi every experts I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput....), after that, whatever I try, I click on Test Query Button at last part, I always get error message The Query did not return any data table. My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web? Thanks anyone, who can give me any advice. Satoshi
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 All, I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
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.....
Hi all,I would like to do something I thought was simple but I can't seem to figure it out. I would like to bind the Text properties to two labels to the columns in an SqlDataSource, much the same as you can bind a listbox to a SqlDataSource.Is this possible?The reason why I'm trying it this way is because I originally used an OleDB connection and datareader, but it doesn't seem to work on our service providers server (keeps saying that it can't find the database) even though it works on the four other server's I've tried. It definitely connects to the database when I use the SqlDataSource on a listbox control, but it fails when I use the same connection string with the OleDB connection.Is this the best way to go about it, or should I persist with finding the OleDB/datareader (the service provider has been no help at all). Thanks.
My code behind file builds a select statement and I would like to fill an SqlDataSource control with it. Can some show me an example of how I might do that? Maybe something like this? Me.SqlDataSourceSearchResult.ConnectionString = "ConnectStr" Me.SqlDataSourceSearchResult.SelectCommand = "SelectStatement" gvSearchResult.DataSource = Me.SqlDataSourceSearchResult gvSearchResult.DataBind()
I tried to install SQL Server 2K on a W2K server running IIS and SSL. The SQL installation failed until I deleted the certs from my system. Now, the SQL logs say "SuperSocket Info: Bind failed on TCP port 1433". I have tried everything on the MS KB, TechNet, and MSDN sites that I could find. I have also uninstalled IIS and Certificate Services from the server. Since there are no certs left, I suspect that SQL Server is getting in it's own way on startup. Does anyone know of a way to trap/log this kind of conflict?
