Configure Data Adapter Wizard Failing

J

Jeff Dillon

The Configure Data Adapter wizard works on 20 other tables in my SQL2000
database, but fails on the following with an error "There were errors
configuring the data adapter"

Granted it's an oversize table, but what are the limits? Also, can I "view"
the errors configuring the adapter?

I've tried in an VB.NET and ASP.NET app.

I can reproduce by building this table in the Pubs database, creating a SQL
connection, then going through the wizard and using the SQL statement
"Select * from NHTSAPatients". It also fails if I explicitly state each
fieldname in the SQL statement.

CREATE TABLE [dbo].[NHTSAPatients] (
[PatientID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[AID] [int] NULL ,
[UID] [int] NULL ,
[LastName] [varchar] (200) NULL ,
[FirstName] [varchar] (100) NULL ,
[MI] [varchar] (1) NULL ,
[HomeAddress] [varchar] (1000) NULL ,
[HomeCity] [varchar] (100) NULL ,
[HomeState] [varchar] (2) NULL ,
[HomeZip] [varchar] (9) NULL ,
[HomeCountry] [varchar] (10) NULL ,
[SSN] [varchar] (9) NULL ,
[DriversLicenseNumber] [varchar] (50) NULL ,
[DriversLicenseState] [varchar] (2) NULL ,
[Gender] [varchar] (1) NULL ,
[Pregnancy] [varchar] (1) NULL ,
[Race] [varchar] (1) NULL ,
[Subrace] [varchar] (200) NULL ,
[Ethnicity] [varchar] (1) NULL ,
[DOB] [datetime] NULL ,
[Age] [varchar] (3) NULL ,
[AgeUnits] [varchar] (1) NULL ,
[PrimaryPhone] [varchar] (10) NULL ,
[SecondaryPhone] [varchar] (10) NULL ,
[RGLastName] [varchar] (200) NULL ,
[RGFirstName] [varchar] (100) NULL ,
[RGMI] [varchar] (1) NULL ,
[RGAddress] [varchar] (1000) NULL ,
[RGCity] [varchar] (100) NULL ,
[RGState] [varchar] (2) NULL ,
[RGZip] [varchar] (9) NULL ,
[RGPhone] [varchar] (10) NULL ,
[RGRelationship] [varchar] (1) NULL ,
[Employer] [varchar] (200) NULL ,
[EmployerAddress] [varchar] (1000) NULL ,
[EmployerCity] [varchar] (100) NULL ,
[EmployerState] [varchar] (2) NULL ,
[EmployerZip] [varchar] (9) NULL ,
[EmployerPhone] [varchar] (10) NULL ,
[PrimaryMethodOfPayment] [varchar] (1) NULL ,
[CertificateOfMedicalNecessity] [varchar] (1) NULL ,
[ResponseUrgency] [varchar] (1) NULL ,
[WorkRelated] [varchar] (1) NULL ,
[PatientsOccupationalIndustry] [varchar] (2) NULL ,
[PatientsOccupation] [varchar] (2) NULL ,
[MassCasualtyIncident] [bit] NULL ,
[PriorAid] [varchar] (1000) NULL ,
[PriorAidPerformedBy] [varchar] (20) NULL ,
[OutcomeOfPriorAid] [varchar] (1) NULL ,
[ChiefComplaintNarrative] [text] NULL ,
[ComplaintAnatomicLocation] [varchar] (50) NULL ,
[ComplaintOrganAgency] [varchar] (2) NULL ,
[PrimarySymptom] [varchar] (2) NULL ,
[OtherAssociatedSymptoms] [varchar] (100) NULL ,
[ProvidersPrimaryImpression] [varchar] (6) NULL ,
[ProvidersSecondaryImpression] [varchar] (6) NULL ,
[BarriersToPatientCare] [varchar] (50) NULL ,
[PatientsPrimaryPractitioner] [varchar] (200) NULL ,
[AdvancedDirectives] [varchar] (50) NULL ,
[MedicationAllergies] [varchar] (1000) NULL ,
[MedicalHistoryObtainedFrom] [varchar] (1) NULL ,
[AlcoholDrugUseIndicators] [varchar] (50) NULL ,
[EstimatedBodyWeight] [varchar] (53) NULL ,
[CardiacArrest] [bit] NULL ,
[CardiacArrestEtiology] [varchar] (1) NULL ,
[ResuscitationAttempted] [varchar] (50) NULL ,
[ArrestWitnessedBy] [varchar] (1) NULL ,
[FirstMonitoredRhythm] [varchar] (1) NULL ,
[AnyReturnOfSpontaneousCirculation] [varchar] (10) NULL ,
[ETAPriorToEMSArrival] [varchar] (2) NULL ,
[DTResuscitationDiscontinued] [datetime] NULL ,
[ReasonCPRDiscontinued] [varchar] (50) NULL ,
[RhythmOnArrivalAtDestination] [varchar] (100) NULL ,
[TraumaInjuryPresent] [bit] NULL ,
[CauseOfInjury] [varchar] (6) NULL ,
[MechanismOfInjury] [varchar] (10) NULL ,
[PTSSize] [varchar] (53) NULL ,
[PTSAirway] [varchar] (53) NULL ,
[PTSSBP] [varchar] (53) NULL ,
[PTSCNS] [varchar] (53) NULL ,
[PTSSkeletalInjury] [varchar] (53) NULL ,
[PTSOpenWounds] [varchar] (53) NULL ,
[PTScore] [varchar] (53) NULL ,
[GCSEye] [varchar] (53) NULL ,
[GCSVerbal] [varchar] (53) NULL ,
[GCSMotor] [varchar] (53) NULL ,
[GCSQualifier] [varchar] (10) NULL ,
[GCSScore] [float] NULL ,
[RTSRespiratory] [float] NULL ,
[RTSBlood] [float] NULL ,
[RTSNeurological] [float] NULL ,
[RTScore] [float] NULL ,
[VehicularInjuryIndicators] [varchar] (50) NULL ,
[AreaOfVehicleImpactedByCollision] [varchar] (50) NULL ,
[SeatRowLocationOfPatientInVehicle] [varchar] (2) NULL ,
[PositionOfPatientInSeatOfVehicle] [varchar] (2) NULL ,
[UseOfOccupantSafetyEquipment] [varchar] (50) NULL ,
[AirbagDeployment] [varchar] (50) NULL ,
[DestinationTransferredToName] [varchar] (500) NULL ,
[DestinationTransferredToCode] [varchar] (500) NULL ,
[DestinationZipCode] [varchar] (25) NULL ,
[IncidentPatientDisposition] [varchar] (2) NULL ,
[TransportModeFromScene] [varchar] (1) NULL ,
[ReasonForChoosingDestination] [varchar] (2) NULL ,
[TypeOfDestination] [varchar] (2) NULL ,
[EmergencyDepartmentDisposition] [varchar] (1) NULL ,
[HospitalDisposition] [varchar] (1) NULL ,
[DestinationMedicalRecordNumber] [varchar] (100) NULL ,
[LawEnforcementCrashReportNumber] [varchar] (100) NULL ,
[FireIncidentReportNumber] [varchar] (100) NULL ,
[PatientIDBandTagNumber] [varchar] (100) NULL ,
[Narrative] [text] NULL ,
[Complete] [bit] NULL ,
[SessionID] [varchar] (50) NULL ,
[Signature] [varchar] (50) NULL ,
[PickupLocationCode] [varchar] (50) NULL ,
[ResidencyStatusCode] [varchar] (50) NULL ,
[MBIExported] [int] NULL ,
[PreexistingConditions] [varchar] (500) NULL
)
 
M

Marina

I believe there is a limitation on the number of columns the wizard can work
with that are in a single table.
You have 2 options:
1) Split up the columns into 2 tables (that should be enough), where there
is a 1 to 1 between the tables, and put the data into separate datatables in
your app
2) Stop using the wizard since it is a piece of junk anyway and write code
by hand. That doesn't mean duplicating what the wizard does, but write your
logic in a generic manner to generate the correct sql statements.


Jeff Dillon said:
The Configure Data Adapter wizard works on 20 other tables in my SQL2000
database, but fails on the following with an error "There were errors
configuring the data adapter"

Granted it's an oversize table, but what are the limits? Also, can I "view"
the errors configuring the adapter?

I've tried in an VB.NET and ASP.NET app.

I can reproduce by building this table in the Pubs database, creating a SQL
connection, then going through the wizard and using the SQL statement
"Select * from NHTSAPatients". It also fails if I explicitly state each
fieldname in the SQL statement.

CREATE TABLE [dbo].[NHTSAPatients] (
[PatientID] [int] IDENTITY (1, 1) NOT NULL ,
[CID] [int] NULL ,
[AID] [int] NULL ,
[UID] [int] NULL ,
[LastName] [varchar] (200) NULL ,
[FirstName] [varchar] (100) NULL ,
[MI] [varchar] (1) NULL ,
[HomeAddress] [varchar] (1000) NULL ,
[HomeCity] [varchar] (100) NULL ,
[HomeState] [varchar] (2) NULL ,
[HomeZip] [varchar] (9) NULL ,
[HomeCountry] [varchar] (10) NULL ,
[SSN] [varchar] (9) NULL ,
[DriversLicenseNumber] [varchar] (50) NULL ,
[DriversLicenseState] [varchar] (2) NULL ,
[Gender] [varchar] (1) NULL ,
[Pregnancy] [varchar] (1) NULL ,
[Race] [varchar] (1) NULL ,
[Subrace] [varchar] (200) NULL ,
[Ethnicity] [varchar] (1) NULL ,
[DOB] [datetime] NULL ,
[Age] [varchar] (3) NULL ,
[AgeUnits] [varchar] (1) NULL ,
[PrimaryPhone] [varchar] (10) NULL ,
[SecondaryPhone] [varchar] (10) NULL ,
[RGLastName] [varchar] (200) NULL ,
[RGFirstName] [varchar] (100) NULL ,
[RGMI] [varchar] (1) NULL ,
[RGAddress] [varchar] (1000) NULL ,
[RGCity] [varchar] (100) NULL ,
[RGState] [varchar] (2) NULL ,
[RGZip] [varchar] (9) NULL ,
[RGPhone] [varchar] (10) NULL ,
[RGRelationship] [varchar] (1) NULL ,
[Employer] [varchar] (200) NULL ,
[EmployerAddress] [varchar] (1000) NULL ,
[EmployerCity] [varchar] (100) NULL ,
[EmployerState] [varchar] (2) NULL ,
[EmployerZip] [varchar] (9) NULL ,
[EmployerPhone] [varchar] (10) NULL ,
[PrimaryMethodOfPayment] [varchar] (1) NULL ,
[CertificateOfMedicalNecessity] [varchar] (1) NULL ,
[ResponseUrgency] [varchar] (1) NULL ,
[WorkRelated] [varchar] (1) NULL ,
[PatientsOccupationalIndustry] [varchar] (2) NULL ,
[PatientsOccupation] [varchar] (2) NULL ,
[MassCasualtyIncident] [bit] NULL ,
[PriorAid] [varchar] (1000) NULL ,
[PriorAidPerformedBy] [varchar] (20) NULL ,
[OutcomeOfPriorAid] [varchar] (1) NULL ,
[ChiefComplaintNarrative] [text] NULL ,
[ComplaintAnatomicLocation] [varchar] (50) NULL ,
[ComplaintOrganAgency] [varchar] (2) NULL ,
[PrimarySymptom] [varchar] (2) NULL ,
[OtherAssociatedSymptoms] [varchar] (100) NULL ,
[ProvidersPrimaryImpression] [varchar] (6) NULL ,
[ProvidersSecondaryImpression] [varchar] (6) NULL ,
[BarriersToPatientCare] [varchar] (50) NULL ,
[PatientsPrimaryPractitioner] [varchar] (200) NULL ,
[AdvancedDirectives] [varchar] (50) NULL ,
[MedicationAllergies] [varchar] (1000) NULL ,
[MedicalHistoryObtainedFrom] [varchar] (1) NULL ,
[AlcoholDrugUseIndicators] [varchar] (50) NULL ,
[EstimatedBodyWeight] [varchar] (53) NULL ,
[CardiacArrest] [bit] NULL ,
[CardiacArrestEtiology] [varchar] (1) NULL ,
[ResuscitationAttempted] [varchar] (50) NULL ,
[ArrestWitnessedBy] [varchar] (1) NULL ,
[FirstMonitoredRhythm] [varchar] (1) NULL ,
[AnyReturnOfSpontaneousCirculation] [varchar] (10) NULL ,
[ETAPriorToEMSArrival] [varchar] (2) NULL ,
[DTResuscitationDiscontinued] [datetime] NULL ,
[ReasonCPRDiscontinued] [varchar] (50) NULL ,
[RhythmOnArrivalAtDestination] [varchar] (100) NULL ,
[TraumaInjuryPresent] [bit] NULL ,
[CauseOfInjury] [varchar] (6) NULL ,
[MechanismOfInjury] [varchar] (10) NULL ,
[PTSSize] [varchar] (53) NULL ,
[PTSAirway] [varchar] (53) NULL ,
[PTSSBP] [varchar] (53) NULL ,
[PTSCNS] [varchar] (53) NULL ,
[PTSSkeletalInjury] [varchar] (53) NULL ,
[PTSOpenWounds] [varchar] (53) NULL ,
[PTScore] [varchar] (53) NULL ,
[GCSEye] [varchar] (53) NULL ,
[GCSVerbal] [varchar] (53) NULL ,
[GCSMotor] [varchar] (53) NULL ,
[GCSQualifier] [varchar] (10) NULL ,
[GCSScore] [float] NULL ,
[RTSRespiratory] [float] NULL ,
[RTSBlood] [float] NULL ,
[RTSNeurological] [float] NULL ,
[RTScore] [float] NULL ,
[VehicularInjuryIndicators] [varchar] (50) NULL ,
[AreaOfVehicleImpactedByCollision] [varchar] (50) NULL ,
[SeatRowLocationOfPatientInVehicle] [varchar] (2) NULL ,
[PositionOfPatientInSeatOfVehicle] [varchar] (2) NULL ,
[UseOfOccupantSafetyEquipment] [varchar] (50) NULL ,
[AirbagDeployment] [varchar] (50) NULL ,
[DestinationTransferredToName] [varchar] (500) NULL ,
[DestinationTransferredToCode] [varchar] (500) NULL ,
[DestinationZipCode] [varchar] (25) NULL ,
[IncidentPatientDisposition] [varchar] (2) NULL ,
[TransportModeFromScene] [varchar] (1) NULL ,
[ReasonForChoosingDestination] [varchar] (2) NULL ,
[TypeOfDestination] [varchar] (2) NULL ,
[EmergencyDepartmentDisposition] [varchar] (1) NULL ,
[HospitalDisposition] [varchar] (1) NULL ,
[DestinationMedicalRecordNumber] [varchar] (100) NULL ,
[LawEnforcementCrashReportNumber] [varchar] (100) NULL ,
[FireIncidentReportNumber] [varchar] (100) NULL ,
[PatientIDBandTagNumber] [varchar] (100) NULL ,
[Narrative] [text] NULL ,
[Complete] [bit] NULL ,
[SessionID] [varchar] (50) NULL ,
[Signature] [varchar] (50) NULL ,
[PickupLocationCode] [varchar] (50) NULL ,
[ResidencyStatusCode] [varchar] (50) NULL ,
[MBIExported] [int] NULL ,
[PreexistingConditions] [varchar] (500) NULL
)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why am I getting ForeignKeyConstraint requires child key value error? 12
System.Data.Common.DbDataAdapter Bug. 1
expects parameter ??? , which was not supplied." 2
Typed Dataset with Update SPROC 5
returning data from a Stored Procedure 2
Transaction deadlock on inserting into a table 49
Excel/SQL Query Quandry 3
Pass-through Query to MS SQL dB 4

Top