[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected

[obryant32]

Can someone please help me out with the error I'm getting in the subject of this thread? I'm trying to loop into an Access database and pull out a recordset based on a parameter that is selected from a dropdown Form. Here's the code:
<!---------------------------------------------------------------------------------------------------------------------->
Sub writeBranchInfo()

Dim objConnInfo, objRS, Query

Set objConnInfo = Server.CreateObject("ADODB.Connection")

Set objRS = Server.CreateObject("ADODB.Recordset")

theTeam=Request.Querystring("id")




objConnInfpen "branch","",""


Query = "Select id, Branch_Name, Branch_Address, Branch_City, Branch_State, Branch_Zip, Branch_Phone, Branch_Fax, Branch_Website, Owner_Name from branch where branch.id = '" & theTeam & "' order by rank"
objRS.Open Query, objConnInfo

%>

<TABLE border="0" align="right" valign="bottom" CELLPADDING="0" CELLSPACING="0">


<%DO UNTIL objRS.eof%>


<TR><TD><IMG SRC="../images/grayPix.gif" width="200" height="1" BORDER=0></TD></TR>
<TR><TD class="empTitletBold"><%=objRS("Branch_Name")%></TD></TR>
<TR><TD class="empInfo"><%=objRS("Branch_Address")%></TD></TR>
<TR><TD class="empInfo"><%=objRS("Branch_City")%>, <%=objRS("Branch_State")%> <%=objRS("Branch_Zip")%></TD></TR>


<TR><TD class="empInfo">Phone: <%=objRS("Branch_Phone")%></TD></TR>
<TR><TD class="empInfo">Fax: <%=objRS("Branch_Fax")%></TD></TR>
<TR><TD class="empInfo"><A CLASS="empEmail" HREF=<%=objRS("Branch_website")%></A></TD></TR>

<TR><TD><IMG SRC="../images/clearPix.gif" width="1" height="10" BORDER=0></TD></TR>

<%
objRS.movenext
LOOP
%>
</TABLE>
<%

objConnInfo.Close

Set objConnInfo = Nothing
Set objRS = Nothing

End Sub

Thanks so much,
Matt

 

[DotNetGnat]

make sure you have no typos in your query...

also id is a reserved word...make sure all the column names are valid...

Query = "Select [id], Branch_Name, Branch_Address, Branch_City, Branch_State, Branch_Zip, Branch_Phone, Branch_Fax, Branch_Website, Owner_Name from branch where branch.[id] = '" & theTeam & "' order by rank"

if theTeam is an integer then you dont need single quotes...

-DNG

 

[DotNetGnat]

are you sure the Owner_Name is in the branch table??

-DNG

 

[obryant32]

Yeah, it's in the table. Do I need to query every value in the table or can I leave something out like Branch_owner

 

[DotNetGnat]

there is no problem if you leave out one or more fields from your table...but you cannot include a field name which does not exist in the table...

your query looks ok to me...just make sure you have everything spelled correctly...

also do a response.write on your query and see what actually gets printed...

also did you try putting square brackets around field id...

-DNG

 

[obryant32]

Well,

I've tried really simplifying everything. I'm just trying to pull one value. I'm still getting that error it just says one parameter expected.

Here's what I've got

Sub writeBranchInfo()

Dim objConnInfo, objRS, Query

Set objConnInfo = Server.CreateObject("ADODB.Connection")

Set objRS = Server.CreateObject("ADODB.Recordset")


theBranch=Request.Querystring("id")




objConnInfpen "Branch","",""


Query = "Select ID from Branch where Branch.id = '" & theBranch & "' order by rank"
objRS.Open Query, objConnInfo


%>

<TABLE border="0" align="right" valign="bottom" CELLPADDING="0" CELLSPACING="0">


<%DO UNTIL objRS.eof%>


<TR><TD><IMG SRC="../images/grayPix.gif" width="200" height="1" BORDER=0></TD></TR>
<TR><TD class="empTitletBold"><%=objRS("Branch_Name")%></TD></TR>


<TR><TD><IMG SRC="../images/clearPix.gif" width="1" height="10" BORDER=0></TD></TR>


<%

objRS.movenext
LOOP

%>

</TABLE>

<%

objConnInfo.Close

Set objConnInfo = Nothing
Set objRS = Nothing



End Sub

What could it be?
Matt

 

[DotNetGnat]

on what line are you getting the error...

i suspect the below two lines..

objConnInfpen "Branch","",""

try this instead:

[blue]objConnInfpen Branch[/blue]

and also this...

Query = "Select [red][ID][/red] from Branch where Branch.[red][ID][/red] = '" & theBranch & "' order by rank"
objRS.Open Query, objConnInfo

-DNG

 

[obryant32]

This is the line that's giving me th error

objRS.Open QueryBranch, objConnInfo


Not sure why

Thanks

 

[DotNetGnat]

that is because you have named your query as [red]query[/red] and you are using

objRS.Open QueryBranch, objConnInfo

and what error does it give

-DNG

 

[obryant32]

I know that I changed that line, I renamed the query so that's not the issue but here's a copy of the error I'm getting:
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/lendia/aspFunc/aspFunc.asp, line 598



of course, line 598 is the line I just posted above.

 

[DotNetGnat]

can you please response.write your sql query to see what actually is getting passed...

is your id a string or an integer...

if it an integer then you dont need single quotes...

-DNG

 

[obryant32]

Pardon my ignorance...wher ewould I put the response.write? I've tried it a number of ways and I'm not getting anything.

 

[DotNetGnat]

you can put right after your sql query...

Response.Write QueryBranch

and post the output here...

-DNG

 

[Sheco]

He means put that line right after you finish building the query string but before you actually execute the query using rs.Open

Chances are that the SQL you are executing is not what you think it is because of an error building the query string.

 

[obryant32]

Well, the above query works now but I have another query that's oh so simple and it doesn't work. I'm opnly trying to pull one bit of data. I've checked the spelling but it's still getting the one parameter expected....

Sub writeBranchManName()

Dim objConnInfo, objRS, Query1

Set objConnInfo = Server.CreateObject("ADODB.Connection")

Set objRS = Server.CreateObject("ADODB.Recordset")

Set theBranch=Request.Querystring("id")



objConnInfpen "Branch","",""



Query1 = "Select Owner_name from Branch where Branch.id = " & theBranch & " "
response.write Query1
objRS.Open Query1, objConnInfo




%>

<TABLE border="0" align="right" valign="bottom" CELLPADDING="0" CELLSPACING="0">


<%DO UNTIL objRS.eof
name=objRS("owner_name")
%>


<TR><TD class="empTitletBold">Branch Owner</TD></TR>
<TR><TD class="empInfo"><%=name%></TD></TR>



<%

objRS.movenext
LOOP

%>

</TABLE>

<%

objConnInfo.Close

Set objConnInfo = Nothing
Set objRS = Nothing



End Sub

Any ideas?
Thanks so much for your help so far!
Matt

 

[DotNetGnat]

so you are sure that when you response.write your sql query

Query1 = "Select Owner_name from Branch where Branch.id = " & theBranch & " "

you are seeing the output as something like this....

Select Owner_name from Branch where Branch.id = 3

what i mean to say is ARE YOU SURE THAT THE VARIABLE "theBranch" IS GETTING CORRECTLY PASSED OR NOT...

and if the variable is a string..change your query to

Query1 = "Select Owner_name from Branch where Branch.id = '"&theBranch&"' "

and the variable is an integer, then your query to...

Query1 = "Select Owner_name from Branch where Branch.id = "&theBranch

-DNG

 

[obryant32]

Positive.

Ther response.write is showing this:

Select Owner_name from Branch where Branch.id = 2

That's also a field in the table. I don't understand it.

Thanks

 

[DotNetGnat]

ok i see this..

Query1 = "Select Owner_name


and this

objRS("owner_name")

which one is correct Owner_name or owner_name...

-DNG

 

[obryant32]

Well, technically it's Owner_Name. So, I changed both and I still get the error. Besides, I didn't think that was case sensative but good idea.

Still getting same error.

 

[DotNetGnat]

the field names are definitely case sensitive...

if you have SELECT owner_name...the query would have errored...

but besides that everything looks fine to me...i dont know what to suggest now...

-DNG