Check if there is a value in a parameter collection

[kebele]

Hi Experts,
I am using the below code to see if there is a value in the paramter collection and if there is a value it will execute the next line sql.However, if the users leave the text box empty then i do not have a value in the paramter collection and i do not want to execute the next line of sql statemnt.I do not know how to do this can someone help.thanks a bunch

If Not txtVendorName.Text = "" Then
Sql = Sql & " AND (Upper(V.VNAMEL) LIKE '%' || Trim(UpperVendorName))|| '%') "
End If
Sql = Sql & " AND V.VOBSOLET = 'N' "
Sql = Sql & "GROUP BY V.VENDOR, R.ADDRNUM,V.VNAMEL,R.AADDR1,R.AADDR2,R.ACITY,R.ASTATE, R.AZIPCODE, R.APHONE, R.VASST1, P.DATEPUR "
Sql = Sql & "ORDER BY V.VENDOR "

Response.Write("Sql " & "<br/>" & Sql & "<hr/>")
SqlDataSource1.SelectCommand = Sql

 

[jbenson001]

The simpilist solution, that I would use, is to use a RequiredFieldValidator on the textVenorName textbox.

Jim

 

[kebele]

Thank you jim for quick response.The reason that i can not use that is because the customers have the choice not to enter any value in the text box and select from the other two dropdowns.here is the code i use to text for the other two dropdowns but the text box since i am using sql paramters i do not know how to test this. any help would be appreciated

If (strState.Length > 0 And lstState.SelectedIndex <> 0) Then
strState = Left(strState, strState.Length - 1)
strState = "(" & strState & ")"
Sql = Sql & "AND R.ASTATE IN " & strState
End If

If (drPur.SelectedIndex <> 0 And drPurM.SelectedIndex <> 0) Then
Sql = Sql & " AND to_number(to_char(p.datepur, 'YYYY')) between " & "'" & MinPDate & "'" & " AND " & "'" & MaxMDate & "'"
End If

If Not txtVendorName.Text = "" Then
Sql = Sql & " AND (Upper(V.VNAMEL) LIKE '%' || Trim(UpperVendorName))|| '%') "
End If
Sql = Sql & " AND V.VOBSOLET = 'N' "
Sql = Sql & "GROUP BY V.VENDOR, R.ADDRNUM,V.VNAMEL,R.AADDR1,R.AADDR2,R.ACITY,R.ASTATE, R.AZIPCODE, R.APHONE, R.VASST1, P.DATEPUR "
Sql = Sql & "ORDER BY V.VENDOR "

Response.Write("Sql " & "<br/>" & Sql & "<hr/>")
SqlDataSource1.SelectCommand = Sql

 

[jbenson001]

i do not know how to test this.

Run the project and enter parmeters as the user would and use your response.write or the debug window to make sure it is correct, just don exectue the SQL until you know it is correct.

As for me, I would do this in a stored procedure and checking the parameters in it and execute the sql as necessary.

Jim

 

[kebele]

thank you jim agin.I know if would have been a lot easier if i use sp unfortunately i do not have the right to create sp.
Here is the situation jim, I did check my sql script using Response.Write("Sql " & "<br/>" & Sql & "<hr/>") and it works fine excetp when i do not enter a value for the text box then i do not row selected but i was suppose to get the resutl because i execlude the paramter if the vlaue is not enter to the text box. here is what i did

If Not txtVendorName.Text = "" Then
Sql = Sql & " AND (Upper(V.VNAMEL) LIKE '%' || Trim(UpperVendorName))|| '%') "
End If

this is just a guess probably this line is executing SqlDataSource1.SelectCommand = Sql

 

[jbenson001]

Are you saying that thest lines execute no matter if you enter a value or not?

If Not txtVendorName.Text = "" Then
            Sql = Sql & " AND (Upper(V.VNAMEL) LIKE '%' || Trim(Upper(:VendorName))|| '%')  "
        End If

Jim

 

[kebele]

No it does not execute if no value entered and the sql line won't be in my sql statemnt when i checked it Response.Write("Sql " & "<br/>" & Sql & "<hr/>") i was suppose to get a value when I make a selection from the other dorpdowns but i just get a now result. here is my sql statement when i do not enter a value for text box. thanks jim

Sql
SELECT distinct V.VENDOR "Vendor Id",R.ADDRNUM,V.VNAMEL "Vendor Name",R.AADDR1,R.ACITY,R.VASST1, R.ASTATE State,R.AZIPCODE, to_char(Max(P.DATEPUR),'YYYY/DD/MM') "Plan Purchased Date" , TRIM (r.aaddr1 || decode(trim(r.aaddr2),null,'',' - ') || r.aaddr2) Address, substr(decode(trim(r.vasst1),null, 'N/A','000/000-0000?','N/A','000/000-0000','N/A', r.vasst1),1,12) Fax, substr(decode(trim(r.aphone),null, 'N/A','000/000-0000?','N/A', r.aphone),1,12)Phone FROM VENDOR V,VENDADDR R, PLANHOLD P WHERE V.VENDOR = R.VENDOR AND P.VENDOR = R.VENDOR AND (P.DATEPUR >= TO_DATE('1999-01-01','YYYY-MM-DD')) AND P.DATEPUR In ( select Max(P.DATEPUR) from PLANHOLD P where P.vendor = R.VENDOR) AND R.ASTATE IN ('AL') or (Upper(V.VNAMEL) LIKE '*' ) AND V.VOBSOLET = 'N' GROUP BY V.VENDOR, R.ADDRNUM,V.VNAMEL,R.AADDR1,R.AADDR2,R.ACITY,R.ASTATE, R.AZIPCODE, R.APHONE, R.VASST1, P.DATEPUR ORDER BY V.VENDOR

 

[kebele]

Jim is it possible to check the there is a value for this paramtere

like sqlDataSource.selectedparamteres..... thanks
<SelectParameters>
<asp:ControlParameter ControlID="txtVendorName" Name="VendorName" PropertyName="Text" Type="String" />
</SelectParameters>

from the code behind

 

[kebele]

Finally, it worked for me! I just added the below lines of code

Dim myCommand3 As OracleCommand = New OracleCommand(Sql, New OracleConnection(GetConnectionString()))
myCommand3.Parameters.Clear() 'Clear the parameters
myCommand3.Parameters.AddWithValue(":VendorName", txtVendorName.Text)

Thank you all for the help. I really appreciate that

 

[jbenson001]

Great!! glad you got it

Jim