Main data query retriveing record count

[Kevin Changet]

IP 2003, Jscript, Custom Submit code, Main Data Connection connected to 1
simple SQL Server table. Table has two columns, DI_NUM and SHIP_SD_NUM,
SHIP_SD_NUM is the Primary Key.

On my custom submit, I'd like to query the SQL Table and see if the Primary
key arleady exists in the table by adding a a condition like "Where
SHIP_SD_NUM = '123456'". If so, throw up an error message, if not, process
the submit.

I'm having problems getting IP to let me know if the primary key already
exists in the table. I would think this is possible, and am maybe missing
something obvious, but am pretty much stuck.

Code snippets as follows:
var origCommand = XDocument.DataAdapters["Main connection"].Command;
var objMainDataAdapter = XDocument.DataAdapters["Main connection"];
var strSHIP_SD_NUM = "123456";
objMainDataAdapter.Command = "select count(*) as Expr1 from CUSTOMER_PROFILE
where SHIP_SD_NUM = '" + strSHIP_SD_NUM + "'";

What I've tried:
1) ? objMainDataAdapter.Command
"select count(*) as Expr1 from CUSTOMER_PROFILE where SHIP_SD_NUM = '123456'"
? objMainDataAdapter.Query()
The query method on the Document object failed.
InfoPath cannot run the specified query. No query fields have been
generated because the specified SQL statement is too complex for InfoPath to
parse.

1b) I get the feeling that IP doesn't like the "count(*)" portion and that
my XML Schema isn't set up to accept that...ok...change tactics to return a
row of data.

2) objMainDataAdapter.Command = "select \"DI_NUM\", \"SHIP_SD_NUM\" from
\"dbo\".\"CUSTOMER_PROFILE\" where \"SHIP_SD_NUM\" = \'123456\'"

? objMainDataAdapter.Query()
undefined

Any suggestions appreciated, I'm open to any solution (short of creating a
secondary data connection/web service as I know how to pursue these avenues
already).

 

[Clay Fox]

Hey Kevin.

How are you creating new Primary Key values?

Usually you can just check your new kay value by equating it across the
whole record set to find if it is a duplicate or not.
To me the challenge is always if multiple users are using the form and how
to keep them from duplicating.
I often had a secondary which would fire right before submit to get the
latest from the DB.
Since I have been using the Database Accelerator, I have not had to worry
about it as it handles whether it is an update or new record within the
webservice.
http://www.qdabra.com
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com