dynamisk sql

Which performance are you concerned about,
database, or building your SQL statement?

Somethingelse you should concider. What happens if some of your fields arent used?

Jeg bruge flg. i et Access Data Project:

Function BuildSQLString() As String
    \'.----------------------------------.
    \'| Bygger SQL-streng til søgning    |
    \'\'----------------------------------\'
    Dim strSELECT As String
    Dim strFROM As String
    Dim strWHERE As String
   
    strSELECT = \"* \"
   
    strFROM = \"Sta_customer INNER JOIN \" & _
        \"Par_zipcode ON \" & _
        \"Sta_customer.Sta_customer_zipcodeid = Par_zipcode.Par_zipcode_zipcodeid \" & _
        \"LEFT OUTER JOIN opr_customer_grouping ON Sta_customer.Sta_customer_id = \" & _
        \"opr_customer_grouping.opr_customer_grouping_customer_id_from_sta_customer\"
   
    If chkCustomerID.Value = True Then
        txtCustomerID.SetFocus
        strWHERE = \" AND Sta_customer_id = \" & txtCustomerID.Text
    End If
   
    If chkCustomerName.Value = True Then
        txtCustomerName.SetFocus
        strWHERE = strWHERE & \" AND Sta_customer_name LIKE \'%\" & txtCustomerName.Text & \"%\'\"
    End If
   
    If chkCustomerAdress.Value = True Then
        txtCustomerAdress.SetFocus
        strWHERE = strWHERE & \" AND Sta_customer_address LIKE \'%\" & txtCustomerAdress.Text & \"%\'\"
    End If
   
    If chkCustomerZipcode.Value = True Then
        txtCustomerZipcode.SetFocus
        strWHERE = strWHERE & \" AND Sta_customer_zipcodeid = \" & txtCustomerZipcode.Text
    End If
   
    If chkCustomerCity.Value = True Then
        txtCustomerCity.SetFocus
        strWHERE = strWHERE & \" AND Par_zipcode_townname = \'\" & txtCustomerCity.Text & \"\'\"
    End If
   
    If chkCustomerPhone.Value = True Then
        txtCustomerPhone.SetFocus
        strWHERE = strWHERE & \" AND Sta_customer_phone = \'\" & txtCustomerPhone.Text & \"\'\"
    End If
   
    If chkCustomerFax.Value = True Then
        txtCustomerFax.SetFocus
        strWHERE = strWHERE & \" AND Sta_customer_fax = \'\" & txtCustomerFax.Text & \"\'\"
    End If
   
    If chkCustomerEmail.Value = True Then
        txtCustomerEmail.SetFocus
        strWHERE = strWHERE & \" AND Sta_customer_email = \'\" & txtCustomerEmail.Text & \"\'\"
    End If
   
    If chkCustomerWWW.Value = True Then
        txtCustomerWWW.SetFocus
        strWHERE = strWHERE & \" AND Sta_customer_website = \'\" & txtCustomerWWW.Text & \"\'\"
    End If
   
    If chk_CustomerGroups.Value = True Then
        cmb_CustomerGroups.SetFocus
        strWHERE = strWHERE & \" AND opr_customer_grouping.opr_customer_grouping_group_id_from_opr_customer_groups = \" & cmb_CustomerGroups.Value
    End If
   
    If chk_PrivatePerson.Value = True Then
        chk_ChoicePrivatePerson.SetFocus
        strWHERE = strWHERE & \" AND Sta_customer.sta_customer_privateperson = - 1\"
    End If
   
    BuildSQLString = \"SELECT \" & strSELECT
    BuildSQLString = BuildSQLString & \"FROM \" & strFROM
    If strWHERE <> \"\" Then BuildSQLString = BuildSQLString & \" WHERE \" & Mid$(strWHERE, 6)
   
End Function

dfens> Just a comment to your strWHERE.
To avoid this Mid$(strWHERE, 6)
   
You can write stWhere = \"1\" at the start and then
in the others strWHERE = strWHERE & \" AND .....

also in the first which for some reason isnt the same as the others, what happens if it isnt used?

the first???

If chkCustomerID.Value = True Then
        txtCustomerID.SetFocus
        strWHERE = \" AND Sta_customer_id = \" & txtCustomerID.Text
    End If
   
in the others you write strWHERE = strWHERE & \" AND .....

doesnt matter.

you can make it the same way as the others.

I dont think anyone have ever used the first, so I works quite nicely

I think it would too :o)
But do you understand what I mean about setting the strWHERE to \"1\" at the start? This obviosuly isnt necessary as it is working. Its just a suggestion to avoid doing the Mid(..

I cant see how you can avoid using mid.

If I initiate the strWhere with \"1\", I\'ll get something like select * from table where 1 and blabla = value and blabla = value

I cant see why I should avoid using mid.

dfens> I dont say you HAVE to avoid using it I am just suggestion that you CAN avoid uisng it :o)

(Du behøver ikke brug det)

Ok. But I still cannot see how to avoid using mid.

I\'ll get something like select * from table where 1 and blabla = value and blabla = value, if I use your example

Exactly! \"WHERE 1\" will return all records.
If any search fields were selected then you will get your WHERE 1 and blabla = value and blabla = value.
Without having to use mid.

ok....that I didnt know. Thx

jeg tror jeg forstår princippet i opbygningen af den sql streng dfens, men jeg er ikke i stand til at læse hvordan jeg skal putte værdierne fra en dropdown boks med multiple valg ind i opbygningen

ja....det er der med at læse spørgsmålet grundigt. :-) Jeg beklager.

Jeg mener at den bedste løsning må være:

select * from table where kolonne1 in (\'A,B,C,F,H\')

Den anden del er svaret var mere generel opbygning af søge-formular og sql-forespøgsler.

undskyld jeg er lidt lang i spyttet men jeg har haft problemer med at få adgang til experten.

Jeg er bange for at mine array og sql kundskaber ikke rækker til at få implementeret dit løsningsforslag.

Kunne du eventuelt skitsere hvordan du ville omskrive: strWHERE = strWHERE & \" AND Sta_customer_phone = \'\" & txtCustomerPhone.Text & \"\'\" hvis det i stedet for et tekst felt var værdierne fra et array der skulle fyldes ind?

Jeg kan kun fortælle hvordan jeg kan gøre det uden for SQL, dvs. vb eller asp:

Jeg vil forslå at du løber dit array igennem denne funktion:
http://www.activeserverpages.dk/aspdigital/kapitel1/arrays01.asp

og får skrevet resultaterne ud i kommasepereret format og patcher den kommasep. string ind i din sql kommando

alt conn.execute(\"select * from table where kolonne1 in (\'\" & strKommasep & \"\')\"

I am getting confused here! bispen> Are we talking ASP, Access or what?

tjah efter min opfattelse taler vi mest sql i og med det handler om at opbygge en sqlsætning. Og sætningen skal afvikles med en sql 7 så vi taler ikke access?

tjah!

terry> we\'re talking sql, I guess but the easiiest reference material I could find where vbs.

And the idea is the same ...ending up with the sql-statement that does the job.

Hi dfens> yes I know we are talking SQL, but there must be some sort of user interface (Access, VB, WEB(asp, vbs, jscript) or something) and it all depends on this on how things are done. Yes the idea is still the same but the coding will more than likely be different.
What do you say bispen?

Okay nu er jeg med på hvad du spurgte til tidligere. Jeg troede at selve sql\'en kunne opbygges uafhængigt af applikationen den skal kaldes fra - sorry. Sql kaldet skal afvikles fra en asp side (vb script).

bispen>are you EVER going to close this question?