Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

passing values 1

Status
Not open for further replies.

kebele

MIS
Joined
Jul 31, 2006
Messages
107
Location
US
Trying to pass the value from the above. It works fine when the selection is only one state,however, if the selection is more than one i get an error msg.
here is what i wanted to do
Sql = Sql & "AND R.ASTATE IN ('MN',WI','RI') something like this can some one help. thanks

Dim strState As String = ""
For Each liThisOne In lstState.Items
If liThisOne.Selected Then
strState = strState & "(" & "'" & liThisOne.Value & "'" & ")" & " , "
End If
Next
Response.Write("Vendor Name " & strState & "<br />")

If strState.Length > 0 Then
gvSearch.Visible = True
strState = Left(strState, strState.Length - 3)

here is clip of my sql statement

Sql = Sql & "AND R.ASTATE IN" & strState
 
Hi guys,
I think i figured it out but i am not quite sure it is the best approach. let me know if you have any suggestion for me thanks. here is what i did to fix it


Dim strState As String = ""
For Each liThisOne In lstState.Items
If liThisOne.Selected Then
strState = strState & "'" & liThisOne.Value & "'" & ","
End If
Next

Response.Write("Vendor Name " & strState & "<br />")

If strState.Length > 0 Then
gvSearch.Visible = True
strState = Left(strState, strState.Length - 1)
strState = "(" & strState & ")
 
As you have suggested to me I build a parameterized query instead of building ad hoc SQL but I am not getting any result. I just run the query and it works fine.
any clue why this is not working? I am working with oracle 9i and wondering if i use "@" or ":" ? please help thanks

Dim Sql As String = " SELECT DISTINCT V.VENDOR,R.ADDRNUM,V.VNAMEL,R.AADDR1,R.ACITY,R.VASST1, "
Sql = Sql & "R.ASTATE,R.AZIPCODE, R.APHONE, R.VASST1, to_char(Max(P.DATEPUR),'MM/DD/YYYY') Last_Plan_Purchased_Date "
Sql = Sql & "FROM VENDOR V,VENDADDR R, PLANHOLD P "
Sql = Sql & "WHERE V.VENDOR = R.VENDOR AND P.VENDOR = R.VENDOR "
Sql = Sql & " AND (P.DATEPUR >= TO_DATE('1999-01-01','YYYY-MM-DD')) "
Sql = Sql & "AND P.DATEPUR In ( select Max(P.DATEPUR) from PLANHOLD P where P.vendor = R.VENDOR) "
Sql = Sql & "AND V.VENDOR =:vendname "

Dim myCommand3 As OracleCommand = New OracleCommand(Sql, New OracleConnection(GetConnectionString()))

myCommand3.Parameters.AddWithValue(":vendname", "%" & txtVendorName.Text & "%")
Dim myAdapter3 As New OracleDataAdapter(myCommand3)

Dim myDataSet As New DataSet
myAdapter3.Fill(myDataSet, "VendorSearch")

gvSearch.DataSource = myDataSet
gvSearch.DataBind()
lblTotal.Text = gvSearch.Rows.Count
 
I believe your use of the colon is correct, but it looks like you want to use LIKE instead of = as in "AND V.VENDOR LIKE :vendname".

Does that fix the problem?
 
Yes, the problem has been fixed. as you suggested i had to change = to like. now it worked! you deserve my star. thanks again
 
I Just added a couple more paramters where i can select two max and min date from drop down controla and i am getting an error message. I kind of pin point where the error happened but do not know how to fix it. any help would be appreciated. I get the error msg when i added between clause to select my min and max date.If i remove that line and the two parameter the error disappear. I really appreciate your help as always.

Sql = Sql & "AND V.VENDOR like :vendID "
Sql = Sql & "AND to_number(to_char(p.datepur, 'YYYY'))BETWEEN :MinPDate And :MaxPDate"



Exception Details: System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number
myCommand3.Parameters.Clear() 'Clear the parameters
myCommand3.Parameters.AddWithValue(":vendID", "%" & txtVendorName.Text & "%")
myCommand3.Parameters.AddWithValue(":MinPDate", drPur.SelectedItem.Value)
myCommand3.Parameters.AddWithValue(":MaxPDate", drPurM.SelectedItem.Value)

ORA-01036: illegal variable name/number

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.


Source Error:


Line 120:
Line 121: Dim myDataSet As New DataSet
Line 122: myAdapter3.Fill(myDataSet, "VendorSearch")
Line 123:
Line 124: gvSearch.DataSource = myDataSet

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top