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