Hi,

I've searched the groups a lot before posting. Even tho a lot of people
posted with the same error, none of the solution worked for me.

Here's what not working. I am going to post the whole function on .net
side and in access so you see for yourself...

I am getting the error -> Parameter "strUser" has no default value.
Just so you know, I have tried with and without [ and ]. I also tried
with "@strUser" when  calling from ADO. I have other "select" stored
procedures with parameters and they work fine. Maybe its because of the
varchar type ? this is my only SP with varchars as params.

Thanks for your help.

Stephane.

####################

  Function processLogin(ByVal pstrUser As String, ByVal pstrPassword As
String) As DataSet
    Dim da As OleDbDataAdapter = Nothing
    Dim ds As DataSet = Nothing
    Dim conn As OleDbConnection = Nothing
    Dim cmd As OleDbCommand = Nothing
    Dim paramUser As OleDbParameter = Nothing
    Dim paramPassword As OleDbParameter = Nothing

    Try
      paramUser = New OleDbParameter
      With paramUser
        .ParameterName = "strUser"
        .OleDbType = OleDbType.VarChar
        .Size = 10
        .Value = pstrUser
        .Direction = ParameterDirection.Input
      End With

      paramPassword = New OleDbParameter
      With paramPassword
        .ParameterName = "strPassword"
        .OleDbType = OleDbType.VarChar
        .Size = 10
        .Value = pstrPassword
        .Direction = ParameterDirection.Input
      End With

      conn = New OleDbConnection(connectionString)
      cmd = New OleDbCommand
      cmd.Connection = conn

      cmd.CommandText = "EXECUTE spLogin"
      cmd.Parameters.Add(paramUser)
      cmd.Parameters.Add(paramPassword)

      da = New OleDbDataAdapter
      da.SelectCommand = cmd
      ds = New DataSet
      da.Fill(ds)

    Catch ex As Exception
      Throw ex

    Finally
      If conn.State <> ConnectionState.Closed Then
        conn.Close()
      End If
      conn.Dispose()
      cmd.Dispose()
      da.Dispose()
    End Try

##############
Here is the MS Access 2002 "stored procedure". When i run it in access,
everything runs fine.

PARAMETERS strUser Text ( 255 ), strPassword Text ( 255 );
SELECT Count(c.icContact) AS Expr1
FROM tblContact AS c
WHERE [c.cUsername]=[strUser] And [c.cMotDePasse]=[strPassword];


    Return ds
  End Function

I don't use Access much, but the first thing that looks like an issue with
normal access queries is that you're using Named parameters.  Access doesn't
support them. So try replacing the named variables with ? and then just add
them to the collection to correspond to the ? symbol, they have to be the
same position since Access doesn't recognize named params.
<Eni***@gmail.com> wrote in message
Show quoteHide quote

I'll try that because im fresh out of options but im not totally
convinced it will work.

This article is what originally taugh me how to make stored procedures
for access
http://www.devcity.net/Articles/18/msaccess_sp.aspx

The guys is using named parameters, among other things, and its working
all good.

Besides, i have other stored procedures that i call from vb.net with
named parameters and they're working.

I'm gonna try your suggestion anyway and I'll post the results here.

Thanks for your input.

Hi, me again :)

sorry to be the bearer of bad news but it didnt work. first, in order
to do that i had to bring the code as a SQL statement in ado.net
(rather than calling a SP in access) otherwise i was unable to use the
"?" parameters.

so here's the newest code, that is not working, this time im getting
Parameter ?_1 has no default value. Which is basically the same thing.

      conn = New OleDbConnection(connectionString)
      cmd = New OleDbCommand
      cmd.Connection = conn

      cmd.CommandText = "SELECT Count(C.icContact) as ContactCount " &
_
                          "FROM tblContact as C " & _
                         "WHERE C.cUserName = ? " & _
                           "AND C.cMotDePasse = ?"
      cmd.CommandType = CommandType.Text

'Note, i had to put "?" as parameter name since construct will not
allow me to add a nameless parameter
      cmd.Parameters.Add("?", OleDbType.VarChar, 10).Value = pstrUser
      cmd.Parameters.Add("?", OleDbType.VarChar, 10).Value =
pstrPassword

      da = New OleDbDataAdapter
      da.SelectCommand = cmd

Since i need to get this thing moving, for now, I've just modified the
SQL statement to simply embed the user & password in the select
statement. Obviously that worked - problem solved for now.

But I'd rather fix it for real and call it like the other procedures,
its faster and more consistent with the rest of my code.

For references purposes, I mentioned i had other SP working with named
parameters. I'll paste it here, in hope that someone sees something
that i do not.

If you refer to my original post you will be able to compare both
procedures just like I did  in hope to find a difference between the
two.

##########
VB.NET
##########
  Public Function SearchForProduct(ByVal intType As Integer, ByVal
intCategory As Integer) As DataSet
    Dim conn As OleDbConnection = Nothing
    Dim cmd As OleDbCommand = Nothing
    Dim ds As DataSet = Nothing
    Dim da As OleDbDataAdapter = Nothing
    Dim paramType As OleDbParameter = Nothing
    Dim paramCategory As OleDbParameter = Nothing

    Try
      paramType = New OleDbParameter
      With paramType
        .ParameterName = "intType"
        .OleDbType = OleDbType.Integer
        .Size = 8
        .Value = intType
      End With

      paramCategory = New OleDbParameter
      With paramCategory
        .ParameterName = "intCategory"
        .OleDbType = OleDbType.Integer
        .Size = 8
        .Value = intCategory
      End With

      conn = New OleDbConnection(connectionString)
      cmd = New OleDbCommand
      cmd.Connection = conn
      cmd.CommandText = "EXECUTE spSearchProduct"
      cmd.Parameters.Add(paramType)
      cmd.Parameters.Add(paramCategory)

      da = New OleDbDataAdapter
      da.SelectCommand = cmd
      ds = New DataSet
      da.Fill(ds)


    Catch ex As Exception
      Throw ex

    Finally
      If conn.State <> ConnectionState.Closed Then
        conn.Close()
      End If
      conn.Dispose()
      cmd.Dispose()
      da.Dispose()
    End Try

    Return ds
  End Function


############
MS ACCESS
############
PARAMETERS intType Long, intCategory Long;
SELECT inv.icProduit, inv.cDescription, inv.nCategorie,
categ.cDescription, inv.nType, type.cDescription, inv.nQteInv,
inv.nPrixDetail, inv.nPrixGros, inv.nPrixCoutant, inv.nQteCommande
FROM tblInventaire AS inv, tblInventaireCategorie AS categ,
tblTypeInventaire AS type
WHERE inv.nType = type.icType
And inv.nCategorie = categ.icCategorie
and inv.nType = intType
and inv.nCategorie = intCategory;

*sigh* .... problem solved.... if you note the delay with my last
post...thats like 2 minutes after.

*gulp* the code i originally posted was working perfectly, except that
some coder I know, namely, me, forgot to make sure that "strUser" and
"strPassword" HAD values, it turns out the calling procedure had
declared the variables to call processLogin but did not populate them,
thus it was sending empty parameters.

Funny thing is.... I posted in here like 4 times, mostly talking to
myself :)  I'm sorry for even wasting your time Ryan.

Saving DateTime to Sql Server 2005
Locking an Access table
Create WebServices with Word
DataRow Rowstate and drag and drop
Default SQLExpress connectionString