Cetin, I put your aspx in to my virtual root and copied the testdata files into my working vfpoledb and got your app to work! It's a bit difficult for me to interpret as I do not program in c#, but rather VB. Do you do VB or just C#? My connections are done in VS wizard so I am lost in your logic but it looks like when you post back to the server, you re-establish your connection? Does this take into account the web.config?
>Tim,
>I said ADO.Net not ADO (anyway both is not just for XML). I will give a rough sample but in C#. Save as .aspx in a virtual root and open in browser (you might need to change database path):
>
><%@ Page Language="C#" AutoEventWireup="True" EnableViewState="False"%>
><%@ Import Namespace="System.Data" %>
><%@ Import Namespace="System.Data.OleDb" %>
>
><html>
> <script language="C#" runat="server">
> private string strCon = @"Provider=VFPOLEDB;
>Data Source= C:\Program Files\Microsoft Visual FoxPro 9\Samples\Data\testdata.dbc";
>
> void Page_Load(Object sender, EventArgs e)
> {
> if (!IsPostBack)
> {
> OleDbConnection cn = new OleDbConnection();
> cn.ConnectionString = strCon;
> cn.Open();
> OleDbDataAdapter da = new OleDbDataAdapter("select * from customer",cn);
> DataSet ds = new DataSet();
> da.Fill(ds,"Customer");
> cn.Close();
>
> myDataGrid.DataSource = ds;
> myDataGrid.DataMember = "Customer";
> myDataGrid.DataBind();
> }
> }
> private void GetCustomerOrders(object sender,EventArgs e)
> {
> OleDbConnection cn = new OleDbConnection();
> cn.ConnectionString = strCon;
> cn.Open();
> string strSelect = String.Format("select * from orders where cust_id = '{0}'",
> myTextBox.Text);
> OleDbDataAdapter da = new OleDbDataAdapter(strSelect,cn);
> DataSet ds = new DataSet();
> da.Fill(ds,"orders");
> cn.Close();
>
> ordersDataGrid.DataSource = ds;
> ordersDataGrid.DataMember = "orders";
> ordersDataGrid.DataBind();
> }
> </script>
><body>
> <form runat="server">
>Enter customer id:<asp:textbox id="myTextBox" runat="server"/>
> <asp:button type="submit" id="cmdGetData" runat="server"
> onClick="GetCustomerOrders" Text="Get Customer's Orders"/>
> <asp:datagrid id="ordersDataGrid" runat="server"/>
> <asp:datagrid id="myDataGrid" runat="server"/>
> </form>
></body>
></html>
>Cetin
>
>>Cetin, isn't ADO for xml data? I have not delved into that before.
>>Using my method,for now, how would I build my string passing a textbox value from my aspx page(if you're familiar with that) instead of using the @box syntax? I do this in my vb "code-behind"
>>
>>
>>>"But to pass a var from a textbox (ie @box1) in the vfpoledb scenario involves all that code you supplied?"
>>>
>>>No and yes. In .Net not surprisingly there are N ways to do the same thing (as in VFP). The code I supplied was my recommendation. The way you do it your database is vulnerable what is known as "SQL injection". In other words if you create such structs a hacker could steal valuable info from your backend.
>>>
>>>"To what language does your "selectCmd.CreateParameter" belong?"
>>>
>>>ADO.Net. Again it's a less used way. Creates a strongly typed parameter. Instead you might use new OleDbParameter(...) and OleDbCommand.Parameters.Add(...).
>>>
>>>
>>>"Also, what is '%{0}%', is this some reserved search syntax?"
>>>
>>>No it has nothing to do with search. It's a string construction method. I find it more readable and maintainable from:
>>>
>>>string strSelect = "select * from myTable where title like '%" + @box1 + "%'"
>>>
>>>Result string is same if you used:
>>>
>>>string strSelect = String.Format("select * from myTable where title like '%{0}%'", @box1)
>>>{0} is the placeholder for parameter @box1. Check System.String class' Format() method.
>>>
>>>
Re: Using OleDbParameters and FOXPRO MEMO Fields Thread #
1026872 Message #
1026953>>>Also check thread #
1078811>>>
>>>Cetin
>>>
>>>>Cetin, I've got the sqlserver vers of this project up and running using:
>>>>SqlDataSource1.SelectCommand = "SELECT [title], [type_dish],[recipe_id] FROM [dish] WHERE [title] LIKE '%' + @box1 + '%'".
>>>>It's pretty simple. But to pass a var from a textbox (ie @box1) in the vfpoledb scenario involves all that code you supplied? To what language does your "selectCmd.CreateParameter" belong? I try do do as much in the code behind as possible. Also, what is '%{0}%', is this some reserved search syntax?
>>>>
>>>>
>>>>
>>>>
>>>>>Tim,
>>>>>If it works with any backend then would work with VFP too (I doubt it would work however).
>>>>>Here is what I mean to be more clear:
>>>>>You're constructing the full SQL string (it is not parametric ADO.Net query). Yours look like to be the same as writing (correcting typo closing parentheses):
>>>>>
>>>>>String.Format("SELECT title, type_dish FROM dish WHERE title LIKE '%{0}%'",box1)
>>>>>>>>>>and would send to backend something like:
>>>>>SELECT title, type_dish FROM dish WHERE title LIKE '%sometitle%'
>>>>>
>>>>>Also note that:
>>>>>vDataSource2.SelectCommand expects a Command object not a string. ie:
>>>>>vDataSource2.SelectCommand = new OleDbCommand( "...", connection)
>>>>>
>>>>>like '%sometitle%'
>>>>>
>>>>>doesn't work the same way in VFP and SQL server. SQL server case sensitivity could be adjusted with a single setting for all SQLs while in VFP it's case sensitive (ie: it would miss SomeTitle). In other words accept there are major differences between VFP and SQL server SQL. It's an illusion that you could write SQL that's compatible with any backend (well you might but a very hard thing to do IMHO). You can come close to it using SQL and functions that are available in both (ie: SQL server doesn't have alltrim() but have rtrim() and ltrim() when combined same as alltrim).
>>>>>
>>>>>Next try to use parameters collection instead of formatting the whole string. ie:
>>>>>
>>>>>
>>>>>strSelect = "SELECT title, type_dish FROM dish WHERE title LIKE ?"
>>>>>
>>>>>
>>>>>VFP doesn't support @box style named parameters but only positional parameters (that's first parameter added to the collection correspond to first ? -parameter placeholder- in SQL string). While SQL server supports both. So this would work with VFP or SQL server (not exact syntax, because I'm weak with VB and C# might confuse you):
>>>>>
>>>>>
>>>>>strSelect = "SELECT title, type_dish FROM dish WHERE title LIKE ?"
>>>>>selectCmd = new OleDbCommand( strSelect, connection )
>>>>>myTitle = selectCmd.CreateParameter('myTitle',OleDbType.Char)
>>>>>selectCmd.Parameters.Add(myTitle)
>>>>>vDataSource2.SelectCommand = selectCmd;
>>>>>
>>>>>myTitle.Value = "%SomeTitle%"
>>>>>' execute command and fill say a dataset
>>>>>myTitle.Value = "%AnotherTitle%"
>>>>>' execute command
>>>>>>>>>>While building the whole string vs using parameters look similar they are not same. This is especially important if your parameter is a long string. Building the string would fail upon calling VFPOLEDB, parameterized one succeeds (check a recent thread about memo fields and ASP.Net in .Net forum - it also has a full C# sample).
>>>>>Cetin
>>>>>
>>>>>
>>>>>>Thanks again Cetin. If I'm going the vfp route, are there any other major differences in syntax from using SQLSERVER? My main concern is my selectcommand string. I have to form it according to VFP rules I assume.
>>>>>>I am having trouble with variables in vb.net and my statement as follows:
>>>>>>vDataSource2.SelectCommand = "SELECT title, type_dish FROM dish WHERE title LIKE '%' + @box1 + '%')"
>>>>>>Does vfp handle @variables like this do you know?
"Build a man a fire, and he's warm for a day.
Set a man on fire, and he's warm for the rest of his life."
