asp.net & SQL

Baz · 16 Mar 2006 at 17:27

I am using Microsoft's free Visual web developer express to play about with asp.net (having already written the intranet in good old asp...) and especially the dataview and data list controls. In particular I have a table that I wish to pull data from when you enter it into a text box (i also have a stored procedure that does similar but uses wildcards)
I have placed a text box and submit button on the page, and a gridview, but I cannot seem to be able to link the two together to get the results...

Gman · 16 Mar 2006 at 18:15

from what you've written it sounds like you want it to do the following

When you click the button you want the info you've entered into the text box to be added to some sql statement and retrieve the data so you can display it in the gridView control ?

If thats right they you need to double click on the submit button and it'll auto create the even handler for the button press. In the event handler your best calling another function or place the code in the event handler to run the sql query.

Your sql query will need the contents of the text box, to do this you use textBoxID.text where textBoxID is the id you've given to the text box.
Now your sql code needs to return either a dataset or a datareader (datareader is the prefered option for this bit). Then you use the datareader to databind to the GridView control like so:

gridViewIDName.datasource = datareaderName
gridViewIDName.dataBind()

That should do it or you should at least be able to work out the steps needed to get exactly what you what. Post again if your still stuck

Baz · 16 Mar 2006 at 20:39

Cheers Gman, I will have a look tomorrow.

Baz · 17 Mar 2006 at 09:07

DOuble clicking on the submit button works ok....

no idea where to put the datareader bits..

heres the asp code for you :-s

Code:

<%@ Page Language="VB" %>

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

<script runat="server">

	Protected Sub DataList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

	End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
	<title>Untitled Page</title>
<script language="javascript" type="text/javascript" id="txtpo">
<!--



function Text1_onclick() {

}

function Submit1_onclick() {

}

// -->
</script>
</head>
<body>
	<form id="form1" runat="server">
	<div>
		<input id="txtpo" type="text" language="javascript" onclick="return Text1_onclick()" />
		<input id="Submit1" type="submit" value="submit" language="javascript" onclick="return Submit1_onclick()" />
		&nbsp;
		<br />
		<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="pk"
			DataSourceID="SqlDataSource1" Height="50px" Width="125px">
			<Fields>
				<asp:BoundField DataField="A_PO" HeaderText="A_PO" SortExpression="A_PO" />
				<asp:BoundField DataField="a_store" HeaderText="a_store" SortExpression="a_store" />
				<asp:BoundField DataField="A_Haulier" HeaderText="A_Haulier" SortExpression="A_Haulier" />
				<asp:BoundField DataField="A_EDD" HeaderText="A_EDD" SortExpression="A_EDD" />
				<asp:BoundField DataField="a_esad" HeaderText="a_esad" SortExpression="a_esad" />
				<asp:BoundField DataField="a_esat" HeaderText="a_esat" ReadOnly="True" SortExpression="a_esat" />
				<asp:BoundField DataField="a_pexp" HeaderText="a_pexp" SortExpression="a_pexp" />
				<asp:BoundField DataField="a_asa" HeaderText="a_asa" SortExpression="a_asa" />
				<asp:BoundField DataField="a_seal" HeaderText="a_seal" SortExpression="a_seal" />
				<asp:BoundField DataField="a_tempu" HeaderText="a_tempu" SortExpression="a_tempu" />
				<asp:BoundField DataField="a_insp" HeaderText="a_insp" SortExpression="a_insp" />
				<asp:BoundField DataField="a_prec" HeaderText="a_prec" SortExpression="a_prec" />
				<asp:BoundField DataField="v_mot" HeaderText="v_mot" SortExpression="v_mot" />
				<asp:BoundField DataField="v_vesvoy" HeaderText="v_vesvoy" SortExpression="v_vesvoy" />
				<asp:BoundField DataField="v_cont" HeaderText="v_cont" SortExpression="v_cont" />
				<asp:BoundField DataField="v_peach" HeaderText="v_peach" SortExpression="v_peach" />
				<asp:BoundField DataField="v_supplier" HeaderText="v_supplier" SortExpression="v_supplier" />
				<asp:BoundField DataField="v_description" HeaderText="v_description" SortExpression="v_description" />
				<asp:BoundField DataField="insertdate" HeaderText="insertdate" SortExpression="insertdate" />
				<asp:BoundField DataField="pk" HeaderText="pk" InsertVisible="False" ReadOnly="True"
					SortExpression="pk" />
			</Fields>
		</asp:DetailsView>
		<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:icddevConnectionString2 %>"
			SelectCommand="stpwildcard" SelectCommandType="StoredProcedure">
			<SelectParameters>
				<asp:FormParameter FormField="form1" Name="a_PO" Type="String" />
			</SelectParameters>
		</asp:SqlDataSource>
	</div>
	</form>
</body>
</html>

Gman · 17 Mar 2006 at 13:56

I think your using .Net 2.0 at the moment I haven't got the code at hand for calling stored prcedures and accessing SQL server. all i've got is some code for access an access database, but I do have some code for .Net 1.1 which should be very very similar with only minor changes required.

To start with I would get rind of your Sql Data source object as I tend to only use these for data binding on page load , but saying that try changing the "Name" in the FormParameter tag to the id of your text box i.e. txtpo. Then try running it as that might work.

failing that get rid of the sql data source and put this code in your script runat=server tags.

Code:

Protected Sub DataList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
    DetailsView1.datasource = fillDataGrid(txtpo.text)
    DetailsView1.dataBind()
End Sub


Public Function fillDataGrid(ByVal textBoxEntry As String) As SqlDataReader
    ' Create the connection object
    Dim connection As New SqlConnection(connectionString)
    ' Create and initilise the command object
    Dim command As New SqlCommand(stpwildcard, connection)
    command.CommandType = CommandType.StoredProcedure
    command.Parameters.Add("@texBoxEntry", SqlDbType.VarChar)
    command.Parameters("@texBoxEntry").Value = textBoxEntry

    Try
        ' Open the connection
        connection.Open()
        'Return a SqlDataReader to the calling function
        Return command.ExecuteReader(CommandBehavior.CloseConnection)
    Catch e As Exception
        ' Close the connection 
        connection.Close()
    End Try
End Function

like I said you might have to change some of the code as some things have changed in .net 2.0. You might also have to import some namespaces so the datareader works. The name space will be Imports System.Data.SqlClient but again this could have changed in 2.0.

hope that helps