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?

 

[jbenson001]

There is no way for us to know if you are a clutz or not without seeing some code

 

[crabgrass]

Aah, but then I might become embarrassed. Can you answer my first question?

 

[jbenson001]

The answer is no. You would pull the data into a dataset or datatable and that is bound to your control(s). It doesn't matter what store the data is in, it could be any database, a file, xls, XML etc.

 

[crabgrass]

OK, I have a simple VFP table with only 2 character fields in it. The table is in a database DBC. When I configure the datasource for the formview the options to generate UPDATE and INSERT statements is not available. (grayed out) Why? and how do I fix it?

Thanks

 

[jbenson001]

I don't know. I don't use the datasource controls because they limit your flexibility. If you code the data access your self, you will have more control. The datasource controls are good for quick simple pages, but it the long run they are not good to use.

 

[crabgrass]

So, do you use the old oConn = "some string" and oRS = oConn.execute(sql) etc? If we do this I don't see much benefit in moving from ASP to ASPX. I still have to write a complete routine to manage any editing and paging.

 

[jbenson001]

We use the helper classes from Microsoft. You can write your own DLL and just use the connection and command objects.

If we do this I don't see much benefit in moving from ASP to ASPX

There is a HUGE benefit to moving to .NET!

I still have to write a complete routine to manage any editing and paging.

The built in Data objects, such as the GridView, FormView, etc. have paging functionality built in.

 

[crabgrass]

Whoops! I think we've gone full circle. I was (am) trying to use the data objects for the very reason you point out. BUT, I'd like to use them with a VFP database. If I build the connection myself in code, how would I associate it with a formview for example?

I can (and have) written several dlls with VFP but they introduce other issues I always like to avoid if possible, such as the host not wanting to install them on his server.

 

[TheInsider]

Yes, speaking as an ASP.Net novice but former ASP 3.0 expert, I can attest to the fact that .Net is much cleaner and requires far less code than the old spaghetti that used to be inevitable.

Further, in ASP.Net, you can do anything that you would in a normal fat-client application because you have access to all the same libraries and (real) classes. That's not to say .Net is perfect, but I prefer it to Java.

My advice is to stick with ASP.Net, as ASP 3.0 is obsolete being messier and requiring more code to accomplish the same tasks.

 

[ethorn10]

Crabgrass -- you would need to show us some code-behind and we'll help point you in the right direction. Nobody will make fun of your code. If anything, they'll help make it/you better.

In general, you would need to set the DataSource of the FormView to your SQL statement.

 

[crabgrass]

OK if I understand jbenson001 advice, its better to build the connection in code rather than using the wizards built in to the data controls. So, suppose you do something like this:

<%
session("dataloc") = "c:\inetpub\[URL unfurl="true"]wwwroot\myproject\app_data\"[/URL]
dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
ConnStr = "Provider=VFPOLEDB;Data source=" & session("dataloc") & "library.dbc" 
with oConn
	 .Mode = 16 'adModeShareDenyNone - this is value of adModeShareDenyNone constant
	 .Open ConnStr
	 .CursorLocation = adUseClient
	 .Execute ("set null off")
end with
%>

Which is what I would use with regular ASP. Then do I set the formview's DataSourceID to oConn? And where does the SQL come into play?

 

[jbenson001]

http://msdn.microsoft.com/en-us/library/aa977092(VS.71).aspx

 

[jmeckley]

OK if I understand jbenson001 advice, its better to build the connection in code rather than using the wizards built in to the data controls

it's just better to avoid the wizards all together

use the code behind model instead of intertwining your markup and code.
the page declaration will reference the code file.
<%@Page .... codebhind="Mypage.aspx.cs"% codeclass="MyPage"> something like that
code would be

public partial MyPage : Page
{
   //welcome to the page lifecycle!
}

now for connecting to the database. use ADO.Net not ADODB. If your fimilar with OOP I would recommend a database connection factory to create instances of db connection/commands. Also new to ADO.Net (from ADODB) is parameterized queries. no more sql injection.

I prefer to program to the interface, and use the DbProviderFactories.GetFactory("name of provider");
from here i create a connection. from the connection I create a command. from the command I execute. the only place in my application that specifically references my database is the connection string stored in the web.config.

in very simplified terms it would look like this. note: lots of room to refactor into seperate objects.

ConnectionSettings settings = ConfigurationManager.ConnectionStrings["foo"];

DataTable table = new DataTable();
using(IDbConnection connection = DbProviderFactories.GetFactory(settings.ProviderName).CreateConnection())
{
   connection.ConnectionString = settings.ConnectionString;
   connection.Open();
   IDbCommand command = connection.CreateCommand();
   command.CommandText = "select * from table where id = @id";
   command.CommandType = CommandType.Text;
   IDbParameter id = command.CreateParameter("id");
   id.Value = 1;
   command.Parameters.Add(id);
   
   table.Load(command.ExecuteReader());
}
//use table to read data

there's alot happening here. the using statement automatically closes/disposes the connection even if an exception is thrown. I use 1 parameter in the query. the DB resuls are loaded into a data table which can then be used however you want.

personally I map the data table to rich domain objects, but that's best left for another post

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[crabgrass]

OK folks. Thanks to all of you who have responded to this thread thus far. I was able to resolve (I think) the problem with the data source configuration. It seems the table must have a designated keyfield index before the GENERATE INSERT, UPDATE, AND DELETE options will be made available. So I have done that and the data connection tests OK. The form will run OK with the data connection only. However, when I add a formview I get the following error

[OleDbException (0x80004005): Feature is not available.]
   System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1131233
   System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
   System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
   System.Data.OleDb.OleDbConnection.Open() +37
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.FormView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.FormView.EnsureDataBound() +163
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +50
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

Here is the entire form code.

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="EditUser.aspx.vb" Inherits="EditUser" title="Untitled Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>

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

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConflictDetection="CompareAllValues" ConnectionString="Provider=VFPOLEDB.1" 
            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="System.Data.OleDb" 
            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>
 
        <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 />
        <br />
        
        </center>
</asp:Content>

The error makes no sense to me. Can someone tell me where to look next?
Thanks

 

[TheInsider]

Someone correct me if I'm wrong (and I most likely am), but I don't think you can use the "?" operator in ADO.Net. I think you're supposed to use named parameters like "@user_id" now.

 

[crabgrass]

Seems like the problem is still in the definition of the connection. In the Modify Connection dialog there are 3 text entries:
Data Source = ".NET Framework Data Provider for OLE DB"

OLE DB Provider = "Microsoft OLE DB Provider for Visual FoxPro"

Server or filename = ""

I can't figure out what to put in this last box. Seems like it should be the filename "library.dbc". But that gives an error and it doesn't work with the full filespec either. If I leave it blank and move on it presents a dialog for the DBC file which when selected seems to work but it isn't saved into the definition.

Any help?

 

[ca8msm]

If you use the method that jmeckley has shown (especially the code behind mode rather than inline SqlDataSource), you will find it much easier to debug the problem.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[jmeckley]

not too fimiliar with VPF or it's databases, but shouldn't there be a PK on the user table? or at least a field that acts as a PK?

the more you rely on [X]DataSource the more difficult it is to debug. you can't run unit tests, and manual <f5> debug doesn't step through the markup.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[crabgrass]

I found the problem. Apparently the wizard doesn't build the connection string properly. When I coded it in by hand it works OK. Now can someone tell me how to safely change the name of a project and the folder it resides in?

Thanks