Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

ASP Syntax using Variable for DropDownList1 as criteria in Pervasive SQL query

2 views
Skip to first unread message

bry...@4dnssupport.com

unread,
May 5, 2007, 1:09:11โ€ฏAM5/5/07
to
Hi all.

I am new to ASP and working in Expression Web. The following query
displays the information I need in the gridview for a single record.

SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM", "OE_HDR"."SLS_MAN_NO",
"OE_HDR"."SLS_MAN_INITIALS", "OE_HDR"."ORD_DAT", "OE_HDR"."SHIP_DAT"
FROM "OE_HDR" WHERE ("OE_HDR"."ORD_NO"='174310')

I also have DropDownList1 working properly. For the WHERE portion of
the query I would like to pass a variable that references the value of
DropDownList1

SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM", "OE_HDR"."SLS_MAN_NO",
"OE_HDR"."SLS_MAN_INITIALS", "OE_HDR"."ORD_DAT", "OE_HDR"."SHIP_DAT"
FROM "OE_HDR" WHERE ("OE_HDR"."ORD_NO"= @"DropDownList1")

I have been searching for the propoer syntax and have been unable to
get it functioning properly.

Thank you!

Alexey Smirnov

unread,
May 5, 2007, 3:32:08โ€ฏAM5/5/07
to

I think

<asp:SqlDataSource
...
WHERE OE_HDR.ORD_NO = @DropDownList1
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="DropDownList1"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

bry...@4dnssupport.com

unread,
May 5, 2007, 1:16:25โ€ฏPM5/5/07
to
> </asp:SqlDataSource>- Hide quoted text -
>
> - Show quoted text -

Thanks for the reply.

Here is the code that works and displays one record based on the
definitive ...WHERE OE_HDR.ORD_NO= '140173'">

<asp:SqlDataSource runat="server" ID="SqlDataSource2"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT OE_HDR.ORD_NO, OE_HDR.CUST_NAM,
OE_HDR.SLS_MAN_NO, OE_HDR.SLS_MAN_INITIALS, OE_HDR.ORD_DAT,
OE_HDR.SHIP_DAT FROM OE_HDR WHERE OE_HDR.ORD_NO= '140173'">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList2"
Name="DropDownList2"


PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>

</asp:SqlDataSource>


When I change the code to use the variable:

<asp:SqlDataSource runat="server" ID="SqlDataSource2"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT OE_HDR.ORD_NO, OE_HDR.CUST_NAM,
OE_HDR.SLS_MAN_NO, OE_HDR.SLS_MAN_INITIALS, OE_HDR.ORD_DAT,
OE_HDR.SHIP_DAT FROM OE_HDR WHERE OE_HDR.ORD_NO= @DropDownList2">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList2"
Name="DropDownList2"


PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>

</asp:SqlDataSource>


I get this error in my page:

ERROR [42000] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC
Engine Interface]Syntax Error: SELECT OE_HDR.ORD_NO, OE_HDR.CUST_NAM,
OE_HDR.SLS_MAN_NO, OE_HDR.SLS_MAN_INITIALS, OE_HDR.ORD_DAT,
OE_HDR.SHIP_DAT FROM OE_HDR WHERE OE_HDR.ORD_NO=@<< ???
>>DropDownList2
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: ERROR [42000]
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine
Interface]Syntax Error: SELECT OE_HDR.ORD_NO, OE_HDR.CUST_NAM,
OE_HDR.SLS_MAN_NO, OE_HDR.SLS_MAN_INITIALS, OE_HDR.ORD_DAT,
OE_HDR.SHIP_DAT FROM OE_HDR WHERE OE_HDR.ORD_NO=@<< ???
>>DropDownList2

Source Error:

An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace
below.

Stack Trace:


[OdbcException (0x80131937): ERROR [42000] [Pervasive][ODBC Client
Interface][LNA][Pervasive][ODBC Engine Interface]Syntax Error: SELECT
OE_HDR.ORD_NO, OE_HDR.CUST_NAM, OE_HDR.SLS_MAN_NO,
OE_HDR.SLS_MAN_INITIALS, OE_HDR.ORD_DAT, OE_HDR.SHIP_DAT FROM OE_HDR
WHERE OE_HDR.ORD_NO=@<< ??? >>DropDownList2]
System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
RetCode retcode) +35
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod) +1735
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method, Boolean needReader) +60
System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior
behavior) +75
System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior
behavior) +4

System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior) +141
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) +83

System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments
arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments
arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
+82

System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
+69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
+1360


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.42;
ASP.NET Version:2.0.50727.42


Any Ideas?
Thanks again for you help.

Alexey Smirnov

unread,
May 5, 2007, 2:36:39โ€ฏPM5/5/07
to
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBeยญhavior

> behavior) +7
> System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior) +141
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior) +137
> System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
> srcTable) +83
>
> System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectAยญrguments

> arguments) +1770
> System.Web.UI.DataSourceView.Select(DataSourceSelectArguments
> arguments, DataSourceViewSelectCallback callback) +17
> System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
> System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
> System.Web.UI.WebControls.GridView.DataBind() +4
> System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
> +82
>
> System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
> +69
> System.Web.UI.Control.EnsureChildControls() +87
> System.Web.UI.Control.PreRenderRecursiveInternal() +41
> System.Web.UI.Control.PreRenderRecursiveInternal() +161
> System.Web.UI.Control.PreRenderRecursiveInternal() +161
> System.Web.UI.Page.ProcessRequestMain(Boolean
> includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
> +1360
>
> ---------------------------------------------------------------------------ยญ-----

> Version Information: Microsoft .NET Framework Version:2.0.50727.42;
> ASP.NET Version:2.0.50727.42
>
> Any Ideas?
> Thanks again for you help.- Hide quoted text -

>
> - Show quoted text -

wait... I made a misspelling, don't you see it?

<asp:ControlParameter
ControlID="DropDownList2"
Name="DropDownList2"

Name should be a name of the db-field, e.g. ORD_NO

bry...@4dnssupport.com

unread,
May 5, 2007, 10:46:34โ€ฏPM5/5/07
to
> Name should be a name of the db-field, e.g. ORD_NO- Hide quoted text -

>
> - Show quoted text -

I changed to name to ORD_NO and also changed the query to reflect that
change:

<asp:SqlDataSource runat="server" ID="SqlDataSource2"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
SelectCommand="SELECT OE_HDR.ORD_NO, OE_HDR.CUST_NAM,
OE_HDR.SLS_MAN_NO, OE_HDR.SLS_MAN_INITIALS, OE_HDR.ORD_DAT,

OE_HDR.SHIP_DAT FROM OE_HDR WHERE OE_HDR.ORD_NO= @ORD_NO">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList2" Name="ORD_NO"


PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

Same Error now except the last error now references ORD_NO

ERROR [42000] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC
Engine Interface]Syntax Error: SELECT OE_HDR.ORD_NO, OE_HDR.CUST_NAM,
OE_HDR.SLS_MAN_NO, OE_HDR.SLS_MAN_INITIALS, OE_HDR.ORD_DAT,

OE_HDR.SHIP_DAT FROM OE_HDR WHERE OE_HDR.ORD_NO= @<< ??? >>ORD_NO

I am wondering if this is not more of a PervasiveSQL problem with the
syntax of the variable rather than an ASP qestion...

Alexey Smirnov

unread,
May 6, 2007, 5:19:32โ€ฏAM5/6/07
to
> syntax of the variable rather than an ASP qestion...- Hide quoted text -

>
> - Show quoted text -

Please post here a code of the DropDownList Control and its
SqlDataSource.

It looks a little bit strange, you received the same ODBC-error when
the Name property was not defined at all. I'm curious if you defined
it properly...

Alexey Smirnov

unread,
May 6, 2007, 8:01:56โ€ฏAM5/6/07
to
> it properly...- Hide quoted text -

>
> - Show quoted text -

I got it working!

You should specify your parameter as the following:

....WHERE ORD_NO = ?"

The reason is simple: this is an ODBC provider, and in the
SqlDataSource Control it supports only positional parameters
identified by the "?" character, and not a named parameters with "@".

Source: http://msdn2.microsoft.com/en-us/library/z72eefad(VS.80).aspx

Example (works at my box):

<%@ Page Language="C#" %>
<html>
<head id="Head1" runat="server">
<title>Test</title>
</head>
<body>
<form id="form1" runat="server">
<asp:DropDownList ID="DropDownList1"
DataSourceID="SqlDataSource2" AutoPostBack="true"
DataTextField="Type" Runat="server" />
<asp:SqlDataSource ID="SqlDataSource2" Runat="server"
SelectCommand="select distinct Type from Room"
ConnectionString="Dsn=DEMODATA"
ProviderName="System.Data.Odbc" />
<br />
<asp:GridView ID="GridView1" Runat="server"
DataSourceID="SqlDataSource1"
AutoGenerateColumns="True">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="select top 10 building_name, number, capacity
from Room where type=?"
ConnectionString="Dsn=DEMODATA"
ProviderName="System.Data.Odbc">
<SelectParameters>
<asp:ControlParameter Name="Type" ControlID="DropDownList1" /
>
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>

Enjoy :-)

bry...@4dnssupport.com

unread,
May 6, 2007, 10:35:23โ€ฏAM5/6/07
to
> ...
>
> read more ยป- Hide quoted text -

>
> - Show quoted text -

Thank you! That works perfectly. Thanks for helping me over my first
asp hurdle. :)

0 new messages