[noob101]

Hi I just started using the Microsoft Access driver and I'm a little lost.
Keeps giving me the syntax error. I've searching for the solution but I can't find it.

//Create StockTrades table
		try
		{
			System.out.println("Creating StockTrades table.....");

			stmt.executeUpdate("CREATE TABLE StockTrades ("

					+"userID TEXT(20) "
					+"CONSTRAINT FK1_StockTrades REFERENCES Users (userID), "
					+"symbol TEXT(8) "
					+"CONSTRAINT FK2_Stocktrades REFERENCES Stocks (symbol), "
					+"numStocks INT, "
                                        
                                        //the problem seems to be with the six lines of code below this (but I have to   

					+"date TEXT(30), "
					+"priceShare DECIMAL(5,2), "
					+"numBought INT,"
					+"numSold INT, "
					+"totalPrice DECIMAL(9,2), "

					+")");

		}
		catch(Exception e)
		{
			System.out.println("Exception creating StockTrades table: "+e.getMessage());
		}

[modi123_1]

What's the syntax error?

[noob101]

modi123_1, on 22 February 2012 - 08:07 AM, said:

What's the syntax error?

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

[modi123_1]

Is that an extra comma at the end of line 20? Try removing that.

[noob101]

I've tried removing it same error message shows.
Up to line 12 everything works perfectly, but after that it always makes this error. If I take out lines 15 - 20 it will work.

[modi123_1]

Then slap all that code into a string, put a break point down, and see what the whole string looks like. Post that here as well.

Also start removing extraneous crap from the line (the CONSTRAINT junk) so you just have columns and types... that should simplify things.

[noob101]

I'm a bit noob here so I wasn't too sure what you meant. I'm guessing you want it in String format so you can see what the database sees, I've posted that below. Not sure what you meant be break point though.


CREATE TABLE StockTrades (userID TEXT(20) CONSTRAINT FK1_StockTrades REFERENCES Users (userID), symbol TEXT(8) CONSTRAINT FK2_Stocktrades REFERENCES Stocks (symbol), numStocks INT date TEXT(30), priceShare DECIMAL(5,2), numBought INT,numSold INT, totalPrice DECIMAL(9,2) )

I went through each line to find out the exact lines that are causing problems. The problems come from the line that aren't integers.
The following code works perfectly

{
System.out.println("Creating StockTrades table.....");

			stmt.executeUpdate("CREATE TABLE StockTrades ("

					+"userID TEXT(20) "
					+"CONSTRAINT FK1_StockTrades REFERENCES Users (userID), "
					+"symbol TEXT(8) "
					+"CONSTRAINT FK2_Stocktrades REFERENCES Stocks (symbol), "
					+"numStocks INT, "
					//+"date TEXT(30), "
					//+"priceShare DECIMAL(5,2), "
					+"numBought INT,"
					+"numSold INT "
					//+"totalPrice DECIMAL(9,2) "

					+")");

		}

[modi123_1]

Perhaps not using 'int' and the full datatype's name "integer"?

https://office.micro...A010233292.aspx

[noob101]

In the post above inside the string example after numStocks INT I forgot to place a comma. I have tried it with the comma and it still doesn't work.

Tried changing the INT to integer and it still won't work. But I don't think that will be the problem though, because it's any other data type that seems to be giving me problems.

[modi123_1]

I'm pretty certain access only likes 'integers'. You are also missing a comma at the end of line 14.


What IDE are you using? I mean seriously - you should be able to DEBUG your program by putting a break point (a stopping point in your code's excecution) to the left of the line number and you can see what your string is formiing up... a malformed string shouldn't be this giant of an ordeal.

[noob101]

Using text pad for this project, I'm still learning the basics of eclipse so I didn't want to move my current project over thought I'd just finish it in text pad.
Tomorrow I'll transfer the project to eclipse and see what happens.

Thanks for your help so far.