Amjad Ali Khawaja: August 2012

Monday 27 August 2012

"Insert multiple selected items of ListBox into SQL Server database in ASP.NET."

default.aspx;

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><
head runat="server"><title></title></
head><
body><form id="form1" runat="server"><div><asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><
br /><
asp:ListBox ID="ListBox1" runat="server" SelectionMode="Multiple"Height="100px" Width="90px"><asp:ListItem>USA</asp:ListItem><asp:ListItem>UK</asp:ListItem><asp:ListItem>Germany</asp:ListItem><asp:ListItem>France</asp:ListItem><asp:ListItem>Canada</asp:ListItem></
asp:ListBox><
br /><
asp:Button ID="Button1" runat="server" Text="Insert" onclick="Button1_Click" /><
br /><
asp:Label ID="Label1" runat="server"></asp:Label><
asp:Label ID="Label2" runat="server"></asp:Label></div></form></
body></
html>
There are lot of way to store data in Sql database but Following are some procedure for do.

Procedure1(StoreData in Single Row):

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e){
}

protected void Button1_Click(object sender, EventArgs e){

string insertText = "INSERT INTO CountryVisited(Name, CountryVisited) Values(@PersonName, @CountryVisited)";
string connString = @"Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(insertText, conn);conn.Open();
cmd.Parameters.Add(
"@PersonName", SqlDbType.NChar, 20);cmd.Parameters[
"@PersonName"].Value = TextBox1.Text;cmd.Parameters.Add(
"@CountryVisited", SqlDbType.NChar, 10);
foreach (ListItem item in ListBox1.Items){

if (item.Selected){

try{
Label2.Text = item.Text + "," + Label2.Text;cmd.Parameters[
"@CountryVisited"].Value = Label2.Text;

}
catch (Exception ex){
Label1.Text = ex.Message;
}
}
}
cmd.ExecuteNonQuery();
Label1.Text =
"Data Inserted";conn.Close();
}
}

Image:

Procedure2(StoreData in Multi Rows):

using System;
using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e){
}

protected void Button1_Click(object sender, EventArgs e){

string insertText = "INSERT INTO CountryVisited(Name, CountryVisited) Values(@PersonName, @CountryVisited)";
string connString = @"Data Source=.\sqlexpress;Initial Catalog=Test;Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(insertText, conn);conn.Open();
cmd.Parameters.Add(
"@PersonName", SqlDbType.NChar, 20);cmd.Parameters[
"@PersonName"].Value = TextBox1.Text;cmd.Parameters.Add(
"@CountryVisited", SqlDbType.NChar, 10);
foreach (ListItem item in ListBox1.Items){

if (item.Selected){

try{
cmd.Parameters["@CountryVisited"].Value = item.Text;

cmd.ExecuteNonQuery();

Label1.Text = "Data Inserted";

}

catch (Exception ex)

{

Label1.Text = ex.Message;

}

conn.Close();

}

Friday 24 August 2012

"Databinding in Combobox using C#."

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
namespace Dynamically_bind_data_to_combobox_from_database
{
public partial class Form1 : Form
{
string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["dsn"];
OleDbCommand com;
OleDbDataAdapter oledbda;
DataSet ds;
string str;
DataTable dt;
public Form1()
{
InitializeComponent();
}
//process1
private void button1_Click(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();
str = "select * from student";
com = new OleDbCommand(str, con);
oledbda = new OleDbDataAdapter(com);
ds = new DataSet();
oledbda.Fill(ds, "student");
dt = ds.Tables["student"];
int i;
for (i = 0; i <= dt.Rows.Count - 1; i++)
{
comboBox1.Items.Add(dt.Rows[i].ItemArray[0]);
}
con.Close();
}
//process2
private void button2_Click(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection(ConnectionString);
con.Open();
str = "select * from student";
com = new OleDbCommand(str, con);
oledbda = new OleDbDataAdapter(com);
ds = new DataSet();
oledbda.Fill(ds, "student");
comboBox2.DataSource = ds.Tables["student"];
comboBox2.DisplayMember = "sname";
con.Close();
}

private void Form1_Load(object sender, EventArgs e)

{

comboBox1.Text="Plz Click the button";

comboBox2.Text="Plz Click the button";

}

"Error Solving Code technique for Dataset and gridview."

In immediate Window

Dataset:
?ds.Tables[0].Rows[0].ItemArray[1] ==> Returned Column value (ItemArray[1] use for Column)
?ds.tables.count ==> Numbers of Table
?ds.tables[0].rows.count ==> Rows Count.

GridView
? gridview1.rows.count ==> Rows Count
? gridview1.Rows[0].Cells[1].Text ==> (Cells[1] use for Column)

Note
shift+f9 ==> Quick Watch(for value)

"Automatic Generate Serial Number Column Value in Grid View when another column value Returned from Database"

First create a Column Serial Number in Grid View.
Use "RowDataBound" GridView Event.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[0].Text = Convert.ToString(e.Row.DataItemIndex + 1);

}
}

"Total days between two dates."

Date Format(yr,days,month) 2012, 6, 10

WFTimeDateForm.cs[Design]

Text boxes Two,
Label One and
One Button.

WFTimeDateForm.cs[Code].

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Total_days_from_two_dates_csharp
{
  public partial class Form1 : Form
  {
  public Form1()
  {
  InitializeComponent();
  }

        private void btn_total_Click(object sender, EventArgs e)
  {
    int Totaldays = duration(textBox1.Text, textBox2.Text);
label1.Text = Convert.ToString(Totaldays);
  }

public int duration(string startdate, string enddate)
{

            DateTime dt1 = DateTime.Parse(Convert.ToDateTime(textBox1.Text).ToString("dd/MM/yyyy"));
  DateTime dt2 = DateTime.Parse(Convert.ToDateTime(textBox2.Text).ToString("dd/MM/yyyy"));
  TimeSpan ts = dt2.Subtract(dt1);
  int days = ts.Days;
  return days;
  }
    }
}

Thursday 16 August 2012

"Retrieve a Code behind file method in aspx form Page"

default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SimpleWebForms.aspx.cs" Inherits="SimpleWebForms" %>

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<U>Showtimes for <%WriteDate();%></U>

</div>
</form>
</body>
</html>

default.aspx.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class SimpleWebForms : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void WriteDate()
{
Response.Write(DateTime.Now.ToString());
}
}

"Insert Image into DB and Retrieve into a Data List using asp.net C#"

Create Table in Database:

Column Name DataType Key

id int PK

img varchar(500)

Create Store Procedure:

Insert

create PROC Insert_Image(@img varchar(100))

INSERT INTO Image(img)VALUES(@img)

Retrieve:

create proc sp_Get_Image

SELECT img from Image

default.aspx

//Create Images Folder in Project

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

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

</head>

<body>

<div>

<tr>

<td>

<asp:Label ID="Label1" runat="server" Text="Image"></asp:Label>

</td>

<td>

<asp:FileUpload ID="UploadFile" runat="server" />

<asp:TextBox ID="txtimg" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

<asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />

<asp:Button ID="btnShow" runat="server" Text="Show" onclick="btnShow_Click" />

</td>

</tr>

</table>

<br />

<asp:DataList ID="dlImageGet" runat="server" Height="368px" Width="286px">

<tr>

</td>

</tr>

</table>

</FooterTemplate>

<tr>

</td>

</tr>

</table>

</HeaderTemplate>

<tr>

<asp:Image ID="img" runat="server" ImageUrl='<%# Bind("~/Images/{0}","img") %>' />

</td>

</tr>

</table>

</ItemTemplate>

</asp:DataList>

</div>

</form>

</body>

</html>

default.aspx.cs;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=DataListDb;Integrated Security=True");

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnSave_Click(object sender, EventArgs e)

{

try

{

Upload_Documents();

txtimg.Text = "~/Images/" + UploadFile.FileName;

SqlCommand cmd = new SqlCommand("Insert_Image", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@img", txtimg.Text);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

catch (Exception ex) { throw new Exception(ex.Message); }

}

protected void btnShow_Click(object sender, EventArgs e)

{

try

{

//SqlCommand cmd = new SqlCommand("Get_Records", sqlcon);

SqlDataAdapter da = new SqlDataAdapter("sp_Get_Image", con);

da.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();

da.Fill(ds);

if (ds.Tables[0].Rows.Count > 0)

{

dlImageGet.DataSource = ds.Tables[0];

dlImageGet.DataBind();

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

protected void Upload_Documents()

{

try

{

// Save the file

string filePath = Server.MapPath("~/Images/" + UploadFile.FileName);

UploadFile.SaveAs(filePath);

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}