VB.NET Access DB DataSet problem

culbysl

I have dropped all my emails into an access database, and now building a vb.NET windows app to query to return needed email bodies. The application runs through the database dataset function and returns 0 rows. I console.writeline the connection string and SQL statement and am able to pull records from the database. I can't see what I have wrong in the code. The function hitting the database is listed below. Please any help would be a really great thing. Thanks in advance. Public Function GetSelectAnd2Records(ByVal strTableName, ByVal strCriteria1, ByVal strCriteria3) As DataSet Dim ds As New DataSet Dim strSQL As String Dim dbConn As New OleDbConnection dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NovaEmailKB\KNOWLEDGE BASE SSC.mdb;" strCriteria1 = "*" & strCriteria1 & "*" strCriteria3 = "*" & strCriteria3 & "*" strSQL = "SELECT [ID], [Subject], [Body], [Received], [From] " & _ "FROM " & strTableName & " " & _ "WHERE (((Body) Like """ & strCriteria1 & """ And (Terminals.Body) Like """ & strCriteria3 & """)) " & _ "ORDER BY Received DESC; " Dim cn As New OleDbConnection(dbConn.ConnectionString) Dim cmd As New OleDbCommand(strSQL, cn) Dim da As New OleDbDataAdapter(cmd) Console.WriteLine(dbConn.ConnectionString) Console.WriteLine(strSQL) Try cn.Open() Console.WriteLine(cn.State) cmd.Connection = cn cmd.CommandText = strSQL cmd.ExecuteNonQuery() da.Fill(ds, "SelectAnd2Rtn") If cn.State = ConnectionState.Open Then cn.Close() End If cn.Dispose() cn = Nothing da.Dispose() da = Nothing Catch ex As Exception MsgBox("An unexpected MS Access error has occured: " & ex.Message) If cn.State = ConnectionState.Open Then cn.Close() End If cn.Dispose() cn = Nothing da.Dispose() da = Nothing Return Nothing Exit Function End Try Return ds End Function

Dave Kreskowiak

I'd start by picking a single method to fill a dataset and sticking with it. You've got two different methods in here. And I detest using string concatentation to build an SQL statement. Simplify it!

Public Function GetSelectAnd2Records(ByVal strTableName, ByVal strCriteria1, ByVal strCriteria3) As DataSet
' Are you sure Access uses asterisks as wildcards???
' I don't know because I don't use Access anymore...
strCriteria1 = "*" & strCriteria1 & "*"
strCriteria3 = "*" & strCriteria3 & "*"

Dim strSQL As String = String.Format("SELECT \[ID\], \[Subject\], \[Body\], \[Received\], \[From\] " & \_
        "FROM {0} WHERE \[Body) Like ""{1}"" And (Terminals.Body) Like ""{2}""" & \_
        "ORDER BY Received DESC", strTableName, strCriteria1, strCriteria3)

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\NovaEmailKB\\KNOWLEDGE BASE SSC.mdb;")
Dim comm As New OleDbCommand(strSQL, conn)

Console.WriteLine(strSQL)

Try
    Using da As OleDbDataAdapter = New OleDbDataAdapter(comm)
        da.Fill(ds)
    End Using
Catch ex As Exception
    MsgBox("An unexpected MS Access error has occured: " & ex.Message)
End Try

Return ds

End Function

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007

culbysl

Thanks so much for the advise, however using your code for the function I am still returning 0 rows. When I put the query directly into Access I return 8 rows. Again thanks so much for your thought and advise, I'll keep plugging along.

Dave Kreskowiak

It's not obvious in the code. Either the query string your using directly in Access is different than the one generated by the code or the table name your passing in is different, or the database the app is using is different from the one your direct query is using.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007

culbysl

Thanks once again for your thoughts. I ran console.writeline on both the connection string and the sql statement then using start run opened the database and ran the query that was placed in the output window. What I have found is when I take out the like and run an equal to where I get results in the datagridview on my form. It is becoming obvious that my problem has to do with like in the query which allows it to run in Access but not through the application. My next move is to try % as the wildcard not *. I sure wish I could use SQL Server but that is just not in the cards. Again thanks for all your help.