Ch 13

adkybe · January 26th, 2014, 05:42 PM

Ok, to preface this issue, I decided that I wanted to use a Microsoft Access DB file instead of setting up the SQL server just for simplicity purposes. I've been able to follow all the examples, up until now.

I created the AddEditReview.aspx page and coded it appropriately, but the problem I run into is adding a New entry, I get a "You tried to assign the Null value to a variable that is not a Variant data type" error message. If I enable the view for the ID field and manually type in the next available ID, it works just fine. Why isn't it taking the auto-number property of the field when creating the new entry and trying to pass a "null" value?

I know this isn't exactly to what is laid out in the book, but any help or push in the right direction would be appreciated... Thanks.

Also, I have ensured that the ID field in the Access DB is set properly: It is set to autonumber and it is unique.

Imar · January 26th, 2014, 05:48 PM

Hi there,

Are you sure you set it to be an AutoNumber correctly? This:

Quote:

If I enable the view for the ID field and manually type in the next available ID, it works just fine

seems to imply it isn't.

Did you set it before you created the data controls? Can you post the code for the relevant controls on the page?

Cheers,

Imar

adkybe · January 26th, 2014, 06:02 PM

Well here is the link to a screen shot of my Access page with the ID field highlighted.

https://drive.google.com/file/d/0B11...it?usp=sharing

Here is the code for the page after creating all the controls:

Code:

<%@ Page Title="Planet Wrox - Management - Insert and Update Reviews" Language="C#" MasterPageFile="~/MasterPages/Management.master" AutoEventWireup="true" CodeFile="AddEditReview.aspx.cs" Inherits="Management_AddEditReview" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="cpMainContent" Runat="Server">
    <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="ID" DataSourceID="SqlDataSource1" DefaultMode="Insert" Height="50px" Width="125px">
        <Fields>
            <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
            <asp:BoundField DataField="Summary" HeaderText="Summary" SortExpression="Summary" />
            <asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" />
            <asp:BoundField DataField="GenreId" HeaderText="GenreId" SortExpression="GenreId" />
            <asp:CheckBoxField DataField="Authorized" HeaderText="Authorized" SortExpression="Authorized" />
            <asp:BoundField DataField="CreateDateTime" HeaderText="CreateDateTime" SortExpression="CreateDateTime" />
            <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" SortExpression="ID" />
            <asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
        </Fields>
    </asp:DetailsView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [Review] WHERE [ID] = ?" InsertCommand="INSERT INTO [Review] ([Title], [Summary], [Body], [GenreId], [Authorized], [CreateDateTime], [ID]) VALUES (?, ?, ?, ?, ?, ?, ?)" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT [Title], [Summary], [Body], [GenreId], [Authorized], [CreateDateTime], [ID] FROM [Review] WHERE ([ID] = ?)" UpdateCommand="UPDATE [Review] SET [Title] = ?, [Summary] = ?, [Body] = ?, [GenreId] = ?, [Authorized] = ?, [CreateDateTime] = ? WHERE [ID] = ?">
        <DeleteParameters>
            <asp:Parameter Name="ID" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="Title" Type="String" />
            <asp:Parameter Name="Summary" Type="String" />
            <asp:Parameter Name="Body" Type="String" />
            <asp:Parameter Name="GenreId" Type="Int32" />
            <asp:Parameter Name="Authorized" Type="Boolean" />
            <asp:Parameter Name="CreateDateTime" Type="DateTime" />
            <asp:Parameter Name="ID" Type="Int32" />
        </InsertParameters>
        <SelectParameters>
            <asp:QueryStringParameter Name="ID" QueryStringField="Id" Type="Int32" />
        </SelectParameters>
        <UpdateParameters>
            <asp:Parameter Name="Title" Type="String" />
            <asp:Parameter Name="Summary" Type="String" />
            <asp:Parameter Name="Body" Type="String" />
            <asp:Parameter Name="GenreId" Type="Int32" />
            <asp:Parameter Name="Authorized" Type="Boolean" />
            <asp:Parameter Name="CreateDateTime" Type="DateTime" />
            <asp:Parameter Name="ID" Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource>
</asp:Content>

Imar · January 26th, 2014, 06:09 PM

>> Well here is the link to a screen shot of my Access page with the ID field highlighted.

And how does its Design View look?

Question: why are you using Access? SQL Server is much more stable, and designed for a multi-user environment such as web sites, whereas Access isn't....

Cheers,

Imar

adkybe · January 26th, 2014, 06:21 PM

Here is the Design View:
https://drive.google.com/file/d/0B11...it?usp=sharing

I decided to use an Access file because I thought it was simpler. I began initially using SQL server 2012, but during my use, at some point kept getting an error message when I was trying to create the relationship, something about memory....Couldn't figure it out, tried a repair install of SQL and it seemed to mess it up more. I am also running this on a virtual machine just to keep it separate from my main system.

So, you can't upload the access file to a web server in the App_Data folder and it work like in the development environment?

Imar · January 27th, 2014, 07:27 AM

Hi there,

Sorry, I meant the Design View of the table in Access with the properties for the ID field.

>> So, you can't upload the access file to a web server in the App_Data folder and it work like in the development environment?

The answer is: it depends. Your host / server needs to support it, and you may have to install additional drivers to access the database. But even if you get it to work, you may still run into problems if your site has many visitors as Access isn't designed for a large number of concurrent users.

Cheers,

Imar