SQL query from ASP page

[Guest]

I'm new at ASP and I'm trying to query an MS SQL database (Northwind). I
want the page to be setup with a textbox where the user inputs a list of
comma separated numbers and the query would be something like:

select * from categories where categoryid in ('1','2','3','4','5')

and the results would be in a datagrid, I think. This is easy in Query
analyzer but putting it on a web form seems very hard.(for me)

This doesn't seem possible, is it possible? suggestions ?

 

[alex_s]

It is possible.


Aare you using ASP or ASP.NET? In old ASP2.0 you will have to build you
datagrid as HTML. ASP.NET will do this for you.
Let me know what version of ASP are you using and I can help you with
some samples.

 

[Guest]

I'm using ASP.net in Visual Web Developer 2005 express edition btea 2.

code:


<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"
TextMode="MultiLine"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" /><br />
<br />
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="CategoryID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="CategoryID"
HeaderText="CategoryID" InsertVisible="False"
ReadOnly="True" SortExpression="CategoryID" />
<asp:BoundField DataField="CategoryName"
HeaderText="CategoryName" SortExpression="CategoryName" />
<asp:BoundField DataField="Description"
HeaderText="Description" SortExpression="Description" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [CategoryID], [CategoryName],
[Description] FROM [Categories] WHERE ([CategoryID] = @CategoryID)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="CategoryID"
PropertyName="Text"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

</div>
</form>

 

[Guest]

frogive me, I used the wizard...

I'm using ASP.net in Visual Web Developer 2005 express edition btea 2.

code:


<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"
TextMode="MultiLine"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" /><br />
<br />
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" DataKeyNames="CategoryID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="CategoryID"
HeaderText="CategoryID" InsertVisible="False"
ReadOnly="True" SortExpression="CategoryID" />
<asp:BoundField DataField="CategoryName"
HeaderText="CategoryName" SortExpression="CategoryName" />
<asp:BoundField DataField="Description"
HeaderText="Description" SortExpression="Description" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [CategoryID], [CategoryName],
[Description] FROM [Categories] WHERE ([CategoryID] = @CategoryID)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="CategoryID"
PropertyName="Text"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

</div>
</form>

It is possible.


Aare you using ASP or ASP.NET? In old ASP2.0 you will have to build you
datagrid as HTML. ASP.NET will do this for you.
Let me know what version of ASP are you using and I can help you with
some samples.

 

[Cor Ligthert [MVP]]

Daniel,

You mean this?
\\\
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As New SqlClient.SqlConnection _
("Server=(Local); DataBase=Northwind;" & _
"Integrated Security=SSPI")
Dim da As New SqlClient.SqlDataAdapter _
("select * from categories where categoryid in (" _
& TextBox1.Text & ")", conn)
Dim ds As New DataSet
da.Fill(ds, "categories")
DataGrid1.DataSource = ds.Tables(0)
DataGrid1.DataBind()
End Sub
///
You can as well use a datareader instead of a dataadapter/dataset

I hope this helps,

Cor

 

[Guest]

looks good, it's been a while since I've done any vb.net. Let me make sure
this will work well though. What if the categories table contained ....over
a million rows? would this still work ok?

 

[Guest]

actually this pulled all of the categories from the category table, I would
like it be based on the textbox input, separated by commas.

 

[Cor Ligthert [MVP]]

Daniels,

Did you try it?

Cor

 

[Guest]

yes, I did but when I run the page, the query runs automatically with no
input and all i get is the results of 'select * from categories'.

 

[Cor Ligthert [MVP]]

Daniel,

Strange I tested it, did you drag a textbox, a button and datagrid on your
page?

I expected that it was clear from the sample that it was needed.


Cor

 

[Guest]

Yes, I've done all that, are you using ASP.net in Visual Web Developer 2005
express edition beta 2?

I create a page, drag a textbox, button and gridview on the page. This is
ASP.NET 2.0 beta btw,

 

[Cor Ligthert [MVP]]

Daniel,

I create a page, drag a textbox, button and gridview on the page. This is
ASP.NET 2.0 beta btw,

It is always nice when you give good information. This was your question.

However doing this in 2.0 gives the same result.

\\\
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As New SqlClient.SqlConnection _
("Server=(Local); DataBase=Northwind;" & _
"Integrated Security=SSPI")

TextBox1.Text = "'1','2','3'" 'Hardcoded to avoid mistyping in this
sample
'this above should be deleted for real use

Dim da As New SqlClient.SqlDataAdapter _
("select * from categories where categoryid in (" _
& TextBox1.Text & ")", conn)
Dim ds As New DataSet
da.Fill(ds, "categories")
GridView1.DataSource = ds.Tables(0)
GridView1.DataBind()
End Sub
////

I hope this helps,

Cor