Active Topics Active Topics  Display List of Forum Members Memberlist  Invert Forum Background Colours Invert Colours  Search The Forum Search  Help Help  Register Register  Login Login
Sponsored by Beatnik Games
Hardware / Software Bits & Bobs
 |SFH| Sent From Hell :: Community Forums : Hardware / Software Bits & Bobs
Subject Topic: SQL Nobs please reply (Topic Closed Topic Closed) Post ReplyPost New Topic
Author
Message << Prev Topic | Next Topic >>
ChiefPFF
Honourary Member
Honourary Member
Avatar
CheesePFF

Joined: 09 September 2002
Location: Scotland
Posts: 567
Posted: 22 April 2005 at 3:35am | IP Logged  
Ok lads, this is very frustrating. I'm trundling along quite happily building this site in .asp and when i try and do something which i thought should be rather simple - I hit a brick f**kin wall.

Ok snippet coming up, but let me tell you what im doing..

I used deamweaver to set up a record insert form - used to create Users for my system. Fine - all works.

I then try and edit it - so that as soon as it creates the record, the DB passes back the new primary key value - which i then use whilst recursively creating Permission Records for several "sites". Making sense? Hope so. :s

heres the snippet..

blah blah - all fine and admin record created by next line
--------------------------------------------------
MM_editCmd.Execute
     
rsNewAdminID = MM_editCmd.Execute("SELECT @@IDENTITY")
     
While (NOT rsShops.EOF)
     
MM_editCmd.Execute("INSERT INTO tblPermInst (AdminUserID, PermShopID) VALUES ('" & rsNewAdminID(0) & "', '" & (rsShops.Fields.Item("ShopID").Value) & "')")
          
rsShops.MoveNext()
          
Wend

-------------------------------------------------------
Error I get is a 3021 if i remember correctly..

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index..."

Which is wierd because the Admin record is created fine - its just the records in the other table that dont go in. And atm those only one bloody shop to recurse through - so how is it going to duplicate!?!

Now cmon lads someone must know? specially you heriot watt boys - i know you did SQL!


Edited by ChiefPFF on 22 April 2005 at 3:48am
Back to Top View ChiefPFF's Profile Search for other posts by ChiefPFF Visit ChiefPFF's Homepage Send Private Message Add to Buddy List
 
ChiefPFF
Honourary Member
Honourary Member
Avatar
CheesePFF

Joined: 09 September 2002
Location: Scotland
Posts: 567
Posted: 22 April 2005 at 3:49am | IP Logged  
and duplicates are OK for those records in the DB
Back to Top View ChiefPFF's Profile Search for other posts by ChiefPFF Visit ChiefPFF's Homepage Send Private Message Add to Buddy List
 
ChiefPFF
Honourary Member
Honourary Member
Avatar
CheesePFF

Joined: 09 September 2002
Location: Scotland
Posts: 567
Posted: 22 April 2005 at 4:03am | IP Logged  
hmm

ok isee whats wrong there - its the using Execute again..

hmmm...
Back to Top View ChiefPFF's Profile Search for other posts by ChiefPFF Visit ChiefPFF's Homepage Send Private Message Add to Buddy List
 
ChiefPFF
Honourary Member
Honourary Member
Avatar
CheesePFF

Joined: 09 September 2002
Location: Scotland
Posts: 567
Posted: 22 April 2005 at 4:16am | IP Logged  
http://support.microsoft.com/default.aspx?scid=kb;EN-US;221931
Back to Top View ChiefPFF's Profile Search for other posts by ChiefPFF Visit ChiefPFF's Homepage Send Private Message Add to Buddy List
 
Harv
Honourary Member
Honourary Member
Avatar
TF2 GOD

Joined: 24 January 2003
Location: United Kingdom
Posts: 2716
Posted: 22 April 2005 at 4:43am | IP Logged  
bleh!
Back to Top View Harv's Profile Search for other posts by Harv Send Private Message Add to Buddy List
 
Sirwomble
Site Admin
Site Admin
Avatar
Occasional Table

Joined: 09 September 2002
Location: United Kingdom
Posts: 6687
Posted: 22 April 2005 at 8:55am | IP Logged  
nice weather were having .

__________________
Back to Top View Sirwomble's Profile Search for other posts by Sirwomble Send Private Message Add to Buddy List
 
Puss
Honourary Member
Honourary Member
Avatar

Joined: 04 August 2003
Location: Scotland
Posts: 1518
Posted: 22 April 2005 at 12:12pm | IP Logged  
Was about to say that chief. If you send it to me if you still have probs I will have a look. I do lots of ASP and SQL nowadays. If I can't get it to work there are people here devoted to SQL. SO they will be able to I think.

__________________
You're about as useful as a cock flavored lollipop.
Back to Top View Puss's Profile Search for other posts by Puss Send Private Message Add to Buddy List
 
ChiefPFF
Honourary Member
Honourary Member
Avatar
CheesePFF

Joined: 09 September 2002
Location: Scotland
Posts: 567
Posted: 28 April 2005 at 1:57pm | IP Logged  
For anyone thats vaguely interested..

I couldnt get it to work by returning the autonumber after the execute (and in thre same connection session).

So I bodged my way around it like this..

----------------------------------------------------

Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

     Dim rsAdmin
     Dim rsAdmin_numRows
     
     Set rsAdmin = Server.CreateObject("ADODB.Recordset")
     rsAdmin.ActiveConnection = MM_gforceDBConn_STRING
     rsAdmin.Source = "SELECT AdminUserID FROM tblAdminUsers WHERE AdminUserName = '" & Request.Form("AdminUsername") & "'"
     rsAdmin.CursorType = 0
     rsAdmin.CursorLocation = 2
     rsAdmin.LockType = 1
     rsAdmin.Open()
     
     rsAdmin_numRows = 0
     
     If NOT rsAdmin.EOF Then
          
          While NOT rsShops.EOF
          
               Set objAddPerm = Server.CreateObject("ADODB.Command")
               objAddPerm.ActiveConnection = MM_editConnection
               objAddPerm.CommandText = "INSERT INTO tblPermInst (AdminUserID, PermShopID, PermEditProd, PermDelProd, PermEditCat, PermDelCat, PermProcOrder, PermEditOrder, PermSendMail) VALUES ('" & rsAdmin("AdminUserID") & "','" & rsShops("ShopID") & "','0','0','0','0','0','0','0') "
               objAddPerm.Execute
               objAddPerm.ActiveConnection.Close
               rsShops.MoveNext()
          
          Wend          
          
     rsAdmin.Close()
     Set Admin = Nothing
          
     Else
          Response.Write(BOLLOX)
     End If

----------------------------------------------

Edited by ChiefPFF on 28 April 2005 at 1:59pm
Back to Top View ChiefPFF's Profile Search for other posts by ChiefPFF Visit ChiefPFF's Homepage Send Private Message Add to Buddy List
 
Puss
Honourary Member
Honourary Member
Avatar

Joined: 04 August 2003
Location: Scotland
Posts: 1518
Posted: 28 April 2005 at 2:57pm | IP Logged  
Well, that looked like fun. I do stuff like that everyday. I just spent 30 mins trying to look for your error. Then I read the post properly and notice that there isn't one. Ah well. At least I tried.

__________________
You're about as useful as a cock flavored lollipop.
Back to Top View Puss's Profile Search for other posts by Puss Send Private Message Add to Buddy List
 
ChiefPFF
Honourary Member
Honourary Member
Avatar
CheesePFF

Joined: 09 September 2002
Location: Scotland
Posts: 567
Posted: 28 April 2005 at 3:14pm | IP Logged  
aww puss fanks..

you plonker       
Back to Top View ChiefPFF's Profile Search for other posts by ChiefPFF Visit ChiefPFF's Homepage Send Private Message Add to Buddy List
 
Harv
Honourary Member
Honourary Member
Avatar
TF2 GOD

Joined: 24 January 2003
Location: United Kingdom
Posts: 2716
Posted: 28 April 2005 at 5:53pm | IP Logged  
bleh!
Back to Top View Harv's Profile Search for other posts by Harv Send Private Message Add to Buddy List
 
TheFraggle
Clan Member
Clan Member
Avatar

Joined: 23 January 2003
Location: United Kingdom
Posts: 661
Posted: 29 April 2005 at 2:43pm | IP Logged  
erm....come again in english, my type of programming was:

DEF PROCEDURE

IF M = 12 THEN GOTO DEF PROCME

DEF PROCME

PRINT "YOU ARE THICK!"

omg i'm OLD!!!!

__________________

I Fank Ewe
Back to Top View TheFraggle's Profile Search for other posts by TheFraggle Send Private Message Add to Buddy List
 
Sirwomble
Site Admin
Site Admin
Avatar
Occasional Table

Joined: 09 September 2002
Location: United Kingdom
Posts: 6687
Posted: 29 April 2005 at 3:42pm | IP Logged  
luvly and sunny today .

__________________
Back to Top View Sirwomble's Profile Search for other posts by Sirwomble Send Private Message Add to Buddy List
 
Puss
Honourary Member
Honourary Member
Avatar

Joined: 04 August 2003
Location: Scotland
Posts: 1518
Posted: 29 April 2005 at 4:17pm | IP Logged  
Too windy womble. Besides, this thread should be labelled - For Geeks Only!

__________________
You're about as useful as a cock flavored lollipop.
Back to Top View Puss's Profile Search for other posts by Puss Send Private Message Add to Buddy List
 
TheFraggle
Clan Member
Clan Member
Avatar

Joined: 23 January 2003
Location: United Kingdom
Posts: 661
Posted: 29 April 2005 at 5:27pm | IP Logged  
You said it Puss!!

__________________

I Fank Ewe
Back to Top View TheFraggle's Profile Search for other posts by TheFraggle Send Private Message Add to Buddy List
 

Sorry, you can NOT post a reply.
This topic is closed.

  Post ReplyPost New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 1.2363 seconds.

Sponsored by Beatnik Games