Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQLServerCE give error on ExecuteReader

I am an experienced programmer and know my way around quite well, however I am stumped by what should be quite easy. If anybody can help, please help me before I find myself crazed from frustration.

I am using visual studio 2003 developing an application for Pocket PC (only my 3rd).

I am trying to read data from an sqlce database. I get an error on the execute reader statement, and I can not find anything on it and can not see any problems, It is likely something stupid I am overlooking. The code is as follows...

Dim SQL As String
Dim R As New ListViewItem
Dim Conn As SqlServerCe.SqlCeConnection
Dim Cmd As SqlServerCe.SqlCeCommand
Dim Rdr As SqlServerCe.SqlCeDataReader

SQL = "instr(ucase(ProgName),'" + txtFind.Text.ToUpper + "') > 0"
SQL += " OR instr(ucase(ProgVer),'" + txtFind.Text.ToUpper + "') > 0"
SQL += " OR instr(ucase(ProgMfg),'" + txtFind.Text.ToUpper + "') > 0"
If lstPlat.SelectedIndex > 0 Then
SQL += " OR instr(ucase(ProgPlatform),'" + lstPlat.SelectedItem.ToString.ToUpper + "') > 0"
End If

Conn = New SqlServerCe.SqlCeConnection("Data Source = " + txtPath.Text + "Code6.sdf")
Conn.Open()

Cmd = New SqlServerCe.SqlCeCommand("Select ProgName,ProgVer,ProgMfg,ProgCode,ProgPlatform from Code6 where " + SQL, Conn)
******************************
Rdr = Cmd.ExecuteReader(CommandBehavior.CloseConnection) <---- Error occurs here
******************************
lstResult.Items.Clear()
While Rdr.Read()

End While
Conn.Close()

Comments

  • BarkeeperBarkeeper Member Posts: 335
    :
    : Dim SQL As String
    : Dim R As New ListViewItem
    : Dim Conn As SqlServerCe.SqlCeConnection
    : Dim Cmd As SqlServerCe.SqlCeCommand
    : Dim Rdr As SqlServerCe.SqlCeDataReader
    :
    : SQL = "instr(ucase(ProgName),'" + txtFind.Text.ToUpper + "') > 0"
    : SQL += " OR instr(ucase(ProgVer),'" + txtFind.Text.ToUpper + "') > 0"
    : SQL += " OR instr(ucase(ProgMfg),'" + txtFind.Text.ToUpper + "') > 0"
    : If lstPlat.SelectedIndex > 0 Then
    : SQL += " OR instr(ucase(ProgPlatform),'" + lstPlat.SelectedItem.ToString.ToUpper + "') > 0"
    : End If
    :
    : Conn = New SqlServerCe.SqlCeConnection("Data Source = " + txtPath.Text + "Code6.sdf")
    : Conn.Open()
    :
    : Cmd = New SqlServerCe.SqlCeCommand("Select ProgName,ProgVer,ProgMfg,ProgCode,ProgPlatform from Code6 where " + SQL, Conn)
    : ******************************
    : Rdr = Cmd.ExecuteReader([red]CommandBehavior.CloseConnection[/red]) <---- Error occurs here
    : ******************************
    : lstResult.Items.Clear()
    : While Rdr.Read()
    :
    : End While
    : Conn.Close()
    :

    Right, i don't know the Object-Model of the SQLServerCE, but my guts are telling me, that the red code is dodgy!

    What kind of Object is "CommandBehavior"?
    And "CloseConnection" sounds like a method, but in your ExecuteReader either you need a Statement like a SELECT-Statement or something like an Index!
    The only thing making sense would be, if "CloseConnection" has a return-Value you want to use as the Argument in the ExecuteReader-Method/Property!

    Greetz
    Zvoni
    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • seanpennseanpenn Member Posts: 5
    I have tried with the execute reader blank and with the commandbehavior. both give same error. Microsoft examples do it both ways. My other projects using mysql and access have no parameters, and I did it that way and still nothing but error.

    : :
    : : Dim SQL As String
    : : Dim R As New ListViewItem
    : : Dim Conn As SqlServerCe.SqlCeConnection
    : : Dim Cmd As SqlServerCe.SqlCeCommand
    : : Dim Rdr As SqlServerCe.SqlCeDataReader
    : :
    : : SQL = "instr(ucase(ProgName),'" + txtFind.Text.ToUpper + "') > 0"
    : : SQL += " OR instr(ucase(ProgVer),'" + txtFind.Text.ToUpper + "') > 0"
    : : SQL += " OR instr(ucase(ProgMfg),'" + txtFind.Text.ToUpper + "') > 0"
    : : If lstPlat.SelectedIndex > 0 Then
    : : SQL += " OR instr(ucase(ProgPlatform),'" + lstPlat.SelectedItem.ToString.ToUpper + "') > 0"
    : : End If
    : :
    : : Conn = New SqlServerCe.SqlCeConnection("Data Source = " + txtPath.Text + "Code6.sdf")
    : : Conn.Open()
    : :
    : : Cmd = New SqlServerCe.SqlCeCommand("Select ProgName,ProgVer,ProgMfg,ProgCode,ProgPlatform from Code6 where " + SQL, Conn)
    : : ******************************
    : : Rdr = Cmd.ExecuteReader([red]CommandBehavior.CloseConnection[/red]) <---- Error occurs here
    : : ******************************
    : : lstResult.Items.Clear()
    : : While Rdr.Read()
    : :
    : : End While
    : : Conn.Close()
    : :
    :
    : Right, i don't know the Object-Model of the SQLServerCE, but my guts are telling me, that the red code is dodgy!
    :
    : What kind of Object is "CommandBehavior"?
    : And "CloseConnection" sounds like a method, but in your ExecuteReader either you need a Statement like a SELECT-Statement or something like an Index!
    : The only thing making sense would be, if "CloseConnection" has a return-Value you want to use as the Argument in the ExecuteReader-Method/Property!
    :
    : Greetz
    : Zvoni
    : ------------------------------------------
    : Only stupidity of mankind and the universe
    : are infinite, but i'm not sure concerning
    : the universe. A. Einstein
    :
    :

  • BarkeeperBarkeeper Member Posts: 335
    Can you post the Description of Cmd.ExecuteReader (Result, Arg-Types etc.) as well as the Description of CommandBehavior.CloseConnection (Result etc.). If it's in the MSDN, note that i only have the 1998-Version of MSDN (Yes, i'm an Oldtimer *grin*)

    Zvoni

    : I have tried with the execute reader blank and with the commandbehavior. both give same error. Microsoft examples do it both ways. My other projects using mysql and access have no parameters, and I did it that way and still nothing but error.

    : : : ******************************
    : : : Rdr = Cmd.ExecuteReader([red]CommandBehavior.CloseConnection[/red]) <---- Error occurs here
    : : : ******************************
    : : : lstResult.Items.Clear()
    : : : While Rdr.Read()
    : : :
    : : : End While
    : : : Conn.Close()
    : : :
    : :
    : : Right, i don't know the Object-Model of the SQLServerCE, but my guts are telling me, that the red code is dodgy!
    : :
    : : What kind of Object is "CommandBehavior"?
    : : And "CloseConnection" sounds like a method, but in your ExecuteReader either you need a Statement like a SELECT-Statement or something like an Index!
    : : The only thing making sense would be, if "CloseConnection" has a return-Value you want to use as the Argument in the ExecuteReader-Method/Property!
    : :
    : : Greetz
    : : Zvoni
    : : ------------------------------------------
    : : Only stupidity of mankind and the universe
    : : are infinite, but i'm not sure concerning
    : : the universe. A. Einstein
    : :
    : :
    :
    :

    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • seanpennseanpenn Member Posts: 5
    looks like I got it. turned out it was the sql syntax. other db's will use the commands from the language. but sqlce will not. had to entirely change the where clause and it worked.

    Thanks


    : Can you post the Description of Cmd.ExecuteReader (Result, Arg-Types etc.) as well as the Description of CommandBehavior.CloseConnection (Result etc.). If it's in the MSDN, note that i only have the 1998-Version of MSDN (Yes, i'm an Oldtimer *grin*)
    :
    : Zvoni
    :
    : : I have tried with the execute reader blank and with the commandbehavior. both give same error. Microsoft examples do it both ways. My other projects using mysql and access have no parameters, and I did it that way and still nothing but error.
    :
    : : : : ******************************
    : : : : Rdr = Cmd.ExecuteReader([red]CommandBehavior.CloseConnection[/red]) <---- Error occurs here
    : : : : ******************************
    : : : : lstResult.Items.Clear()
    : : : : While Rdr.Read()
    : : : :
    : : : : End While
    : : : : Conn.Close()
    : : : :
    : : :
    : : : Right, i don't know the Object-Model of the SQLServerCE, but my guts are telling me, that the red code is dodgy!
    : : :
    : : : What kind of Object is "CommandBehavior"?
    : : : And "CloseConnection" sounds like a method, but in your ExecuteReader either you need a Statement like a SELECT-Statement or something like an Index!
    : : : The only thing making sense would be, if "CloseConnection" has a return-Value you want to use as the Argument in the ExecuteReader-Method/Property!
    : : :
    : : : Greetz
    : : : Zvoni
    : : : ------------------------------------------
    : : : Only stupidity of mankind and the universe
    : : : are infinite, but i'm not sure concerning
    : : : the universe. A. Einstein
    : : :
    : : :
    : :
    : :
    :
    : ------------------------------------------
    : Only stupidity of mankind and the universe
    : are infinite, but i'm not sure concerning
    : the universe. A. Einstein
    :
    :

Sign In or Register to comment.