"No value given for one or more required parameters" Error

[Grayscale]

Hello,

When I execute the code below, I get:

"Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters." error message in
the first line.

Rs.Open "SELECT * From Unvanlar WHERE Unvan = " & kayit8, Con, 3,3
If rs.EOF Then
Con.Execute ("INSERT INTO Unvanlar (Unvan) VALUES
('"&kayit8&"')" ),,129
End If
Rs.Close

The variable is string and the field in access table is text. I'm sure
that field names in the code and table are correct as well.

What can I do to solve that?

[Mike Brind]

Re: "No value given for one or more required parameters&quot ; Error


Grayscale wrote:[color=blue]
> Hello,
>
> When I execute the code below, I get:
>
> "Microsoft JET Database Engine (0x80040E10)
> No value given for one or more required parameters." error message in
> the first line.
>
> Rs.Open "SELECT * From Unvanlar WHERE Unvan = " & kayit8, Con, 3,3
> If rs.EOF Then
> Con.Execute ("INSERT INTO Unvanlar (Unvan) VALUES
> ('"&kayit8&"')" ),,129
> End If
> Rs.Close
>
> The variable is string and the field in access table is text. I'm sure
> that field names in the code and table are correct as well.
>
> What can I do to solve that?[/color]

If Unvan is a text field, the variable should be delimited as text:

WHERE Unvan = '" & kayit9 & "'"

You would have found that out if you response.write your sql

sql = "SELECT * From Unvanlar WHERE Unvan = '" & kayit9 & "'"
'response.write sql
rs.open sql con,3,3

But why are you selecting * from the table when you are only checking
to see if one value exists?

sql = "SELECT Unvan From Unvanlar WHERE Unvan = '" & kayit9 & "'"
'response.write sql
rs.open sql con,3,3

The cursor you are using is this context is expensive and unnecessary.
The default one would be better.

sql = "SELECT Unvan From Unvanlar WHERE Unvan = '" & kayit9 & "'"
'response.write sql
rs.open sql con,,1

I'd mention to you the dangers of using dynamic SQL, but I notice that
Bob Barrows has already done so in a previous post.

--
Mike Brind

[Bob Barrows [MVP]]

Re: "No value given for one or more required parameters&quot ; Error

Grayscale wrote:[color=blue]
> Hello,
>
> When I execute the code below, I get:
>
> "Microsoft JET Database Engine (0x80040E10)
> No value given for one or more required parameters." error message in
> the first line.
>
> Rs.Open "SELECT * From Unvanlar WHERE Unvan = " & kayit8, Con, 3,3
> If rs.EOF Then
> Con.Execute ("INSERT INTO Unvanlar (Unvan) VALUES
> ('"&kayit8&"')" ),,129[/color]

the syntax here is incorrect - see below:
[color=blue]
> End If
> Rs.Close
>
> The variable is string and the field in access table is text. I'm sure
> that field names in the code and table are correct as well.
>
> What can I do to solve that?[/color]
You cannot solve ssql syntax issues without seeing the actual sql statements
being executed by the database. That means you need to see the result of
your concatenations:

sql="SELECT * From Unvanlar WHERE Unvan = " & kayit8
response.write sql
rs.open sql, Con, 3,3
....
sql="INSERT INTO Unvanlar (Unvan) VALUES ('" & kayit8 & "')"
response.write sql
Con.Execute (sql,,129)

You will, of course, comment out the response.write statements when
everything is running correctly.
I believe fixing the syntax of your Execute call should solve your problem
so I will leave you with this:

Further points to consider:
You use of dynamic sql is leaving you vulnerable to hackers using sql
injection:

http://mvp.unixwiz.net/techtips/sql-injection.html

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries as
they are known in Access:

Access:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

Bob barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Mike Brind]

Re: "No value given for one or more required parameters&quot ; Error


Mike Brind wrote:[color=blue]
> Grayscale wrote:[color=green]
> > Hello,
> >
> > When I execute the code below, I get:
> >
> > "Microsoft JET Database Engine (0x80040E10)
> > No value given for one or more required parameters." error message in
> > the first line.
> >
> > Rs.Open "SELECT * From Unvanlar WHERE Unvan = " & kayit8, Con, 3,3
> > If rs.EOF Then
> > Con.Execute ("INSERT INTO Unvanlar (Unvan) VALUES
> > ('"&kayit8&"')" ),,129
> > End If
> > Rs.Close
> >
> > The variable is string and the field in access table is text. I'm sure
> > that field names in the code and table are correct as well.
> >
> > What can I do to solve that?[/color]
>
> If Unvan is a text field, the variable should be delimited as text:
>
> WHERE Unvan = '" & kayit9 & "'"
>
> You would have found that out if you response.write your sql
>
> sql = "SELECT * From Unvanlar WHERE Unvan = '" & kayit9 & "'"
> 'response.write sql
> rs.open sql con,3,3
>
> But why are you selecting * from the table when you are only checking
> to see if one value exists?
>
> sql = "SELECT Unvan From Unvanlar WHERE Unvan = '" & kayit9 & "'"
> 'response.write sql
> rs.open sql con,3,3
>
> The cursor you are using is this context is expensive and unnecessary.
> The default one would be better.
>
> sql = "SELECT Unvan From Unvanlar WHERE Unvan = '" & kayit9 & "'"
> 'response.write sql
> rs.open sql con,,1
>
> I'd mention to you the dangers of using dynamic SQL, but I notice that
> Bob Barrows has already done so in a previous post.
>
> --
> Mike Brind[/color]

Oops. Missed out the comma after sql in the above:

rs.open sql, con,,1