September 8, 2008 at 12:19 pm
Hello!
I have just installed SQL server 2005 standard after completely uninstalling sql express and used a database back up from the sql express to restore from. Since the upgrade i have been experiencing an error when updating from the DB's "products" table I have not applied any service packs in fear of creating a bigger problem.
This error only happens when the action is trying to update a column in the products table. And the action does change multiple records at once.
the exact error is: (i have removed the actual column name)
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'column name'.
/shopa_addproduct.asp, line 424
PLease help.
September 8, 2008 at 12:20 pm
You have a syntax error there with the name of the column. What is the column name of the table?
September 8, 2008 at 12:32 pm
I have only changed the the actual name to column name for this post guess i didnt need to eh?
the name of the column is mcpartnum
September 8, 2008 at 1:40 pm
any one have any Ideas??
September 9, 2008 at 7:57 am
Am i missing any info that is not helping in the matter?...PLease help?
September 10, 2008 at 8:31 am
I've upgraded from express to standard with no similar error, so I have some suggestions along a different line.
What happens when you execute "Select mcpartnum from products"?
* Are you getting this error by updating the products table with a stored procedure?
** If so, can you recreate the error at will by manually running the sproc, or does it only occur occasionally?
*** If you can reproduce it, have you put print statements in the sproc to see what is happening at various steps?
September 10, 2008 at 9:54 am
dcorbitt
When i run the query:
SELECT mcpartnum from products
I get this error:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/shopa_editdisplay.asp, line 354
The other things you asked:
the original error (not the one posted above in this reply) happens every single time I update products table.
they are not running from a stored procedure. its jsut a query that updates multiple recorsds at once if they share a relationship by certain tables.
September 10, 2008 at 10:05 am
Re-reading your original submission, are you updating the products table, or updating some other table?
September 10, 2008 at 10:28 am
I am updating the products table
but the code references 2 other tables to get information on there relationships to update all products that are of the same categories
September 10, 2008 at 10:34 am
dcorbitt
I jsut wanted to add(since i cant edit my last post) that when i run this query from my shopping cart software that is where i get the error.
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/shopa_editdisplay.asp, line 354
when i run the query in the SQL manager it works fine
September 10, 2008 at 10:44 am
Is your "cart" pointing to a different SQL instance (maybe on a different machine) from where sql manager is pointing (live versus staging, perhaps)? Your "cart" appears to be pointing to a products table that doesn't have the column.
September 10, 2008 at 4:37 pm
No its pointing to the correct instance, but when i change the config file to just point at the IP address it doesn't work but when i point it to both IP and The computer name/instance it works. <----not sure if that has anything to do with the problem, but it is a problem.
September 10, 2008 at 7:25 pm
The 800a0cc1 error returned by the ADO Recordset Object is usually accompanied by the following text:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
The error occurs in one of two situations:
You try to access a field in the fields collection that is not in the Recordset
You try to access a field using an ambiguous name.
Scenario 1 - Missing field
Consider the following code:
<%
strSQL = _
"SELECT field1, field2 " & _
"FROM table1 " & _
"WHERE field1 = 'foobar'"
Set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.Source = strSQL
.ActiveConnection = objConn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
End With
objRS.Open ,,,,adCmdText
Response.Write(objRS("field3"))
%>
An error will be generated by the last line of code because an attempt it being made to access a field that is not present in the recordset. Looking at the first line of code we can see that the SQL string that we are creating selects only field1 and field2 from the database - and our resulting recordset will contain only those two fields, not field3.
Scenario 2 - Ambiguous Field Name
Consider the following code:
<%
strSQL = _
"SELECT table1.ID, table2.ID " & _
"FROM table1, table2 " & _
"WHERE table1.ID = 1"
Set objRS = Server.CreateObject("ADODB.Recordset")
With objRS
.Source = strSQL
.ActiveConnection = objConn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
End With
objRS.Open ,,,,adCmdText
Response.Write(objRS("ID"))
%>
In this case an error will be generated because the ADO Recordset Object does not know which ID field you are attempting to write to the screen. To solve this problem either:
rename your fields so that they don't have the same name
use the SQL "AS" keyword
use ordinals
Probably the best of the above alternatives involves renaming your fields. For example, if table1 and table2 above where instead called "Customers" and "Orders" then instead of calling the fields simply "ID" an alternative naming scheme would have called the two fields "CustomerID" and "OrderID" respectively.
If renaming the fields is out of the question, then another alternative involves using the "AS" keyword, as shown in the following code (the code to open the recordset is omitted):
<%
strSQL = _
"SELECT Customers.ID AS CustomerID, " & _
"Orders.ID AS OrderID " & _
"FROM Customers, Orders " & _
"WHERE Customers.ID = 1"
'...
Response.Write( _
objRS("CustomerID") & "
" & vbCrLf &_
objRS("OrderID") & "
" & vbCrLf)
%>
The last alternative involves using ordinals instead. By using an ordinal reference we avoid any confusion over which ID field we are attempting to reference:
<%
strSQL = _
"SELECT table1.ID, table2.ID " & _
"FROM table1, table2 " & _
"WHERE table1.ID = 1"
'...
Response.Write( _
objRS.Fields(0).Value & "
" & vbCrLf &_
objRS.Fields(1).Value & "
" & vbCrLf)
Hope this helps...:)
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 11, 2008 at 6:49 am
James, just to be sure we're comparing apples to apples...
If you're putting the IP address in the config file, then it needs to be of the form "IPAddress\InstanceName," not just "IPAddress." Your note contrasted "IP address" with "ComputerName\InstanceName." Maybe that was only an oversight in shorthand, but leaving off the instance name after the IP address will leave you pointing to the default instance, not the named instance.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply