Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Creating a table with a variable as name


    Why isn't this working? Later, "Username" will be pulled from a database.

    Dim strSQL
    Dim strSQL2
    strSQL = "CREATE TABLE " & Username & " (Userid text(20),Password text(20),First text(20),Last text(20),Emadd text(40));"
    strSQL2 = "CREATE TABLE " & Username & "_sales (Date text(20),Name text(20),Desc text(50),Qty text(3),Price text(10));"
    conn.Execute strSQL
    conn.Execute strSQL2
    set conn=nothing

    I'm getting an error in strSQL. "Syntax error in field definition."

    Thanks.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    Which SQL do you use?
    can you post (copy/paste) the complete error message?

    Maybe it complains because it know your design is wrong.
    Where did you get the idea to create separated tables for each user?
    A better approach would be to have two tables: one with all the users and another with all the sales.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    Originally Posted by MrFujin
    Which SQL do you use?
    can you post (copy/paste) the complete error message?

    Maybe it complains because it know your design is wrong.
    Where did you get the idea to create separated tables for each user?
    A better approach would be to have two tables: one with all the users and another with all the sales.
    How do I know which SQL I'm using? I'm working on IIS 7.0 on Vista.

    The sales are user-specific. So within the sales table, there would have to be a listing for username. And then I'd just have to loop through the whole table, finding records specific to each username. I felt it made more sense to give each username its own table? Could be wrong.

    Thanks!
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    Looking at that setup I assume you're using MS SQL.
    You use 'Date' as fieldname which should be avoided as it is one of the reserved word.
    Full list of reserved words can be found here: http://msdn.microsoft.com/en-us/library/ms189822.aspx
    Can be this field it complain about.

    Regarding the table-per-user, this design is not good in terms of scalability and data analyses, for example if you want to know the amount in sales (sum of price) per user or in total.

    Another thing you can take into consideration, when first part/problem is solved, is what field types you are using.
    Instead of using 'text' all places you could try to use a more appropriated type like datetime, integer (int) or double.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    Hmm nope changed the date to something else, and it still doesn't work.

    ...Any other suggestions? Thanks for your time.
  10. #6
  11. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    Hmm.. don't think there should be a semi-colon at the end, but can be wrong.
    For another test, try change text to char.

    Otherwise, try to print out the SQL and see/post how it looks like.
    It can also be a good idea to test it directly into MS SQL, but for this test you will probably have to install MS SQL Express Studio.
  12. #7
  13. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,430
    Rep Power
    4539
    In addition, you probably should be using varchar(N) instead of text(N). A TEXT column is usually a BLOB column without a character length argument.

    If you're going to program the site, you really should find out exactly what database system and version that you're using for the site. Then you can locate the applicable documentation for your dbms which will have complete docs on syntax in sql statements.

    And one other comment, it's extremely dangerous to pass unfiltered user data to a sql statement. If you are getting username from a user input or environment variable you should validate the username string before you use it in your sql.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    Okay, I've managed to get the first of the two strings working. I stopped the script and had it write its SQL query so I could troubleshoot.

    CREATE TABLE asdfasdf234134 (id autoincrement,firstname varchar(155),lastname varchar(155),email varchar(155),CONSTRAINT id_pk PRIMARY KEY(id));

    CREATE TABLE asdfasdf234134_sales (id autoincrement,saletime varchar(155),name varchar(155),desc varchar(155),qty varchar(155),price varchar(155),CONSTRAINT id_pk PRIMARY KEY(id));

    It's fine with the first table creation, but not the second one. Why? the asdf.. is the username that the user inputs on the registration page. I understand that quantity and saletime should be different data types, I figured it would simplify the troubleshooting for now to make them all varchar.

    Gabriel
  16. #9
  17. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,430
    Rep Power
    4539
    First, like MrFujin I think you should reconsider modifying your database structure dynamically by creating tables for each user.

    Do you get error messages? Post the complete error message text to help pin down the problem. As a guess, dbms have "reserved words" that you can't use as identifiers, probably your column named "name" won't work because "name" is a reserved world.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    Here's the error message, after changing name to a non-reserved word.

    Microsoft JET Database Engine error '80040e14'

    Syntax error in field definition.

    /create.asp, line 59
  20. #11
  21. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,430
    Rep Power
    4539
    What's line 59?

    Also you should output the actual sql string then copy it to your Access query window and see if the sql works in the db itself.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    Fixed, thank you! Name and Desc were both reserved words.
  24. #13
  25. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,430
    Rep Power
    4539
    Glad you got it working. One more thing, you should consider what your code should do if the table you're trying to create already exists in the db.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    Thanks. That can't happen because it checks if the username already exists when someone registers.

    I guess it COULD happen if someone chose to make their username "myname_sales" when someone else has already used "myname."

    Gabriel
  28. #15
  29. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    Originally Posted by o2bnlv
    That can't happen because it checks if the username already exists when someone registers.
    Just curious, how do you perform that check?
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo