Hello Ben:
Thanks for the info. I think we're on the same page. I did create my stored procedure to insert a record and return the new ID value. It looks like this:
CREATE PROCEDURE INFOREQUESTINSERT (
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(20),
ADDRESS1 VARCHAR(40),
ADDRESS2 VARCHAR(40),
CITY VARCHAR(20),
STATE CHAR(2),
POSTALCODE VARCHAR(10),
COUNTRY VARCHAR(20),
COMMENTS VARCHAR(50),
PHONE VARCHAR(20),
FAX VARCHAR(20),
EMAIL VARCHAR(50),
CONTACTPREFERENCE VARCHAR(10),
HEARDABOUT VARCHAR(20),
COMPANYSIZE INTEGER
) RETURNS (
INFOREQUESTID INTEGER
) AS BEGIN
InfoRequestID = GEN_ID(INFOREQUEST,1);
Insert Into INFOREQUEST (INFOREQUESTID, FIRSTNAME, LASTNAME, ADDRESS1, ADDRESS2, CITY, STATE, POSTALCODE, COUNTRY,
COMMENTS, PHONE, FAX, EMAIL, CONTACTPREFERENCE, HEARDABOUT, COMPANYSIZE)
VALUES (:INFOREQUESTID, :FIRSTNAME, :LASTNAME, :ADDRESS1, :ADDRESS2, :CITY, :STATE, :POSTALCODE, :COUNTRY,
:COMMENTS, :PHONE, :FAX, :EMAIL, :CONTACTPREFERENCE, :HEARDABOUT, :COMPANYSIZE);
suspend;
END
However, now I'm having a problem with the ADO code. I'm getting back the following error:
System.Data.Odbc.OdbcException: ERROR [HY105] [INTERSOLV][ODBC InterBase driver]Invalid parameter type
The way the TableAdapter forms the InsertCommand is strange. My first parameter is the ID which is an output parameter. I think the TableAdapter is trying to include this parameter as the first parameter of my InsertCommand which is:
Select * from InfoRequestInsert(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
Should I just avoid the whole TableAdapter approach and build my own DataSet and DataAdapter?
""Ben Hart"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]...> ------=_Part_1620_21979854.1141673335420
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
> Content-Disposition: inline
>
>>
>> My Insert statement (defined through the use of a TableAdapter) obviously
>> does not include the id column. * My question is what mechanism do I use
>> to return the ID to the DataSet?*
>
>
> Use an output parameter with the TableAdapter InsertCommand in conjunction
> with an output parameter in a stored proc (or even in your passed sql
> statement). Your stored proc will look something like:
>
> CREATE PROCEDURE REPORTGROUPBENCHMARKINSERT (
> FK_REPORTGROUPID INTEGER,
> FK_BENCHMARKID INTEGER)
> RETURNS (
> ID INTEGER)
> AS
> begin
> ID =3D GEN_ID(GEN_REPORTGROUPBENCHMARK_ID,1);
> INSERT INTO REPORTGROUPBENCHMARK (REPORTGROUPBENCHMARKID,
> FK_REPORTGROUPID, FK_BENCHMARKID)
> VALUES (:ID, :FK_REPORTGROUPID, :FK_BENCHMARKID);
> suspend;
> end
>
> When the insert runs the ID will be returned through the parameters, and
> your dataset will be updated (just make sure that the parameter in .NET is
> correctly linked with the field.
