Search code examples
vb.netdatasettableadapter

TableAdapter: Number of query values and destination fields are not the same

I have been scratching my head about why this TableAdapter is giving me the error Number of query values and destination fields are not the same when calling the Update command for a couple days now. It looks good to me, with 5 parameters everywhere except where previously spotted in the comments, but obviously there is something I am missing.

TableAdapter

Query Builder

Parameters

Table in Database

Here's the relevant portions of the xml backing it:

    <TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="UomListTableAdapter" GeneratorDataComponentClassName="UomListTableAdapter" Name="UomList" UserDataComponentName="UomListTableAdapter">
            <MainSource>
              <DbSource ConnectionRef="DataConnectionString (MySettings)" DbObjectName="UomList" DbObjectType="Table" FillMethodModifier="Public" FillMethodName="Fill" GenerateMethods="Both" GenerateShortCommands="true" GeneratorGetMethodName="GetData" GeneratorSourceName="Fill" GetMethodModifier="Public" GetMethodName="GetData" QueryType="Rowset" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="true" UserGetMethodName="GetData" UserSourceName="Fill">
                <DeleteCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>DELETE FROM UomList
WHERE        (SetID = ?) AND (Name = ?)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="true" AutogeneratedName="Param1" ColumnName="" DataSourceName="" DataTypeServer="unknown" DbType="String" Direction="Input" ParameterName="SetID" Precision="0" ProviderType="VarWChar" Scale="0" Size="50" SourceColumn="SetID" SourceColumnNullMapping="false" SourceVersion="Original" />
                      <Parameter AllowDbNull="false" AutogeneratedName="Param2" ColumnName="" DataSourceName="" DataTypeServer="unknown" DbType="String" Direction="Input" ParameterName="Name" Precision="0" ProviderType="VarWChar" Scale="0" Size="31" SourceColumn="Name" SourceColumnNullMapping="false" SourceVersion="Original" />
                    </Parameters>
                  </DbCommand>
                </DeleteCommand>
                <InsertCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>INSERT INTO UomList
                         (SetID, Name, Abbr, Qty, IsBase)
VALUES        (?, ?, ?, ?)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="true" AutogeneratedName="Param3" ColumnName="SetID" DataSourceName="" DataTypeServer="unknown" DbType="String" Direction="Input" ParameterName="SetID" Precision="0" ProviderType="VarWChar" Scale="0" Size="50" SourceColumn="SetID" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="Param4" ColumnName="Name" DataSourceName="" DataTypeServer="unknown" DbType="String" Direction="Input" ParameterName="Name" Precision="0" ProviderType="VarWChar" Scale="0" Size="31" SourceColumn="Name" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="Param5" ColumnName="Abbr" DataSourceName="" DataTypeServer="unknown" DbType="String" Direction="Input" ParameterName="Abbr" Precision="0" ProviderType="VarWChar" Scale="0" Size="31" SourceColumn="Abbr" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="Param6" ColumnName="Qty" DataSourceName="" DataTypeServer="unknown" DbType="Double" Direction="Input" ParameterName="Qty" Precision="0" ProviderType="Double" Scale="0" Size="0" SourceColumn="Qty" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="Param1" ColumnName="IsBase" DataSourceName="" DataTypeServer="unknown" DbType="Boolean" Direction="Input" ParameterName="IsBase" Precision="0" ProviderType="Boolean" Scale="0" Size="0" SourceColumn="IsBase" SourceColumnNullMapping="false" SourceVersion="Current" />
                    </Parameters>
                  </DbCommand>
                </InsertCommand>
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>SELECT        SetID, Name, Abbr, Qty, IsBase
FROM            UomList</CommandText>
                    <Parameters />
                  </DbCommand>
                </SelectCommand>
                <UpdateCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>UPDATE       UomList
SET                Abbr = ?, Qty = ?, IsBase = ?
WHERE        (SetID = ?) AND (Name = ?)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="false" AutogeneratedName="Param1" ColumnName="Abbr" DataSourceName="" DataTypeServer="unknown" DbType="String" Direction="Input" ParameterName="Abbr" Precision="0" ProviderType="VarWChar" Scale="0" Size="31" SourceColumn="Abbr" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="Param2" ColumnName="Qty" DataSourceName="" DataTypeServer="unknown" DbType="Double" Direction="Input" ParameterName="Qty" Precision="0" ProviderType="Double" Scale="0" Size="0" SourceColumn="Qty" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="false" AutogeneratedName="Param3" ColumnName="IsBase" DataSourceName="" DataTypeServer="unknown" DbType="Boolean" Direction="Input" ParameterName="IsBase" Precision="0" ProviderType="Boolean" Scale="0" Size="0" SourceColumn="IsBase" SourceColumnNullMapping="false" SourceVersion="Current" />
                      <Parameter AllowDbNull="true" AutogeneratedName="Param4" ColumnName="SetID" DataSourceName="" DataTypeServer="unknown" DbType="String" Direction="Input" ParameterName="Original_SetID" Precision="0" ProviderType="VarWChar" Scale="0" Size="50" SourceColumn="SetID" SourceColumnNullMapping="false" SourceVersion="Original" />
                      <Parameter AllowDbNull="false" AutogeneratedName="Param5" ColumnName="Name" DataSourceName="" DataTypeServer="unknown" DbType="String" Direction="Input" ParameterName="Original_Name" Precision="0" ProviderType="VarWChar" Scale="0" Size="31" SourceColumn="Name" SourceColumnNullMapping="false" SourceVersion="Original" />
                    </Parameters>
                  </DbCommand>
                </UpdateCommand>
              </DbSource>
            </MainSource>
            <Mappings>
              <Mapping SourceColumn="SetID" DataSetColumn="SetID" />
              <Mapping SourceColumn="Name" DataSetColumn="Name" />
              <Mapping SourceColumn="Abbr" DataSetColumn="Abbr" />
              <Mapping SourceColumn="Qty" DataSetColumn="Qty" />
              <Mapping SourceColumn="IsBase" DataSetColumn="IsBase" />
            </Mappings>
            <Sources />
          </TableAdapter>



        <xs:element name="UomList" msprop:Generator_UserTableName="UomList" msprop:Generator_RowDeletedName="UomListRowDeleted" msprop:Generator_RowChangedName="UomListRowChanged" msprop:Generator_RowClassName="UomListRow" msprop:Generator_RowChangingName="UomListRowChanging" msprop:Generator_RowEvArgName="UomListRowChangeEvent" msprop:Generator_RowEvHandlerName="UomListRowChangeEventHandler" msprop:Generator_TableClassName="UomListDataTable" msprop:Generator_TableVarName="tableUomList" msprop:Generator_RowDeletingName="UomListRowDeleting" msprop:Generator_TablePropName="UomList">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="SetID" msprop:Generator_UserColumnName="SetID" msprop:Generator_ColumnPropNameInRow="SetID" msprop:Generator_ColumnVarNameInTable="columnSetID" msprop:Generator_ColumnPropNameInTable="SetIDColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="50" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="Name" msprop:Generator_UserColumnName="Name" msprop:Generator_ColumnPropNameInRow="Name" msprop:Generator_ColumnVarNameInTable="columnName" msprop:Generator_ColumnPropNameInTable="NameColumn">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="31" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="Abbr" msprop:Generator_UserColumnName="Abbr" msprop:Generator_ColumnPropNameInRow="Abbr" msprop:Generator_ColumnVarNameInTable="columnAbbr" msprop:Generator_ColumnPropNameInTable="AbbrColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="31" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="Qty" msprop:Generator_UserColumnName="Qty" msprop:Generator_ColumnPropNameInRow="Qty" msprop:Generator_ColumnVarNameInTable="columnQty" msprop:Generator_ColumnPropNameInTable="QtyColumn" type="xs:double" minOccurs="0" />
              <xs:element name="IsBase" msprop:Generator_UserColumnName="IsBase" msprop:Generator_ColumnPropNameInRow="IsBase" msprop:Generator_ColumnVarNameInTable="columnIsBase" msprop:Generator_ColumnPropNameInTable="IsBaseColumn" type="xs:boolean" minOccurs="0" />
              <xs:element name="ListDisplay" msdata:ReadOnly="true" msdata:Expression="IIF(SetID = '', Name, Name + '     ' + Qty)" msprop:Generator_UserColumnName="ListDisplay" msprop:Generator_ColumnPropNameInRow="ListDisplay" msprop:Generator_ColumnVarNameInTable="columnListDisplay" msprop:Generator_ColumnPropNameInTable="ListDisplayColumn" type="xs:string" minOccurs="0" />
              <xs:element name="SelDisplay" msdata:ReadOnly="true" msdata:Expression="IIF(SetID = '', Qty, Abbr + '(' + Qty + ')')" msprop:Generator_UserColumnName="SelDisplay" msprop:Generator_ColumnPropNameInRow="SelDisplay" msprop:Generator_ColumnVarNameInTable="columnSelDisplay" msprop:Generator_ColumnPropNameInTable="SelDisplayColumn" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>



    <xs:unique name="UomKey1" msdata:PrimaryKey="true">
      <xs:selector xpath=".//mstns:UomList" />
      <xs:field xpath="mstns:SetID" />
      <xs:field xpath="mstns:Name" />
    </xs:unique>

Where is the mismatch in number of parameters?

Solution

  • Reviewing the XML it seems the Insert command seems to have 4 question marks and 5 parameters. 

    <InsertCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="true">
                    <CommandText>INSERT INTO UomList (SetID, Name, Abbr, Qty, IsBase) VALUES (?, ?, ?, ?)
                    </CommandText>

    The Error referencing "Update" is probably about TableAdapter.Update not necessarily the Update command. The TableAdapter.Update is calling the Insert, Update, and Delete commands as needed depending on the state of the rows in the DataTable.