Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

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

Results 1 to 7 of 7
Share This Thread →
  1. #1
    ccstarr is offline Registered User
    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
    Memnoch's Avatar
    Memnoch is offline Unholy Moderator
    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)"

  3. #3
    ccstarr is offline Registered User
    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 ;

  4. #4
    Memnoch's Avatar
    Memnoch is offline Unholy Moderator
    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]"

  5. #5
    ccstarr is offline Registered User
    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

  6. #6
    Memnoch's Avatar
    Memnoch is offline Unholy Moderator
    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]"

  7. #7
    ccstarr is offline Registered User
    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

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates