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

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 !

Answer 1

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...

Answer 2

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>

Answer 3

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....

Answer 4

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

Answer 5

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))" 

Answer 6

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

Answer 7

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