10 Replies - 5619 Views - Last Post: 22 February 2012 - 02:13 PM Rate Topic: -----

#1 noob101  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 01-November 11

ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 06:28 AM

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());
		}




Is This A Good Question/Topic? 0
  • +

Replies To: ODBC Microsoft Access Driver Syntax error in Field definition

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9286
  • View blog
  • Posts: 34,805
  • Joined: 12-June 08

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 08:07 AM

What's the syntax error?
Was This Post Helpful? 0
  • +
  • -

#3 noob101  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 01-November 11

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 09:20 AM

View Postmodi123_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.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9286
  • View blog
  • Posts: 34,805
  • Joined: 12-June 08

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 09:24 AM

Is that an extra comma at the end of line 20? Try removing that.
Was This Post Helpful? 0
  • +
  • -

#5 noob101  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 01-November 11

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 11:06 AM

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.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9286
  • View blog
  • Posts: 34,805
  • Joined: 12-June 08

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 12:01 PM

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.
Was This Post Helpful? 0
  • +
  • -

#7 noob101  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 01-November 11

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 01:41 PM

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) "

					+")");

		}

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9286
  • View blog
  • Posts: 34,805
  • Joined: 12-June 08

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 01:49 PM

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

https://office.micro...A010233292.aspx
Was This Post Helpful? 0
  • +
  • -

#9 noob101  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 01-November 11

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 01:58 PM

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.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9286
  • View blog
  • Posts: 34,805
  • Joined: 12-June 08

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 02:03 PM

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.
Was This Post Helpful? 0
  • +
  • -

#11 noob101  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 01-November 11

Re: ODBC Microsoft Access Driver Syntax error in Field definition

Posted 22 February 2012 - 02:13 PM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1