Visual Studio SQL adapter SELECT query with parameter (SQL AND, OR , IN)

Question

I have developed an app in Visual Studio in my company. Everything works well (with many difficulties for me) but now I'm not able to solve a problem and I haven't found a solution on the web. So I need your help.

I have a database with one table in which I store different data (I summarize here):

ID of line (st_ID)
code (st_ID_Prova)
Type of test (st_tipo_prova)
many values

What I have done and work well with Table adapter wizard in Dataset manager is to filter my data by 'st_ID_Prova' and 'st_tipo_prova'

SELECT        
    st_ID, st_ID_Prova, st_termica1, st_termica2, st_termica3, st_termica4, 
    st_termica5, st_termica6, st_termica7, st_termica8, st_termica9, 
    st_termica10, st_delta, st_commento, st_f_switch, st_KLIXON, st_ora, 
    st_gradiente1, st_gradiente10, st_gradiente2, st_gradiente3, 
    st_gradiente4, st_gradiente5, st_gradiente6, st_gradiente7, 
    st_gradiente8, st_gradiente9, st_tipo_prova, st_termica11, 
    st_gradiente11, st_termica12, st_gradiente12, st_termica13, 
    st_gradiente13, st_termica14, st_gradiente14, st_termica15, 
    st_gradiente15, st_termica16, st_gradiente16
FROM
    st_termiche
WHERE        
    (st_ID_Prova LIKE @ID_PROVA) AND (st_tipo_prova LIKE @test_type)
ORDER BY 
    st_ID

Each 'st_ID_Prova' contains many 'st_tipo_prova' and I want to display data of more than one 'st_tipo_prova' for only one 'st_ID_Prova'

For example

st_ID_Prova = 3

contains

st_tipo_prova = A
st_tipo_prova = B
st_tipo_prova = C

One line ST_ID, ST_ID_PROVA, st_tipo_prova, ...many values

1,3,A,.....
2,3,A,.....
3,3,B,.....
4,3,C,.....
4,3,C,.....
4,3,C,....

I have tried to pass parameter using

variabile_tipo_prova="A or B or C"

Me.St_termicheTableAdapter.Fill(Me.Dati_vari_dataset.st_termiche, "" & st_grafico_ID_PROVAToolStripTextBox.Text & "", "" & variabile_tipo_prova)**

but it is not correct. The correct way is:

(st_tipo_prova LIKE A) or (st_tipo_prova LIKE B) or (st_tipo_prova LIKE C)

But I cannot do that.

I have tried with SQL IN, very good for me

SELECT 
    st_ID, st_ID_Prova, 
    --- all those many many other columns 
FROM            
    st_termiche
WHERE
    (st_ID_Prova LIKE @ID_PROVA) 
    AND (st_tipo_prova IN (@test_type))
ORDER BY 
    st_ID

I have tried to pass parameter

variabile_tipo_prova="'A', 'B', 'C'"

Me.St_termicheTableAdapter.Fill(Me.Dati_vari_dataset.st_termiche, "" & st_grafico_ID_PROVAToolStripTextBox.Text & "", "" & variabile_tipo_prova)

but this doesn't work.

But if I change the select string in the dataset in this way

SELECT 
    st_ID, st_ID_Prova, 
    --- all those many many other columns 
FROM            
    st_termiche
WHERE
    (st_ID_Prova LIKE @ID_PROVA) 
    AND (st_tipo_prova IN ('A','B','C'))
ORDER BY 
    st_ID

IT RUNS

WHY? How can I pass the parameter @test_type to obtain the same result? How can I display the select command during debug to understand what happened.

Thanks to everyone.

You need a Table-Valued Parameter. The reason why passing actual code in the parameter doesn't work is because **parameters are data, not code** and will never be interpreted as such. And that is a really bad DB design. Those `col1,col2,col3` should be separate rows — Charlieface, Feb 25 '21 at 00:22
Hy, many Thanks for your time I have just Used a view so I will try...why are you speaking about bad design... The app is for motor testing. Each line contains, number of test, type of running test, time, values of thermal sensors. Col1=thermal sensor1, col2=thermal srnsor2 and so on. It is a picture of the motor at time x... Then I plot on a chart all data... — Claudio Fassi, Feb 25 '21 at 00:38
I suppose it would depend on whether the relation `st_termiche` is defined as having exactly 16 of `st_termica` and `st_gradiente`, no more no less. I think that unlikely. See also https://stackoverflow.com/questions/23194292/normalization-what-does-repeating-groups-mean — Charlieface, Feb 25 '21 at 00:57

score 0 · Answer 1 · answered Feb 26 '21 at 15:48

AND (st_tipo_prova IN (@test_type)) this will never work because the IN expects a number of values, seperated by a comma.
But you are passing a variable, which is only ONE value. It does not matter that you put some text like "A, B, C" in your variable, it is still one value "A, B, C" and not 3 values.
The only way to do it like this is using a dynamic query.
But your real problem is a fault in your DB design. If you have more then one value in one column, then you need to create another table to hold these values.
And then the query you need would be very simple.

If you really want to continue on this path, than google for "dynamic query" it will enable you to run this query. But keeping a flaw in your DB design will only get you into more trouble next time you need to change something.

Visual Studio SQL adapter SELECT query with parameter (SQL AND, OR , IN)

1 Answers1