Results 1 to 8 of 8

Thread: [RESOLVED] VB.NET SQL Syntax Error

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2010
    Posts
    51

    Resolved [RESOLVED] VB.NET SQL Syntax Error

    Hello,

    I was trying to figure out how to programmatically create database tables using the CREATE TABLE statement as I need to create several tables and add various amounts of data to them. For example the first table might only have 4 fields and 100 records, the second table might have 60 fields and 100 records. Anyway, I just thought it would be faster than manually inputting the data. Back to the problem...

    I started out with a blank database, no tables created, and this code works:

    Code:
        
    Private Sub UDData()
    
            Try
                dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
                TheDatabase = "CreateTableTest.mdb"
                MyDocumentsFolder = AppDomain.CurrentDomain.BaseDirectory
                FullDatabasePath = MyDocumentsFolder & TheDatabase
                dbSource = "Data Source = " & FullDatabasePath
                rulercon.ConnectionString = dbProvider & dbSource
                rulercon.Open()
                testtablesql = "CREATE TABLE TestTable (LastName NVARCHAR(40) NOT NULL, FirstName NVARCHAR(40) NOT NULL, EmailAddress NVARCHAR(200))"
                Dim cmd As New OleDbCommand(testtablesql, rulercon)
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                MessageBox.Show("Table Created Successfully!")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                rulercon.Close()
                rulercon.Dispose()
            End Try
        End Sub
    But now if I change the SQL statement to what I actually want to use to:

    Code:
    testtablesql = "CREATE TABLE Provinces (PlayerID int NOT NULL AUTO_INCREMENT, " &
                    "RulerName VARCHAR(255) NOT NULL, ProvinceName VARCHAR(255) NOT NULL, " &
                    "PlayerRace VARCHAR(255) NOT NULL, PlayerPersonality VARCHAR(255) NOT NULL, " &
                    "PlayerLand int NOT NULL, PlayerPeasants int NOT NULL, PlayerGold int NOT NULL, " &
                    "PlayerFood int NOT NULL, PlayerRunes int NOT NULL, PlayerTrade int NOT NULL, " &
                    "PlayerNetworth int NOT NULL, PlayerHonor int NOT NULL, PlayerTimeIn datetime NOT NULL, " &
                    "PlayerTimeOut datetime NOT NULL)"
    This does not work and returns a SQL Syntax Error. Am I using a wrong datatype? I'm using varchar for strings, int for numbers, datetime to store date and time

    Any help is appreciated,

    Thanks

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: VB.NET SQL Syntax Error

    Hello,

    Install SSMS (SQL-Server Management Studio), it's free. Create a database followed by tables then for relationships right click on Database Diagrams and setup the relations. To get the SQL to create the tables right click on database, select task, select generate scripts to a new query window.

    https://docs.microsoft.com/en-us/sql...l-server-ver15

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: VB.NET SQL Syntax Error

    I agree that it would be much easier to create the tables in SSMS.

    As to the problem, if the code worked fine with a simpler SQL statement, and then failed with a syntax error when you used a more complicated SQL statement, then you have an error in the SQL statement. If the SQL was correct, it would have worked. Perhaps the error message gives you some clue as to where you have something wrong, but those error messages are not always sufficiently specific, and they are sometimes misleading. It all depends on the actual error.

    If you want to do this in code, and not through SSMS, then what I would suggest is that you wrap the Create statement in a Transaction, and roll the transaction back after each try. By doing this, you won't be creating anything at all, you'd just be testing whether or not the SQL worked. By rolling back the transaction, it will undo anything that happened if the SQL works. Therefore, you can start with one column. If that works, add in a few more. Keep doing that until you get your whole SQL statement working, at which point you can change the code to Commit the transaction rather than rolling it back.

    What this approach does is allow you to build up your SQL statement bit by bit such that you don't have to look at the whole statement to try to find a problem. If it works, then you add something and it fails, you know that the problem is in the part you just added, and not in the part that was working before.
    My usual boring signature: Nothing

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: VB.NET SQL Syntax Error

    From what I can gather, the data type is INTEGER rather than int in Access. It looks to me like you've been reading information for SQL Server.

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: VB.NET SQL Syntax Error

    Quote Originally Posted by blaknite View Post

    But now if I change the SQL statement to what I actually want to use to:

    Code:
    testtablesql = "CREATE TABLE Provinces (PlayerID int NOT NULL AUTO_INCREMENT, " &
                    "RulerName VARCHAR(255) NOT NULL, ProvinceName VARCHAR(255) NOT NULL, " &
                    "PlayerRace VARCHAR(255) NOT NULL, PlayerPersonality VARCHAR(255) NOT NULL, " &
                    "PlayerLand int NOT NULL, PlayerPeasants int NOT NULL, PlayerGold int NOT NULL, " &
                    "PlayerFood int NOT NULL, PlayerRunes int NOT NULL, PlayerTrade int NOT NULL, " &
                    "PlayerNetworth int NOT NULL, PlayerHonor int NOT NULL, PlayerTimeIn datetime NOT NULL, " &
                    "PlayerTimeOut datetime NOT NULL)"
    This does not work and returns a SQL Syntax Error. Am I using a wrong datatype? I'm using varchar for strings, int for numbers, datetime to store date and time

    Any help is appreciated,

    Thanks
    this part is wrong
    Code:
    PlayerID int NOT NULL AUTO_INCREMENT,
    change it to..
    Code:
    PlayerID AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY,
    to make the SQL more readable you can do it this way(but that's up to you)
    Code:
     sSQL = "CREATE TABLE Provinces"
            sSQL &= "(PlayerID AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
            sSQL &= ", RulerName VARCHAR(255) NOT NULL"
            sSQL &= ", ProvinceName VARCHAR(255) NOT NULL"
            sSQL &= ", PlayerRace VARCHAR(255) NOT NULL"
            sSQL &= ", PlayerPersonality VARCHAR(255) NOT NULL"
            sSQL &= ", PlayerLand int NOT NULL"
            sSQL &= ", PlayerPeasants int NOT NULL"
            sSQL &= ", PlayerGold int NOT NULL"
            sSQL &= ", PlayerFood int NOT NULL"
            sSQL &= ", PlayerRunes int NOT NULL"
            sSQL &= ", PlayerTrade int NOT NULL"
            sSQL &= ", PlayerNetworth int NOT NULL"
            sSQL &= ", PlayerHonor int NOT NULL"
            sSQL &= ", PlayerTimeIn datetime NOT NULL"
            sSQL &= ", PlayerTimeOut datetime NOT NULL"
            sSQL &= ")"
    hth
    Last edited by ChrisE; Jun 22nd, 2020 at 12:45 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Member
    Join Date
    Jun 2010
    Posts
    51

    Re: VB.NET SQL Syntax Error

    Quote Originally Posted by ChrisE View Post
    this part is wrong
    Code:
    PlayerID int NOT NULL AUTO_INCREMENT,
    change it to..
    Code:
    PlayerID AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY,
    to make the SQL more readable you can do it this way(but that's up to you)
    Code:
     sSQL = "CREATE TABLE Provinces"
            sSQL &= "(PlayerID AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
            sSQL &= ", RulerName VARCHAR(255) NOT NULL"
            sSQL &= ", ProvinceName VARCHAR(255) NOT NULL"
            sSQL &= ", PlayerRace VARCHAR(255) NOT NULL"
            sSQL &= ", PlayerPersonality VARCHAR(255) NOT NULL"
            sSQL &= ", PlayerLand int NOT NULL"
            sSQL &= ", PlayerPeasants int NOT NULL"
            sSQL &= ", PlayerGold int NOT NULL"
            sSQL &= ", PlayerFood int NOT NULL"
            sSQL &= ", PlayerRunes int NOT NULL"
            sSQL &= ", PlayerTrade int NOT NULL"
            sSQL &= ", PlayerNetworth int NOT NULL"
            sSQL &= ", PlayerHonor int NOT NULL"
            sSQL &= ", PlayerTimeIn datetime NOT NULL"
            sSQL &= ", PlayerTimeOut datetime NOT NULL"
            sSQL &= ")"
    hth
    This solution worked... Other than putting AutoIncrement in incorrectly, I wouldn't have guessed not setting the primary key was the issue. Also I'm all for readability! Other SQL strings that I now know how to fix in this same program and quite long get harder and harder to look at especially when you're error hunting. Not so bad when I'm connected to my 46 inch screen, but when I am unplugged from that and have to use the laptop screen as I currently am, the continuation character gets used a lot!! It's become a peeve of mine, when I'm on my laptop screen that VS automatically sets itself to 125% scaling mode. Something I never asked it to do but can only gather it does it when I change from different screen sizes.

    I appreciate everyone's suggestions and as a follow-up: In addition to setting up databases through SQL Server rather than Access, does it also make deployment easier as well? or is that a nonissue? Database programming with VB and Access is all I have ever known. It's what I was taught in college and has stuck with me. So I had no reason to stray to another method.
    Last edited by blaknite; Jun 22nd, 2020 at 07:07 AM.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: VB.NET SQL Syntax Error

    Quote Originally Posted by blaknite View Post
    Also I'm all for readability! Other SQL strings that I now know how to fix in this same program and quite long get harder and harder to look at especially when you're error hunting. Not so bad when I'm connected to my 46 inch screen, but when I am unplugged from that and have to use the laptop screen as I currently am, the continuation character gets used a lot!!
    VB has supported multiline String literals for the last couple of versions and supported XML literals since at least 2005 and possibly even earlier. You should use one or the other for SQL:
    vb.net Code:
    1. Dim sql = "SELECT *
    2.            FROM MyTable
    3.            WHERE ParentId = @ParentId"
    4.  
    5. command.CommandText = sql
    or:
    vb.net Code:
    1. Dim sql = <sql>
    2.               SELECT *
    3.               FROM MyTable
    4.               WHERE ParentId = @ParentId
    5.           </sql>
    6.  
    7. command.CommandText = sql.Value

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: VB.NET SQL Syntax Error

    Quote Originally Posted by blaknite View Post
    I appreciate everyone's suggestions and as a follow-up: In addition to setting up databases through SQL Server rather than Access, does it also make deployment easier as well? or is that a nonissue? Database programming with VB and Access is all I have ever known. It's what I was taught in college and has stuck with me. So I had no reason to stray to another method.
    They are designed for different purposes, and therefore are suited to different situations.
    • File-based databases like those created by Access (or alternatives like SQLite) are designed for single-user systems on a single computer.
    • Server-based databases like SQL Server (or alternatives like MySQL or Oracle) are designed for multi-user systems accessed across a network.
    Based on this thread it seems that you are working on a single-user system, so a file-based database is probably the way to go.

    In terms of deployment, a server-based system only needs to be set up in one place, so it is easier from that point of view... but obviously requires that all users are connected to the server, instead of having their own local databases.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width