#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2004
    Posts
    22
    Rep Power
    0

    Unhappy RENAME TABLE, Change Field type varchar(50) to varchar(100)


    Thank you in advance, I am having trouble with renaming tables. I've tried every syntax I can get my hands on. Here is the code, is the statement wrong?

    if form_in_new_name <> 0 then
    sSQL = "ALTER TABLE " & form_in_name & "_table RENAME TO " & form_in_new_name & "_table ;"
    Set rs2 = cn.Execute(sSQL)
    Set rs2 = Nothing
    end if

    My second question is, is there a way through an SQL statement to change a field type, either from one type to another, or simply the size such as, varchar(50) to varchar(100). If so what would the statement(s) be?

    Muchas Gracias!!!!!!!!
  2. #2
  3. Unholy Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Location
    In hell, where did you think?
    Posts
    12,026
    Rep Power
    658
    To rename that table try this
    Code:
    sql = "ALERT TABLE '" & form_in_name & "'_table RENAME TO '" & form_in_new_name & "'_table"
    To change the datatype try this
    Code:
    sql = "ALTER TABLE <tableName> ALTER COLUMN <columnName> SET DATA TYPE VARCHAR(100)"
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2004
    Posts
    22
    Rep Power
    0
    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Incorrect syntax near the keyword 'TO'.

    /edit_category.asp, line 38

    When I don't tell it to run the line, I can output the statement. This is exactly what statement is giving the error:

    ALTER TABLE Computer_Displays_table RENAME TO Computer_Display_table ;
  6. #4
  7. Unholy Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Location
    In hell, where did you think?
    Posts
    12,026
    Rep Power
    658
    try modifying it to this
    Code:
    sql = "ALERT TABLE ['" & form_in_name & "'_table] RENAME TO ['" & form_in_new_name & "'_table]"
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2004
    Posts
    22
    Rep Power
    0
    ALTER TABLE [Computer_Displays_table] RENAME TO [Computer_Display_table] ;


    Microsoft OLE DB Provider for SQL Server error '80040e14'

    Incorrect syntax near the keyword 'TO'.

    /edit_category.asp, line 38
  10. #6
  11. Unholy Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Location
    In hell, where did you think?
    Posts
    12,026
    Rep Power
    658
    okay, try this
    Code:
    sql = "ALERT TABLE [" & form_in_name & "_table] RENAME TO [" & form_in_new_name & "_table]"
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2004
    Posts
    22
    Rep Power
    0
    I have actually been trying without the single quotes.. the single quotes throws an error beyond the one i was already getting. I knew the single quotes was wrong to begin with and thus have been ignoring them... hence this last suggestion I have already tried and get the same error.

    -Jason

Similar Threads

  1. Replies: 1
    Last Post: February 26th, 2004, 03:22 PM
  2. Executing Oracle SP that returns Table Type.
    By sgotur in forum .NET Development
    Replies: 0
    Last Post: February 26th, 2004, 01:27 PM
  3. Using SQL to change data type
    By Chris_Hay in forum SQL Development
    Replies: 1
    Last Post: February 10th, 2004, 10:36 AM
  4. Synchronizing 2 tables with different field structure
    By nickcwj in forum SQL Development
    Replies: 0
    Last Post: October 29th, 2003, 12:16 AM
  5. difference between relational table and flat field table
    By firetrap2 in forum SQL Development
    Replies: 1
    Last Post: August 21st, 2003, 06:59 AM

IMN logo majestic logo threadwatch logo seochat tools logo