Other databases

[crabgrass]

Is it true that in order to use the full functionality of the new datacontrols (formview, gridview, etc) you have to maintain the data in a SQL server/express database? I have tables in a VFP database and can't seem to get them to work with a proper connection definition for update/delete/insert.

Or am I just a clutz?

 

[crabgrass]

Whoops, I thought I was there but not quite. I can now read the data in the Formview and I can INSERT new records. Paging works also. However the UPDATE fails with an error that says "Column '' is not found". DELETE does not throw an error but does not work either. The screen looks like it refreshes but the record is still there. All of the commands look to be formatted properly.

To those of you who have suggested Jason's technique, I would be glad to give it a try, but I am afraid that at this stage of my development it is all greek to me. I would need a lot of hand holding to be able to make that work.

Here's my current code.

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <center><h2>Edit User</h2>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConflictDetection="CompareAllValues" 
            ConnectionString="<%$ ConnectionStrings:VFPUsers %>" 
            DeleteCommand="DELETE FROM [users] WHERE [user_id] = ? AND [username] = ? AND [password] = ? AND [fullname] = ? AND [authlevel] = ? AND [email] = ?" 
            InsertCommand="INSERT INTO [users] ([user_id], [username], [password], [fullname], [authlevel], [email]) VALUES (?, ?, ?, ?, ?, ?)" 
            OldValuesParameterFormatString="original_{0}" 
            ProviderName="<%$ ConnectionStrings:VFPUsers.ProviderName %>" 
            SelectCommand="SELECT * FROM [users] ORDER BY [username]" 
            UpdateCommand="UPDATE [users] SET [username] = ?, [password] = ?, [fullname] = ?, [authlevel] = ?, [email] = ? WHERE [user_id] = ? AND [username] = ? AND [password] = ? AND [fullname] = ? AND [authlevel] = ? AND [email] = ?"
            >
            <DeleteParameters>
                <asp:Parameter Name="original_user_id" Type="Int32" />
                <asp:Parameter Name="original_username" Type="String" />
                <asp:Parameter Name="original_password" Type="String" />
                <asp:Parameter Name="original_fullname" Type="String" />
                <asp:Parameter Name="original_authlevel" Type="Int32" />
                <asp:Parameter Name="original_email" Type="String" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="username" Type="String" />
                <asp:Parameter Name="password" Type="String" />
                <asp:Parameter Name="fullname" Type="String" />
                <asp:Parameter Name="authlevel" Type="Int32" />
                <asp:Parameter Name="email" Type="String" />
                <asp:Parameter Name="original_user_id" Type="Int32" />
                <asp:Parameter Name="original_username" Type="String" />
                <asp:Parameter Name="original_password" Type="String" />
                <asp:Parameter Name="original_fullname" Type="String" />
                <asp:Parameter Name="original_authlevel" Type="Int32" />
                <asp:Parameter Name="original_email" Type="String" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="user_id" Type="Int32" />
                <asp:Parameter Name="username" Type="String" />
                <asp:Parameter Name="password" Type="String" />
                <asp:Parameter Name="fullname" Type="String" />
                <asp:Parameter Name="authlevel" Type="Int32" />
                <asp:Parameter Name="email" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
 
        <br />
        <asp:FormView ID="FormView1" runat="server" AllowPaging="True" 
            DataKeyNames="user_id" DataSourceID="SqlDataSource1">
            <EditItemTemplate>
                user_id:
                <asp:Label ID="user_idLabel1" runat="server" Text='<%# Eval("user_id") %>' />
                <br />
                username:
                <asp:TextBox ID="usernameTextBox" runat="server" 
                    Text='<%# Bind("username") %>' />
                <br />
                password:
                <asp:TextBox ID="passwordTextBox" runat="server" 
                    Text='<%# Bind("password") %>' />
                <br />
                fullname:
                <asp:TextBox ID="fullnameTextBox" runat="server" 
                    Text='<%# Bind("fullname") %>' />
                <br />
                authlevel:
                <asp:TextBox ID="authlevelTextBox" runat="server" 
                    Text='<%# Bind("authlevel") %>' />
                <br />
                email:
                <asp:TextBox ID="emailTextBox" runat="server" Text='<%# Bind("email") %>' />
                <br />
                <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 
                    CommandName="Update" Text="Update" />
                &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" 
                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
            </EditItemTemplate>
            <InsertItemTemplate>
                user_id:
                <asp:TextBox ID="user_idTextBox" runat="server" Text='<%# Bind("user_id") %>' />
                <br />
                username:
                <asp:TextBox ID="usernameTextBox" runat="server" 
                    Text='<%# Bind("username") %>' />
                <br />
                password:
                <asp:TextBox ID="passwordTextBox" runat="server" 
                    Text='<%# Bind("password") %>' />
                <br />
                fullname:
                <asp:TextBox ID="fullnameTextBox" runat="server" 
                    Text='<%# Bind("fullname") %>' />
                <br />
                authlevel:
                <asp:TextBox ID="authlevelTextBox" runat="server" 
                    Text='<%# Bind("authlevel") %>' />
                <br />
                email:
                <asp:TextBox ID="emailTextBox" runat="server" Text='<%# Bind("email") %>' />
                <br />
                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 
                    CommandName="Insert" Text="Insert" />
                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" 
                    CausesValidation="False" CommandName="Cancel" Text="Cancel" />
            </InsertItemTemplate>
            <ItemTemplate>
                user_id:
                <asp:Label ID="user_idLabel" runat="server" Text='<%# Eval("user_id") %>' />
                <br />
                username:
                <asp:Label ID="usernameLabel" runat="server" Text='<%# Bind("username") %>' />
                <br />
                password:
                <asp:Label ID="passwordLabel" runat="server" Text='<%# Bind("password") %>' />
                <br />
                fullname:
                <asp:Label ID="fullnameLabel" runat="server" Text='<%# Bind("fullname") %>' />
                <br />
                authlevel:
                <asp:Label ID="authlevelLabel" runat="server" Text='<%# Bind("authlevel") %>' />
                <br />
                email:
                <asp:Label ID="emailLabel" runat="server" Text='<%# Bind("email") %>' />
                <br />
                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" 
                    CommandName="Edit" Text="Edit" />
                &nbsp;<asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" 
                    CommandName="Delete" Text="Delete" />
                &nbsp;<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" 
                    CommandName="New" Text="New" />
            </ItemTemplate>
        </asp:FormView>
        <br />
        
        </center>
</asp:Content>

Jason - the user_id is the Primary Key. It's an integer field. Also, I ran the data connection builder again and this time it worked OK. I must have had some other setting wrong when I tried it before.

 

[crabgrass]

I found the problem with the UPDATE and DELETE SQL statements. Seems VFP doesn't like the square brackets [] surrounding the field names.