Question Passing Parameters to a Stored Procedure, getting results, but incorrect usage? :/

[Flippedbeyond]

Hi all,

I consider myself to be a beginner to intermediate programmer, i'm pretty new to working with an access file through vb. This is of low priority since i am getting the results that i want, but i just don't understand why it's working this way. Any explanation would be much appreciated.

I have a MS access 2007 .accdb file that contains a query named addStudentRegistration with the following sql

INSERT INTO Students ( Student_ID, FirstName, LastName, Type_ID )
SELECT Student_ID AS Expr1, firstName AS Expr2, lastName AS Expr3, getID_Type.ID
FROM getID_Type;

the code from my function to call this query

    Protected Function storedProcedure(ByVal procedureName As String, Optional ByVal parameterValues As Dictionary(Of String, String) = Nothing) As Boolean
        Dim success As Boolean = False

        If connectionFound Then
            Dim parameters As String() = parameterValues.Keys.ToArray()

            Try
                Dim AccessConnection As New System.Data.OleDb.OleDbConnection(connectionString)
                AccessConnection.Open()

                Dim cmd As New OleDb.OleDbCommand(procedureName, AccessConnection)
                cmd.CommandType = CommandType.StoredProcedure

                If Not (parameterValues Is Nothing) Then
                    For Each parameter As String In parameters
                        cmd.Parameters.AddWithValue(parameter, parameterValues(parameter))
                    Next
                End If


                cmd.ExecuteNonQuery()

                cmd.Dispose()
                AccessConnection.Close()


                success = True
            Catch ex As Exception
                success = False
            End Try
        End If

        Return success
    End Function

Now the issue is that if i call this function and pass in the following

        Dim parameters As New Dictionary(Of String, String)
        parameters.Add("typeName", "alumni")
        parameters.Add("student_id", "343117")
        parameters.Add("firstName", "wjwo")
        parameters.Add("lastName", "lool")

        MyBase.storedProcedure("addStudentRegistration", parameters)

and if i changed the parameter name to something different, it still does the same thing, its as if the parameter names are useless.. i dont understand exactly what is going on, but i figure it should relate to the parameter name that was given in the query from the access file no? i can't seem to get this code to work by using the Parameters.Add and just doing something like Parameters.Add("alumni"), it only works when i use the Parameters.AddWithValue("typename","alumni")

so if the previous code was exchanged with the following, the same results are still given.

        Dim parameters As New Dictionary(Of String, String)
        parameters.Add("tye", "alumni")
        parameters.Add("std", "343117")
        parameters.Add("fiNe", "wjwo")
        parameters.Add("lase", "lool")

        MyBase.storedProcedure("addStudentRegistration", parameters)

It seems to just get the parameters in order and disregards anything i put for the parameter name.. So to be more specific. How exactly should the AddWithValue function from System.Data.OleDb.OleDbParameter be used? And what is the proper way to call a query from a .accdb file with parameters passed in if this method is incorrect?

Thank you, any help is much appreciated

Regards,
-Flippedbeyond

 

[jmcilhinney]

The parameter names are useless. Jet and ACE use positional parameters, not named parameters. You can use names in your code to make the purpose clearer but they are basically ignored. The order that you add the parameters to the command is the order that they are substituted into the SQL code. For that reason, you just need to make sure that to add the correct number of parameters and in the same order as they appear in the SQL code.

Having said that, your SQL code is getting the data to insert from a table rather than parameters anyway, so what you're doing doesn't seem to make sense anyway.

 

[Flippedbeyond]

Thanks for the fast reply jmcilhinney and clearing things up for me.

Having said that, your SQL code is getting the data to insert from a table rather than parameters anyway, so what you're doing doesn't seem to make sense anyway.

I added the parameters to the SQL after reading what you said. It was working before but i guess access was just making assumptions? which is why the typeName parameter had to be first rather than what i had specified since i didnt write any actual parameters in the sql code? The TypeName is a parameter for another query named getID_Type that i am calling within this query.

PARAMETERS [Student_ID] Short, [FirstName] Text ( 255 ), [LastName] Text ( 255 ), [TypeName] Text ( 255 );
INSERT INTO Students ( Student_ID, FirstName, LastName, Type_ID )
SELECT [Student_ID] AS Expr1, [FirstName] AS Expr2, [LastName] AS Expr3, getID_Type.ID FROM getID_Type;

This is the code from the other query named getType_ID

PARAMETERS [TypeName] Text ( 255 );
SELECT FormField_Type.ID
FROM FormField_Type
GROUP BY FormField_Type.ID, FormField_Type.Type
HAVING (((FormField_Type.Type)=[TypeName]));

i thought about combining them rather than using two different queries, but i dont really know SQL that well. Basically i just wanted to look up the id number for the corresponding type from a table and have that inserted rather than the actually type text. I actually have another 3 fields that i am going to lookup for the ID number as well, so maybe if i kept them as separate queries it can keep the code cleaner?


Any suggestions is more than welcome. Thanks again.