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

Simple Variable Pass on to SQL Where Query question.

P: n/a
I'm a newbie using VS2005 to learn and test. I'm testing against the
Northwind DB.
I created a simple variable called "myVariable" and I assigned it the
character "c". I want my SQL query to use this variable in the "Where"
statement where it will pull up all names with the letter "c" in it. How do
I pass this variable on the the WHERE statement? I thought all I had to do
was use the <% %> with my variable inside and that would work but when I ran
it, nothing appeared. I have enclosed the acutal codes.

Please help and thank you!
Newbie Phil


Actual Code Begins here:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb"
Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<% Dim myVariable As String

myVariable = "c"

%>

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:NorthwindConnectionString %>"

SelectCommand="SELECT ContactName FROM Customers WHERE
(ContactName LIKE N'%<%myVariable %>%')">

</asp:SqlDataSource>
</div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">

<Columns>

<asp:BoundField DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName" />

</Columns>

</asp:GridView>

</form>

</body>

</html>
Apr 2 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Do something for example:-
SELECT * FROM Persons
WHERE FirstName LIKE '%YourVariable%'
Or just use stored procedure.
Patrick

"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:u9**************@TK2MSFTNGP12.phx.gbl...
I'm a newbie using VS2005 to learn and test. I'm testing against the
Northwind DB.
I created a simple variable called "myVariable" and I assigned it the
character "c". I want my SQL query to use this variable in the "Where"
statement where it will pull up all names with the letter "c" in it. How
do I pass this variable on the the WHERE statement? I thought all I had
to do was use the <% %> with my variable inside and that would work but
when I ran it, nothing appeared. I have enclosed the acutal codes.

Please help and thank you!
Newbie Phil


Actual Code Begins here:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb"
Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<% Dim myVariable As String

myVariable = "c"

%>

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

SelectCommand="SELECT ContactName FROM Customers WHERE
(ContactName LIKE N'%<%myVariable %>%')">

</asp:SqlDataSource>
</div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">

<Columns>

<asp:BoundField DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName" />

</Columns>

</asp:GridView>

</form>

</body>

</html>

Apr 2 '06 #2

P: n/a
I'll have look up stored procedure and see what that's all about.

I did do it just like you said where I put '%Varible%' but the Where
statement actually thinks I'm using the word Variable versus looking at the
variable.

Please help.
"Patrick.O.Ige" <na********@hotmail.com> wrote in message
news:OI**************@tk2msftngp13.phx.gbl...
Do something for example:-
SELECT * FROM Persons
WHERE FirstName LIKE '%YourVariable%'
Or just use stored procedure.
Patrick

"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:u9**************@TK2MSFTNGP12.phx.gbl...
I'm a newbie using VS2005 to learn and test. I'm testing against the
Northwind DB.
I created a simple variable called "myVariable" and I assigned it the
character "c". I want my SQL query to use this variable in the "Where"
statement where it will pull up all names with the letter "c" in it. How
do I pass this variable on the the WHERE statement? I thought all I had
to do was use the <% %> with my variable inside and that would work but
when I ran it, nothing appeared. I have enclosed the acutal codes.

Please help and thank you!
Newbie Phil


Actual Code Begins here:
<%@ Page Language="VB" AutoEventWireup="false"
CodeFile="Default2.aspx.vb" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<% Dim myVariable As String

myVariable = "c"

%>

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

SelectCommand="SELECT ContactName FROM Customers WHERE
(ContactName LIKE N'%<%myVariable %>%')">

</asp:SqlDataSource>
</div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">

<Columns>

<asp:BoundField DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName" />

</Columns>

</asp:GridView>

</form>

</body>

</html>


Apr 2 '06 #3

P: n/a
+1 for using Stored Procedures - life's easier without SQL scattered
through your pages, but...
<asp:SqlDataSource id="myDataSource" runat="server"
ConnectionString="..."
SelectCommand="SELECT Column1, Column2 FROM table WHERE key =
@MyKeyName">

<SelectParameters>
<asp:Parameter Name="MyKeyName" DefaultValue="SomeValue" />
</SelectParameters>
</asp:SqlDataSource>
You can use a ControlParameter to bind the value to a control, a
QueryStringParameter to bind the value to a query string field, or just
use bog-standard Parameter and set the value yourself.

Best place to do that is in Page_Load if your value won't change eg.
if( ! Page.IsPostback)
MyDataSource.SelectParameters["MyKeyName"].DefaultValue =
TheUserBrowsingThisPage.UserId;

Or in the SqlDataSourceSelecting event if you want to change it
dynamically for each select.

Futher info here
http://msdn2.microsoft.com/en-us/lib...ad(VS.80).aspx
and here
http://msdn2.microsoft.com/en-US/lib...ng(VS.80).aspx
HTH. But you should still prefer stored procedures, or at least keeping
your SQL in a data access class.

Apr 2 '06 #4

P: n/a
You have a fundamental problem which you will need to address in short
order, or you will find yourself totally frustrated with ASP.Net before you
know it. It looks to me like you have done classic ASP in the past, and that
you are now beginning to work with ASP.Net. However, these 2 technologies
are very different. The chief difference is that classic ASP is procedural
in nature, while ASP.Net is fully object-oriented and event-driven. If you
try to do ASP.Net in a procedural fashion, you will be frustrated and
confused at nearly every turn. Because ASP.Net is fully object-oriented and
event-driven, program execution does not simply flow from top to bottom. It
is event-driven, which means that your program flow is going to bounce all
over the place, from one event and one class to the next. It is also
multi-threaded, although you won't have to deal with that very much in the
beginning. But its object-orientation, the Microsoft System.Web.UI.Page
execution model, and its event-driven nature tend to compel the developer to
design his/her application in accordance with these. Do yourself a favor and
read up on all of them before you try to go any further. It is better to
start out right than to have to re-start over and over again.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.

"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:u9**************@TK2MSFTNGP12.phx.gbl...
I'm a newbie using VS2005 to learn and test. I'm testing against the
Northwind DB.
I created a simple variable called "myVariable" and I assigned it the
character "c". I want my SQL query to use this variable in the "Where"
statement where it will pull up all names with the letter "c" in it. How
do I pass this variable on the the WHERE statement? I thought all I had
to do was use the <% %> with my variable inside and that would work but
when I ran it, nothing appeared. I have enclosed the acutal codes.

Please help and thank you!
Newbie Phil


Actual Code Begins here:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb"
Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<% Dim myVariable As String

myVariable = "c"

%>

<div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

SelectCommand="SELECT ContactName FROM Customers WHERE
(ContactName LIKE N'%<%myVariable %>%')">

</asp:SqlDataSource>
</div>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataSourceID="SqlDataSource1">

<Columns>

<asp:BoundField DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName" />

</Columns>

</asp:GridView>

</form>

</body>

</html>

Apr 2 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.