SEARCH YOUR SOLUTION HERE  

SQL SP Validation

I have project where I am building an ASP.NET Internal Website that will run Updates, Selects, and Inserts on a SQL Server 2005 Database. There will be a series of forms that will call stored procedures rather than have embedded SQL.

I would like to design the SP in such a way that I can do validation like, if the record already exists, do not INSERT and return an error code to the web page. I would also like to have a main Validation SP that all other SPs will call if a particular error occurs. For example, if a record already exists, it will return an error code of 1. OR ... it will call another SP and send that error code of 1 as a variable which will then display a standard message.

Any ideas regarding this and the design of that SP. How would I send the message to the web app Is that a RETURN value

Thank You,
Marc

Posted On: Tuesday 20th of November 2012 12:23:32 AM Total Views:  277
View Complete with Replies




Related Messages:

Login.asp page not responsing.   (85 Views)
Previously login.asp is working fine. The page is not responsing, it seem like hanging long time when i enter username and password. I am using MSSQL server stored procedure. in global.asa Sub Session_OnStart 'Session("ConnectionString") = "Driver={Microsoft Access Driver (*.mdb)};" &_ ' "Dbq=C:\HMS\ScheduleDB.mdb;" & _ ' "Uid=Admin;" & _ ' "Pwd=" Session("ConnectionString") ="Provider=SQLOLEDB;Data Source=Local;" _ & "Initial Catalog=hms;User Id=sa;Password=;" _ & "Connect Timeout=3600;Network Library=dbmssocn;" 'Change Session Timeout to 60 minutes (if you need to) 'Session.Timeout = 0 Session("Start") = Now ' The Root Directory Session("FILE_PATH") = Request.ServerVariables("APPL_PHYSICAL_PATH") Session("CSS_FOLDER") = "stylesheet\" ' Other Include Files are stored in this folder Session("INCLUDE_FOLDER") = "Include\" Session("DEBUG_WIN")="YES" Session("REPORTS")="reports\" ' Increase the active visitors count when we start the session Application.Lock Application("ActiveUsers") = Application("ActiveUsers") + 1 Application.UnLock End Sub how to attach asp file Can anyone advise me
Importing an excel 2007 spreadsheet into SQL Server   (187 Views)
First of all, it is great to be back on here.. been away too long. I have popped on from time to time but with my new job and its security, haven't been allowed to get on here as much. I am trying to import an excel spreadsheet into a database/table. How would I go about doing that. My goal is into get this data into some table and use ASP to source the output in a format for one of my CEOs. The vendor that provides this data has been unsuccessful in doing such thus far. Before converting to them, I had it in excel perfectly but now we are not using my db from then any longer. Help with suggestions or solutions.
sp_OACreate and sp_OAMethed Heelllllpppp   (76 Views)
I have a question and I hoping to not sound to lame. I am trying to use the sp_oacreate and the sp_oamethed to run my dll from sql sever 2005 It looks some thing like this EXEC @hr = sp_OACreate 'ActiveDirectoryCom.Adcom', @object OUT,5 IF @hr 0 BEGIN --Not able to create the object Return @hr END --Not able to create the object EXEC @hr = sp_OAMethod @object, 'AuthenticateUserFullPath', @FunctionReturn OUT, strUser, strPassword, strDoman IF @hr 0 BEGIN --Not able to execute Com function Return @hr END --Not able to execute Com function I have created my dll in .net and placed it in the root drive of the server. How does OACreate know where to locate my dll One executing this fucntion I am returning -2147221005 -214721148 error code. Have I created my dll incorrectly The name of the dll is ActiveDirectoryCom and the Class with in the dll is called Adcom and the function that need to run is called AuthenticateUserFullPath. Have I correctly Written the sp_Oa's have tried to use CRL integration in SQL Server 2005 manage it with CREATE ASSEMBLY statement. However when I try to execute: CREATE ASSEMBLY ActiveDirectoryCom from 'C:\ActiveDirectoryCom\bin\Release\ActiveDirectory Com.dll' WITH PERMISSION_SET = SAFE Msg 6213, Level 16, State 1, Line 6 CREATE ASSEMBLY failed because method "add_LookupUserInfoFromIDCompleted" on type "ActiveDirectoryCom.com.cshPeopleFinder" in safe assembly "ActiveDirectoryCom" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies. cshPeopleFinder is a webreference to a webservice. I know that there are many other ways to do this however It is requested that I keep this functionality runing with this stored procedure and that the dll remain on the the secure server.
connections with asp and sql?   (159 Views)
I have an assignment which consists of allowing users to register to my site, then, let them browse some products which they can add to a shopping cart and "order" them, the products details are stored in a sql database(at my uni). I want to be able to do this at home, instead of at uni, so i need some help on setting up the connections. I would like to be able to either connect to the uni server from home, or set up a db at home and connect to that. I have installed Web Matrix, .net framework 1.1 and 2.0, oracle 8.1.7 and microsoft visual studio.I have no idea how to set up the connections so that i can access a db from the web site. I get this error when i try to run it. Any help would be great. Exception Details: System.Data.OleDb.OleDbException: ORA-12154: TNS:could not resolve service name
Displaying INT   (87 Views)
I have an int field in a mssql database, the values in it are in this format 1234567 how can i make it display in this format 1,234,567
Help with scalar sp   (167 Views)
hi, I want to run a stored proc that updates a table, and then returns the @@rowcount. I want to use the @@rowcount in my program so that I can create a message whether or not a file has been updated. I can't capture the @@rowcount properly in the program - it's always zero. Can you help Here's what I have: Code: ALTER PROCEDURE dbo.SPSubmitTS ( @TSKey iNT , @subDate datetime , @inputDate1 datetime, @inputDate2 datetime, @RECCOUNT INT OUTPUT ) AS BEGIN UPDATE TimeData SET submitted = @subDate where tkey = @tskey AND TDATE>= convert(char(10),@inputDate1, 111) and TDATE 0 return 0 else return 1
connect asp to SQL   (109 Views)
hi , i wanna ask your expertise. Is about SQL Server. Now i am currently coding asp in my local computer using IIS where my project need to retrieve a record from SQL database, i don't know how to connect asp to SQL and how to set up the DSN. I have see some of the code in asp where they write like: Can u solve it,
please help with asp database connection 'login fails'   (201 Views)
I've been trying to suss this for hours now with no luck at all. I'm trying to connect to a sql server database on a win2003 webserver running IIS6 (both IIS and sql server on same machine). I have set up the database and added a user 'CLUSR' with a password, I can log into query analyzer and return the info I want using this username and password. I am consistantly getting the following error trying to connect with asp: - Microsoft OLE DB Provider for SQL Server error '80004005' Cannot open database requested in login 'my_database'. Login fails. /test.asp, line 7 test is a simple asp file I created to try and get this to work and contains the following code: - I've been going around in circles with sql server users, permissions, etc as well as getting completely lost in IIS security settings. If anyone can shed any light on this at all you will gain instant promotion to the top of my christmas card list and save me from near imminent frustration overload! Cheers
Working with more than 1 select in asp.   (163 Views)
Hi : ) [edit] i found some examples in asp forum ^^ gonna try ^^ thanks [/edit] I am starting to learn SQL and every piece of time is a new discovery. I come with this query: Code: Set objRS = objConn.Execute("SELECT TOP 8 * FROM Table, Table2 WHERE Table.idUsu = Table2.idUsu") and after i bring the results using: Its working good, but i am trying to build a more complex query using UNION and COUNT and I cant realize how to... W3Schools is my friend as Google is.. and I am learning a lot... but if someone point me some light... I would like to do more 'SELECTs" in the objRS, so... I am looking for some example... I hava tried a lot of combinations like: Code: Set objRS = objConn.Execute("SELECT TOP 8 * FROM (SELECT * FROM AnotherTable) Table, Table2 WHERE Table.idUsu = Table2.idUsu") I dunno if my sintax or my query is wrong... i am looking for the sintax way... what is the syntax to use more than 1 select in ASP if someone could help me ^^ thanks a lot
Taking Back Up of specific tables in SQL SERVER 2000   (120 Views)
Dear Sir i want to take back up Of Specific Tables of Sql Server 200 Data base... how i can do this.... plz help me i shall be very
Inserting Data Error In Asp Vb Script   (92 Views)
hi i need some help regardin my sql codings. i can add the thread only for the first time thereafter it wld fail and give me this error Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E21) ODBC driver does not support the requested properties. strangely i dun noe y the thread which i add turns up at the very first row of my database..i was thinkin of makin it appear at the last row and not the very first row. can any1 provide me with a solution
Desperate help needed   (176 Views)
, im studing this at university and im stuck on question 2 and 3, was wondering if anybody can help me. Below are the created tables and the questions underneath. Cheers to anyone who can help!! CREATE TABLE CUST (CUSTOMER_ID NUMBER(8) PRIMARY KEY, CUSTOMER_NAME VARCHAR2(60), CUSTOMER_ADDRESS VARCHAR2(100), CUSTOMER_TELEPHONE VARCHAR2(22) ); CREATE TABLE PART (PARTREF VARCHAR2(7) PRIMARY KEY, DESCRIPTION VARCHAR2(100), PRICE NUMBER(7,2), STOCK_QUANTITY NUMBER(2) ); CREATE TABLE PARTUSED (PARTREF VARCHAR2(10) PRIMARY KEY, REQUESTID VARCHAR2(10), QUANTITY_USED NUMBER(10) ); CREATE TABLE REPAIRREQUEST (REQUESTID VARCHAR2(10) PRIMARY KEY, CUSTOMER VARCHAR2(30), CONTRACT_NO VARCHAR2(10), SERIAL_NO VARCHAR2(10), DATE_RAISED DATE, PLANNED_START VARCHAR2(20), PLANNED_END VARCHAR2(20), ENGINEER CHAR(20) ); CREATE TABLE ENGINEER (STAFFNO NUMBER(3) PRIMARY KEY, STAFFNAME VARCHAR2(20), GRADE CHAR(10) ); INSERT INTO CUST(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS,CU STOMER_TELEPHONE) VALUES ('3281','MRS J SMITH','28 SCHOOL LANE DONCASTER S85 3UT','01302 136544') INSERT INTO CUST(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS,CU STOMER_TELEPHONE) VALUES ('4811','EFFICIENT HEATING','17 HIGH STREET ROTHERHAM S60 4ES','01709 654789') INSERT INTO CUST(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS,CU STOMER_TELEPHONE) VALUES ('5977','MR K BROWN','55 THE MOOR SHEFFIELD S1 2SR','01142 625876') INSERT INTO PART(PARTREF,DESCRIPTION,PRICE,STOCK_QUANTITY) VALUES ('127-52','VIGLEN V45-A COOLING FAN','15.50','12') INSERT INTO PART(PARTREF,DESCRIPTION,PRICE,STOCK_QUANTITY) VALUES ('235-005','HP1530 PAPER FEED ROLLER','24.75','9') INSERT INTO PART(PARTREF,DESCRIPTION,PRICE,STOCK_QUANTITY) VALUES ('369-999','MAINS CABLE 1 METRE','3.50','53') INSERT INTO PARTUSED(PARTREF,REQUESTID,QUANTITY_USED) VALUES ('127-52','06/01256','1') INSERT INTO REPAIRREQUEST(REQUESTID,CUSTOMER,CONTRACT_NO,SERIA L_NO,DATE_RAISED,PLANNED_START,PLANNED_END,ENGINEE R) VALUES ('06/01256','MR K BROWN','05-2256/01','JH765H/5','06/FEB/06','17/FEB/06 AM','17/FEB/06 AM','JESS OAKROYD') INSERT INTO REPAIRREQUEST(REQUESTID,CUSTOMER,CONTRACT_NO,SERIA L_NO,DATE_RAISED,PLANNED_START,PLANNED_END,ENGINEE R) VALUES ('06/01258','EFFICIENT HEATING','07-2011/01','1258/K/8','16/FEB/06','17/FEB/06 PM','17/FEB/06 PM','MIKE MENDIT') INSERT INTO ENGINEER(STAFFNO,STAFFNAME,GRADE) VALUES ('179','JESS OAKROYD','ENGINEER') INSERT INTO ENGINEER(STAFFNO,STAFFNAME,GRADE) VALUES ('180','MIKE MENDIT','ENGINEER') 2. The Sales Manager has requested a listing. For those engineers that have carried out repairs, list by Customer the number of Repair Requests they have carried out Ensure you display appropriate columns in a suitable order 3.Write an instruction to produce a listing of Customers, the Repair Requests they have made and the total value of any parts used for each repair request. The list should include the customer's name, repair request id, date raised and value of parts used. Ensure the list is in reverse value of parts used within customer.
Group By display all column   (109 Views)
h ttp://img509.imageshack.us/img509/3043/sql.jpg how display all column info when using group by
mssql linking with asp   (155 Views)
hi people!! can u help me how to link mssql with asp, asp and not asp.net i'm using macromedia dreamweaver to create the webpage, and i really don't know how to link the database of mssql into asp.. can you help me hope you can help me..
Users for specific tables only   (118 Views)
I have a very large database in which they are asking me to allow a new department access to specific tables within this database. They need read/write to about 8 tables but we need to limit it to these tables and keep them out of the others. If I give them read/write to the database they can come and go at will through out the entire database. How do you add users by table rather than by database
Cannot establish a connection to SQL Server 2K sp3a remotely   (213 Views)
Cannot establish a connection to SQL Server 2K sp3a remotely Ok, here's the scenario. 1. Windows Server 2003 Standard running SQL Server 2000 sp3a 2. Windows Server 2003 Standard (where the website sits) 3. This is not a network, the connections are over the Net 4. I have set up SQL Server 2k to allow for TCP/Named Pipes listening. checking the SQL Logs I see the following: (note: I replaced private information with "x's") ------------------------ 2004-03-09 10:39:51.39 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: ) 2004-03-09 10:39:51.39 server Copyright (C) 1988-2002 Microsoft Corporation. 2004-03-09 10:39:51.39 server rights reserved. 2004-03-09 10:39:51.39 server Server Process ID is 3252. 2004-03-09 10:39:51.39 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'. 2004-03-09 10:39:51.39 server SQL Server is starting at priority class 'normal'(1 CPU detected). 2004-03-09 10:39:51.62 server SQL Server configured for thread mode processing. 2004-03-09 10:39:51.62 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks. 2004-03-09 10:39:51.64 server Attempting to initialize Distributed Transaction Coordinator. 2004-03-09 10:39:53.65 spid3 Starting up database 'master'. 2004-03-09 10:39:53.95 server Using 'SSNETLIB.DLL' version '8.0.766'. 2004-03-09 10:39:54.21 server SQL server listening on x.x.x.x: 1433. 2004-03-09 10:39:54.21 server SQL server listening on 127.0.0.1: 1433. 2004-03-09 10:39:54.23 spid5 Starting up database 'model'. 2004-03-09 10:39:54.26 spid3 Server name is 'xxxxxxx'. 2004-03-09 10:39:54.26 spid8 Starting up database 'msdb'. 2004-03-09 10:39:54.26 spid9 Starting up database 'pubs'. 2004-03-09 10:39:54.26 spid10 Starting up database 'Northwind'. 2004-03-09 10:39:54.26 spid11 Starting up database 'xxxxxxxx'. 2004-03-09 10:39:55.12 spid5 Clearing tempdb database. 2004-03-09 10:39:55.23 spid9 Starting up database 'xxxxxxxx'. 2004-03-09 10:39:55.60 spid10 Starting up database 'xxxxxxxx'. 2004-03-09 10:39:56.48 server SQL server listening on TCP, Shared Memory, Named Pipes. 2004-03-09 10:39:56.48 server SQL Server is ready for client connections 2004-03-09 10:39:56.53 spid5 Starting up database 'tempdb'. 2004-03-09 10:39:56.64 spid3 Recovery complete. 2004-03-09 10:39:56.64 spid3 SQL global counter collection task is created. ------------------------ Ok, so I try to connect via ASP, ASP.NET, ODBC, Enterprise Manager all from the Web server. In every attempt, I get access denied messages. On the SQL Server box, I went into a command prompt and ran the following command: ISQL -Stcp:SQLServerNameHere -E -Q"select @@version" This ran fine. Here is the result: ------------- Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copy right (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: ) (1 row affected) ------------- I also ran the following command: netstat -an This ran fine with the following results: (note: I replaced private information with "x's") ----------------- Active Connections Proto Local Address Foreign Address State TCP 0.0.0.0:21 0.0.0.0:0 LISTENING TCP 0.0.0.0:25 0.0.0.0:0 LISTENING TCP 0.0.0.0:80 0.0.0.0:0 LISTENING TCP 0.0.0.0:99 0.0.0.0:0 LISTENING TCP 0.0.0.0:110 0.0.0.0:0 LISTENING TCP 0.0.0.0:135 0.0.0.0:0 LISTENING TCP 0.0.0.0:143 0.0.0.0:0 LISTENING TCP 0.0.0.0:366 0.0.0.0:0 LISTENING TCP 0.0.0.0:445 0.0.0.0:0 LISTENING TCP 0.0.0.0:465 0.0.0.0:0 LISTENING TCP 0.0.0.0:993 0.0.0.0:0 LISTENING TCP 0.0.0.0:995 0.0.0.0:0 LISTENING TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING TCP 0.0.0.0:1101 0.0.0.0:0 LISTENING TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING TCP 0.0.0.0:3389 0.0.0.0:0 LISTENING TCP 0.0.0.0:5222 0.0.0.0:0 LISTENING TCP 0.0.0.0:5223 0.0.0.0:0 LISTENING TCP 0.0.0.0:5269 0.0.0.0:0 LISTENING TCP 0.0.0.0:32000 0.0.0.0:0 LISTENING TCP 0.0.0.0:32001 0.0.0.0:0 LISTENING TCP x.x.x.x:110 66.25.183.245:33080 TIME_WAIT TCP x.x.x.x:110 66.25.183.245:33401 TIME_WAIT TCP x.x.x.x:110 66.25.183.245:33448 TIME_WAIT TCP x.x.x.x:110 66.25.183.245:33555 TIME_WAIT TCP x.x.x.x:110 66.25.183.245:33564 TIME_WAIT TCP x.x.x.x:139 0.0.0.0:0 LISTENING TCP x.x.x.x:1330 x.x.x.x:1433 TIME_WAIT TCP x.x.x.x:3389 66.25.183.245:33260 ESTABLISHED UDP 0.0.0.0:445 *:* UDP 0.0.0.0:500 *:* UDP 0.0.0.0:1106 *:* UDP 0.0.0.0:1107 *:* UDP 0.0.0.0:1258 *:* UDP 0.0.0.0:1259 *:* UDP 0.0.0.0:1434 *:* UDP 0.0.0.0:4500 *:* UDP x.x.x.x:123 *:* UDP x.x.x.x:137 *:* UDP x.x.x.x:138 *:* UDP 127.0.0.1:123 *:* ---------------- The Windows 2003 Firewall is DISABLED for both machines. When I attempt to set up an ODBC connection from the Web Server, I get the following error: ---------------------------- Connection Failed: SQLState: '01000' SQL Server Error: 10051 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()). Connection failed: SQLState: '08001' SQL Server Error: 17 [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied. ---------------------------- Now, I attempted to set up the SAME ODBC connection on the SQL Server box, with the same login credentials (both using SQL Server Authentication), and it worked fine. So, as far as I can tell, the SQL Server box is listening on port 1433. I have all the usernames/passwords set up for each DB. I can set all this up on the local box. The SQL Server used to house the web server as well so there doesn't seem to be any firewall or connection issues going INTO the box (again, Internet Firewall is turned OFF on both machines). I have also run the following command from the command prompt on both machines: ISQL -Stcp:ServerNameHere -Uxxxxxx -Pxxxxxx -Q"select @@version" On the SQL Server Box, I get a good return message, on the Web Server I get the following message: -------------------------- C:\>ISQL -Stcp:66.135.32.79 -Usa -Pxxxxxx -Q"select @@version" DB-Library: Unable to connect: SQL Server is unavailable or does not exist. Una ble to connect: SQL Server does not exist or network access denied. Net-Library error 10051: ConnectionOpen (Connect()). -------------------------- Can anyone help I appreciate your help!
Really basic query speed question..   (200 Views)
. In general, better to have 30 simple queries running to like 6 different tables, or 1 table which could return all results in one query Purely for speed. It doesn't matter if it's less work for me that space is duplicated. What does SQL Server run faster Oh yes, and the tables have about 800 rows.
probelm with asp and sql 2000 server   (152 Views)
hi, help needed!!!! i want to connect SQL with ASP using connection string .how to install sql 2000 server on windows xp professional edition .
Asp - Mysql Problem :(   (100 Views)
Hi all this is my first post, came accross this forum while looking for help topics on google. I have just changed webhosts and ive had to change my Access DB to a MYSQL DB, this all seems to have worked fine on my local testing server, when i upload it i get a problem, i belive its due to an earlyer version of mysql on the new host, please can any body help me get around this for i am a NOOB! Error = Error Type: ADODB.Connection.1 (0x80004005) SQLState: 42000 Native Error Code: 1064 [TCX][MyODBC]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exec getproducts 71' at line 1 /productsnew2.asp, line 172 Browser Type: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1) Page: GET /productsnew2.asp And here is the the part by line 172... (also highlighted line 172) if id 0 then dim lastname,name,content setconnection set rst = server.createobject("adodb.recordset") sql = "SELECT * FROM Types WHERE TypeID = " & id rst.open sql,conn if not rst.eof then descr = rst("Desc") & "" nametype = rst("Type") end if rst.close set rst = nothing set rst = conn.execute("exec getproducts " & id) if not rst.eof then response.write "" if printscreen 1 then response.write "" else response.write "" end if if descr "" then response.write "" & replace(descr,vbnewline,"") & "" else if printscreen = 1 then response.write " " end if lastname = "" dim counter counter = 0 do until rst.eof Please help
special characters within a SQL 7 DB   (104 Views)
Ok.. so you need something in a DB to be... AT&T.... obviously this sometimes cause problems and sometimes not... Do you just replace the & with it's ascii value... & sometimes if i have to pull the db item such as 'AT&T Partner' the page prints 'AT' Or if i need 'Cabinets, KSU's & Something else' ... I get 'Cabinets, KSU' do I have to do a replace in and a replace out the ' i can avoid all together.. but I'm not getting out of AT&T