[02/03] stored proc problem

[Ddans]

I have a vb program that adds data into an Access Database. The query for this add works perfectly, but for some reason I can't get it to link to my program correctly. My break is comming on the ".executenonquery()", which leads me to believe its a query problem, but I don't see how thats possible if it works in Access...Here is the code, thanks for any help.
Code in my module that i call

Public Sub AddMember(ByVal storedProc As String)
        Dim spName As String
        Dim cmd As New OleDbCommand
 
        odaMembers.SelectCommand = New OleDb.OleDbCommand
        spName = storedProc
        cnnAggieSatMembers.Open()
 
        With odaMembers.SelectCommand
            .CommandType = CommandType.StoredProcedure
            .CommandText = spName
 
            .Parameters.Add("@spLastName", OleDbType.VarChar, 25).Value = frmMain.txtNewLastName.Text
            .Parameters.Add("@spFirstName", OleDbType.VarChar, 25).Value = frmMain.txtNewFirstName.Text
            .Parameters.Add("@spPhone", OleDbType.VarChar, 25).Value = frmMain.txtNewPhone.Text
            .Parameters.Add("@spEmail", OleDbType.VarChar, 50).Value = frmMain.txtNewEmailAddress.Text
            .Parameters.Add("@spClass", OleDbType.VarChar, 10).Value = frmMain.txtNewClass.Text
            .Parameters.Add("@spexpectedgraduation", OleDbType.VarChar, 25).Value = frmMain.txtNewExpectedGraduation.Text
            .Parameters.Add("@spMajor", OleDbType.VarChar, 25).Value = frmMain.txtNewMajor.Text
 
            .Connection = cnnAggieSatMembers
            .ExecuteNonQuery()
        End With
        cnnAggieSatMembers.Close()
    End Sub

Code from my form

Private Sub btnNewMember_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewMember.Click
        Dim spLastName As String
        Dim spFirstName As String
        Dim spPhoneNumber As String
        Dim spEmail As String
        Dim spClass As String
        Dim spExpectedGraduation As String
        Dim spMajor As String
        Dim spName As String
 
        Call AddMember("spAddMember")
        odaMembers.SelectCommand = New OleDb.OleDbCommand
 
        Try
            cnnAggieSatMembers.Open()
            spName = "spSearchAll"
            dsMembers.Clear()
 
            With odaMembers.SelectCommand
                .CommandType = CommandType.StoredProcedure
                .CommandText = spName
                .Connection = cnnAggieSatMembers
            End With
 
            odaMembers.Fill(dsMembers, "myTable890")
            dgResults.DataSource = dsMembers.Tables("myTable890")
 
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        cnnAggieSatMembers.Close()
        GRBNewMemberINFO.Visible = False
 
        txtNewLastName.Text = ""
        txtNewFirstName.Text = ""
        txtNewEmailAddress.Text = ""
        txtNewPhone.Text = ""
        txtNewClass.Text = ""
        txtNewMajor.Text = ""
        txtNewExpectedGraduation.Text = ""
        btnSearchMember.Focus()
 
    End Sub

[JAKSupport]

You will need to post the stored procedure, and the exact error message for us to help you.

[Ddans]

The stored procedure (query in access) looks like this...

PARAMETERS spLastName Text ( 255 ), spFirstName Text ( 255 ), spPhoneNumber Text ( 255 ), spEmail Text ( 255 ), spClass Text ( 255 ), spExpectedGraduation Text ( 255 ), spMajor Text ( 255 );
INSERT INTO tblMembers ( LastName, FirstName, PhoneNumber, email, class, expectedgraduation, Major )
VALUES ([spSearchLastName], [spFirstName], [spPhoneNumber], [spEmail], [spClass], [spexpectedgraduation], [spMajor]);

There error I'm getting is the following, "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll." It hits on the "executenonquery()" part of the code. Thanks for any help.

[techgnome]

Help me here..... when the heck did they add Stored Procs to Access? .....

As far as I know Access does NOT SUPPORT stored procedures..... now.... it is possible to use PARAMETERIZED QUERIES..... but they have a different syntax....

NSERT INTO tblMembers ( LastName, FirstName, PhoneNumber, email, class, expectedgraduation, Major )
VALUES (?, ?, ?, ?, ?, ?, ?);

And your CommandType should be Text... not StoredProcedure.

-tg

[Ddans]

Yeah thats what I meant tg. Access does not have SP's like Server SQL does, but I've used parameterized queries and adapted them to something similar to a stored procedure. Oh, and the commandtype works as storedprocedure with every other query that I've adapted to this format, so I'm not sure if thats the problem or not.