Problem with index on sql query

I have a sql query which runs fine in Foxpro 6.0.   When I run the code from vb.net 2003, no results are returned but if I remove the index, I get the expected results but its way too slow.  Any help in getting this query to work is appreciated.   Thank-you.
premsql = "Select policy, txtype from premsht where policy + str(sheet_no, 4) = padr(""" & CasPolicyNo & """, 15)"
        Response.Write(premsql)
        Dim premCmd As New Odbc.OdbcCommand
        Dim premDataAdapter As New Odbc.OdbcDataAdapter
        Dim premDataView As DataView
        Dim premDataSet As New DataSet
        premDataAdapter = New OdbcDataAdapter(premsql, connIFIS)
        premDataAdapter.Fill(premDataSet, "premsht")
        premDataView = premDataSet.Tables("premsht").DefaultView
        For Each dr As DataRow In premDataSet.Tables(0).Rows
            CasPolicyNo = dr(0)
            Response.Write(CasPolicyNo)
            txtype = dr(1)
            Response.Write("<br>")
            Response.Write(txtype)
        Next

Open in new window

lnshopAsked:
Who is Participating?
 
CarlWarnerConnect With a Mentor Commented:
>>>No the policy number is CRX53328, the sheet_no is 4
>>>select policy from premsht where policy+str(sheet_no, 4)=padr("CRX53328", 15)

When you pad the right side of the equation, you are padding it with spaces.
So, it ends up being "CRX53328       " or "CRX53328xxxxxxx" where x=<space>, right?

On the left side, you show a policy no, which is eight characters, right?  And then you toss in the concatention of the sheet_no, which is four more characters.  That concatenation of 8 chars and 4 chars is 12 characters.  Are you sure you want to compare a string of 12 chars on the left to 15 chars on the right, where the right side seems to believe the last seven chars are spaces?
0
 
randomjamesCommented:
Should you padr(policy + str(sheet_no, 4)) like you did with CasPolicyNo?
0
 
CarlWarnerCommented:
Since the problem seems to be the index. what expression you indexing on and what are you calling the index tag within VFP6 where it does work?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
lnshopAuthor Commented:
This is what I ran in Foxpro.  The index is on policy.  The only difference I could see in foxpro is that the expression was policy+STR(sheet_no,4).  I tried using this exact syntax and didn't get any results. but it must be a problem with the index.
Foxpro query
select policy, txtype from premsht where policy+str(sheet_no, 4)=padr("CRX53328", 15)
.net query
premsql = "Select policy from premsht where policy + str(sheet_no, 4) = padr(""" & CasPolicyNo & """, 15)"
0
 
CarlWarnerCommented:
I can't tell from what I've seen what is the Policy part and what is the sheet # part.

However, if that sheet # is NOT exactly four characters long, you will have leading spaces in that portion which will make an unusual concatentation of those strings.

Is policy # "CRX5" and sheet # is "3328" ?
0
 
lnshopAuthor Commented:
No the policy number is CRX53328, the sheet_no is 4
This is exactly what I entered into the Foxpro command window and it retured 6 records.  I have used the str(sheet_no, 4) index in many other queries and didn't have this problem.  I don't what else to try at this point.
select policy from premsht where policy+str(sheet_no, 4)=padr("CRX53328", 15)
0
 
lnshopAuthor Commented:
Thank-you very much for your help with this, you put me on the right track and its now working.  The query is
 premsql = "Select premsht.policy, premsht.sheet_no from premsht where premsht.policy + str(sheet_no, 4) between padr(""" & CasPolicyNo & """, 12) and padr(""" & CasPolicyNo & """, 15) +""9999"""
0
 
CarlWarnerCommented:
Fantastic!  I wasn't sure where we were headed.  Now I see it, too.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.