By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,587 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,587 IT Pros & Developers. It's quick & easy.

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

P: n/a
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!

May 5 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On May 5, 7:09 am, bry...@4dnssupport.com wrote:
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!
I think

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

May 5 '07 #2

P: n/a
On May 5, 3:32 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 5, 7:09 am, bry...@4dnssupport.com wrote:


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!

I think

<asp:SqlDataSource
...
WHERE OE_HDR.ORD_NO = @DropDownList1
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="DropDownList1"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</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(OdbcHa ndle hrHandle,
RetCode retcode) +35
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod) +1735
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader) +60
System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior
behavior) +75
System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior
behavior) +4

System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBehavior
behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(Data Set 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.Execut eSelect(DataSourceSelectArguments
arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSele ctArguments
arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.Perform Select() +149
System.Web.UI.WebControls.BaseDataBoundControl.Dat aBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.Ens ureDataBound()
+82

System.Web.UI.WebControls.CompositeDataBoundContro l.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.

May 5 '07 #3

P: n/a
On May 5, 7:16 pm, bry...@4dnssupport.com wrote:
On May 5, 3:32 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:


On May 5, 7:09 am, bry...@4dnssupport.com wrote:
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!
I think
<asp:SqlDataSource
...
WHERE OE_HDR.ORD_NO = @DropDownList1
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="DropDownList1"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</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(OdbcHa ndle hrHandle,
RetCode retcode) +35
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod) +1735
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader) +60
System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior
behavior) +75
System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior
behavior) +4

System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBe*havior
behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(Data Set 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.Execut eSelect(DataSourceSelectA*rguments
arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSele ctArguments
arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.Perform Select() +149
System.Web.UI.WebControls.BaseDataBoundControl.Dat aBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.Ens ureDataBound()
+82

System.Web.UI.WebControls.CompositeDataBoundContro l.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

May 5 '07 #4

P: n/a
On May 5, 2:36 pm, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 5, 7:16 pm, bry...@4dnssupport.com wrote:


On May 5, 3:32 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 5, 7:09 am, bry...@4dnssupport.com wrote:
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 valueof
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!
I think
<asp:SqlDataSource
...
WHERE OE_HDR.ORD_NO = @DropDownList1
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="DropDownList1"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</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(OdbcHa ndle hrHandle,
RetCode retcode) +35
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod) +1735
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader) +60
System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior
behavior) +75
System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior
behavior) +4
System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBe**havior
behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(Data Set 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.Execut eSelect(DataSourceSelectA**rguments
arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSele ctArguments
arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.Perform Select() +149
System.Web.UI.WebControls.BaseDataBoundControl.Dat aBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.Ens ureDataBound()
+82
System.Web.UI.WebControls.CompositeDataBoundContro l.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- 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...

May 6 '07 #5

P: n/a
On May 6, 4:46 am, bry...@4dnssupport.com wrote:
On May 5, 2:36 pm, Alexey Smirnov <alexey.smir...@gmail.comwrote:


On May 5, 7:16 pm, bry...@4dnssupport.com wrote:
On May 5, 3:32 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 5, 7:09 am, bry...@4dnssupport.com wrote:
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 unableto
get it functioning properly.
Thank you!
I think
<asp:SqlDataSource
...
WHERE OE_HDR.ORD_NO = @DropDownList1
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="DropDownList1"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</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(OdbcHa ndle hrHandle,
RetCode retcode) +35
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod) +1735
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader) +60
System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior
behavior) +75
System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior
behavior) +4
System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBe***havior
behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(Data Set 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.Execut eSelect(DataSourceSelectA***rguments
arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSele ctArguments
arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.Perform Select() +149
System.Web.UI.WebControls.BaseDataBoundControl.Dat aBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.Ens ureDataBound()
+82
System.Web.UI.WebControls.CompositeDataBoundContro l.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- 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...- 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...

May 6 '07 #6

P: n/a
On May 6, 11:19 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 6, 4:46 am, bry...@4dnssupport.com wrote:


On May 5, 2:36 pm, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 5, 7:16 pm, bry...@4dnssupport.com wrote:
On May 5, 3:32 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 5, 7:09 am, bry...@4dnssupport.com wrote:
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!
I think
<asp:SqlDataSource
...
WHERE OE_HDR.ORD_NO = @DropDownList1
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="DropDownList1"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</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(OdbcHa ndle hrHandle,
RetCode retcode) +35
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod) +1735
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader) +60
System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior
behavior) +75
System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior
behavior) +4
System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBe****havior
behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(Data Set 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.Execut eSelect(DataSourceSelectA****rguments
arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSele ctArguments
arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.Perform Select() +149
System.Web.UI.WebControls.BaseDataBoundControl.Dat aBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.Ens ureDataBound()
+82
System.Web.UI.WebControls.CompositeDataBoundContro l.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- 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...- 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...- 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/lib...ad(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 :-)

May 6 '07 #7

P: n/a
On May 6, 8:01 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 6, 11:19 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:


On May 6, 4:46 am, bry...@4dnssupport.com wrote:
On May 5, 2:36 pm, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 5, 7:16 pm, bry...@4dnssupport.com wrote:
On May 5, 3:32 am, Alexey Smirnov <alexey.smir...@gmail.comwrote:
On May 5, 7:09 am, bry...@4dnssupport.com wrote:
Hi all.
I am new to ASP and working in Expression Web. The followingquery
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 thevalue 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!
I think
<asp:SqlDataSource
...
WHERE OE_HDR.ORD_NO = @DropDownList1
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="DropDownList1"
PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</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 executionof
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(OdbcHa ndle hrHandle,
RetCode retcode) +35
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod) +1735
System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior
behavior, String method, Boolean needReader) +60
System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior
behavior) +75
System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior
behavior) +4
System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBe*****havior
behavior) +7
System.Data.Common.DbDataAdapter.FillInternal(Data Set 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.Execut eSelect(DataSourceSelectA*****rguments
arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSele ctArguments
arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.Perform Select() +149
System.Web.UI.WebControls.BaseDataBoundControl.Dat aBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.Ens ureDataBound()
+82
System.Web.UI.WebControls.CompositeDataBoundContro l.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- 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...- 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...- 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/lib...ad(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"
...

read more - Hide quoted text -

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

May 6 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.