Parameter has no default value

[Eniac0]

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

 

[W.G. Ryan - MVP]

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.

 

[Eniac0]

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.

 

[Eniac]

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;

 

[Eniac]

*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.