Need help with error on delete and insert

**HelpR** · 11-01-2006, 03:38 PM

Hi experts.

This is my first post but need extreme help. I am using Oracle 9.2i with classic asp.

I am trying to delete a database table first and then do and insert all in one asp page. I have constructed the syntax and my page has been working with the insert syntax. Once I add the delete syntax I get

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Oracle][ODBC][Ora]ORA-01013: user requested cancel of current operation

Here is what I tried to do to troubleshoot. Unchecked enable query timeout on oracle odbc, added a <% Server.ScriptTimeOut = 900 %> to my page but these did not make any difference.

This is my code to delete

Code:

sSQLDelete = "Delete from LDAP_Users"
objConn.execute sSQLDelete

If I removed or comment out this code my insert works and I do not receive the error.

I also checked the database and the database only has 2 records. Could there be something wrong with my syntax? Could I not use the same connection object to do both a delete and then insert? Is there another method of deleting all records in the database and then doing an insert? Thanks again.

**chrishirst** · 11-01-2006, 04:54 PM

use
TRUNCATE TABLE tablename
it's much faster than a DELETE FROM table

You will probably need to close and then reopen the conn before running the insert this should allow the server to complete the first operation before you start another conflicting query in the same session.

**HelpR** · 11-02-2006, 10:16 AM

ChrisHirst,

Thanks so much for the reply. This works perfectly. I didn't know I could use truncate.

Could I ask another question, if I should have posted this as a new thread my apologies. I will do so at your request.

I am trying to split an extremely long comma delimited string. I'm not sure if it matters but the data is coming from an LDAP server(Active Directory 2000). I am trying to split a string that looks like the following.

Code:

, , , , , , , , , , , , CN=Ron Howard,OU=Users,OU=HQ,DC=xxx,DC=xxx, , , , , , , , , , , , , , , , , , , , CN=John Nestle,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Jack Daniel's,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Selma Hyake,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Angelina O'Neil,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Sam De'Souza,OU=Users,OU=HQ,DC=xxx,DC=xxx, etc etc etc

I need all the names after all instances of CN. So looking at this I would need Ron Howard, John Nestle, Jack Daniel etc etc and then I would need to insert this into a database table first looping through the array as follows. I have successfully split FLNAme, Eaddress arrays and inserted into a table with the following code but have no idea how to split and insert and replace this long string while looping through it's contents.

Code:

For i = 0 to Ubound(strFLName)
 
   sSQL = "INSERT into tablex(FLname, eAddress, Manager)"
   sSQL = sSQL &  "VALUES ('" & strFLName(i) &"', '"& strEAddress(i) &"', '" & strManagerName(i) &"')"
   objConn2.Execute sSQL
 
NEXT

**chrishirst** · 11-02-2006, 05:53 PM

a regular expression will sort that out

Code:

<%
dim objRE
dim Names
dim strName
dim strTest

strTest = ", , , , , , , , , , , , CN=Ron Howard,OU=Users,OU=HQ,DC=xxx,DC=xxx, , , , , , , , , , , , , , , , , , , , CN=John Nestle,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Jack Daniel's,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Selma Hyake,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Angelina O'Neil,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Sam De'Souza,OU=Users,OU=HQ,DC=xxx,DC=xxx,"

set objRE = new RegExp
with objRE
	.IgnoreCase = true
	.Global = true
	.pattern = "CN=(.*?),"
end with
set Names = objRE.execute(strTest)

%>

then process the names any way you need to.

eg: to display them would be;

Code:

<%
For Each strName in Names
  Response.Write strName.Value & "<br/>"
Next
%>

before you store them in the table, you need to run a replace, as each name will still contain the start and finish delimiters ("CN=" & ",") from the original string

**HelpR** · 02:09 PM

This is incredible!!!! But I am still having some issues. Sorry

I have implemented the following code based on what you have given me. Here is my code:

Code:

<%
strManager = ManagerRequest
 
set objRE = new RegExp
with objRE
 .IgnoreCase = true
 .Global = true
 .pattern = "CN=(.*?),"
end with
set Names = objRE.execute(strManager)
%>
 
<%
For Each strName in Names
  strCN= Replace(strName, ("CN="), " ")
  strComma = Replace(strCN, ",", " ")
  strReplaceManager = Replace(strComma, "'", "''")
 
However now I have know idea on how to perform my insert with all my other arrays.  Below is my insert that I originally had.   I used to have a for :
 
'For i = 0 to Ubound(strFullName)
 
however now I have your syntax and have no idea how to combine everything into a successful insert.  Any ideas?
 
Here is my insert statement that I am trying to achieve  

 
 sSQL = "INSERT into TableX(FullName, Email, Manager, Alias, GivenName)"
 sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strManagerName(i) &"', '" & strAliasName(i) & "', '" & strGivenName(i) & "')"
 objConn2.Execute sSQL
  
Next
response.end

Sorry for all the questions but I am still learning asp

Thanks again. Love this forum.

**chrishirst** · 11-03-2006, 03:54 PM

starting at the for ... each construct, You don't need as many variablesin the replace sequence.

For Each strName in Names
strName = replace(strName,"CN=","")
strName = replace(strName,",","")
strName = replace(strName,"'","''")

here you would build your SQL string, no idea how to construct that, simply because I don't know where the other column values come from, but FullName would be strName.
If they are coming from the same LDAP query, it should be possible to run several RegExp on the same data and construct several match collections and use them to concatenate the SQL insert command

**HelpR** · 11-03-2006, 06:19 PM

Well ChrisHirst,

I like to say thank-you very very much for your help. I will be trying what you said possible tommorrow but more likely on Monday. I'm hoping that if I do need your help I can drop another line

For the record my values are coming all from hidden variables that I created for each of my Select fields in my LDAP select query. I.e Fullname get stored in a hidden variable, email get's stored in another variable etc etc. Very straightforward. Then I pass these variables over to another page that does all it's array splitting and replacement for apostrophes in the data so that I can conduct my insert. I thought that would help. Thanks again and hopefully I can apply what you said

Have a good weekend.

**HelpR** · 11-06-2006, 12:20 PM

Well I have been working on this yesterday and today and I am still at a loss. Why is this so hard!!!!! Like I say I am passing hidden variables from my first page to my second page and doing first a replace and then a split as such.(I have not shown the replace variables but they do work i.e FullNameDesc, EmailDesc etc are my replaced variables).

Code:

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strManagerName = Split(ManagerDesc,", ")
strAliasName = Split(AliasDesc,", ")

Then I added the regex expression as such:

Code:

set objRE = new RegExp
with objRE
 .IgnoreCase = true
 .Global = true
 .pattern = "CN=(.*?),"
end with
set Names = objRE.execute(ManagerDesc)

then I thought I could do a replacement on the Names variable in the regex expression as such write below the previous code:

Code:

strName = Replace(Names, ("CN="), " ")
strName = Replace(Names, ",", " ")
strName = Replace(Names, "'", "''")

and then I thought I could insert write after the previous code as such:

Code:

For i = 0 to Ubound(strFullName)
   sSQL = "INSERT into Table (FullName, Email, Manager, Alias)"
   sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strName(i) &"', '" & strAliasName(i)  &"')"
   'objConn2.Execute sSQL
NEXT

However all I keep getting is:

Code:

Error Type:
Microsoft VBScript runtime (0x800A01C2)
Wrong number of arguments or invalid property assignment
/activedirectory/testpass.asp, line 65

I do not need to use the regex expression for any of my other variables except the managerName variable this is why you see only one instance of regex and not multiples like you told me.

Please could you telling me where I am going wrong, possibly modifying my code. Hopefully it can be done or else I will resort to removing the reg ex and insert the who managername string into the database.

Thanks again.

**HelpR** · 11-07-2006, 10:37 AM

Sorry guys just under deadlines. Can anyone take a look at this for me. Thanks very much

**chrishirst** · 11-07-2006, 05:35 PM

Thought I had posted a reply but maybe not.

The replace won't work in the form you have it. Simply because "Names" is a collection not a simple variable. Think of it as an Array.

You are potentially going to have problems because you are splitting several CSV lists into disparate arrays then using the index of only one array to reference them all. This could lead to loss of synchronisation between each of the values when you come to insert them into the DB.

If you are sure the index numbers of the arrays will match, add this nested replace into your sql loop and change strName(i) to strName (or the nested replace).

Code:

  strName = replace(replace(replace(Names(i),"CN=",""),",",""),"'","''")

**HelpR** · 11-07-2006, 07:44 PM

Chris,

Sorry for all the questions. Still a bit confused. What if I am not sure that the index numbers of the arrays will match?

I guess what I'm asking is whether there is a better way to do this? Because if there is I will work non stop to get this done. I would hate to put something into production that may have issues. If you know of a more efficient way without worring whether something will match or not could you please tell me. I thought the best way was to story the results of the LDAP query to hidden variables and then loop through them(well to keep things simple). If you think otherwise please let me know. One thing I am definitely sure about is that you have way more experience then me

Thanks again for all your help