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
)
AS
select MemberPassword from members where membername = @MemberName and memberpassword = @MemberPassword
if @@Rowcount = 0
begin
select BoolLogin = 0
return
end
select BoolLogin=1
/* SET NOCOUNT ON */
RETURN
When 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 String
Dim MemberPassword As String
Dim BoolLogin As Boolean
Dim DBConnection As New Data.SqlClient.SqlConnection(MyCONNECTIONSTRING)
Dim SelectMembers As New Data.SqlClient.SqlCommand("MemberLogin", DBConnection)
SelectMembers.CommandType = Data.CommandType.StoredProcedure
MemberName = txtLogin.Text
MemberPassword = txtPassword.Text
Dim SelectMembersParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameter
'Name
SelectMembersParameter.ParameterName = "@MemberName"
SelectMembersParameter.Value = MemberName
SelectMembers.Parameters.Add(SelectMembersParameter)
'Password
Dim SelectPasswordParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameter
SelectPasswordParameter.ParameterName = "@MemberPassword"
SelectPasswordParameter.Value = MemberPassword
SelectMembers.Parameters.Add(SelectPasswordParameter)
Dim SelectReturnParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameter
SelectReturnParameter.ParameterName = "@BoolLogin"
SelectReturnParameter.Value = BoolLogin
SelectReturnParameter.Direction = Data.ParameterDirection.Output
SelectMembers.Parameters.Add(SelectReturnParameter)
If BoolLogin = False Then
MsgBox("Login Failed")
ElseIf BoolLogin = True Then
MsgBox("Login Successful")
End If
End Sub
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?
Hi! This is what happened to me and my colleagues.We are using SQL 2005. When we were building a view from a table, thefield we selected was not what came out in the output.Let's say you have 2 specific fields in the table, the second one ofthem begins with the letter "L". While your mouse focus is still on thefirst field, and you type "L", the focus will jump to the second field.But now if you left-click it (or press space bar) trying to select it,in the output panel, you will get the first field.I hope I get myself understood. Anyone of you happened to experiencethe same thing? And what's the explanation for this? The thing is itdidn't happen with SQL 2000. And we are suspecting this to be a bug.
I had SQL200 query analyzer loadded on a machine but it crashed.
Culd find CD. We decided to load SQl Server Manager sudio express from a sql 2005 CD. THe problem is that wen running queries with it, it puts some strange characters in the output. They arenon-printable characters so you don't see them
when you edit them in notepad. I have an editor that allows you to edit different
modes. I looked at the file in hex mode and found the characters there at the beginning of the first record. I just want the results in the result pane of the query to save as a regular ASCII text file comma delimited. THis same script was used successfully
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.
Hello, I am currently using vs2005 with sql2000 and have a parameter query getting records form my sql2000 db.
'Load existing data to textboxes for editingDim strSQL1 As String = "SELECT id, LogDate, LogTime, LogDD, StatDD, LogEvent FROM DEMO_DailyOccurrence WHERE completed = 0 AND RecordIdentify = @strOutdate ORDER BY id ASC "Dim scnnNW1 As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("SQLconnection"))Dim scmd1 As New SqlCommand(strSQL1, scnnNW1)With scmd1.Parameters.Add(New SqlParameter("@strOutdate", SqlDbType.DateTime)).Value = strOutdate1End WithDim sda1 As New SqlDataAdapter(scmd1)Dim ds1 As New Data.DataSetTrysda1.Fill(ds1)Catch ex As ExceptionEnd Try
strOutdate1 is a date and has been tested and proven to be OK. When I run the same code against my sql2005 db, the query returns nothing. Do I have to handle something differently for sql2005.
Under sql 2000 I could connect to a server as a speciifc sql user. How can I emulate that under sql 2005 Query Editor. (It doesn't appear to let me define the user to log in as.)
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'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function. This is how I retrieve the longitude: <asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>" SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"> </asp:SqlDataSource> I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this: var map = new GMap2(document.getElementById("map"));var lat = <%=lat%>;var lon = <%=lon%>;var center = new GLatLng(lat,lon);map.setCenter(center, 13); However, lat & long do not contain the retrieved result but rather a useless System.something string. How do I assign the retrieved results to these variables and port them over to Javascript as required? Many thanks!
I have a query which used to run fine on a rubbish SQL 2000 box in about a minute, but with SQL 2005 (SP2) it never finishes, even when left overnight. No errors in the logs or anything. It is the same database which was backed up from SQL 2000 and restored into 2005. Does anybody have any ideas?
Cheers Steve
SELECT DISTINCT R1.RowVersionId, R2.EnumID AS A, R2.EnumID AS B, R4.EnumID AS C, R6.EnumID AS D, R8.EnumID AS E, R10.EnumID AS F, R12.EnumID AS G, R14.EnumID AS H
FROM
RowRuns AS R1
INNER JOIN XRunConfigEnum AS R2 ON R1.RunVersionID = R2.RunVersionId
INNER JOIN RowRuns AS R3 ON R1.RowVersionId=R3.RowVersionId
INNER JOIN XRunConfigEnum AS R4 ON R3.RunVersionID = R4.RunVersionId
INNER JOIN RowRuns AS R5 ON R1.RowVersionId=R5.RowVersionId
INNER JOIN XRunConfigEnum AS R6 ON R5.RunVersionID = R6.RunVersionId
INNER JOIN RowRuns AS R7 ON R1.RowVersionId=R7.RowVersionId
INNER JOIN XRunConfigEnum AS R8 ON R7.RunVersionID = R8.RunVersionId
INNER JOIN RowRuns AS R9 ON R1.RowVersionId=R9.RowVersionId
INNER JOIN XRunConfigEnum AS R10 ON R9.RunVersionID = R10.RunVersionId
INNER JOIN RowRuns AS R11 ON R1.RowVersionId=R11.RowVersionId
INNER JOIN XRunConfigEnum AS R12 ON R11.RunVersionID = R12.RunVersionId
INNER JOIN RowRuns AS R13 ON R1.RowVersionId=R13.RowVersionId
INNER JOIN XRunConfigEnum AS R14 ON R13.RunVersionID = R14.RunVersionId
WHERE
((R2.ParamID='ee72510e-3bab-49f6-1ff9-4d09cbe8670a' AND (R2.EnumID = '1a2868fb-72ef-e1d3-e79d-fbb5814ab481')))
AND
((R4.ParamID='7aadb3a4-3d13-8e0d-bfa4-4243ed1fdb35' AND (R4.EnumID = '745fb00c-0b16-7b4e-bf8f-da0f46777ca0')))
AND
((R6.ParamID='8c9aee3a-df1f-6ec5-131a-8fa0309ce1ff' AND (R6.EnumID = 'c7af1456-56bc-ba9c-f1e4-95cfd5542d10')))
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 just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
After installing sql2005 sp2 a simple select query to a linked server reports the following error message:
Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded. Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.
The linked server is a Visual FoxPro database.
After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.
I am unable to install 32-bit SQL Server Integration Services on the server due to something that was left behind by the 64-bit version.
I've uninstalled SQL Server 2005 64-bit and when I try to install the 32-bit version of Integration Services, I get this error: "Failed to install and configure assemblies C:Program Files (x86)Microsoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087 Error message: Unknown error 0x80131501 Error descrition: FATAL: Could not find component 'Microsoft.SqlServer.Dts.Task.MessageQueueTask.ServCompMQTask' we just installed."
I can't seem to figure out how to resolve this problem with the COM+ and I can't remember if Integration Services is required.
