Alex,

FYI, I am not using the ODBC command/parameter objects. I see in the .NET documentation that AddWithValue is only for OdbcParameter class. I am using OleDbCommand and SQLCommand, so I need something that works with those.

Thanks,
JoeK

>Joe as matter of fact you don't even need a type to add the parameter you can do it just passing the name, the value or in 2005 call the AddWithValue method of the parameters collection.
>What about general fields or binary or blog fields how will u put that into your Sql string?
>if you use parameters is the dataprovider that manages that for you.
>>Alex,
>>
>>I don't follow you... I have been trying to add the parameter objects to the Command object -- OleDb and SQL both require a type to be passed in. I don't ever want to pass in type. That's because I don't want to have to change code when a database field changes to an acceptable new type, like varchar to text. Both use single-quotes to delimit them, and I am confident I can write a method that handles all the types we use. If we ever start using a type I don't account for initially, I just add a new type to my switch statement. This functionality will almost solely be used for inputting values for INSERT and UPDATE statements.
>>
>>The alternative would be to force the developer to always pass the type along with the parameter name and value, yes? I don't want to have to do that. Even without having to declare data sizes, declaring types seems silly to me in this scenario. If you pass the wrong type you will know it right away when you test the statement. The variables are already typed correctly, and the database fields have a strict type. Why do I need to also set the type for the mechanism that send the variable to the database?
>>
>>And even though VFP may have been loosely typed, the "?" SPT sugar works on database backends that ARE strongly typed, not just DBF tables. As I said, the "?" works against anything that VFP can SQLCONNECT() to. Once a developer has a connection, all VFP SPT code is completely consistent from there on out. That's a beautiful thing, in my opinion.
>>
>>Thanks,
>>JoeK
>>
>>>Joe Michel also adds the Parameters to the commadn object that's why he doen't need to convert any data types, you should do the same since will save you alot of prbs since is the data provider i.e. SQL or VFPOLEDB that then are reponsible for the data of the params.
>>>If you try to create a sql statment where you have field = 'value' or something I can tell you that you will end up having prbs with it.
>>>Now about the VFP ? and SQL passthough it is just like all VFP it doesn't have strong typing on any place, now .NET is diferent you are always responsible for declaring types, the prb was more that when VFP team develop the VFPOLEDB they didn't created Parameters names like you have in SQL Server and Oracle, cause if they had to then it would be alot more simple to write 1 sql stament that could work in diferent databases.
>>>
>>>>Michel,
>>>>
>>>>Excellent ideas!
>>>>
>>>>This is essentially what I will be doing (see my response to Alexandre), only I will likely do it by generating a string with a common syntax, and then just build a literal SQL string from the parameters. In any case, my base data class will have to know whether I am in OleDb (DBF) mode or SQL Server mode (nos difficult by using "is"), and then build the SQL string based on data type of the value (single quotes for text, converting numbers to character, curly braces for DBF dates, etc). Your method appears similar, except you build the string as you go instead of substituting for parameters after generating a base SQL string.
>>>>
>>>>Like I said in my other message, it is just disappointing that VFP had this all working under the hood -- a common syntax for any backend that SQLEXEC() talked to, and that's saying a lot. SPT in VFP has been a godsend to me over the years, making heterogenous data source positively sing. It would appear that there is still nothing else like it!
>>>>
>>>>Thanks,
>>>>JoeK
>>>>
>>>>
>>>>>I have methods from the data class for adding parameters. This one adds a parameter into a collection that I can later on include in my data provider object:
>>>>>
>>>>>

>>>>>        ' Add a parameter
>>>>>        ' expC1 Field
>>>>>        ' expO1 Value
>>>>>        Public Function ParameterAdd(ByVal tcField As String, ByVal toValue As Object) As Boolean
>>>>>            Dim loParameter(1, 2) As Object
>>>>>            loParameter(1, 1) = tcField
>>>>>            loParameter(1, 2) = toValue
>>>>>            oParameters.Add(loParameter)
>>>>>        End Function
>>>>>

>>>>>
>>>>>This one is used to add a parameterized field into a SQL command:
>>>>>
>>>>>

>>>>>        ' Add a parameter in the SQL with a proper syntax depending on the backend
>>>>>        ' expC1 Field
>>>>>        Public Function ParameterAddSQL(ByVal tcField As String) As String
>>>>>            Dim lcString As String = ""
>>>>>
>>>>>            ' Different backends handle it differently
>>>>>            Select Case nBackend
>>>>>
>>>>>                ' Visual FoxPro
>>>>>                Case 1
>>>>>                    lcString = "?"
>>>>>
>>>>>                    ' SQL Server
>>>>>                Case 2
>>>>>                    lcString = "@" + tcField
>>>>>
>>>>>            End Select
>>>>>
>>>>>            Return lcString
>>>>>        End Function
>>>>>

>>>>>
>>>>>So, basically, you can build a SQL like this:
>>>>>
>>>>>

>>>>>        loDataProvider.ParameterAdd("Numero", nPrimaryKey)
>>>>>        If Not loDataProvider.SQLUpdate("SELECT Member.FirstName " + _
>>>>>         "FROM Member "+ _
>>>>>         "WHERE Member.Numero=" + loDataProvider.ParameterAddSQL("Numero")) Then
>>>>>            Return False
>>>>>        End If
>>>>>

>>>>>
>>>>>So, basically, if you would follow such an approach, your client wouldn't rely on the backend as your data class would do. As everything would go through it, you can then just build the required code in the data class to handle different ways of adding parameters, conditions, etc.