Pass Null parameter from VB.NET to Access Query

A

akter.babu

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
 
G

Guest

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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top