How to filter distinct records from a normal Select query and display in a web control
‘Select distinct <field> from <table>’.This SELECT statement is used to filter out duplicate results from a query’s output. But sometimes the requirement is to use a Stored Procedure which returns the entire data and display the manipulated distinct data in web server control. To do this use SortedList For using SortedList import namespace System.Collections Stored Procedure Create Procedure GetSuppliers AS SELECT * FROM Suppliers GO <asp:DataList id=’DataList1′ runat=’server’> <ItemTemplate> <%#Container.DataItem%> </ItemTemplate> </asp:DataList> VB.NET Dim ds As New DataSet Dim myda As SqlDataAdapter = New SqlDataAdapter(‘GetSuppliers’, ‘server=localhost;database=Northwind;uid=sa;pwd=;’) myda.SelectCommand.CommandType = CommandType.StoredProcedure myda.Fill(ds, ‘Table’) Dim slist As SortedList = New SortedList Dim dr As DataRow For Each dr In ds.Tables(0).Rows If Not slist.ContainsValue(dr(‘Country’)) Then slist.Add(dr(‘Supplierid’), dr(‘Country’)) End If Next DataList1.DataSource = slist.GetValueList DataList1.DataBind() ’In case of Dropdownlist ’DropDownList1.DataSource = slist.GetValueList ’DropDownList1.DataBind() C# DataSet ds = new DataSet(); SqlDataAdapter myda = new SqlDataAdapter(‘GetSuppliers’, ‘server=localhost;database=Northwind;uid=sa;pwd=;’); myda.SelectCommand.CommandType = CommandType.StoredProcedure; myda.Fill(ds, ‘Table’); SortedList slist = new SortedList(); foreach( DataRow dr in ds.Tables[0].Rows) { if (! slist.ContainsValue(dr[‘Country’])) { slist.Add(dr[‘Supplierid’].ToString (), dr[‘Country’].ToString ()); } } DataList1.DataSource = slist.GetValueList(); DataList1.DataBind(); //In case of DropDownList //DropDownList1.DataSource = slist.GetValueList //DropDownList1.DataBind()
How to insert data in database using Textboxes
<asp:Button id=’btnAdd’ style=’Z-INDEX: 101; LEFT: 104px; POSITION: absolute; TOP: 168px’ runat=’server’ Text=’Add Record’></asp:Button> <asp:TextBox id=’txtFirstName’ style=’Z-INDEX: 102; LEFT: 168px; POSITION: absolute; TOP: 40px’ runat=’server’></asp:TextBox> <asp:TextBox id=’txtLastName’ style=’Z-INDEX: 103; LEFT: 168px; POSITION: absolute; TOP: 80px’ runat=’server’></asp:TextBox> <asp:TextBox id=’txtDate’ style=’Z-INDEX: 104; LEFT: 168px; POSITION: absolute; TOP: 120px’ runat=’server’></asp:TextBox> <asp:Label id=’Label1′ style=’Z-INDEX: 105; LEFT: 56px; POSITION: absolute; TOP: 240px’ runat=’server’></asp:Label> On Page_Load VB.NET if not Page.IsPostBack then ’…. end if C# if(!Page.IsPostBack ) { //… } Use namespaces System.Data.SqlClient, System.Data.SqlTypes On Button Click VB.NET Dim sqlStmt As String Dim conString As String Dim cn As SqlConnection Dim cmd As SqlCommand Dim sqldatenull As SqlDateTime Try sqlStmt = ‘insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) ‘ conString = ‘server=localhost;database=Northwind;uid=sa;pwd=;’ cn = New SqlConnection(conString) cmd = New SqlCommand(sqlStmt, cn) cmd.Parameters.Add(New SqlParameter(‘@FirstName’, SqlDbType.NVarChar, 11)) cmd.Parameters.Add(New SqlParameter(‘@LastName’, SqlDbType.NVarChar, 40)) cmd.Parameters.Add(New SqlParameter(‘@Date’, SqlDbType.DateTime)) sqldatenull = SqlDateTime.Null cmd.Parameters(‘@FirstName’).Value = txtFirstName.Text cmd.Parameters(‘@LastName’).Value = txtLastName.Text If (txtDate.Text = ”) Then cmd.Parameters(‘@Date’).Value = sqldatenull ’cmd.Parameters(‘@Date’).Value = DBNull.Value Else cmd.Parameters(‘@Date’).Value = DateTime.Parse(txtDate.Text) End If cn.Open() cmd.ExecuteNonQuery() Label1.Text = ‘Record Inserted Succesfully’ Catch ex As Exception Label1.Text = ex.Message Finally cn.Close() End Try On Button Click C# string sqlStmt ; string conString ; SqlConnection cn =null; SqlCommand cmd =null; SqlDateTime sqldatenull ; try { sqlStmt = ‘insert into Employees (FirstName,LastName,HireDate) Values (@FirstName,@LastName,@Date) ‘; conString = ‘server=localhost;database=Northwind;uid=sa;pwd=;’; cn = new SqlConnection(conString); cmd = new SqlCommand(sqlStmt, cn); cmd.Parameters.Add(new SqlParameter(‘@FirstName’, SqlDbType.NVarChar, 11)); cmd.Parameters.Add(new SqlParameter(‘@LastName’, SqlDbType.NVarChar, 40)); cmd.Parameters.Add(new SqlParameter(‘@Date’, SqlDbType.DateTime)); sqldatenull = SqlDateTime.Null; cmd.Parameters[‘@FirstName’].Value = txtFirstName.Text; cmd.Parameters[‘@LastName’].Value = txtLastName.Text; if (txtDate.Text == ”) { cmd.Parameters [‘@Date’].Value =sqldatenull ; //cmd.Parameters[‘@Date’].Value = DBNull.Value; } else { cmd.Parameters[‘@Date’].Value = DateTime.Parse(txtDate.Text); } cn.Open(); cmd.ExecuteNonQuery(); Label1.Text = ‘Record Inserted Succesfully’; } catch (Exception ex) { Label1.Text = ex.Message; } finally { cn.Close(); }
What are the pros and cons of using Session or Cache to save intermediate state?
To store in Session: Pros: It’s stored per user session It’s just a handle to a live object, if using InProc If you use StateServer or SQLServer mode, you can access your session state across the webform. (can’t use InProc mode in webform) Cons: If you use StateServer or SQLServer mode, your object must be serializable, and you pay the performance cost of network traffic + serialization cost To use cache: Pros: It’s always stored as just a handle to the live object Can use cache dependencies or other cache features to control its lifetime Can be shared by all users on that web server Cons: It’s per app-domain per web server.
How to Select a record using Listbox and edit/update it’s fields using textboxes
<TABLE class=’cdb-AltRow2′ id=’Table1′ style=’Z-INDEX: 101; LEFT: 189px; POSITION: absolute; TOP: 20px’ cellSpacing=’1′ cellPadding=’1′ width=’300′ border=’0′> <TR> <TD>ProductID</TD> <TD><asp:textbox id=’txtProductId’ runat=’server’ Enabled=’False’></asp:textbox></TD> </TR> <TR> <TD>Quantity per unit</TD> <TD><asp:textbox id=’txtQuantity’ runat=’server’></asp:textbox></TD> </TR> <TR> <TD>UnitPrice</TD> <TD><asp:textbox id=’txtUnitPrice’ runat=’server’></asp:textbox></TD> </TR> <TR> <TD></TD> <TD><asp:button id=’btnUpdate’ runat=’server’ Text=’Update’></asp:button></TD> </TR> <TR> <TD></TD> <TD><asp:label id=’lblError’ runat=’server’ Visible=’False’></asp:label></TD> </TR> </TABLE> <asp:listbox id=’ListBox1′ style=’Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 12px’ runat=’server’ AutoPostBack=’True’></asp:listbox> VB.NET Dim productid As String Dim strSQL As String Dim strConn As String Dim dr, dr1 As SqlDataReader Dim mycn As SqlConnection Dim mycmd As SqlCommand Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load strConn = ‘server=localhost;uid=sa;database=northwind;pwd=;’ lblError.Visible = False ’ Put user code to initialize the page here If Not Page.IsPostBack Then mycn = New SqlConnection(strConn) strSQL = ‘Select * from Products’ mycmd = New SqlCommand(strSQL, mycn) mycn.Open() dr = mycmd.ExecuteReader(CommandBehavior.CloseConnection) ListBox1.DataSource = dr ListBox1.DataTextField = ‘ProductID’ ListBox1.DataValueField = ‘ProductID’ ListBox1.DataBind() End If End Sub ’Page_Load Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Try lblError.Visible = False Dim updateCmd As String = ‘UPDATE Products SET QuantityPerUnit = @QuantityPerUnit ,’ + ‘UnitPrice = @UnitPrice where ProductId=@ProductId’ Dim cn As New SqlConnection(strConn) Dim myCommand As New SqlCommand(updateCmd, cn) myCommand.Parameters.Add(New SqlParameter(‘@QuantityPerUnit’, txtQuantity.Text)) myCommand.Parameters.Add(New SqlParameter(‘@UnitPrice’, Convert.ToDouble(txtUnitPrice.Text))) myCommand.Parameters.Add(New SqlParameter(‘@ProductId’, Convert.ToInt16(txtProductId.Text))) cn.Open() myCommand.ExecuteNonQuery() lblError.Visible = True lblError.Text = ‘Record Updated successfully’ Catch ex As Exception lblError.Visible = True lblError.Text = ex.Message End Try End Sub ’btnUpdate_Click Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged productid = ListBox1.SelectedItem.Value txtProductId.Text = productid.ToString() strSQL = ‘Select * from Products where productid =’ + productid mycn = New SqlConnection(strConn) mycmd = New SqlCommand(strSQL, mycn) mycn.Open() dr1 = mycmd.ExecuteReader(CommandBehavior.CloseConnection) If dr1.Read() Then txtProductId.Text = dr1(‘ProductId’).ToString() txtQuantity.Text = dr1(‘QuantityPerUnit’).ToString() txtUnitPrice.Text = dr1(‘UnitPrice’).ToString() Else Response.Write(‘No data found’) End If End Sub ’ListBox1_SelectedIndexChanged C# string productid; string strSQL; string strConn; SqlDataReader dr,dr1; SqlConnection mycn; SqlCommand mycmd; private void Page_Load(object sender, System.EventArgs e) { strConn =’server=localhost;uid=sa;database=northwind;pwd=;’; lblError.Visible =false; // Put user code to initialize the page here if (!Page.IsPostBack ) { mycn = new SqlConnection(strConn); strSQL =’Select * from Products’; mycmd = new SqlCommand (strSQL, mycn); mycn.Open (); dr=mycmd.ExecuteReader(CommandBehavior.CloseConnection ); ListBox1.DataSource = dr; ListBox1.DataTextField =’ProductID’; ListBox1.DataValueField =’ProductID’; ListBox1.DataBind (); } } private void btnUpdate_Click(object sender, System.EventArgs e) { try { lblError.Visible =false; string updateCmd = ‘UPDATE Products SET QuantityPerUnit = @QuantityPerUnit ,’ + ‘UnitPrice = @UnitPrice where ProductId=@ProductId’; SqlConnection cn = new SqlConnection (strConn); SqlCommand myCommand = new SqlCommand(updateCmd, cn); myCommand.Parameters.Add(new SqlParameter(‘@QuantityPerUnit’, txtQuantity.Text )); myCommand.Parameters.Add(new SqlParameter(‘@UnitPrice’, Convert.ToDouble( txtUnitPrice.Text ))); myCommand.Parameters.Add(new SqlParameter(‘@ProductId’, Convert.ToInt16 ( txtProductId.Text))); cn.Open (); myCommand.ExecuteNonQuery (); lblError.Visible =true; lblError.Text =’Record Updated successfully’; } catch(Exception ex) { lblError.Visible =true; lblError.Text =(ex.Message ); } } private void ListBox1_SelectedIndexChanged(object sender, System.EventArgs e) { productid = ListBox1.SelectedItem.Value ; txtProductId.Text =productid.ToString (); strSQL = ‘Select * from Products where productid =’ + productid ; mycn = new SqlConnection(strConn); mycmd = new SqlCommand (strSQL, mycn); mycn.Open (); dr1=mycmd.ExecuteReader(CommandBehavior.CloseConnection ); if(dr1.Read() ) { txtProductId.Text = dr1[‘ProductId’].ToString (); txtQuantity.Text = dr1[‘QuantityPerUnit’].ToString (); txtUnitPrice.Text = dr1[‘UnitPrice’].ToString (); } else { Response.Write (‘No data found’); } }
How to save the Output of ASP.NET to HTML
Use the namespaces System.Net which have classes WebRequest and WebResponse. WebRequest is the abstract base class for the .NET Framework’s request/response model for accessing data from the internet and WebResponse is the abstract base class from which protocol specific response classes are derived. System.IO which have classes StreamReader and StreamWriter. StreamReader designed for character input in a particular encoding. StreamWriter designed for character output in a particular encoding. VB.NET Dim mywebReq As WebRequest Dim mywebResp As WebResponse Dim sr As StreamReader Dim strHTML As String Dim sw As StreamWriter Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ’Give the Appropriate URL for .aspx page. in this case its ‘http://www.syncfusion.com/faq/aspnet’ mywebReq = WebRequest.Create(‘http://www.syncfusion.com/faq/aspnet’) mywebResp = mywebReq.GetResponse() sr = New StreamReader(mywebResp.GetResponseStream) strHTML = sr.ReadToEnd sw = File.CreateText(Server.MapPath(‘temp.html’)) sw.WriteLine(strHTML) sw.Close() Response.WriteFile(Server.MapPath(‘temp.html’)) End Sub C# WebRequest mywebReq ; WebResponse mywebResp ; StreamReader sr ; string strHTML ; StreamWriter sw; private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here mywebReq = WebRequest.Create(‘http://www.syncfusion.com/faq/aspnet’); mywebResp = mywebReq.GetResponse(); sr = new StreamReader(mywebResp.GetResponseStream()); strHTML = sr.ReadToEnd(); sw = File.CreateText(Server.MapPath(‘temp.html’)); sw.WriteLine(strHTML); sw.Close(); Response.WriteFile(Server.MapPath(‘temp.html’)); } Note:For creating the file (in above case ‘temp.html’) give appropriate rights (modify and write) to the ASPNET user. To specify the Encoding specify the second parameter of the StreamReader accordingly VB.NET sr = New StreamReader(mywebResp.GetResponseStream, System.Text.Encoding.ASCII) C# sr = new StreamReader(mywebResp.GetResponseStream(), System.Text.Encoding.ASCII);