SQL SELECT Statement mismatch

[Alexandru_mbm]

What's wrong with this ?

ERROR: Type mismatch in expression.

        conectare_db()

        data_table = New DataTable
        data_adapter = New OleDb.OleDbDataAdapter("SELECT id, denumire, pret FROM servicii WHERE id IN (SELECT id_serviciu FROM contracte WHERE stare='activ' AND id_beneficiar='" & Me.txt_cod_beneficiar.Text.ToString & "');".ToString, conexiune)
        command_builder = New OleDb.OleDbCommandBuilder(data_adapter)
        data_adapter.Fill(data_table)
        Me.dgv_lista_servicii_contractate.DataSource = data_table

        Dim coloana_1 As DataGridViewColumn = dgv_lista_servicii_contractate.Columns(1)
        Me.dgv_lista_servicii_contractate.Columns(0).Width = 90
        coloana_1.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        Me.dgv_lista_servicii_contractate.Columns(2).Width = 90
        Me.dgv_lista_servicii_contractate.Columns(0).HeaderText = "Cod"
        Me.dgv_lista_servicii_contractate.Columns(1).HeaderText = "Denumire / Descriere"
        Me.dgv_lista_servicii_contractate.Columns(2).HeaderText = "Pret"

        deconectare_db()

I just can't see what's wrong!!!

[Shaggy Hiker]

Which line gives you the error?

[Alexandru_mbm]

On this line, when the data_adapter is filled with data_table query. The SQL statement... but... why ?

data_adapter.Fill(data_table)

[RunsWithScissors]

SELECT id_serviciu FROM contracte WHERE stare='activ' AND id_beneficiar='" & Me.txt_cod_beneficiar.Text.ToString & "'

What datatype is id_beneficiar? If it is a numeric (integer, possibly an Identity or Autonumber?) then the exception results because you are trying to specify a parameter of type String.

You are setting single quotes around the concatenated value, which tells your database to treat the item between the sincle quotes as a String. If id_beneficiar is an Integer type, this will cause the exception you are seeing . . .

Try THIS (If the datatype of id_beneficiar is a Text, VarChar, or type OTHER than Integer, this won't work):

'Convert the String value of the text box to Integer:
Dim IdBeneficiar As Integer = Ctype(Me.txt_cod_beneficiar.Text, Integer)

'Concatenate the variable into your SQL string:
SELECT id_serviciu FROM contracte WHERE stare='activ' AND id_beneficiar= " & IdBeneficiar & "" 

Lastly, I recommend checking out the FAQ section of the Database Development forum and read up on the use of Parameters in your SQL Code . . .

Hope that helps!

[jmcilhinney]

Is 'id_beneficiar' a number, by any chance?

[Alexandru_mbm]

Sorry for the delay

Yes it's a number... LongInteger.

It's a number (not AutoNumber) stored on the "Contracte" table, to "id_beneficiar" column.

I've tried the code above (as Integer, LongInteger) but it's giving me the same error, even if i use it as "Text" (with my original code). I just don't get it.

[RunsWithScissors]

I just woke up, and I am not fully functioning yet. Can you post the rest of your code? ALso , can you tell us a little more about your tables? Is there a Primary Key in one, and a foreign key in the other?

I suspect there is a better way to acheive whatever it is you are attempting to do using IN. I can't check this right now, but I also have a feeling the IN keyword may be part of the problem. It looks to me like you are using IN where you should be using an INNER JOIN. Barring that, possibly the EXISTS keyword?

I am off to work, but I will be interested to see what you post, and what other responses come along.

I suggest checking out INNER JOIN, if there are indeed related records in your two tables. If not, then look into EXISTS (this one can get a little messy, but is actually a very efficient way of returning results in cartin cases . . .).

[Alexandru_mbm]

I have replaced my "data_adapter" with this one

data_adapter = New OleDb.OleDbDataAdapter("SELECT servicii.id, servicii.denumire, servicii.pret FROM servicii INNER JOIN contracte ON servicii.id=contracte.id_serviciu WHERE contracte.id_beneficiar=" & Me.txt_cod_beneficiar.Text & " ORDER BY servicii.id".ToString, conexiune)

For all others who may have a similar problem here is the final working code...

    Private Sub afisare_lista_servicii_contractate()

'######################################################################
' http://www.vbforums.com/showthread.php?p=3868568#post3868568
'######################################################################

        conectare_db()

        Try

            data_table = New DataTable

            data_adapter = New OleDb.OleDbDataAdapter("SELECT servicii.id, servicii.denumire, servicii.pret FROM servicii INNER JOIN contracte ON servicii.id=contracte.id_serviciu WHERE contracte.id_beneficiar=" & Me.txt_cod_beneficiar.Text & " ORDER BY servicii.id".ToString, conexiune)

            command_builder = New OleDb.OleDbCommandBuilder(data_adapter)
            data_adapter.Fill(data_table)
            Me.dgv_lista_servicii_contractate.DataSource = data_table

            Dim coloana_1 As DataGridViewColumn = dgv_lista_servicii_contractate.Columns(1)
            Me.dgv_lista_servicii_contractate.Columns(0).Width = 90
            coloana_1.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            Me.dgv_lista_servicii_contractate.Columns(2).Width = 90
            Me.dgv_lista_servicii_contractate.Columns(0).HeaderText = "Cod"
            Me.dgv_lista_servicii_contractate.Columns(1).HeaderText = "Denumire / Descriere"
            Me.dgv_lista_servicii_contractate.Columns(2).HeaderText = "Pret"

        Catch ex As Exception
            MessageBox.Show("Eroare:" & vbCrLf & vbCrLf & ex.Message, "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        deconectare_db()

    End Sub

Thanks alot RunsWithScissors for the big help.