bblbailey3 Posted February 19, 2007 Share Posted February 19, 2007 I'd say I'm rather new at all this. I've grasped quite a bit by reading...and reading...and reading...to be honest this is my third day playing with asp.net/ado.net (my first week at doing anything with web based data access) I have no idea what things mean...I just know how to make them work...most of the time. I have spent a full two days trying to figure out the answer to my question.My question is:How can I run the Insert SQL code IF and only IF the Request.forms("User1") doesn't already exist in the "TABLEName" User Column?The basic idea for the project is that I have a web-based form (Form.aspx) which has a submit button. I click the button submit...it grabs the data and plunks it into the database. Then sends the user to a different page...Basically I want something like this to be included:If request.forms("User1") Exists In TABLEName([user]) then recordexistslbl.text = "The user name already exists"Else continue with the "sql add record through the transfer to new web page" partEnd IfThe "User" is a unique field in my database so it can only return one record...when queried using a WHERE clause...for exampleI mean it was pretty simple in ADO and record sets....I'm sure I've just overlooked something. Again...I'm new so I hope I explained everything clearly...in my non-professional/experienced way.Here is the relevant code so far. (Sqlver is what I planned on using to define an SQL SELECT statement) <%@ Import Namespace="System.Data.OleDb" %><script runat="server">sub submit(sender As Object, e As System.EventArgs)dim dbconn,sqladd,dbcmd,sqlverif Page.IsValid then dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & server.mappath("Someplace.mdb")) dbconn.Open() sqladd="INSERT INTO TABLEName ([user])" sqladd=sqladd & " VALUES " sqladd=sqladd & "('" & Request.Form("User1") & "')" dbcmd=New OleDbCommand(sql,dbconn) dbcmd.ExecuteNonquery() dbconn.close() dbconn=Nothing AccountCreationlbl1.text="" AccountCreationlbl2.text="" server.transfer("AccountCreated.aspx", true) else If City1check.IsValid=False or State1Check.IsValid=False or Zip1check1.IsValid=False or Zip1Check2.IsValid=False then BillAddylbl1.text="<table cellpadding=0 border=1 width=100%><tr>" BillAddylbl2.text="</tr></table>" End If AccountCreationlbl1.text="<font face=arial color=red size=3><center>ALL FIELDS ARE REQUIRED. PLEASE CORRECT THE ERRORS.</center></font>" AccountCreationlbl2.text="<br><font face=arial color=red size=3><center>ALL FIELDS ARE REQUIRED. PLEASE CORRECT THE ERRORS.</center></font>"end ifend sub</script><html> Link to comment Share on other sites More sharing options...
pulpfiction Posted February 19, 2007 Share Posted February 19, 2007 Dim sql As String = "IF Not Exists (SELECT colname FROM tblname WHERE colname='someval')" _ + "BEGIN " _ + "INSERT INTO tblname VALUES ()" _ + "SELECT @@Identity AS ReturnValue " _ + "End " _ + "ELSE " _ + "SELECT -2 AS ReturnValue " 'UserID already in database Dim dbconn = New OleDbConnection("ConnectionString") dbconn.Open() Dim dbcmd = New OleDbCommand(sql, dbconn) Dim i As Integer = dbcmd.ExecuteScalar() If i=-2 then Response.Write("User already in database") elseif i>0 Response.write("User inserted into db") end if dbconn.close() dbconn = Nothing "IF Not Exists.... " works in stored procedures, guess it will work here too.... Link to comment Share on other sites More sharing options...
bblbailey3 Posted February 20, 2007 Author Share Posted February 20, 2007 I've tried the code you provided and it doesn't work out well...I had to tweak it alot till it finally gave an error message about expecting the sql to start with SELECT INSERT DELETE UPDATE. Thanks for the effort.I'm still looking on my end...getting rather frustrated...It seems to be something everyone should want...I mean IF the data already exists in the table don't try to put it in. Link to comment Share on other sites More sharing options...
bblbailey3 Posted February 20, 2007 Author Share Posted February 20, 2007 I finally figured it out. I'll be adapting the code a bit more later...three days of research to find a simple ohhhh so easy answer...I'm dropping the code that works in...I hope someone else finds it useful. It is rather easy. <%@ Import Namespace="System.Data.OleDb" %><script runat="server">sub submit(sender As Object, e As System.EventArgs)dim dbconn,sql,dbcomm,dbreaddbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & server.mappath("[color="#FFA500"]YOURDATABASEPATH[/color]"))dbconn.Open()sql="SELECT [color="#2E8B57"]COLUMN[/color] FROM [color="#4169E1"]TABLE[/color] Where [color="#2E8B57"]COLUMN[/color]='" & [color="#9932CC"]request.form("User1")[/color] & "'"dbcomm=New OleDbCommand(sql,dbconn)dbread=dbcomm.ExecuteScalar()[color="#FF0000"]lbl.text=dbread[/color][color="#8B0000"]<%-- switch out the lbl.text for any kind of test you need...for example:if dbread=homer then...etc...I'm sure I'm the only idiot that takes forever to get it figured out...lol.---%>[/color]dbconn.close()dbconn=Nothingend sub</script><html><body><form runat="server">E-Mail Address*: <asp:TextBox id="User1" runat="server" columns=30 maxlength=30 /><br><br><asp:Button OnClick="submit" Text="Create Account" runat="server"/><BR><BR><BR><asp:Label id="lbl" runat="server"/></form></body></html> Link to comment Share on other sites More sharing options...
bblbailey3 Posted February 20, 2007 Author Share Posted February 20, 2007 Forgot to say thanks.... Funny thing is I ended up starting over on my "test" file...and I created the code that works from scratch...I thought it was interesting how similar it was to yours which I had been trying to work on HOURS EARLIER and couldn't get to go at all. Thanks you for the help "pulpfiction" and anyone else who glanced at it....or wrote one of the pages I've been reading for hours...lolThanks for the help. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now