ADOX add column to existing table fails in ASP 1

[jjob]

I have an ADOX script that was tested in Access, then converted from VBA to VBS and run in ASP. In Access it happily appends a column to an existing table using the Columns.Append method, in ASP it fails with the message

438 Object doesn't support this property or method

It can append columns when creating a table, but not, apparently, to an existing table.

Is this known behaviour, or am I missing something?

TIA

John

 

[Airpan]

Can you post the code you have for connecting to the db and the query used to append it?

~E

 

[jjob]

The 'core' ASP code is shown below, minus the connection, which is set up in an include file


This displays the columns OK with response.write, but fails on the append.

Thanks

John

Dim cat
Dim tbl
Dim con
Dim tableExists
Dim colExists

tableExists=False
colExists = False

on error goto 0

Set cat = server.createobject("ADOX.Catalog")
Set cat.ActiveConnection = cmdDC

  
  Set tbl = cat.Tables.Item("tblmailList")
  With tbl
      
      response.Write "-----  Column count 1 " & .Columns.Count & "<br>" & vbcrlf
      
      For intCount1 = 0 To .Columns.Count - 1
           if .Columns.Item(intCount1).name = lcase("unsubscribe") then
                colExists = true
                exit for
            end if
                 
           response.write "Cols " & .Columns.Item(intCount1) & "<br>" & vbcrlf
           
      Next
  
      if colExists  then
        '** do nothing except display message, already present
	    response.write "column unsubscribe already exists<BR>" & vbcrlf
      else
 	    response.write "about to add column unsubscribe<BR>" & vbcrlf

        on error resume next	    

        .Columns.add "unsubscribe", 11

        if err.number > 0 then
            response.Write err.number & " " & err.Description & "<br>"
        else
    	    response.write "column unsubscribe added<BR>" & vbcrlf
        end if

      end if  
  
      Debug.Print "------- Column count 2 " & .Columns.Count  & vbcrlf
      
      For intCount1 = 0 To .Columns.Count - 1
           resonse.write "cols " &  .Columns.Item(intCount1) & "<br>" & vbcrlf
      Next
  
  End With

      response.Write "All done update " & "<br>" & vbcrlf
        

'Clean up the references
   Set cat = Nothing
   Set tbl = Nothing
        


	Set tbl = Nothing
End If
Set cat = Nothing
Set con = Nothing

 

[tsuji]

[1] Use append method
[tt]
[red]'[/red].Columns.add "unsubscribe", 11
[blue]set col=server.createobject("adox.column")
col.type=11 'adBoolean
col.name="unsubscribe"
.Columns.append col
set col=nothing[/blue]
[/tt]
[2] If it still fails, further make sure the connection use access oledb connection string driver. (odbc connection string driver would still fail to support it.)

 

[Airpan]

jjob,
I recently had a MAJOR issue with a connection problem. I had written connection information which was not really supported and it kept throwing out errors to me indiscriminately... sometimes it would work, sometimes it wouldn't. I think tsuji is right... checking your connection information is a good place to start.

http://www.codeproject.com/database/connectionstrings.asp

I used the above link to find some solid examples of connection information, but in the end, got some help from here. Go to this thread if you would like a closer example: thread333-1390860

~E

 

[jjob]

Thanks all, my apologies for the delay in getting back to you, I'm working on 2 projects at once and one requires me to work away, will look at your comments when I get back home tonight.

regards

john

 

[jjob]

Excellent, you have both identified the problem, I needed the OLEDB connection, not the ODBC one.

Stars for both of you, I may make the deadline for this now!