problems with Parameters.Add? RSS

• • Reply
  

  bluephoenix

  Member

  10 Points

  2 Posts

  problems with Parameters.Add?

  Sep 25, 2003 10:19 AM|LINK

  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?
• • Reply
  

  greg.darling

  Participant

  1458 Points

  310 Posts

  Re: problems with Parameters.Add?

  Sep 25, 2003 04:58 PM|LINK

  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
• • Reply
  

  bluephoenix

  Member

  10 Points

  2 Posts

  Re: problems with Parameters.Add?

  Sep 29, 2003 11:02 AM|LINK

  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..