Re: Automatic generation of SQL commands using a data adapter

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: JJ (jason_at_nospam.divemaster.org)
Date: 05/01/04 

Date: Sun, 02 May 2004 00:28:42 +0100

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.

On Sun, 02 May 2004 00:10:41 +0100, JJ <jason@nospam.divemaster.org>
wrote:

>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.
>
>On Sat, 1 May 2004 13:57:05 -0400, "William Ryan eMVP"
><dotnetguru@comcast.nospam.net> wrote:
>
>>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" <jason@nospam.divemaster.org> wrote in message
>>news:6qo790ti90hb39jqi505vtueo0q2a92pnf@4ax.com...
>>> 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.
>>>
>>> On Sat, 1 May 2004 13:41:58 -0400, "William Ryan eMVP"
>>> <dotnetguru@comcast.nospam.net> wrote:
>>>
>>> >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" <jason@nospam.divemaster.org> wrote in message
>>> >news:ffn790dvno1q0ff4dqcnb1u6vqjvhsdjpt@4ax.com...
>>> >> 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.
>>> >
>>>
>>

Relevant Pages

  • Re: Bit Datatype - Solved
    ... I suspect I know what I did. ... I have another table called Login with a key LoginID - no nulls ... The only way I can re-create the error is doing a stupid relationship to ... MS SQL Server 2005? ...
    (microsoft.public.dotnet.languages.vb)
  • Enterprise Manager Registration
    ... I have an SQL Serverregistration. ... Now I cannot re-create the registration using: ... I re-put the old passwordand the problem persist. ... I made a successful registration using another login name and password but not for 'sa'. ...
    (microsoft.public.sqlserver.server)
  • Re: Upgrading and Moving
    ... is it better to completely re-create the Merge Publications after ... I am pretty sure we will have to re-create ... our laptop subscriptions as they will be moving to SQL 2008 Express. ...
    (microsoft.public.sqlserver.replication)
  • Re: How to: Import Primary Key from SQL to Access
    ... You can use SQL or DAO commands to re-create those indexes, ... HTH ...
    (microsoft.public.access.gettingstarted)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)