Error: No value given for one or more required paramet

Hi all.
I got an error for my SQL syntax.
The error lies in the commented line shows below.

VB Code:

<%
nokpA = request.Form("kp1")&request.Form("kp2")&request.Form("kp3")
set ic = server.CreateObject("adodb.recordset")
ic.open "select * from staf where nokp = nokpA" ,conn 'error is here
    if not ic.eof then %>
    <script>
    alert("Maaf, no KP telah wujud");
    </script>
    <%end if%>

The error tells that:
No value given for one or more required parameters.

I think there must be something wrong with the SQL syntax but I couldnt figure out whts wrong with that line.
So, I need help from you all to explain to me.
Thanks in advance.

nokpA is a variable in your VB code. SQL knows nothing about the variable. It thinks that nokpA is a field (and upon that failing, tries to use it as a paremeter which is then undefined.)

Odds are what you are looking for is this:

Code:

---

ic.open "select * from staf where nokp = " & nokpA
'Unless the field is a string, then you want this:
ic.open "select * from staf where nokp = '" & nokpA & "'"

---

But beware of the SQL Injection evils, for they can cause many griefs. In this case, you may want to look at using the command object and a parameterized query.

-tg

I got another problem, SQL syntax error in INSERT INTO statement.
I haVe checked the statement and I think theres no error with that.
Can anyone here help me to detect the error?
Thanks in advance.

VB Code:

<%
nokpA = request.Form("kp1")&request.Form("kp2")&request.Form("kp3")
set ic = server.CreateObject("adodb.recordset")
ic.open "select * from staf where nokp = '"&nokpA&"'" ,conn 'error is here
    if not ic.eof then %>
    <script>
    alert("Maaf, no KP telah wujud");
    </script>
    <%
    end if
set idata = server.CreateObject("adodb.recordset")
idata.open "insert into staf (bno,nama,nokp,alamat1,bandar,negeri,poskod,tel1,emel,status,kad_dhl,kad_skr,trk_lantik,jawatan_id,bahagian_id) values("&request.Form("bno")&",'"&request.Form("nama")&"','"&nokpA&"','"&request.Form("alamat")&"','"&request.Form("bandar")&"',"&request.Form("negeri")&",'"&request.Form("tel1")&"','"&request.Form("emel")&"','"&request.Form("status")&"',1,1,#"&tlantik&"#,"&request.Form("jwtn")&","&request.Form("bhgn")&")" ,conn
%>

Have you actually set conn further up your code?

Yeah, I have set the conn var in include file.

Though this may or may not directly help you with this particular query, I've noticed you've been doing a lot of work with ADO recently. There are a few things I'd consider doing if possible.

1. Consider using a DSN if possible for your connection. I'm not sure if you're already using one but once you know it works, it keeps things simple because you just call the same DSN.

2. If you're using a SQL server for your database, consider using stored procedures. They're compiled, so they run quicker. Again, once you know the query works you can eliminate problems easier because you know that you're query is just failing due to the paramters you're passing. They're also slightly more secure. Also, if you ever feel the need, you can look into TSQL and refine your stored procedures and do some of your coding in there. An example of calling one would be:

VB Code:

Const adCmdStoredProc = 4 ' this only needs to be declared once on your page
StrDSN = "DSN=MyDSN;UID=MyUserID;PWD=MyPassword"
Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
cn.Open strDSN
StrSQL="dbo.InetByAdvice('" & varAdviceNo & "','"& varCustomer & "')"
Set rs = cn.execute(StrSQL,,adCmdStoredProc)

As mentioned below we could response.write out the string to check our parameters if it fails.


3. Probably the most simple and useful, place your sql queries into a string and then you can use the execute method of your conn object to perform your query. The big bonus of this is that, when your query is failing, all you need to do is response.write out your string and you can see what is getting sent to your sql server. When you have large queries with lots of paramters getting passed, it can be really frustrating to have misssed out a ' or something similar. So for example, if i had an oracle db I'm trying to query:

VB Code:

StrDSN = "DSN=MyOracleDSN;UID=MyUserID;PWD=MyPassword"
Set Oracn = Server.CreateObject("ADODB.Connection")
Set Orars = Server.CreateObject("ADODB.Recordset")
Oracn.Open StrDSN
StrSQL = "select OHNMOR, OHTBCN, OHCUNA, OHCDOT, OHDTDE from wsohdp where (OHDTDE >= to_date('" + theDate + "','YYYY-MM-DD') and OHDTDE < to_date('" + theDatetwo + "','YYYY-MM-DD')) and OHIDOT = " & "'" & "CM" & "'"
Set Orars = Oracn.execute(StrSQL)

If in this case, if my select statement was failing, I could just add the lines

VB Code:

response.write StrSQL
response.end

before Set Orars = Oracn.execute(StrSQL) and it would tell me what I'm trying to pass.

MAKE SURE there are SPACE BEFORE AND AFTER the &! "&aa" is NOT the same as "& aa".... it doesn't look like there's spaces around any of your & in the code. That may be the problem.

If you are still having issues, have the page display the SQL before trying to execute it. Make sure it looks right.

-tg