Results 1 to 7 of 7

Thread: Error: No value given for one or more required paramet

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2004
    Posts
    100

    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:
    1. <%
    2. nokpA = request.Form("kp1")&request.Form("kp2")&request.Form("kp3")
    3. set ic = server.CreateObject("adodb.recordset")
    4. ic.open "select * from staf where nokp = nokpA" ,conn 'error is here
    5.     if not ic.eof then %>
    6.     <script>
    7.     alert("Maaf, no KP telah wujud");
    8.     </script>
    9.     <%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.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,954

    Re: Error: No value given for one or more required paramet

    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 don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jan 2004
    Posts
    100

    Re: Error: No value given for one or more required paramet

    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:
    1. <%
    2. nokpA = request.Form("kp1")&request.Form("kp2")&request.Form("kp3")
    3. set ic = server.CreateObject("adodb.recordset")
    4. ic.open "select * from staf where nokp = '"&nokpA&"'" ,conn 'error is here
    5.     if not ic.eof then %>
    6.     <script>
    7.     alert("Maaf, no KP telah wujud");
    8.     </script>
    9.     <%
    10.     end if
    11. set idata = server.CreateObject("adodb.recordset")
    12. 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
    13. %>
    Last edited by azrina; Apr 26th, 2006 at 01:40 AM.

  4. #4
    Member
    Join Date
    Mar 2006
    Posts
    55

    Re: Error: No value given for one or more required paramet

    Have you actually set conn further up your code?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jan 2004
    Posts
    100

    Re: Error: No value given for one or more required paramet

    Yeah, I have set the conn var in include file.
    Last edited by azrina; Apr 26th, 2006 at 02:26 AM.

  6. #6
    Member
    Join Date
    Mar 2006
    Posts
    55

    Re: Error: No value given for one or more required paramet

    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:
    1. Const adCmdStoredProc = 4 ' this only needs to be declared once on your page
    2. StrDSN = "DSN=MyDSN;UID=MyUserID;PWD=MyPassword"
    3. Set cn = Server.CreateObject("ADODB.Connection")
    4. Set rs = Server.CreateObject("ADODB.Recordset")
    5. cn.Open strDSN
    6. StrSQL="dbo.InetByAdvice('" & varAdviceNo & "','"& varCustomer & "')"
    7. 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:
    1. StrDSN = "DSN=MyOracleDSN;UID=MyUserID;PWD=MyPassword"
    2. Set Oracn = Server.CreateObject("ADODB.Connection")
    3. Set Orars = Server.CreateObject("ADODB.Recordset")
    4. Oracn.Open StrDSN
    5. 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" & "'"
    6. Set Orars = Oracn.execute(StrSQL)

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

    VB Code:
    1. response.write StrSQL
    2. response.end
    before Set Orars = Oracn.execute(StrSQL) and it would tell me what I'm trying to pass.

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,954

    Re: Error: No value given for one or more required paramet

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width