ASP.NET

How to make this SQL SELECT statement work in ASP.NET

Please support our ASP.NET advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2004

Posts: 38

Reputation: mindfrost82 is an unknown quantity at this point

Solved Threads: 0

mindfrost82 mindfrost82 is offline

Offline

Light Poster

How to make this SQL SELECT statement work in ASP.NET

Jun 11th, 2008

Can someone help me convert this SQL SELECT statement to ASP.NET code (if possible):

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
SELECT * FROM tblCategory RIGHT JOIN tblInventory ON tblCategory.categoryID = tblInventory.categoryID
WHERE (((tblInventory.type)=[strSearch])) OR (((tblInventory.country)=[strSearch])) OR (((tblInventory.date)=[strSearch])) OR (((tblInventory.eventSeries)=[strSearch])) OR (((tblInventory.grade)=[strSearch])) OR (((tblInventory.comment)=[strSearch]));
SELECT * FROM tblCategory RIGHT JOIN tblInventory ON tblCategory.categoryID = tblInventory.categoryID
WHERE (((tblInventory.type)=[strSearch])) OR (((tblInventory.country)=[strSearch])) OR (((tblInventory.date)=[strSearch])) OR (((tblInventory.eventSeries)=[strSearch])) OR (((tblInventory.grade)=[strSearch])) OR (((tblInventory.comment)=[strSearch]));

I'm trying to use it as a SelectCommand.

•

Join Date: Dec 2007

Posts: 291

Reputation: ericstenson is an unknown quantity at this point

Solved Threads: 29

ericstenson ericstenson is offline

Offline

Posting Whiz in Training

Re: How to make this SQL SELECT statement work in ASP.NET

Jun 11th, 2008

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
Dim conn1 as new data.sqlclient.sqlconnection(CONNECTIONSTRING)
Dim Select_String as String = WHAT YOU JUST WROTE
Dim Select_Adapter as new data.sqlclient.sqldataadapter(Select_String,conn1)
Dim YourDataSet as New Data.Dataset
conn1.open
Select_Adapter.Fill(YourDataSet)
conn1.close
Dim conn1 as new data.sqlclient.sqlconnection(CONNECTIONSTRING)
Dim Select_String as String = WHAT YOU JUST WROTE
Dim Select_Adapter as new data.sqlclient.sqldataadapter(Select_String,conn1)
Dim YourDataSet as New Data.Dataset
conn1.open
Select_Adapter.Fill(YourDataSet)
conn1.close

There are free converters to C# if you need.

--
"Dummy."

•

Join Date: Dec 2004

Posts: 38

Reputation: mindfrost82 is an unknown quantity at this point

Solved Threads: 0

mindfrost82 mindfrost82 is offline

Offline

Light Poster

Re: How to make this SQL SELECT statement work in ASP.NET

Jun 11th, 2008

Thanks for the reply. What I'm actually trying to do is to get it to bind to a GridView and set strSearch equal to a QueryString @search.

So in my Page_Load, I have:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
Dim strSearch As String = Request.QueryString("@search")
Dim strSearch As String = Request.QueryString("@search")

Then I have an AccessDataSource defined in my aspx file, and this is what I currently have:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/coindata.mdb"
        SelectCommand="SELECT * FROM tblCategory RIGHT JOIN tblInventory ON tblCategory.categoryID = tblInventory.categoryID WHERE (((tblInventory.type)=[strSearch])) OR (((tblInventory.country)=[strSearch])) OR (((tblInventory.date)=[strSearch])) OR (((tblInventory.eventSeries)=[strSearch])) OR (((tblInventory.grade)=[strSearch])) OR (((tblInventory.comment)=[strSearch]));">
    </asp:AccessDataSource>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/coindata.mdb"
        SelectCommand="SELECT * FROM tblCategory RIGHT JOIN tblInventory ON tblCategory.categoryID = tblInventory.categoryID WHERE (((tblInventory.type)=[strSearch])) OR (((tblInventory.country)=[strSearch])) OR (((tblInventory.date)=[strSearch])) OR (((tblInventory.eventSeries)=[strSearch])) OR (((tblInventory.grade)=[strSearch])) OR (((tblInventory.comment)=[strSearch]));">
    </asp:AccessDataSource>

So if I try to pass a variable onto this page using frmSearch.aspx?@search=Lincoln for example, then I get a "No value given for one or more required parameters" error.

Any help? Sorry I didn't provide more detail earlier.