-
Jun 21st, 2020, 03:59 PM
#1
Thread Starter
Member
[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
-
Jun 21st, 2020, 06:20 PM
#2
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
-
Jun 21st, 2020, 06:49 PM
#3
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
-
Jun 21st, 2020, 08:16 PM
#4
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.
-
Jun 22nd, 2020, 12:38 AM
#5
Re: VB.NET SQL Syntax Error
Originally Posted by blaknite
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.
-
Jun 22nd, 2020, 07:00 AM
#6
Thread Starter
Member
Re: VB.NET SQL Syntax Error
Originally Posted by ChrisE
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.
-
Jun 22nd, 2020, 07:22 AM
#7
Re: VB.NET SQL Syntax Error
Originally Posted by blaknite
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:
Dim sql = "SELECT *
FROM MyTable
WHERE ParentId = @ParentId"
command.CommandText = sql
or:
vb.net Code:
Dim sql = <sql>
SELECT *
FROM MyTable
WHERE ParentId = @ParentId
</sql>
command.CommandText = sql.Value
-
Jun 22nd, 2020, 07:27 AM
#8
Re: VB.NET SQL Syntax Error
Originally Posted by blaknite
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|