I have created a access query where if I give parameter value Dept
Code like 41 then the details of this department is shown. But If I
give null then It will show All the detartments details one after
another. But my problem is that I can't pass that Null value to that
query from VB.NET it is not work. My working sheet is given below:

Private Sub loadDepttest(ByVal off As Object)
              Try

            Dim com As New OleDbCommand("qry_test", pageCon)
            com.CommandType = CommandType.StoredProcedure
            com.Parameters.Add(("[ID]", OleDbType.VarChar).Value = off
            Dim ds As New DataSet
            Dim da As New OleDbDataAdapter
            da.SelectCommand = com
            da.Fill(ds, DeptDSname)
            da.Dispose()
            dgvTest.DataSource = ds.Tables(DeptDSname)
            dgvTest.Columns("dept_ID").Visible = False

            dgvTest.Columns("dept_Desc").HeaderText = "Department"
            dgvTest.Columns("dept_Desc").Width = 250
            dgvTest.ColumnHeadersDefaultCellStyle.Alignment =
DataGridViewContentAlignment.MiddleCenter



        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub



Next I cll this function from a Button like

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
        loadDepttest(TextBox1.Text)
    End Sub

and my access query is given below:

SELECT Department.dept_ID, Department.dept_Desc
FROM Department
WHERE (((Department.off_ID) Like IIf(IsNull([ID]),'*') Or
(Department.off_ID)=([ID])));


How can I solve this problem????????plz Help

You seem to have a syntax error in your Access query. To ensure that you have 
correct syntax, substitute your parameter with its  value and try to run the 
query inside MS Access - this way you eliminate most of them. As for your 
query - IIf function takes 3 parameters, and you only feed it with two :-)

"akter.babu@gmail.com" wrote:


> I have created a access query where if I give parameter value Dept
> Code like 41 then the details of this department is shown. But If I
> give null then It will show All the detartments details one after
> another. But my problem is that I can't pass that Null value to that
> query from VB.NET it is not work. My working sheet is given below:
> 
> Private Sub loadDepttest(ByVal off As Object)
>               Try
> 
>             Dim com As New OleDbCommand("qry_test", pageCon)
>             com.CommandType = CommandType.StoredProcedure
>             com.Parameters.Add(("[ID]", OleDbType.VarChar).Value = off
>             Dim ds As New DataSet
>             Dim da As New OleDbDataAdapter
>             da.SelectCommand = com
>             da.Fill(ds, DeptDSname)
>             da.Dispose()
>             dgvTest.DataSource = ds.Tables(DeptDSname)
>             dgvTest.Columns("dept_ID").Visible = False
> 
>             dgvTest.Columns("dept_Desc").HeaderText = "Department"
>             dgvTest.Columns("dept_Desc").Width = 250
>             dgvTest.ColumnHeadersDefaultCellStyle.Alignment =
> DataGridViewContentAlignment.MiddleCenter
> 
> 
> 
>         Catch ex As Exception
>             MessageBox.Show(ex.ToString)
>         End Try
>     End Sub
> 
> 
> 
> Next I cll this function from a Button like
> 
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
>         loadDepttest(TextBox1.Text)
>     End Sub
> 
> and my access query is given below:
> 
> SELECT Department.dept_ID, Department.dept_Desc
> FROM Department
> WHERE (((Department.off_ID) Like IIf(IsNull([ID]),'*') Or
> (Department.off_ID)=([ID])));
> 
> 
> How can I solve this problem????????plz Help
> 
>