Another duplicate data question

[cwolgamott]

Hello. I am trying to check if a record already exists before I insert it into a table in SQL using ASP. Here is the code that I added:

SQL = SQL + " IF EXISTS(SELECT 'True' From department WHERE DeptName = '" & inputDeptName & "')"
SQL = sQL + " BEGIN "
SQL = SQL + "SELECT 'This record already exists' "
SQL = SQL + "END ELSE BEGIN "
SQL = SQL + "SELECT 'Profile Added' "
SQL = SQL + "INSERT INTO department (department.DeptID, department.DeptName, department.Location, department.Communication, department.BestTime, department.WorstTime, department.Strength, department.Weakness, department.Responsibility1, department.Responsibility2, department.Responsibility3, department.Responsibility4, department.Responsibility5, department.Suggestion1, department.Suggestion2, department.Suggestion3, department.Suggestion4, department.Suggestion5) "
SQL = SQL + "VALUES (" + CStr(depID) + ", " + "'" + inputDeptName + "', " + "'" + inputLocation + "', " + "'" + inputCommunication + "', " + "'" + inputBestTime + "', " + "'" + inputWorstTime + "', " + "'" + inputBestThing + "', " + "'" + inputBiggestThing + "'," + "'" + inputResponsibility1 + "', " + "'" + inputResponsibility2 + "', " + "'" + inputResponsibility3 + "', " + "'" + inputResponsibility4 + "', " + "'" + inputResponsibility5 + "', " + "'" + inputSuggestion1 + "', " + "'" + inputSuggestion2 + "', " + "'" + inputSuggestion3 + "', " + "'" + inputSuggestion4 + "', " + "'" + inputSuggestion5 + "')"
SQL = SQL + " END"
Set rs=conn.Execute(SQL)

If rs(0) = "This record already exists" then
errorArea = errorArea + "The profile you entered already exists. Please press the back button and try again."
Else
addArea = addArea + "Your profile has been added."
End If

It will not add a record if one exists, however, it brings back the same message every time I add one, even if it does not add one. It bring back the :

Your profile has been added

message everytime. I would greatly appreciate any suggestions or tips on why it might possibly be doing this. Thank you.

 

[TonyU]

Here's how I've done it using DW4
Hope it helps:


<%
' *** Redirect if username exists
MM_flag=&quot;MM_insert&quot;
If (CStr(Request(MM_flag)) <> &quot;&quot Then
MM_dupKeyRedirect=&quot;foundmember.asp&quot;
MM_rsKeyConnection=MM_postcards_STRING
MM_dupKeyUsernameValue = CStr(Request.Form(&quot;username&quot)
MM_dupKeySQL=&quot;SELECT username FROM Members WHERE username='&quot; & MM_dupKeyUsernameValue & &quot;'&quot;
MM_adodbRecordset=&quot;ADODB.Recordset&quot;
set MM_rsKey=Server.CreateObject(MM_adodbRecordset)
MM_rsKey.ActiveConnection=MM_rsKeyConnection
MM_rsKey.Source=MM_dupKeySQL
MM_rsKey.CursorType=0
MM_rsKey.CursorLocation=2
MM_rsKey.LockType=3
MM_rsKey.Open
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
MM_qsChar = &quot;?&quot;
If (InStr(1,MM_dupKeyRedirect,&quot;?&quot >= 1) Then MM_qsChar = &quot;&&quot;
MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & &quot;requsername=&quot; & MM_dupKeyUsernameValue
Response.Redirect(MM_dupKeyRedirect)
End If
MM_rsKey.Close
End If
%>

[/color]

[tt]&quot;A Successful man is one who can build a firm foundation with the bricks that others throw at him&quot;[/tt]

​

 

[cwolgamott]

Thank you so much for your response. Have you tried this at all with straight ASP instead of using Dreamweaver? If so, could you give me some suggestions or tips. Thank you so much again for your response.

 

[TonyU]

[tt]No I have not, I only use Dreamweaver (I find it to code 90% of my ASP)

I'm sure that if you look closely at the code and ignore the &quot;MM_&quot; you can figure it out. Sorry.

[tt]&quot;A Successful man is one who can build a firm foundation with the bricks that others throw at him&quot;[/tt]

​