SQLCE DATAREADER Problem (Probably simple)

[indyrob]

I'm pretty much at my wits end here and I know I'm missing something simple...

I have a form (ME) where the user inputs a value in a textbox called textbox2. This the WORKORDERNO.

What's supposed to happen is if there is a match to what was entered in ME.textbox2.text then a new form (FORM1) opens and the data from the database populates the textbox fields.

I'm using the SQLCEDATAREADER.

I have played around with this for a couple of days and I keep getting an error that a parameter is missing (ordinal 1) which is the column STARTMILES in the database. I can substitute WORKORDERNO for this field and it works.

Here is the code for this section I'm getting errors with..

'SET THE CONNECTION STRING
        Dim CONSTRING As String = "DATA SOURCE = C:\TECHTOOLS.SDF"

'SETUP THE CONNECTION
        Dim CON As New SqlCeConnection(CONSTRING)

'DEFINE THE SELECT STATEMENT IN SQL
            Dim SQLSELECT As String = "SELECT WORKORDERNO, STARTMILES, ENDMILES, STARTTIME, " & _   

                                      "ENDTIME, DATE, CUSTOMER, CITY " & _
                                      "FROM WORKORDERS WHERE WORKORDERNO = @WORKORDERNO"

            Dim CMD2 As New SqlCeCommand(SQLSELECT, CON)
            
            CON.OPEN()

            'EXECUTE THE READER
            Dim RDR2 As SqlCeDataReader = CMD2.ExecuteReader

            'DEFINE THE SEARCH PARAMETER
            CMD2.Parameters.Add("@WORKORDERNO", Me.TextBox2.Text)


            'FILL IN THE FIELDS ON FORM1 AS THE READER READS
            While RDR2.Read()
                Form1.wotxtbox.Text = CStr(RDR2("WORKORDERNO"))
                Form1.startmilestxtbox.Text = CStr(RDR2("STARTMILES"))
                Form1.endmilestxtbox.Text = CStr(RDR2("ENDMILES"))
                Form1.starttimetxtbox.Text = CStr(RDR2("STARTTIME"))
                Form1.endtimetxtbox.Text = CStr(RDR2("ENDTIME"))
                Form1.datetxtbox.Text = CStr(RDR2("DATE"))
                Form1.custtxtbox.Text = CStr(RDR2("CUSTOMER"))
                Form1.citytxtbox.Text = CStr(RDR2("CITY"))
            End While

            RDR2.Close()
            CON.Close()

The error I'm getting is that the parameter is not defined (ordinal 1).

Thanks in advance!

 

[jmcilhinney]

You're executing the command BEFORE you add the parameter. You need to put these lines:

            'EXECUTE THE READER
            Dim RDR2 As SqlCeDataReader = CMD2.ExecuteReader

            'DEFINE THE SEARCH PARAMETER
            CMD2.Parameters.Add("@WORKORDERNO", Me.TextBox2.Text)

The other way around.

 

[indyrob]

DOH!!! Thank you very much!

I suppose I should have stepped through it with the debugger *embarrassed*

 

[rickydalley]

that still won't work

Hi.

I hit this using parameters.

It won't recognise the @WORKORDERNO you have in your sql statement.

SQL CE doesn't use named parameters in the sql.

Use ?

As you only have 1 parameter replacing the @WORKORDERNO with a single ? will fix it.

If you have multi ones like

Select * From Fares Where FareId=? And FareHash>?

you'd need to define the FareId one first then the FareHash one next

cmd.Parameters.Add("fareid",somevalue)
cmd.Parameters.Add("farehash",anothervalue)

Does this make sense to you? I had to dig to find why my named parameters weren't working and found it on some obscure website but it fixed my problems.

The trickiest part is making sure you create the parameters in order according to the sql statement.

regards, Ricky

 

[JohnH]

Hi.

I hit this using parameters.

It won't recognise the @WORKORDERNO you have in your sql statement.

SQL CE doesn't use named parameters in the sql.

Use ?

SQL CE 2008 (3.5) that is included with VB 2008 supports named parameters, this is the platform indyrob is using. Also the older SQL CE 2005 (3.0) that perhaps was more common to use with your VB 2005 platform support named parameters. However, SQL CE 2000 (2.0) did not support named parameters.