HELP!! - dbSilentCmdText - HELP!!

[Garth Tuck]

When I execute the following code:

NewNum = Incrementer("ContestID&#34

SQL1 = "(INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES ("&Incrementer("ContestFieldID&#34&","&NewNum&",0,'NumOfShipments&#3 9;,1,1,0,0);INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES ("&Incrementer("ContestFieldID&#34&","&NewNum&",0,'AverageMarginPerS hipment',2,1,0,0);INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES ("&Incrementer("ContestFieldID&#34&","&NewNum&",0,'TotalMargin', 3,1,0,0;INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES ("&Incrementer("ContestFieldID&#34&","&NewNum&",0,'TotalMarginByFive ',4,1,0,0;"

X.dbSilentCmdText = SQL1

I get this error:

Error # (Execute_Silent_Command) = 80040E14(-2147217900)
Description = [Microsoft][ODBC Visual FoxPro Driver]Syntax error.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 200

Please help.

[Mark]

Garth,
You MUST specify the delimiters. See doc at:
http://www.aspdb.com/docs/aspdbman.htm#dbSilentCmdText
So, use something like this:
SQL1 = "(;~)INSERT INTO ..."
Otherwise the comma gets used as a delimiter and you need to use commas INSIDE your SQL statement. So use a tilde (~) instead.

Also, I suggest you add a statement like this, just after the SQL1= statment:

Response.Write(&#34;SQL1=&#34; & SQL1 & &#34;<P>&#34

Mark.

------------
Garth Tuck at 12/5/00 12:32:28 PM

When I execute the following code:

NewNum = Incrementer(&#34;ContestID&#34

SQL1 = &#34;(INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES (&#34;&Incrementer(&#34;ContestFieldID&#34&&#34;,&#34;&NewNum&&#34;,0,&#39;NumOfShipments&#3 9;,1,1,0,0);INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES (&#34;&Incrementer(&#34;ContestFieldID&#34&&#34;,&#34;&NewNum&&#34;,0,&#39;AverageMarginPerS hipment&#39;,2,1,0,0);INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES (&#34;&Incrementer(&#34;ContestFieldID&#34&&#34;,&#34;&NewNum&&#34;,0,&#39;TotalMargin&#39;, 3,1,0,0;INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES (&#34;&Incrementer(&#34;ContestFieldID&#34&&#34;,&#34;&NewNum&&#34;,0,&#39;TotalMarginByFive &#39;,4,1,0,0;&#34;

X.dbSilentCmdText = SQL1

I get this error:

Error # (Execute_Silent_Command) = 80040E14(-2147217900)
Description = [Microsoft][ODBC Visual FoxPro Driver]Syntax error.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 200

Please help.

[Garth Tuck]

Mark,

Thanks for you help. That worked, I also discovered some other syntax errors in my code.

My new question is:

What I&#39;m trying to do is create a set of related records (contestfields) in another table when a person submits a record for the display table (contests).

I only need to execute the X.dbSilentCmdText referred to earlier when a person clicks the submit button on an &#39;ADD&#39; form.

Any suggestions would be greatly appreciated.

Thanks,

Garth





------------
Mark at 12/5/00 1:44:19 PM

Garth,
You MUST specify the delimiters. See doc at:
http://www.aspdb.com/docs/aspdbman.htm#dbSilentCmdText
So, use something like this:
SQL1 = &#34;(;~)INSERT INTO ...&#34;
Otherwise the comma gets used as a delimiter and you need to use commas INSIDE your SQL statement. So use a tilde (~) instead.

Also, I suggest you add a statement like this, just after the SQL1= statment:

Response.Write(&#34;SQL1=&#34; & SQL1 & &#34;<P>&#34

Mark.

------------
Garth Tuck at 12/5/00 12:32:28 PM

When I execute the following code:

NewNum = Incrementer(&#34;ContestID&#34

SQL1 = &#34;(INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES (&#34;&Incrementer(&#34;ContestFieldID&#34&&#34;,&#34;&NewNum&&#34;,0,&#39;NumOfShipments&#3 9;,1,1,0,0);INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES (&#34;&Incrementer(&#34;ContestFieldID&#34&&#34;,&#34;&NewNum&&#34;,0,&#39;AverageMarginPerS hipment&#39;,2,1,0,0);INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES (&#34;&Incrementer(&#34;ContestFieldID&#34&&#34;,&#34;&NewNum&&#34;,0,&#39;TotalMargin&#39;, 3,1,0,0;INSERT INTO contestfields ( contestfieldid, contestid, combofieldname, fieldname, fieldnumber, fieldtype, tablename, databaseconnect ) VALUES (&#34;&Incrementer(&#34;ContestFieldID&#34&&#34;,&#34;&NewNum&&#34;,0,&#39;TotalMarginByFive &#39;,4,1,0,0;&#34;

X.dbSilentCmdText = SQL1

I get this error:

Error # (Execute_Silent_Command) = 80040E14(-2147217900)
Description = [Microsoft][ODBC Visual FoxPro Driver]Syntax error.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
Native Error = 200

Please help.