OleDB Select Top statement not returning data??
in [Visual Basic]
|
Prev: Problem using MsgHook to send msg to running app (Fixed)
Next: The instruction at xxxx referenced memory.... After DotNet update
From: Joseph N. Stackhouse on 13 Jul 2010 04:27 The while reader.read returns no data at all (msgbox doesn't even fire), there should be one row returned. When I execute this exact statement against the same access database within Microsoft Access I get my one row. Any ideas? MsgBox("building sql") Dim SQLcmd As String = String.Format("SELECT TOP 1 Suspects.Moniker FROM Suspects WHERE (((Suspects.Moniker) Like 'Unknown.*')) ORDER BY Suspects.Moniker DESC") Dim sVersion As String = vbNullString Dim SQLExec As New OleDbCommand SQLExec.Connection = SpyBareDB SQLExec.CommandText = SQLcmd Dim reader As OleDbDataReader = SQLExec.ExecuteReader() While reader.Read() MsgBox("getting string") MsgBox(reader.GetString(0)) End While
From: Nobody on 13 Jul 2010 11:16 "Joseph N. Stackhouse" <junkmauler(a)hotmail.com> wrote in message news:uqMjFVmILHA.4400(a)TK2MSFTNGP05.phx.gbl... > Dim SQLcmd As String = String.Format("SELECT TOP 1 This group is for VB6 and earlier(VB Classic). For VB.Net, you need to use the online forums provided by Microsoft or others, as they have shut down the dotnet newsgroups about a week ago.
From: Paul Clement on 13 Jul 2010 15:23 On Tue, 13 Jul 2010 04:27:37 -0400, "Joseph N. Stackhouse" <junkmauler(a)hotmail.com> wrote: � The while reader.read returns no data at all (msgbox doesn�t even fire), � there should be one row returned. When I execute this exact statement � against the same access database within Microsoft Access I get my one row. � Any ideas? � � MsgBox("building sql") � Dim SQLcmd As String = String.Format("SELECT TOP 1 � Suspects.Moniker FROM Suspects WHERE (((Suspects.Moniker) Like 'Unknown.*')) � ORDER BY Suspects.Moniker DESC") � Dim sVersion As String = vbNullString � Dim SQLExec As New OleDbCommand � � SQLExec.Connection = SpyBareDB � SQLExec.CommandText = SQLcmd � � Dim reader As OleDbDataReader = SQLExec.ExecuteReader() � While reader.Read() � MsgBox("getting string") � MsgBox(reader.GetString(0)) � End While Probably more of a SQL question. The wild-card character for ADO/OLEDB is a percent sign character and not an asterisk (as when running the query from Microsoft Access). If you need to run this query from Access as well then use the undocumented ALIKE keyword with the percent sign as the wild-card character. Paul ~~~~ Microsoft MVP (Visual Basic)
From: Joseph N. Stackhouse on 14 Jul 2010 05:21
Paul thank you very much I racked my brain for a few hours on this one! Damn you asterisks! ;) "Paul Clement" wrote in message news:e1fp36h6cpn64n1vr23j9v6eekj81n63vi(a)4ax.com... On Tue, 13 Jul 2010 04:27:37 -0400, "Joseph N. Stackhouse" <junkmauler(a)hotmail.com> wrote: ¤ The while reader.read returns no data at all (msgbox doesn't even fire), ¤ there should be one row returned. When I execute this exact statement ¤ against the same access database within Microsoft Access I get my one row. ¤ Any ideas? ¤ ¤ MsgBox("building sql") ¤ Dim SQLcmd As String = String.Format("SELECT TOP 1 ¤ Suspects.Moniker FROM Suspects WHERE (((Suspects.Moniker) Like 'Unknown.*')) ¤ ORDER BY Suspects.Moniker DESC") ¤ Dim sVersion As String = vbNullString ¤ Dim SQLExec As New OleDbCommand ¤ ¤ SQLExec.Connection = SpyBareDB ¤ SQLExec.CommandText = SQLcmd ¤ ¤ Dim reader As OleDbDataReader = SQLExec.ExecuteReader() ¤ While reader.Read() ¤ MsgBox("getting string") ¤ MsgBox(reader.GetString(0)) ¤ End While Probably more of a SQL question. The wild-card character for ADO/OLEDB is a percent sign character and not an asterisk (as when running the query from Microsoft Access). If you need to run this query from Access as well then use the undocumented ALIKE keyword with the percent sign as the wild-card character. Paul ~~~~ Microsoft MVP (Visual Basic) |