Suntower
Posts: 225
|
| Posted: 08/22/2006, 3:22 PM |
|
I've still can't figure out how to get the following to work
cW = 'sometext'
nH = 4567
dW = 45.34
INSERT INTO Specs (whatever,however,wherever) VALUES('sometext', 4567, 45.34)
I create a CUSTOM INSERT of type SQL
I type in the top window:
INSERT INTO Specs (whatever,however,wherever) VALUES('{cW}',{nH},{dW}) WHERE TransactionID={TRANSID}
I then create 2 parameters which refer to controls on the form and one which is global variable I create at the top of the form and one for the 'where'
Variable Parameter Type
---------------------------------
TransID TRANSID Int
cW CWHATEVER control Text
nH NHOWEVER control Int
dW DWHEREVER expression Float
This always gives a SQL Server syntax error.
So I inserted a response.write just before the insert executes and it seems as though the 'parameter substitution' isn't happening EXCEPT in the where clause. IOW: what seems to happen is that the {} aren't being replaced by the parameter -values-
How come? I -did- get it to work for a -where- clause on another page. Is it only for where clauses? ?????
Yo Yessoftware! THIS IS ONE OF THOSE KEY CONCEPT THINGS WHERE AN -EXAMPLE- WOULD BE GREAT! (Sorry for shouting, but this is ridiculous trying to figure out.)
Thanks in advance,
---JC
_________________
---On a campaign for more examples and better docs! |
 |
 |
wkempees
Posts: 1679
|
| Posted: 08/22/2006, 3:58 PM |
|
SQL Server syntax error.
INSERT does not support a WHERE caluse.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Suntower
Posts: 225
|
| Posted: 08/22/2006, 5:58 PM |
|
Yes I know. In my frustration I'm using shorthand. I just want to know if there are limits to the parameter substitution. Again, shouldn't I be able to use -any- SQL expression I like, right or wrong, and have everything within braces be substituted.
Shouldn't I be able to have an expression like
DROP UNDERWEAR small, medium large INTO VALUE({whatever},{whenever}, however})
...and have the parameters substituted. I must be doing something wrong. PPPPPPPPLLLLLLLLLLLLLLLLLLLLLLLLLEASE can -someone- give me a specific example?
---JC
Quote wkempees:
SQL Server syntax error.
INSERT does not support a WHERE caluse.
_________________
---On a campaign for more examples and better docs! |
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 08/23/2006, 5:46 PM |
|
Suntower
So are you trying to add a record or update a record?? Can we see the actual code that you are using?? What is the environment that you are programming for??
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/24/2006, 4:07 AM |
|
Suntower,
A custom insert on fo instance an editableGrid:
(This is PhP/MySQL)
First I generated an EditableGrid on the Intranet Example table products
That table I added a field mod_date type DateTime to.
The goal of this explanation is to simply add today's date time to the field using a Custom Insert.
So step-by-step:
In Design, NewPage, add an editableGrid on table Products from the CCSIntranet database.
Accept all defaults as we are only interested in CustomInsert.
Publish and test, that should ADD records straight out of the box but the mod_date field will be NULL.
So back to Design
Click in the Grid to open the Properties.
CustomInsertType, set it to Table
CustomInsert, initialy blank, press [...]
now the CustomInsert Dialog will pop-up.
For the moment we have Custom Insert Type = Table.
As you look at the screen it has the Tablename and the Parameters.
clicking the Parameters + - and edit are self-expl.
Press the + to add our mod_date
WoW(Word-of-Warning) in Table mode and in SQL mode the parameter details window is mirrored meaning the left and the right part interchange.
In the Custom Insert Parameter Dialog I enter:
Field: mod_date, Type: Date, Format: yyyy-mm-dd HH:nn:s
Parameter: time(), Type: Expression, Format:Blank
Default Value I left empty, but I suspect that entering time() would also be correct (test it)
Press OK twice to go back to design.
Now to be absolutely sure, and to T&E (Trial&Error) I put an
echo "SQL ". $Component->ds->SQL in the Before ExecuteInsert Event.
This will show me the actual SQL goin to my DB.
(remember PhP/MySQL)
This works for me, hope it does for you too.
The Type SQL version is not that different except for the WoW.
When you have this example working, go back to the CustomInsert and simply change type Table to Type SQL, you can then easily see what is what.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Suntower
Posts: 225
|
| Posted: 08/24/2006, 10:35 AM |
|
Hi Walter,
Don't have time to go through this now, but I just wanted to say CHEERS, MATE! for taking the time.
Back with my results ASAP.
You da man.
---JC
_________________
---On a campaign for more examples and better docs! |
|
|
|
WKempees
|
| Posted: 08/24/2006, 4:55 PM |
|
Suntower,
Quote :
Yes I know. In my frustration I'm using shorthand. I just want to know if
there
are limits to the parameter substitution. Again, shouldn't I be able to use
-any- SQL expression I like, right or wrong, and have everything within
braces
be substituted.
Shouldn't I be able to have an expression like
DROP UNDERWEAR small, medium large INTO VALUE({whatever},{whenever},
however})
Shorthand and thinking the way you express here, brought me nothing than
trouble!
The next bit of coding generated error "String not found"
$success = lower(g_string);
Walter
|
|
|
|
Suntower
Posts: 225
|
| Posted: 08/24/2006, 8:50 PM |
|
My point was that the parameterization should happen -regardless of the content of the expression. Hence the silly example. I am still unsure as to why -sometimes- it works and -sometimes- it doesn't.
I'm working around it with a slower executing solution as I -have- to get this boat in the water.
But I'll be back when it's time to optimise this thing.
---JC
_________________
---On a campaign for more examples and better docs! |
|
|
|
|
