locked
Help Needed with 'No value given for one or more required parameters' RRS feed

  • Question

  • User-1065726372 posted

    Hi All,


    I am building a basic data driven web site using Visual Web Developer 2008 Express. 

    I have created a simple Access Database and can retrieve and display the data OK. 

    I have dropped a data Grid View into web page with the Update and Delete links enabled. However, when I click on one of these links I get the error. 

    The Access database contains 1 table with the following parameters,

    Table Name - NewsFlash

    ID - Int32

    headline - String

    datePublished - DateTime

    addedByUser - String

    The ASP code automatically written is as follows;

    <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            ConflictDetection="CompareAllValues" DataFile="~/App_Data/preschoolWebData.mdb" 
            DeleteCommand="DELETE FROM [NewsFlash] WHERE [ID] = ? AND (([headline] = ?) OR ([headline] IS NULL AND ? IS NULL)) AND (([datePublished] = ?) OR ([datePublished] IS NULL AND ? IS NULL)) AND (([addedByUser] = ?) OR ([addedByUser] IS NULL AND ? IS NULL))" 
            InsertCommand="INSERT INTO [NewsFlash] ([ID], [headline], [datePublished], [addedByUser]) VALUES (?, ?, ?, ?)" 
            OldValuesParameterFormatString="original_{0}" 
            SelectCommand="SELECT * FROM [NewsFlash]" 
            UpdateCommand="UPDATE [NewsFlash] SET [headline] = ?, [datePublished] = ?, [addedByUser] = ? WHERE [ID] = ? AND (([headline] = ?) OR ([headline] IS NULL AND ? IS NULL)) AND (([datePublished] = ?) OR ([datePublished] IS NULL AND ? IS NULL)) AND (([addedByUser] = ?) OR ([addedByUser] IS NULL AND ? IS NULL))">
            <DeleteParameters>
                <asp:Parameter Name="original_ID" Type="Int32" />
                <asp:Parameter Name="original_headline" Type="String" />
                <asp:Parameter Name="original_datePublished" Type="DateTime" />
                <asp:Parameter Name="original_addedByUser" Type="String" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="headline" Type="String" />
                <asp:Parameter Name="datePublished" Type="DateTime" />
                <asp:Parameter Name="addedByUser" Type="String" />
                <asp:Parameter Name="original_ID" Type="Int32" />
                <asp:Parameter Name="original_headline" Type="String" />
                <asp:Parameter Name="original_datePublished" Type="DateTime" />
                <asp:Parameter Name="original_addedByUser" Type="String" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="ID" Type="Int32" />
                <asp:Parameter Name="headline" Type="String" />
                <asp:Parameter Name="datePublished" Type="DateTime" />
                <asp:Parameter Name="addedByUser" Type="String" />
            </InsertParameters>
        </asp:AccessDataSource>


    I can see no reason why the Query won't work. The parameters seem to match the Table parameters fine.


    Your thoughts most welcomed please as I am absolutely stuck at the moment !


    Monday, August 16, 2010 4:36 PM

Answers

  • User-1199946673 posted

    Dorry, I didn'tlook good! The problem is that your using CompareAllValues, but you're using the same parameter name for both the new and original value, so your looking in the WHERE clause for records with the new values, which obviously doesn't exist!

    UpdateCommand="UPDATE [NewsFlash] SET [headline] = @headline, [datePublished] = @datePublished,
    [addedByUser] = @addedByUser WHERE [ID] = @ID  AND (([headline] = @original_headline) OR ([headline] IS NULL AND @original_headline IS NULL)) AND (([datePublished] = @original_datePublished) OR ([datePublished] IS NULL AND @original_datePublished IS NULL)) AND (([addedByUser] = @original_addedByUser) OR ([addedByUser] IS NULL AND @original_addedByUser IS NULL))" 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 17, 2010 4:55 AM
  • User-1065726372 posted

    All-Star,


    You are a star !


    Thank you very much for your help and it now works !


    I thought Visual Studio and all the clever tags solved these subtle issues with the SQL statements !

    Obviously not - I will be more careful in the future !


    MartRum



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 17, 2010 6:36 AM

All replies

  • User-1199946673 posted

    when using ? as parameters, each ? is interpreted as a new parameter  

    AND (([headline] = ?) OR ([headline] IS NULL AND ? IS NULL)) 
     

    How would Access know that both ? are the same parameter? The answer is, Access can't know that. The solution is very simple. Used Named parameters (like in SQL server), so Access recognizes both parameters to be the same:

    AND (([headline] = @headline) OR ([headline] IS NULL AND @headline IS NULL))

    More info on optional parameters:

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

    Also note that when parameters can be Null, you should set the CancelSelectOnNullParameter of the  (Access) Datasource to False, otherwise the command won't execute...

    Monday, August 16, 2010 5:08 PM
  • User-1065726372 posted

    Thanks hans_v.


    Problem is partly solved !


    DELETE now works with no errors and actually delets some data.

    UPDATE however does nothing - I can change the text in a field, click on UPDATE and the page returns BUT without the update having taken place to the data. No errors are reported.

    My modified code is as follows;

    <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            ConflictDetection="CompareAllValues" DataFile="~/App_Data/preschoolWebData.mdb" 
            DeleteCommand="DELETE FROM [NewsFlash] WHERE [ID] = @ID AND (([headline] = @headline) OR ([headline] IS NULL AND @headline IS NULL)) AND (([datePublished] = @datePublished) OR ([datePublished] IS NULL AND @datePublished IS NULL)) AND (([addedByUser] = @addedByUser) OR ([addedByUser] IS NULL AND @addedByUser IS NULL))" 
            InsertCommand="INSERT INTO [NewsFlash] ([ID], [headline], [datePublished], [addedByUser]) VALUES (?, ?, ?, ?)" 
            OldValuesParameterFormatString="original_{0}" 
            SelectCommand="SELECT * FROM [NewsFlash]" 
            
             UpdateCommand="UPDATE [NewsFlash] SET [headline] = @headline, [datePublished] = @datePublished, [addedByUser] = @addedByUser WHERE [ID] = @ID AND (([headline] = @headline) OR ([headline] IS NULL AND @headline IS NULL)) AND (([datePublished] = @datePublished) OR ([datePublished] IS NULL AND @datePublished IS NULL)) AND (([addedByUser] = @addedByUser) OR ([addedByUser] IS NULL AND @addedByUser IS NULL))" 
             CancelSelectOnNullParameter="False">
            <DeleteParameters>
                <asp:Parameter Name="original_ID" Type="Int32" />
                <asp:Parameter Name="original_headline" Type="String" />
                <asp:Parameter Name="original_datePublished" Type="DateTime" />
                <asp:Parameter Name="original_addedByUser" Type="String" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="headline" Type="String" />
                <asp:Parameter Name="datePublished" Type="DateTime" />
                <asp:Parameter Name="addedByUser" Type="String" />
                <asp:Parameter Name="original_ID" Type="Int32" />
                <asp:Parameter Name="original_headline" Type="String" />
                <asp:Parameter Name="original_datePublished" Type="DateTime" />
                <asp:Parameter Name="original_addedByUser" Type="String" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="ID" Type="Int32" />
                <asp:Parameter Name="headline" Type="String" />
                <asp:Parameter Name="datePublished" Type="DateTime" />
                <asp:Parameter Name="addedByUser" Type="String" />
            </InsertParameters>
        </asp:AccessDataSource>



    Monday, August 16, 2010 5:59 PM
  • User-1199946673 posted

    UPDATE however does nothing - I can change the text in a field, click on UPDATE and the page returns BUT without the update having taken place to the data. No errors are reported.
     

     

    Also very simple. OleDb Parameters are recognized by position, not by their name, so you need to add them in the same order they (first) appear in the command. So just change the order of the updateparameters....

    Tuesday, August 17, 2010 2:51 AM
  • User-1065726372 posted

    Hi All-Star,


    I'm not quite sure what you mean ? 

    All the UPDATE parameters are in the correct order and the same order as the fields in that Table.


    MartRum

    Tuesday, August 17, 2010 4:01 AM
  • User-1199946673 posted

    Dorry, I didn'tlook good! The problem is that your using CompareAllValues, but you're using the same parameter name for both the new and original value, so your looking in the WHERE clause for records with the new values, which obviously doesn't exist!

    UpdateCommand="UPDATE [NewsFlash] SET [headline] = @headline, [datePublished] = @datePublished,
    [addedByUser] = @addedByUser WHERE [ID] = @ID  AND (([headline] = @original_headline) OR ([headline] IS NULL AND @original_headline IS NULL)) AND (([datePublished] = @original_datePublished) OR ([datePublished] IS NULL AND @original_datePublished IS NULL)) AND (([addedByUser] = @original_addedByUser) OR ([addedByUser] IS NULL AND @original_addedByUser IS NULL))" 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 17, 2010 4:55 AM
  • User-1065726372 posted

    All-Star,


    You are a star !


    Thank you very much for your help and it now works !


    I thought Visual Studio and all the clever tags solved these subtle issues with the SQL statements !

    Obviously not - I will be more careful in the future !


    MartRum



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 17, 2010 6:36 AM
  • User-1199946673 posted

    I thought Visual Studio and all the clever tags solved these subtle issues with the SQL statements !
     

    With OleDb unfortuantly not....

    Your welcome Wink

    Tuesday, August 17, 2010 6:43 AM