Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

SQL statement not finding data

I have a sql statement:

SELECT AgentName FROM tblAgent WHERE AgentName LIKE '*Del*Dwy*' AND Region LIKE 'O*';

I run the sql in MS access as a query, it retrieves all Agents that match the above criteria.  I have placed the same statement in VB when the users presses "Save", but finds NO records.  Same database, same sql statement....

What am I missing?   I have many sql statements that query the database but the only difference is I am using the LIKE option instead of the "=".
0
chellert
Asked:
chellert
  • 7
  • 6
  • 3
  • +2
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
Try:

    SELECT AgentName FROM tblAgent WHERE AgentName LIKE '%Del%Dwy%' AND Region LIKE 'O%';
0
 
MageDribbleCommented:
When connecting to an Access database you are using an ODBC connection so you'll need to use the ODBC wildcards % or _.
0
 
chellertAuthor Commented:
I receive a syntax error when using %
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MageDribbleCommented:
remove the semi-colon (;) at the end of the statement

" SELECT AgentName FROM tblAgent WHERE AgentName LIKE '%Del%Dwy%' AND Region LIKE 'O%' "
0
 
chellertAuthor Commented:
Still getting zero records when I should see 6 records
0
 
SanclerCommented:
Are you using ODBC or OleDb?  I've not checked ODBC but, with OleDb, either of the following work for me: different data, obviously ;-)

"SELECT Name2 FROM Tbl1 WHERE Name2 LIKE '%arc%mon%' AND Name1 LIKE 'a%'"

and

"SELECT Name2 FROM Tbl1 WHERE Name2 LIKE '%arc%mon%' AND Name1 LIKE 'a%';"

So it looks to me as though something else may be going on.  Can you please show us the code in which you use this sql statement and that is reporting no records returned when there should be six?

Roger

0
 
salan_alaniCommented:
Try to switch your data provider, maybe the current one you are using is currepted.

If you are using OleDb with connection string like this:
conStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[databasepath]\[database].mdb;Persist Security Info=False"

Try to use Odbc with connection string like this:
conStr= "DRIVER={MicroSoft Access Driver (*.MDB)};DBQ=[databasepath]\[database].mdb"

Or, if you are using the second one try to use the first one.

However, Microsoft Access Driver requires Microsoft MDAC 2.6 or later installed on the machine.



Good luck
0
 
chellertAuthor Commented:
The MDAC is up to date with the XP.  My code goes as follows:

To declare:
      Public strEvConn As String
      Public rsEvent As New ADODB.Recordset
      Public EvCon As New ADODB.Connection
      Public EvCmd As New ADODB.Command
      Public rsAgent As New ADODB.Recordset

To Connect:
            strEvConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=K:\BVTS\Brokerdata.mdb"
            EvCon.Open(strEvConn)
            EvCmd.ActiveConnection = EvCon

SQL Statement:
                    strSQL = "SELECT AgentName FROM tblAgent WHERE AgentName LIKE " & _
                        "'*Dell*Dwy*' AND Region LIKE 'O%'"

                    EvCmd.CommandText = strSQL
                    EvCmd.CommandType = ADODB.CommandTypeEnum.adCmdText
                    rsAgent = EvCmd.Execute

And the recordset comes back as EOF
0
 
salan_alaniCommented:
Why you don't use the Odbc driver, like this:

Dim ConnectionString As String
ConnectionString = "DRIVER={MicroSoft Access Driver (*.MDB)};DBQK:\BVTS\Brokerdata.mdb"

Dim SqlSelect As String
SqlSelect="SELECT AgentName FROM tblAgent WHERE AgentName LIKE " & _
                        "'%Dell%Dwy%' AND Region LIKE 'O%'"

' Beaware that '_' means any single character and '%' means any sequence of characters

Dim da As New System.Data.Odbc.OdbcDataAdapter(SqlSelect, ConnectionString)

Dim dt As New DataTable("dt")        
da.Fill(dt)

' Now the results will be stored in the DataTable 'dt'
' For example, if you want to see the results in a ListBox control

Dim i As Integer
For i = 0 To dt.Rows.Count - 1
    ListBox1.Items.Add(dt.Rows(i).Item(0).ToString())
Next


This works for me without any problems.

Good luck
0
 
salan_alaniCommented:
Sorry,

The ConnectionString should be like this:
ConnectionString = "DRIVER={MicroSoft Access Driver (*.MDB)};DBQ=K:\BVTS\Brokerdata.mdb"

I forget the equal ('=') sign
0
 
SanclerCommented:
I agree with salan_alani, although I would tend to use OleDB for Access rather than ODBC.  "%" would be the wildcard place holder for that.  The Connection string would be "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & <your file path> & ";User Id=admin;Password=;""

But, and this is the real point of this post, you need to be careful with the "fixed" part of any string you use in a LIKE statement.  Your first post had 'Del': your latest had 'Dell'.  Whereas, once the wildcard place holders are right, the former might return 6 records, the latter might return none.

Roger

0
 
chellertAuthor Commented:
I have tried the ODBC and OLEdb and I made sure the string was correct and I am still not working.  I tried Salan's method of creating a datatable and I received that the ODBC driver was not specified.

I am lost
0
 
salan_alaniCommented:
What is the exact error you get when you used the ODBC driver?
Are you sure you have the latest MDAC? check www.microsoft.com

0
 
chellertAuthor Commented:
I downloaded the latest MDAC off Microsoft and went to install, received message that I already had the latest.

Found my error, but when I run through, still no records found.
0
 
salan_alaniCommented:
But I did not understand the below messege in your previous post:
"ODBC driver was not specified"

Is it error? If so where you are getting it?


One more thing: are you using .NET 1.1 or .NET 2.0? and are you using Access 97, 2000, or 2003?
0
 
chellertAuthor Commented:
I found my ODBC error message on my own.  After I fixed the problem, I still don't have records.

Using .NET 2.0 and ACcess 2000
0
 
SanclerCommented:
Can I suggest you try a test.  Create a new table in Access.  Three fields: ID (Autonumber, Primary Key), Name1 (Text), Name2 (Text).  Save it as Tbl1.  Fill it as follows.

ID      Name1      Name2
1      a      arctic monkeys
2      a      a
3      g      archie month
4      c      morley mongol
5      e      archduke zeon

In VB.NET 2005, open a new project and drag a datagridview onto the default Form1.  Copy and paste this code (replacing FPath with your own reference).

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim strConn As String = _
                       "Provider = Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source = " & FPath & ";"
        Dim objConnection As New OleDb.OleDbConnection(strConn)
        Dim strSQL As String = "SELECT * FROM Tbl1 WHERE Name2 LIKE '%arc%mon%' AND Name1 LIKE 'A%';"
        Dim OleDBDataAdapter1 As New OleDb.OleDbDataAdapter(strSQL, objConnection)
        Dim dt As New DataTable
        Dim ActivesDS1 As New DataSet
        OleDBDataAdapter1.Fill(dt)
        DataGridView1.DataSource = dt

    End Sub

Run it.  This is copied and pasted precisely from my Access 2000 and VB.NET 2005 Framework 2 test table and test project and it works for me.  I get one entry in the datagridview or, if I change the SQL string by getting rid of the second test, I get two entries.  If we can get this far then we can start to change things bit by bit to see what's going wrong at your end.

Roger
0
 
chellertAuthor Commented:
I had to remove the ; for that to work, but I receive the one record

And I have no ; in my sql statement
0
 
salan_alaniCommented:
You can do one last thing. You said before that this query works for you from MS Access application using the Query builder. So build that query again and save it under any name (let say TestQuery). Then use the below code:

Dim ConnectionString As String
ConnectionString = "DRIVER={MicroSoft Access Driver (*.MDB)};DBQ=K:\BVTS\Brokerdata.mdb"
Dim cmd As New System.Data.Odbc.OdbcCommand("SELECT * FROM TestQuery")
cmd.Connection = New System.Data.Odbc.OdbcConnection(ConnectionString)

Dim da As New System.Data.Odbc.OdbcDataAdapter(cmd)

Dim dt As New DataTable("dt")        
da.Fill(dt)

Dim i As Integer
For i = 0 To dt.Rows.Count - 1
    ListBox1.Items.Add(dt.Rows(i).Item(0).ToString())
Next
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 7
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now