ASP.NET Request.Form in SQL - HELP PLEASE...!

PHTech · Jan 27, 2010

Hi There...!

I need to do something in an ASP.NET page, filtering data by using inputs from a form in a pevious page. I know how to pass the variable to the next page, but doesn't seem to get it right in the SQL (or in fact - I do not know what syntax to use). I know it sounds stupid, but when I googled it, I can't find anything that can be of help. So if anybody can just maybe point me in the right direction, I would appreciate it...

Here is my code: (where it is marked in red, is where my problem is)

Code:

<div class="tip">
        <p>You have searched for: "<% Dim s As String = Request.Form("telfind").ToString 
  response.Write(s)%>"</p>
      </div>
  <div class="content01" id="content04">
  <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/assets/database/AllContacts.mdb" SelectCommand="SELECT ContactsMainTable.Heading, ContactsMainTable.Surname, ContactsMainTable.NameOfContact, ContactsMainTable.CellPhoneNo, ContactsMainTable.Designation, ContactsMainTable.OfficeExtension, ContactsMainTable.SpeedDial, ContactsMainTable.FaxNo, ContactsMainTable.EmailAdd
FROM ContactsMainTable
WHERE (((ContactsMainTable.Surname) LIKE [B][COLOR="red"]'@s'[/COLOR][/B]))
ORDER BY ContactsMainTable.ID;"></asp:AccessDataSource>

crazy_cat · Jan 27, 2010

On the SQL side:
1) does the query work?
2) SQL injection waiting to happen:wtf:

Also the '@s' is structured as a SQL parameter so the ref to the String s which is defined will not be in scope of the SQL string.

maybe something along the lines of

Code:

WHERE (((ContactsMainTable.Surname) LIKE '" s "'))

the gurus can correct if i am wrong

PHTech · Jan 27, 2010

Thanx crazy_cat... This site is only a small intranet site for our company, but wan't to provide nice functionalities... The query is working, but can't seem to find the correct syntax to use... And the worst part of all, it is required that this site is done on ASP.NET platform... It truly sucks, as with PHP, I would have managed this a long time ago... I am totally clueless when it comes to ASP stuff...

crazy_cat · Jan 27, 2010

what seems to be the problem still?

PHTech · Jan 27, 2010

The query is working with other parameters (static) but I still do not know how to make use of the previous page's form value to be working in the SQL... I have played around with that " LIKE 's' " statement, but do not know how to pass the variable into the SQL statement...

PHTech · Jan 27, 2010

OK... I will place my SQL code in here, and where marked in RED, you can see what I want to accomplish...

Code:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/assets/database/AllContacts.mdb" SelectCommand="SELECT ContactsMainTable.Heading, ContactsMainTable.Surname, ContactsMainTable.NameOfContact, ContactsMainTable.CellPhoneNo, ContactsMainTable.Designation, ContactsMainTable.OfficeExtension, ContactsMainTable.SpeedDial, ContactsMainTable.FaxNo, ContactsMainTable.EmailAdd
FROM ContactsMainTable
WHERE (((ContactsMainTable.Surname) LIKE [COLOR="red"][B]Request.Form("telfind")[/B][/COLOR]))
ORDER BY ContactsMainTable.ID;"></asp:AccessDataSource>

crazy_cat · Jan 27, 2010

http://aspnet.4guysfromrolla.com/articles/030106-1.aspx <--- this one discusses your scenario exactly

http://aspnet.4guysfromrolla.com/articles/020205-1.aspx

koeks · Jan 27, 2010

don't forget the single quotes...

WHERE (((ContactsMainTable.Surname) LIKE '" & Request.Form("telfind") & "'"

guest2013-1 · Jan 27, 2010

Remember the % when you're doing your like...

PHTech · Jan 27, 2010

I now get this error:

HTML:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Parser Error 
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately. 

Parser Error Message: The server tag is not well formed.

Source Error: 


Line 26:       </div>
Line 27:   <div class="content01" id="content04">
Line 28:   <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
Line 29:             DataFile="~/assets/database/AllContacts.mdb" SelectCommand="SELECT ContactsMainTable.Heading, ContactsMainTable.Surname, ContactsMainTable.NameOfContact, ContactsMainTable.CellPhoneNo, ContactsMainTable.Designation, ContactsMainTable.OfficeExtension, ContactsMainTable.SpeedDial, ContactsMainTable.FaxNo, ContactsMainTable.EmailAdd
Line 30: FROM ContactsMainTable
 

Source File: /pages/11_search/contacts/asp_contacts/findres.aspx    Line: 28 


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

This kinda sucks as I do not know what to do next... Its simple in PHP to take a form variable, and pass it to the net page and filter your SQL... Easy, but when applying the same principle to the ASPx page, I seem to get endless troubles...!

nfbs · Jan 27, 2010

AcidRaZor said:
Remember the % when you're doing your like...

TSQL uses % as wildcard he is using access. Access uses * as wildcard character.

Also you are not passing the variable value but the variable name.

So try (in your original code)
WHERE (((ContactsMainTable.Surname) LIKE '*"+s+"'))
but I think access uses double quotation to identify string instead of single quotation so can also try then
WHERE (((ContactsMainTable.Surname) LIKE \"*"+s+"\"))

PHTech · Jan 27, 2010

nfbs said:
TSQL uses % as wildcard he is using access. Access uses * as wildcard character.

Also you are not passing the variable value but the variable name.

So try (in your original code)
WHERE (((ContactsMainTable.Surname) LIKE '*"+s+"'))
but I think access uses double quotation to identify string instead of single quotation so can also try then
WHERE (((ContactsMainTable.Surname) LIKE \"*"+s+"\"))

Same error... I have tried alot of possibilities with this and still the same error...! I don't know what I am doing wrong, as when I give it static parameters in the SQL, it is working fine, but the moment I try to pass a variable, it doesn't want to work...

PLEASE CAN SOMEONE SEE WHAT I AM DOING WRONG...!

nfbs · Jan 27, 2010

Forgot you are using VB.net and not C# so a quotation is not \" but "" and its & instead of +. See http://msdn.microsoft.com/en-us/library/267k4fw5.aspx
Variable also needs to be in codebehind marks.

Code:

  <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/assets/database/AllContacts.mdb" SelectCommand="SELECT ContactsMainTable.Heading, ContactsMainTable.Surname, ContactsMainTable.NameOfContact, ContactsMainTable.CellPhoneNo, ContactsMainTable.Designation, ContactsMainTable.OfficeExtension, ContactsMainTable.SpeedDial, ContactsMainTable.FaxNo, ContactsMainTable.EmailAdd
FROM ContactsMainTable
WHERE (((ContactsMainTable.Surname) LIKE  ""*" & "<% s %>" & """)) 
ORDER BY ContactsMainTable.ID;"></asp:AccessDataSource>

Rather set the SelectCommand in the code behinds page load so you don't have use code tags.

PHTech · Jan 27, 2010

nfbs said:
Forgot you are using VB.net and not C# so a quotation is not \" but "" and its & instead of +. See http://msdn.microsoft.com/en-us/library/267k4fw5.aspx
Variable also needs to be in codebehind marks.

Code:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/assets/database/AllContacts.mdb" SelectCommand="SELECT ContactsMainTable.Heading, ContactsMainTable.Surname, ContactsMainTable.NameOfContact, ContactsMainTable.CellPhoneNo, ContactsMainTable.Designation, ContactsMainTable.OfficeExtension, ContactsMainTable.SpeedDial, ContactsMainTable.FaxNo, ContactsMainTable.EmailAdd FROM ContactsMainTable WHERE (((ContactsMainTable.Surname) LIKE ""*" & "<% s %>" & """)) ORDER BY ContactsMainTable.ID;"></asp:AccessDataSource>

Rather set the SelectCommand in the code behinds page load so you don't have use code tags.

I have done it like explained, but still get the error I have previously posted... Also like I have previously mentioned, if I write like 'Surname' after the LIKE (withou the " " double quotation marks, but only with single ' ' marks, it works with the surname I have specified, BUT stil NOT with the info from the previous page... How is this possible, that the query is fine, but the moment you put in a variable, it bombs out...! I also do not know how the SelectCommand works... Is there ANY ANY ANY other way perhaps to make this stupid thing works right... Again, I am forced to work with ASPx and do not really know it like PHP...

guest2013-1 · Jan 27, 2010

The server tag is not well formed <-- there's what's wrong

Oh.. and you're using Access. Another wrong.

crazy_cat · Jan 28, 2010

This simple thing was bugging me so I whipped the ffg simple example together.
1) Two pages in total(Webforms1.aspx and Default.aspx) with webforms1.aspx posting to the second(default.aspx) using the Response.Redirect() method which include a query string parameter called 'name'

Code:

[COLOR="green"]' code within the Button Click event on webforms1.aspx[/COLOR]
Dim targetURL as String
targetURL &= "Default.aspx" [COLOR="green"]' the name of the page being posted to[/COLOR]
targetURL &= "name=" & Server.URLEncode(name.Text.Trim()) '[COLOR="green"]' where name is a 
TextBox control on Webforms1.aspx[/COLOR] 
Repsonse.Redirect(targetURL)

'

The key to this issue imo is that use the features that .Net and asp provide you!!!
This being said, they are in order of importance Datasource Parameters - http://is.gd/7cogD
Which results in the ffg:

Code:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/assets/database/AllContacts.mdb" SelectCommand="SELECT *
FROM ContactsMainTable
[COLOR="red"][B]WHERE ((ContactsMainTable.Surname) LIKE  '%' + ? + '%')[/B][/COLOR]
ORDER BY ContactsMainTable.ID;">
[COLOR="red"][B]<SelectParameters>
[INDENT]<asp:QueryStringParameters DefaultValue="a" Name="?" QueryStringField="name" Type="String" />[/INDENT]
</SelectParameters>[/B][/COLOR]
</asp:AccessDataSource>

and ditch vb for c#

PHTech · Jan 28, 2010

crazy_cat said:
This simple thing was bugging me so I whipped the ffg simple example together.
1) Two pages in total(Webforms1.aspx and Default.aspx) with webforms1.aspx posting to the second(default.aspx) using the Response.Redirect() method which include a query string parameter called 'name'

Code:

[COLOR="green"]' code within the Button Click event on webforms1.aspx[/COLOR] Dim targetURL as String targetURL &= "Default.aspx" [COLOR="green"]' the name of the page being posted to[/COLOR] targetURL &= "name=" & Server.URLEncode(name.Text.Trim()) '[COLOR="green"]' where name is a TextBox control on Webforms1.aspx[/COLOR] Repsonse.Redirect(targetURL)

'

The key to this issue imo is that use the features that .Net and asp provide you!!!
This being said, they are in order of importance Datasource Parameters - http://is.gd/7cogD
Which results in the ffg:

Code:

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/assets/database/AllContacts.mdb" SelectCommand="SELECT * FROM ContactsMainTable [COLOR="red"][B]WHERE ((ContactsMainTable.Surname) LIKE '%' + ? + '%')[/B][/COLOR] ORDER BY ContactsMainTable.ID;"> [COLOR="red"][B]<SelectParameters> [INDENT]<asp:QueryStringParameters DefaultValue="a" Name="?" QueryStringField="name" Type="String" />[/INDENT] </SelectParameters>[/B][/COLOR] </asp:AccessDataSource>

and ditch vb for c#

Thanx for this... Makes it a bit simpler... BUT now I get the following error:

HTML:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Parser Error 
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately. 

Parser Error Message: Unknown server tag 'asp:QueryString'.

Source Error: 


Line 32: ORDER BY ContactsMainTable.ID;">
Line 33: 	<SelectParameters>
Line 34: 		<asp:QueryStringParameters DefaultValue="a" Name="?" QueryStringField="name" Type="String" />
Line 35: 	</SelectParameters>
Line 36: </asp:AccessDataSource>
 

Source File: /pages/11_search/contacts/asp_contacts/findres.aspx    Line: 34 


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

Any Suggestions...?

PHTech · Jan 28, 2010

And this is my code up to now:

Code:

<div class="tip">
        <p>You have searched for: "<% Dim sa As String = Request.Form("telfind").ToString 
  response.Write(sa)%>"</p>
      </div>
  <div class="content01" id="content04">
  <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/assets/database/AllContacts.mdb" SelectCommand="SELECT ContactsMainTable.Heading, ContactsMainTable.Surname, ContactsMainTable.NameOfContact, ContactsMainTable.CellPhoneNo, ContactsMainTable.Designation, ContactsMainTable.OfficeExtension, ContactsMainTable.SpeedDial, ContactsMainTable.FaxNo, ContactsMainTable.EmailAdd
FROM ContactsMainTable
WHERE (((ContactsMainTable.Surname) LIKE '%' + ? + '%'))
ORDER BY ContactsMainTable.ID;">
	<SelectParameters>
		<asp:QueryStringParameters DefaultValue="a" Name="?" QueryStringField="name" Type="String" />
	</SelectParameters>
</asp:AccessDataSource>

PHTech · Jan 28, 2010

OK... Now I have done something to make it work almost correctly... Now it shows every surname containing the letter "a" and not the stuff I want to be searched...

This is the code for the parameter:

Code:

<asp:QueryStringParameter DefaultValue="a" Name="?" QueryStringField="name" Type="String" />

This is the code I used to get the variable from the previous page:

Code:

<% Dim sa As String = Request.Form("telfind")

So how do I now make the form field from the previous page with specified parameter "sa" work with the DefaultValue thing...?

crazy_cat · Jan 28, 2010

PHTech said:
OK... Now I have done something to make it work almost correctly... Now it shows every surname containing the letter "a" and not the stuff I want to be searched...

This is the code for the parameter:

Code:

<asp:QueryStringParameter DefaultValue="a" Name="?" QueryStringField="[B][COLOR="red"]name[/COLOR][/B]" Type="String" />

This is the code I used to get the variable from the previous page:

Code:

<% Dim sa As String = Request.Form("telfind")

So how do I now make the form field from the previous page with specified parameter "sa" work with the DefaultValue thing...?

actually, it is working as intended, since there is no query string argument called name, it reverts to the default value which 'a' and this is used in the SQL query

forgot to mention that name in the server tag needs to be replaced with query string parameter that you are using which is telfind

ASP.NET Request.Form in SQL - HELP PLEASE...!

Senior Member

Well-Known Member

Senior Member

Well-Known Member

Senior Member

Senior Member

Well-Known Member

Expert Member

guest

Senior Member

Expert Member

Senior Member

Expert Member

Senior Member

guest

Well-Known Member

Senior Member

Senior Member

Senior Member

Well-Known Member