If Username Exists, Return Try Again Otherwise Write To Db
Sep 9, 2007
I am a SQL newbie trying to create a registration page. The registration data is stored in "tblUsers," and the username is stored in the field named "UID" -- which is the primary key. If the user enters a username that already exists in the database, I want to return the "That username already exists" message and keep them on the registration page. If the username doesn't exist, I want to write all their registration info to the database.
I tried the code below using both an existing username and then using a username that didn't exist. Both times I got the "that username already exists" error message on the top of the confirmation page, and the record was not written to the database.
I also tried changing the "If objRec.RecordCount <> 0" to "If objRec.RecordCount >0" I got it to write to the database if the username didn't exist, but if the username did exist, I got the microsoft error '80040e14' (The changes you requested to the table were not successful because they would create duplicate values...)
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory]( [username] [varchar](50) NULL, [current_tenure] [int] NULL, [move_in_date] [smalldatetime] NULL, [Cnt_Lead_id] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
I frequently use the "If NOT EXISTS (Select ...) Insert ..." statement and would like to display a message to the user after the execution of the statement to say whether the Insert was or wasn't executed. Is there a way to get SQL to return a value that will tell me if the Insert was carried out or not? If not, perhaps you know of some other efficient way of handling this situation.
Hi all, I am trying to access a sql database and if the userid exists in the database to the let me query another statement within an IF block to check another statement within the data, however I cannot seem to get it to work. I need something like sql.row.count != 0 within the 2nd IF statement below. What can I do?Thanks in advance.Jason using System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnStart_Click(object sender, ImageClickEventArgs e) { if (Session["userid"] == null) { Response.Redirect("accessdenied.aspx"); } else { //Response.Redirect("page1.aspx"); SqlConnection objConnect = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString); objConnect.Open(); SqlCommand cmd = new SqlCommand("SELECT user_id FROM users WHERE user_id = '" + Session["userid"] + "'"); if (cmd == true) { Response.Redirect("page5.aspx"); } objConnect.Close(); } }}
I'm using MSSQL and PHP. I've got the following sql statement:
$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')
The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused: mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not. I tried:
I have a policy table which has policyNumbers and createDate. I need to retrieve all rows where createDate is between 2 dates and there is more than 1 row with the same policy number. I cannot figure out the SQL to return all policy number rows if there are more than 1 row with the policy. Most policies number will be in the table once. I need the others.
Hi, I am trying to retrive some XML from my SQL server 2005 database. The XML is in a table called "myxml" and it is being stored a native XML. The Field type is "XML". The VB code returns nothing when I select SELECT xml. How ever if i SELECT NAME it displays the correct name from the name field. how do I return the XML to be viewed as XML throught the response.write? Thanks! Here is my Table definition:
Column Name Data Type Allow Nulls
Id Int
xml xml Yes
NAME Nvarchar(50) yes My XML that is stored in the XML field: <ROOT> <CHAPTER> <TITLE>This is a test</TITLE> </CHAPTER> </ROOT> Here is my VB code: Dim myconnection As SqlConnection Dim mycommand As SqlCommand myconnection = New SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("myxmlConnectionString2").ConnectionString
Dim strSQL As String = "SELECT [xml] FROM myxml WHERE id = 76"
' create SQL command instance mycommand = New SqlCommand(strSQL, myconnection)
Try ' open database connection myconnection.Open() 'execute T-SQL command Dim dr = mycommand.ExecuteReader() While dr.read() Response.Write("<p>" & dr(0).ToString() & "</p>") End While Response.Write("GOT IT")
Catch ex As Exception Response.Write(ex.Message) Response.Write(strSQL) Response.End()
SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1 UNION SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1 abc-1-200.00 abc-1-500.00
As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as
Now, I want to obtain monthly sales from the data in the two tables by passing in a Year parameter. How to develop such a stored procedure? I have no idea where to get started. I was thinking to call a SELECT statement on each month. But, things trouble me are:
1. how to loop through each month to make a SELECT query for each month for a given Year? Use a cursor or what? 2. how to determine month boundary for a given year and construct the where clause on OrderDate using the month boundary for the SELECT query ? What happens if it is a leap year? 3. how to stop processing for the rest of the year when last order month is done?
I have a SQL database with an Access front end. In the database Ihave a read only and a read write role. When a read only user opensthe database I want all the fields on the form to be locked so thatthe user will not try to change data and get an error from the server.Right now I am doing that with a table. But it's a hassle to have tomaintain a table when if I could answer the question is the currentlylogged in user in the read write role?My server is running SQL Server 2000. So I was wondering if I couldwrite a function to do this? The function would take the role beingchecked as a text parameter and return true if the currently logged inuser is in that role or false if he/she isn't.
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID, dbo.tblProducts.ProductName, dbo.tblProducts.ProductID FROM dbo.tblProducts LEFT OUTER JOIN dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
That's how it looks when you click on the results of a small subset of the query. Just what I want. Unfortunately when you try to right click and save it you get
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1) begin select @errno = @errno | 1 end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE dbo.t1 EXISTS TABLE : nested iteration. Table Scan. Forward scan. Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1 SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01) WHERE f1 = @p1 ) SET ROWCOUNT 0
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 :
A beginner's question I'm sure. When visitors have logged into my site, how do I then use their UserName as a parameter in an SQL WHERE clause so that I can pull up their detailed information. I'm using VB if additional code is required. Thanks in advance.
I built an eommerce site on my local computer at work. It's now time to put the site on our server and get things rolling... anyways, I'm trying to perform an sqldump so I can gather the appropriate text files so I can upload the database. Anyways, the sqldump program I am using requires a username and password to access the sql database.
I am using MSDE and have no clue as to what the username and password may be....
My VisualStudio .Net automatically connects when I work on the site.
Does anyone know what the default username and password may be or what my options are to get this sqldump to work?
Hello out there in SQL land! I'm writing a T-SQL script to create tables and grantlogins to local machine NT accounts. The problem is that I'm sending this script out to about 1700 machines with 1700 different DomainNames! So hard-coding the domainusername is NOT an option. In other words, non-networked computers. In the T-SQL command:
I want to have a variable of some sort pass the DomainName & concatenated username in order to grant login the SQL Server for the NT user. Any help would be greatly appreciated. Thanks!
hey can anyone suggest me how to write the efficient( a bit faster) stored proc to generate alternative usernames( with logical variations like the one of hotmail ) if provided one is already present in database... :)
Here the environment is application in 3 layers. So the user in his client machine, access the application in an application server that sends the request to my sql server database server. My problem is: when I run sp_who from Query Analyzer, I see the host name that is pre-defined inside code application. But, actually, I need to know the user who is requesting the transactio so I can know right the person who is slowing my database performance.... Do you know, any function, procedure or anything else that I can use in the application code in order to provide the user or the host name from the client machine????? I hope some answers...
Currently, I am using a varchar "UserName" as a primary key instead of an Identity or GUID.
System Info: When user's sign up they provide the "UserName" that is used as a PK. Creating a message board type web application. UserNames will never change. UserNames are used extensively as FK's in other Tables. We want to scale well.
Does anyone know the implications? I understand Joins are faster on numerical values than strings but we will not be performing many joins.
In my case is it better to use Identity as a PK or is it better to use a varchar for a "Users" table?
Hi all, I want create a trigger that will log all modification to a table. I need to identify the username that modified the data. Here is my problem : - they are connected to a Terminal Server so stationName il always TSName - They are using an application I cannot modify that always use the same login information using SQL authenticationMode
So I need to be able to get their Windows session Username.
Hi, I hope this is the right place to post this. I am pretty much stuck but think I am doing pretty good so far as I am getting more and more into SQL
using SQL Server 2000 here.
I want to be able to get the last username who replied to a topic. The "DateOfLastReply" works great, it gives me the last date of a reply to a topic so pretty much along with this, I want to get who it was that replied last. Any ideas? so far, I have this but it isnt correct:
(SELECT TOP 1 DateOfReply FROM Replies WHERE Replies.ThreadID = Threads.[ID] ORDER BY DateOfReply DESC) 'LastReplyDate',
(SELECT TOP 1 e.Username FROM Replies, Threads WHERE Replies.UserID = e.[ID] AND Threads.[ID] = Replies.ThreadID AND Threads.ThreadStarterUser = e.[ID] ORDER BY DateOfReply DESC) 'LastUserReplied', --HERE
(SELECT COUNT(ReplyID) FROM Replies WHERE ThreadID = Threads.[ID]) 'NumberOfReplies', e.username 'UsernameCreator' FROM Threads INNER JOIN Users e ON e.[ID] = ThreadStarterUser
VB ASP.NET 2.0 How do I get the Authenticated UserName passed to a Select Command of a data control ? I'm not clear on how to get the logged in username from User.Identity.Name as a string or how to pass it to the Select Command of a control. I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL. I want to show the user data from a database, based on who is logged in . Help, greatly appreciated. Chris
