problems with Parameters.Add? RRS feed

  • Question

  • User-170054847 posted
    I use the following sub to fetch data from the DB (Oracle) ---------------------------------------------------- Private Sub fetch_data(ByVal cutoff_id As Integer, ByVal company_name As String) Dim mySQL As String = "select CT.* from H_CUTOFF_TRANS CT where CT.CUTOFF_ID = ? and CT.COMPANY_NAME in ?" Dim myCmd As New OleDbCommand(mySQL, objOleDbConn) Dim adapter As New OleDbDataAdapter myCmd.Parameters.Add("@CUTOFF_ID", OleDbType.Integer).Value = cutoff_id myCmd.Parameters.Add("@COMPANY_NAME", OleDbType.VarChar).Value = company_name adapter.SelectCommand = myCmd adapter.Fill(myDS, "TEST") DataGrid1.DataSource = myDS.Tables("TEST") DataGrid1.DataBind() End Sub ---------------------------------------------------- cutoff_id = 15 company_name = ('value1','Value2') The following SQL works fine when i run it in SQL Plus, but .NET returns no rows. select CT.* from H_CUTOFF_TRANS CT where CT.CUTOFF_ID = 15 and CT.COMPANY_NAME in ('value1','Value2') This is probably because the SQL doesn't look exactly like this when it is sent to the database. Is there any way that I can se the exact SQL that is sent to the DB? Is it possible that since i declared the parameter as OleDbType.VarChar, it will put 2 single qoutes on each side of the parameter? '('value1','Value2')' the adapter.Fill(myDS, "TEST") doesn't raise an exception so everything looks fine. anyone?
    Thursday, September 25, 2003 6:19 AM

All replies

  • User1349123926 posted
    Hi, You can see the exact sql sent to the database by doing a sqlnet trace. However, a sqlnet trace only shows placeholders for bind variables in the statement, so wont help much. Its kind of hard to piece together the statment from the client side trace. You may want to get a server side trace. See the following for more info: http://asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=265261 However, ultimately I dont think you'll be able to pull off what you're trying to do. The bind is the problem. It will work as long as you only pass a single value, but bombs when you try to pass mutliple ones, even using an anonymous block in pl/sql.. SQL> var v1 number; SQL> var v2 varchar2(20); SQL> begin 2 :v2 := 'SMITH'; 3 select count(*) into :v1 from emp where ename in (:v2); 4 end; 5 / PL/SQL procedure successfully completed. SQL> print v1; V1 ---------- 1 SQL> begin 2 :v2 := 'SMITH,KING'; 3 select count(*) into :v1 from emp where ename in (:v2); 4 end; 5 / PL/SQL procedure successfully completed. SQL> print v1; V1 ---------- 0 SQL> The database just doesnt like it like that. If you know how many values you're going to be passing in, you could bind each one.. ie, select ename from emp where ename in (?,?,?,?) Or, you could simply concatinate the string instead of using binds.. string str = "select ename from emp where ename in ('" + val1 + "','" + val2 +"')"; Hope it helps, Greg
    Thursday, September 25, 2003 12:58 PM
  • User-170054847 posted
    Thanx for your response.. the number of values i'm going to pass in is dynamic. if i concatinate the values in the SQL-statement as desribed above this will cause problems if the values containt characters such as ' chr(39) Does anyone know how to pass in a string like ('value1','value2') as a oledbparameter? or what I can do to resolve my problem? ..bluephoenix..
    Monday, September 29, 2003 7:02 AM