• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

access stored procedure works in Access but not it Vb.net Winform

I have a stored Procedure in MS Access and it works in MS Access but when I call that Stored Procedure from a vb.net win form it returns
nothing in terms of Data. IN Access it looks like this
SELECT *
FROM [Global]
WHERE (((Global.LDAP) Like '*OU=NAZ*'));

The stored Procedure is called NAZUsers and data is from a LDAP Query that pulls every user in the Active Directory. I wanted to narrow data down to one OU (Organizational Unit) so that's what the Like statement is for

In vb.net a have a class

 Public Function GetNazPeople() As DataTable
        '  OleDbConnection.ReleaseObjectPool()
        Dim con As New OleDbConnection(connectionString)
        Dim cmd As New OleDbCommand("NAZUsers", con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim adapter As New OleDbDataAdapter(cmd)

        Dim ds As New DataSet()
        adapter.Fill(ds, "NAZUsers")



        ' Set up a relation between these tables.
        Return ds.Tables("NAZUsers")
        con.Close()
    End Function

In the Win form
the call is  DataGridView1.DataSource = Program.StoreDB.GetNazPeople
I'm using now  Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=NazScheduler.mdb;" instead of accdb extension for Access 2010.

It's just this Stored Procedure that doesn't work called from VB.NET Win form My other stored Proceures
work outside of Access.
0
powerztom
Asked:
powerztom
  • 5
1 Solution
 
PatHartmanCommented:
This may be a semantics issue but ".mdb" files do not support stored procedures.  You need to be using an .accdb to have an ACE stored procedure.  So if it really is an .accdb and you really do have a stored procedure, you need to change the provider to be ACE rather than Jet to access an .accdb.
0
 
powerztomAuthor Commented:
I changed Connection to  Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CHEUSERS1.accdb;Persist Security Info=False;" I get CHEUSERS is not a Stored Procedure
the NazUsers I refered to earlier I recreated it in new .accdb format as CHEUsers but the syntax has not changed
StillSELECT *
FROM CHEUSERS
WHERE (((CHEUSERS.LDAP) Like '*OU=NAZ*'));
here is error message screen shot
Debug.png
0
 
powerztomAuthor Commented:
Please Help.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
powerztomAuthor Commented:
Still same issue when I create a sql string instead of Stored Procedure.
 Dim SQLString As String = "SELECT * FROM CHEUSERS WHERE LDAP Like '*OU=NAZ*'"

        Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CHEUSERS1.accdb;Persist Security Info=False;")
        Dim DataSet1 As New DataSet()
        Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)
        OleDBConn1.Open()

        OleDbDataAdapter1.Fill(DataSet1, "NAZUsers")
        DataGridView1.DataSource = DataSet1.Tables("NazUsers")

        OleDBConn1.Close()

Open in new window

0
 
powerztomAuthor Commented:
I figured it out Here is code I wrote
 Private Sub btnviewNAZ_Click(sender As Object, e As EventArgs) Handles btnviewNAZ.Click
        ' On Error Resume Next
        DataGridView1.DataSource = Nothing
        DataGridView1.ClearSelection()
        Dim ConnetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CHEUSERS1.accdb;Persist Security Info=False"

        ' sql = "Select PlayerID,FirstName,LastName,TeamID,Jersey,Position,Status,Height,Weight,Born,College,"
        ' sql &= "BioHTML, HighSchool FROM NFLPLAYERS WHERE Status ='A' AND Position<> ''"
        ' SqlConnectionStr
        Sql = "SELECT * FROM CHEUsers"
        Sql = Sql & " WHERE " & " " & "LDAP like '%" & _
                "OU=NAZ" & "%'"



        connection = New OleDbConnection(ConnetionString)
        Dim ds As New DataSet
        Try
            connection.Open()
            Dim adapter = New OleDbDataAdapter(Sql, connection)
            adapter.Fill(ds)
            connection.Close()
            DataGridView1.DataSource = ds.Tables(0)

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        ' ds.Tables(Currentrow) = "Lastname" = txtLname.Text
        ' ds.Tables(Currentrow).Column("College") = txtCollege.Text

        ' txtLname.Text = ds.Tables(Currentrow).TableName("Lastname")
        ' txtCollege.Text = ds.Tables(Currentrow).Item("College")

        Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ColumnHeadersHeight = 55
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        ' Add the image column to the grid.
        ' dgvinjury.Columns.Add(imageCol)
    End Sub

Open in new window


Close THIS QUESTION
0
 
powerztomAuthor Commented:
The guy with the Questions Solves his problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now