-1

this i my query and parameter list i got msg "Not All Variable bound"

Dim cmd As New OracleCommand("select POLICY_REFER, ....

from   POLICY_MASTER
where  ISSUE_DATE BETWEEN :DFrom AND :DTo 
                           And    (CLIENT_NAME = :client or :client is null)  
                           and    (POLICY_PRODUCT = :product or :product is null)
                           and DOC_STATUS = 'Parked'", sgcnn)

cmd.Parameters.Add("@DFrom", OracleDbType.Date).Value = DtpFrom.Value.ToShortDateString
            cmd.Parameters.Add("@DTo", OracleDbType.Date).Value = DtpTo.Value.ToShortDateString
            cmd.Parameters.Add("@Product", OracleDbType.Varchar2).Value = CmbProduct.Text
            cmd.Parameters.Add("@Client", OracleDbType.Varchar2).Value = CmbClient.Text

if i used just client_name in query in parameter list then it work but when i used client_name and product i got msg "Not All Variable bound"

Ejaz Sarwar
  • 9
  • 1
  • 8
  • 1
    Have you tried specifying `System.DBNull.Value` as the value? – theduck Nov 12 '18 at 09:37
  • No, how and where i can try this (System.DBNull.Value) ??? please explain little more – Ejaz Sarwar Nov 12 '18 at 09:41
  • 1
    Well - `cmd.Parameters.Add("@Product", OracleDbType.Varchar2).Value = System.DBNull.Value` for instance – theduck Nov 12 '18 at 09:48
  • i tried this like If CmbProduct.Text = "" Then cmd.Parameters.Add("@Product", OracleDbType.Varchar2).Value = System.DBNull.Value Else cmd.Parameters.Add("@Product", OracleDbType.Varchar2).Value = CmbProduct.Text End If ----------- ----------------------------------------------------------------Catch ex As Exception == Not All Variable bound ...... – Ejaz Sarwar Nov 12 '18 at 10:04
  • What if you do `AddWithValue` instead. So `cmd.Parameters.AddWithValue("@Product", System.DBNull.Value)` – theduck Nov 12 '18 at 11:43
  • 1
    I'm using Oracle.ManagedDataAccess.Client and its not support AddWithValue ... is not a member of oracle parameter collection .... this is the real issue – Ejaz Sarwar Nov 12 '18 at 11:52
  • I would prefer `cmd.Parameters.Add("@Product", OracleDbType.Varchar2).Value = IIf(String.IsNullOrEmpty(CmbProduct.Text), DBNull.Value, CmbProduct.Text)` – Wernfried Domscheit Nov 12 '18 at 12:01
  • If you get an `Not All Variable bound` then the error might be somewhere else. Please show us more of your code. – Wernfried Domscheit Nov 12 '18 at 12:02
  • Dim cmd As New OracleCommand("select POLICY_REFER, END_REFER, CLIENT_NAME, POLICY_PRODUCT, ISSUE_DATE, GROSS, DOC_TYPE, DOC_STATUS from POLICY_MASTER where ISSUE_DATE BETWEEN :DFrom AND :DTo And (CLIENT_NAME = :client or :client is null) and (POLICY_PRODUCT = :product or :product is null) and DOC_STATUS = 'Parked'", sgcnn) – Ejaz Sarwar Nov 12 '18 at 12:03
  • cmd.Parameters.Add("@DFrom", OracleDbType.Date).Value = DtpFrom.Value.ToShortDateString cmd.Parameters.Add("@DTo", OracleDbType.Date).Value = DtpTo.Value.ToShortDateString cmd.Parameters.Add("@Product", OracleDbType.Varchar2).Value = CmbProduct.Text cmd.Parameters.Add("@Client", OracleDbType.Varchar2).Value = CmbClient.Text – Ejaz Sarwar Nov 12 '18 at 12:03
  • Please edit you question with additional code instead of putting into a comment. – Wernfried Domscheit Nov 12 '18 at 12:03
  • i sent you both query and parameter list , query is working fine in sql developer but make problem in vb.net , if i used client_name in query and parameter list then its will work but when i use both client_name and product then i get msg Not All Variable bound – Ejaz Sarwar Nov 12 '18 at 12:06

1 Answers1

1

You SQL string has six parameters

**:DFrom** AND **:DTo** 
And    (CLIENT_NAME = **:client** or **:client** is null)  
and    (POLICY_PRODUCT = **:product** or **:product**

Thus you must provide six parameters:

cmd.Parameters.Add("DFrom", OracleDbType.Date).Value = DtpFrom.Value
cmd.Parameters.Add("DTo", OracleDbType.Date).Value = DtpTo.Value
cmd.Parameters.Add("Client", OracleDbType.Varchar2).Value = IIf(String.IsNullOrEmpty(CmbClient.Text), DBNull.Value, CmbClient.Text)
cmd.Parameters.Add("Client", OracleDbType.Varchar2).Value = IIf(String.IsNullOrEmpty(CmbClient.Text), DBNull.Value, CmbClient.Text)
cmd.Parameters.Add("Product", OracleDbType.Varchar2).Value = IIf(String.IsNullOrEmpty(CmbProduct.Text), DBNull.Value, CmbProduct.Text)
cmd.Parameters.Add("Product", OracleDbType.Varchar2).Value = IIf(String.IsNullOrEmpty(CmbProduct.Text), DBNull.Value, CmbProduct.Text)

I don't know whether @ is permitted/required for parameter name. Then you should remove ToShortDateString. Parameter is declared as OracleDbType.Date, so just provide the DateTime value, you don't have to consider any format topics - that's a major benefit of using bind parameters instead of putting the values as strings.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Thank you Wernfried Domscheit, its working as its should be, please explain little why client and product parameter add twice and IIF (if with two II ) ???, basically im business analyst ... not a programmer doing this programming job after 15 years – Ejaz Sarwar Nov 13 '18 at 07:35
  • `IIf(String.IsNullOrEmpty(CmbClient.Text), DBNull.Value, CmbClient.Text)` is just a shortcut for `If String.IsNullOrEmpty(CmbClient.Text) Then DBNull.Value Else CmbClient.Text End If` – Wernfried Domscheit Nov 13 '18 at 08:22
  • You defined the parameter twice in your SQL statement (even though they have the same name, the position and total number counts), thus you must provide it also twice. – Wernfried Domscheit Nov 13 '18 at 08:23
  • Once again Thank you Wernfried Domscheit... im developing an application using oracle 11g, vb.net 2015 and Crystal Reports on window 10, can you give advise/tips how i can deploy the application on client/server , i created a package and install an other pc on same network package is running fine but unable to connect to the database... – Ejaz Sarwar Nov 13 '18 at 09:23
  • Please open a new question for that - with code and proper error messages. – Wernfried Domscheit Nov 13 '18 at 10:06