Automatic generation of SQL commands using a data adapter

[JJ]

Hi All,

is there a limit to the size of an update or delete command that
a data adapter will automatically create. I have just added another
varchar field to my table and when I attempt to now add the modified
data adapter to my form the wizard informs me that it can not
generate the code for the update and delete commands. If I
remove the newly added field and attempt to add the new
data adapter (after removing the existing one) then everything
appears to work fine. There is nothing special about the table,
or the newly added field.

Any ideas, or do I know have to create all of the sql commands
by hand.

I tried to add a da from the data tool box (instead of dragging
the table from the database) and get the wizard to generate
stored procedures as well, but this came back with the same error.

The SP for the SELECT command is


ALTER PROCEDURE dbo.usp_SelectComplaint
AS
SET NOCOUNT ON;
SELECT pID, pcID, pDate, pDetails, pOriginator, pStat FROM
tblComplaint


So as you can see it's not too taxing.

The insert command can be generated. The SP for that is


ALTER PROCEDURE dbo.usp_InsertComplaint
(
@pcID int,
@pDate datetime,
@pDetails varchar(250),
@pOriginator varchar(50),
@pStat char(10)
)
AS
SET NOCOUNT OFF;
INSERT INTO tblComplaint(pcID, pDate, pDetails, pOriginator, pStat)
VALUES (@pcID, @pDate, @pDetails, @pOriginator, @pStat);
SELECT pID, pcID, pDate, pDetails, pOriginator, pStat FROM
tblComplaint WHERE (pID = @@IDENTITY)


Which, once again is reasonably straight forward.

Thanks for the help.

Jason.

 

[William Ryan eMVP]

I don't know if there's a limit, but if there is its a lot more than 5
fields. I created a 4 field table witht he same names as you and then added
another one. It's handling it fine.

is the *only* difference between the two commands the one field?

 

[JJ]

Yeah, with all fields except pStat it works. When I add pStat,
and I have tried several difference field types it fails to auto-
generate. With it excluded all is fine.

I even create a blank windows app. and imported just the
data adapters for the two tables and it didn't want tp
import the DA with this definition.

Thanks for the help so far.

Jason.

 

[William Ryan eMVP]

What two tables, I may be confused? Anyway, if you use just standard sql
statement are you good? I'm going to try to recreate it using the proc defs
you listed.

I'll post back shortly.

 

[JJ]

Both standard SQL and SP generate the same error.

BTW, sorry, my DB has two tables linked with a relationship that is
one-to-many. The parent table imports fine but when I change
the child table to include the new field I encounter the problem.

I am going to re-create the the DB with the same specification
but no data and see what happens. If you have any thoughts
in the mean time I would be please to heat them.

Thanks,
Jason.

 

[JJ]

I re-created the database structure with no data. I added the
connection and data adapters to the project and all the SQL
statements were created just fine.

I am not sure what was wrong with the previous version. It doesn't
really matter, but it would be nice to know in case in happened
again.

Do you have any ideas?

Thanks,

Jason.

 

[Bernie Yaeger]

Hi JJ,

The original .xsd that was created was probably not recognizing the
additional column until you completely rebuild the data adapter.

Bernie Yaeger

 

[JJ]

I deleted all trace of the data adapters (I think), including the
dataset and added all of the tables from stratch to see
if that was the problem. Nothing seemed to work. I even
checked the Windows Form Generated Code region to see if
it hadn't been cleaned out right but there didn't seem to be any trace
on the previous dataadapters. I also manually removed
the schema xsd file as well.

It must have been something though. If any one has any other
suggestions I be pleased to hear them.

Thanks everyone.

Jason.