errore su una select

[mauro99]

Salve non riesco a capire dove possa essere l'errore si mi potete aiutare?
Ho un form dove inserisco dei dati che poi vengono scritti sul database ma quando va a scriverli mi da questo errore:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias 'CustomerID' in query definition's SELECT list.

/apstore/web/classes/estore_classes2.asp, line 289

il codice a cui fa riferimento è questo:

Public Sub GetAccountInfo(ByVal CustomerID, Company, FirstName , LastName, POBox, Address, City, State, Zip , CountryID, CountryName, Phone, Email, UserName, Password, Codicefisc, SpedNome, SpedCognome, SpedIndirizzo, SpedCap, SpedCitta)
	dim Rs, Conn
	Set Rs = Server.CreateObject("adodb.Recordset")
	Set Conn = Server.CreateObject("ADODB.Connection")	
	Conn.open GetConnectionString()
	SQLText = "SELECT Customers.*, Countries.ShipMethodName FROM Countries RIGHT JOIN Customers ON Countries.ShipCountryID = Customers.CountryID where customerid = "&customerID
	Rs.open SQLText,Conn, adOpenStatic,adLockReadOnly
	if rs.recordCount > 0 then 
	    Company = rs("company")
		FirstName = rs("FirstName")
		LastName = rs("LastName")
		POBox = rs("POBox")
		Address = rs("Address")
		City = rs("City")
		State = rs("State")
		Zip = rs("zip")
		CountryID = rs("countryID")
		CountryName = rs("ShipMethodName")
		Phone = rs("phone")
		Email = rs("email")
		Username = rs("username")
		Password = rs("password")
		Codicefisc = rs("Codicefisc")
		SpedNome = rs("SpedNome")
		SpedCognome = rs("SpedCognome")
		SpedIndirizzo = rs("SpedIndirizzo")
		SpedCap = rs("SpedCap")
		SpedCitta = rs("SpedCitta")
	End if
	Set Rs = Nothing
	Set Conn = Nothing
End Sub

End Class


Class EStore_AccountTx
Public Sub UpdateAccount(CustomerID, Company, FirstName, LastName, POBox, Address, City, State, Zip , CountryID, Phone, Email, UserName, Password, Codicefisc, SpedNome, SpedCognome, SpedIndirizzo, SpedCap, SpedCitta)
	dim Conn
	if trim(Company) = "" then Company = Null
	if trim(POBox) = "" then POBox = Null
	Set Conn = Server.CreateObject("ADODB.Connection")	
	Conn.open GetConnectionString()
	SQLText = "UPDATE Customers SET FirstName= '"&ClearSingleQuote(FirstName)&"' , LastName='"&ClearSingleQuote(LastName)&"', City = '"&ClearSingleQuote(City)&"', State= '"&ClearSingleQuote(State)&"', Zip = '"&ClearSingleQuote(zip)&"',CountryID = "&CountryID&", Email = '"&ClearSingleQuote(Email)&"', Phone = '"&ClearSingleQuote(Phone)&"', UserName = '"&ClearSingleQuote(Username)&"', [Password] = '"&ClearSingleQuote(Password)&"', Codicefisc = '"&ClearSingleQuote(Codicefisc)&"', SpedNome = '"&ClearSingleQuote(SpedNome)&"', SpedCognome = '"&ClearSingleQuote(SpedCognome)&"', SpedIndirizzo = '"&ClearSingleQuote(SpedIndirizzo)&"', SpedCap = '"&ClearSingleQuote(SpedCap)&"', SpedCitta = '"&ClearSingleQuote(SpedCitta)&"'"  
	if company <> "" then	SQLText = SQLText & ", company= '"&ClearSingleQuote(Company)&"' "
	if POBox <> "" then SQLText = SQLText & ", POBox= "&ClearSingleQuote(POBox)&" "
	if address <> "" then SQLText = SQLText & ", address= '"&ClearSingleQuote(address)&"' "
	SQLText = SQLText & " where customerid = "&CustomerID
	Conn.Execute SQLText
	Set Rs = Nothing
	Set Conn = Nothing
End Sub


Public Function CreateAccount(FirstName, LastName, Company,  POBox, Address, City, State, Zip , CountryID, Phone, Email, UserName, Password, Codicefisc, SpedNome, SpedCognome, SpedIndirizzo, SpedCap, SpedCitta)
	dim Rs, Conn
	Set Rs = Server.CreateObject("adodb.Recordset")
	Set Conn = Server.CreateObject("ADODB.Connection")	
	Conn.open GetConnectionString()
	Conn.BeginTrans
	if trim(POBox) = "" then
		SQLText = "INSERT INTO Customers ( Company, FirstName, LastName, Address, City, State, Zip, CountryID, Email, Phone, Username, [Password], Codicefisc, SpedNome, SpedCognome, SpedIndirizzo, SpedCap, SpedCitta ) Values ( '"&ClearSingleQuote(Company)&"', '"&ClearSingleQuote(FirstName)&"', '"&ClearSingleQuote(LastName)&"', '"&ClearSingleQuote(Address)&"', '"&ClearSingleQuote(City)&"', '"&ClearSingleQuote(State)&"','"&ClearSingleQuote(zip)&"', "&CountryID&", '"&ClearSingleQuote(Email)&"', '"&ClearSingleQuote(Phone)&"', '"&ClearSingleQuote(Username)&"', '"&ClearSingleQuote(Password)&"', '"&ClearSingleQuote(Codicefisc)&"', '"&ClearSingleQuote(SpedNome)&"', '"&ClearSingleQuote(SpedCognome)&"', '"&ClearSingleQuote(SpedIndirizzo)&"', '"&ClearSingleQuote(SpedCap)&"', '"&ClearSingleQuote(SpedCitta)&"')"  
	else
		SQLText = "INSERT INTO Customers ( Company, FirstName, LastName, POBox, Address, City, State, Zip, CountryID, Email, Phone, Username, [Password], Codicefisc, SpedNome, SpedCognome, SpedIndirizzo, SpedCap, SpedCitta ) Values ( '"&ClearSingleQuote(Company)&"', '"&ClearSingleQuote(FirstName)&"', '"&ClearSingleQuote(LastName)&"', "&POBox&", '"&ClearSingleQuote(Address)&"', '"&ClearSingleQuote(City)&"', '"&ClearSingleQuote(State)&"','"&ClearSingleQuote(zip)&"', "&CountryID&", '"&ClearSingleQuote(Email)&"', '"&ClearSingleQuote(Phone)&"', '"&ClearSingleQuote(Username)&"', '"&ClearSingleQuote(Password)&"', '"&ClearSingleQuote(Codicefisc)&"', '"&ClearSingleQuote(SpedNome)&"', '"&ClearSingleQuote(SpedCognome)&"',	'"&ClearSingleQuote(SpedIndirizzo)&"', '"&ClearSingleQuote(SpedCap)&"', '"&ClearSingleQuote(SpedCitta)&"')"  
	end if
	Conn.Execute SQLText
	SQLText = "select max(CustomerID) as CustomerID from Customers"
	Conn.Execute SQLText
	if rs.recordCount > 0 then 
		CreateAccount = rs("customerid")
	else
		CreateAccount = False
	End if
	Conn.CommitTrans
	Set Rs = Nothing
	Set Conn = Nothing
End Function
End Class

la linea incrimitata è la seguente:
SQLText = "select max(CustomerID) as CustomerID from Customers"
grazie per laiuto.

[Roby_72]

Per recuperare il customerID che mi sembra un contatore dopo la INSERT usa questo:

SQL = "SELECT @@identity FROM Customers"
Apri il recordset rs ed in rs(0) hai il tuo CustomerID

Roby

[mauro99]

o cambiato la stringa come mi hai detto ma ora mi da questo errore:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/apstore/web/classes/estore_classes2.asp, line 290

[Roby_72]

Ma hai aperto il recorset e tutto il resto?

Roby

[mauro99]

grazie per l'aiuto ora funziona sei un grande