vfp Parameter - callin vfp geeks

[Guest]

Hello. I perform a search on a vfp table using select statement. Based on
the input paramter, the db engine performs a lookup by stripping the dashes
(-) from the searched string. The data in table is bad and I must remove
dashes. I use STRTRAN function. Now I have built commandText to select data
based on input parameter, as follows:

public DataTable GetAccountNumber(string Ssn)
{
//initialize objects
OleDbCommand oleXcmd = new OleDbCommand();
OleDbParameter oleParam = new OleDbParameter();
OleDbDataAdapter oleXda = new OleDbDataAdapter();
DataTable retVal = new DataTable();

try
{
oleXcmd.CommandText = "Select AcctNbr FROM Accounts.dbf ";
oleXcmd.CommandType = CommandType.Text;

oleParam = oleXcmd.Parameters.Add("@Ssn", OleDbType.VarChar, 9);

oleParam.Value = Ssn;

oleXcmd.CommandText += " WHERE STRTRAN(A.Ssn,[-],[]) = [@Ssn]";

oleXda.SelectCommand = oleXcmd;
oleXcmd.Connection = Connection;

// Execute the query
oleXda.Fill(retVal);
}
catch
.....
.....

If I hard-code value instead of passing the param, I return a value from
datatable. If using params, code executes but does not return any rows.
Oh yeah one more thing...I try to remove brackets surrounding param but C#
throws exception when the fill method is executed. So, my problem is that
parameter is not being evaluated.

 

[tomb]

Hello. I perform a search on a vfp table using select statement. Based on
the input paramter, the db engine performs a lookup by stripping the dashes
(-) from the searched string. The data in table is bad and I must remove
dashes. I use STRTRAN function. Now I have built commandText to select data
based on input parameter, as follows:

public DataTable GetAccountNumber(string Ssn)
{
//initialize objects
OleDbCommand oleXcmd = new OleDbCommand();
OleDbParameter oleParam = new OleDbParameter();
OleDbDataAdapter oleXda = new OleDbDataAdapter();
DataTable retVal = new DataTable();

try
{
oleXcmd.CommandText = "Select AcctNbr FROM Accounts.dbf ";
oleXcmd.CommandType = CommandType.Text;

oleParam = oleXcmd.Parameters.Add("@Ssn", OleDbType.VarChar, 9);

oleParam.Value = Ssn;

oleXcmd.CommandText += " WHERE STRTRAN(A.Ssn,[-],[]) = [@Ssn]";

I don't get it - why use a parameter object if you are appending a WHERE
clause to the query text?

T

 

[Cindy Winegarden]

Hi Toco,

The following VB 2005 code works for me:
'-- ------------------------------------------
Imports System.Data
Imports System.Data.OleDb

Module Module1

Sub Main()

Try
' Create some data
Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp;")
cn1.Open()
Dim cmd1 As New OleDbCommand( _
"Create Table Accounts (AcctNbr C(11))", cn1)
Dim cmd2 As New OleDbCommand( _
"Insert Into Accounts Values ('123-45-6789')",
cn1)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()

Dim cmd3 As New OleDbCommand( _
"Select AcctNbr FROM Accounts.dbf " + _
"Where StrTran(AcctNbr, [-], []) = ? ", cn1)
cmd3.Parameters.AddWithValue("MyAcctNbr", "123456789")

Dim da As New OleDbDataAdapter(cmd3)
Dim ds As New DataSet
da.Fill(ds)

Console.WriteLine(ds.Tables(0).Rows(0).Item(0).ToString())
Console.ReadLine()

cn1.Close()

Catch e As Exception
MsgBox(e.ToString())
End Try

End Sub

End Module
'-- ------------------------------------------

I notice that you refer to A.Ssn in your StrTran clause but you have not
aliased Accounts in the first part of your Select statement.

Also, it's worth noting that your query will not be optimized unless there
is an index on the Accounts table with the _exact_ same expression as you
have in your Where clause. In other words you would need an index such as
this:

Index On StrTran(AcctNbr, [-], []) Tag MyTagName