| Create Table in VB [message #159048] |
Wed, 15 February 2006 17:51  |
jayoung
Messages: 21
Registered: October 2005
Location: USA
|
Junior Member |
|
|
I know this is probably not the right forum for this but i am trying to create a table from inside VB and i keep getting an error thrown ORA-000922 Missing or Invalid option and i just wanted to know if i was over looking something. is it possible to create the constraints also?
string -- txtUID.text is the user name being created
string -- BwYear is the current 4 year digit ie 2006
string -- cy is the current years last 2 digits ie: 06
so the table name being created should be ie: jayweevil2006
constraints should come out - CONSTRAINT trappers_id_pk_jay06
OraDatabase.DBExecuteSql ("create table " & txtUID.Text & ".weevil & " & BwYear & ".trappers " & _
"(ID varchar2(15) " & _
"CONSTRAINT trappers_id_pk_" & txtUID.Text & Cy & " PRIMARY KEY, " & _
"name varchar2(30) " & _
"CONSTRAINT trappers_name_nn_" & txtUID.Text & Cy & " NOT NULL, " & _
"fndcontractor Number(1) " & _
"CONSTRAINT trappers_fnd_nn_" & txtUID.Text & Cy & " NOT NULL) " & _
"PctFree 10 PctUsed 80 " & _
"default Tablespace USER_Weevil_data_2005;")
Sorry if this is the wrong forum for this 
Thanks Jay
|
|
|
|
| Re: Create Table in VB [message #159058 is a reply to message #159048] |
Wed, 15 February 2006 20:21  |
kmmfoo
Messages: 38
Registered: June 2005
Location: massachusetts
|
Member |
|
|
Hi --
You're right, this isn't the right forum to ask this question... but what the heck.
The first key to debugging a problem like this is to understand what command is being executed. The way you do that is generally to print out the entire text of the runtime command to some debugging venue -- in VB Design mode you would just debug.print, or in some other environment you could msgbox it, or write it out somehow. Whatever, the trick is to just be able to LOOK at the command.
I copied a version of your command into my own vb runtime, and set up the replaceable variables to look exactly like you said yours should have been... here's what the command looked like when I displayed it:
create table jay.weevil & 06.trappers (ID varchar2(15) CONSTRAINT trappers_id_pk_weevil06 PRIMARY KEY, name varchar2(30) CONSTRAINT trappers_name_nn_weevil06 NOT NULL, fndcontractor Number(1) CONSTRAINT trappers_fnd_nn_weevil06 NOT NULL) PctFree 10 PctUsed 80 default Tablespace USER_Weevil_data_2005;
Other than the run-together spacing (there are no newlines in your command) the first thing that jumps out at me is that you've got a spurious ampersand stuck in the middel of the table name.
Is that for real? Or just an artifact of the process of posting to this forum? Dunno, but I'd suggest you first fix that problem, then try to debug the problem further using this approach as a model.
Hope this helps!
(By the way, to insert newlines into your command, you would concat vbCrlf into the command.)
|
|
|
|
| Re: Create Table in VB [message #159062 is a reply to message #159048] |
Wed, 15 February 2006 21:44 |
jayoung
Messages: 21
Registered: October 2005
Location: USA
|
Junior Member |
|
|
well after trying it with just the static info in the create table like you suggested i get another error ORA-02253 Constraints not allowed here..
I was just trying to continue the lines not really trying to insert new lines just trying to make it easy for me to read....
Anyway
Thanks
[Updated on: Wed, 15 February 2006 21:47] Report message to a moderator |
|
|
|
| Re: Create Table in VB [message #159153 is a reply to message #159062] |
Thu, 16 February 2006 07:27 |
kmmfoo
Messages: 38
Registered: June 2005
Location: massachusetts
|
Member |
|
|
Well, frankly I'm not an expert on the syntax of the Oracle "CREATE TABLE" command, but in general if you can't execute a command in SQL-plus you can't execute it in vb.
So I'd suggest you go back to checking the syntax of your command. In fact I _was_ able to execute a slightly-modified version of your CREATE TABLE (the only mods I made were to use my own usernames and tablespace names).
create table weevil06trappers
(
ID varchar2(15) CONSTRAINT trappers_id_pk_weevil06 PRIMARY KEY,
name varchar2(30) CONSTRAINT trappers_name_nn_weevil06 NOT NULL,
fndcontractor Number(1) CONSTRAINT trappers_fnd_nn_weevil06 NOT NULL
)
PctFree 10
PctUsed 80
I ran into one problem... you had the keyword "default" after the PctUsed entry, the syntax parser barfed on that (it said ORA-922 missing or invalid option, actually).
So basically I'd say go back to square one and validate the syntax of the command you're trying to execute, and be sure you know what you're executing before you execute it.
HTH...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
