|
|Insert | Select
| Update | Delete |Parameter
Passing |
| INSERT ( User
type into text box or select from dropdownlist and submit info)
void SubRequest(Object
s, EventArgs e)
{
objcmd=new SqlCommand("INSERT INTO HELPDESK
(EmployeeID,StationNumber,CategoryID, SubjectID, Description,
StatusID) Values "+
" (@EmployeeID ,@StationNumber, @CategoryID ,@SubjectID,
@Description, @StatusID) ",objconn);
objcmd.Parameters.Add("@EmployeeID", 6);
objcmd.Parameters.Add("@StationNumber", Stationnum.Text
);
objcmd.Parameters.Add("@CategoryID ", Category.SelectedItem.Value
); //value &Text of dropdownlist
objcmd.Parameters.Add("@SubjectID ",Subject.SelectedItem.Value
);
objcmd.Parameters.Add("@Description ",Comment.Text );
objcmd.Parameters.Add("@StatusID ", 1);
objconn.Open();
objcmd.ExecuteNonQuery();
objconn.Close();
Response.Redirect("itself.aspx");
}
<form runat="server"> /* WEB CONTROLS
*/
<asp:Textbox id="Stationnum"
cssclass="textbox" runat="server"/>
<asp:DropDownList id="Category"
CssClass="dropdownmenu" runat="server" />
<asp:DropDownList id="Subject"
CssClass="dropdownmenu" runat="server" />
<p>
<asp:textbox id="Comment"
textmode="multiline" columns="50" runat="server"/>
<asp:Button id="btnsubmit" text="Submit
Request" OnClick="SubRequest"
runat="server" />
</form>
Manual SqlDatasource Insert
| protected void btnsubmit_Click(object
sender, EventArgs e)
{
SqlDataSource objsql = new SqlDataSource();
objsql.ConnectionString =ConfigurationManager.ConnectionStrings["StudentConnectionString"].ToString();
objsql.InsertCommandType = SqlDataSourceCommandType.Text;
objsql.InsertCommand="Insert into Email (EmailAddress,
DateStamp, IpAddress) values (@EmailAddress, @DateStamp,
@IPAddress)";
objsql.InsertParameters.Add("EmailAddress",
txtemail.Text);
objsql.InsertParameters.Add("DateStamp", DateTime.Now.ToString()
);
objsql.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
int rowaffected = 0;
objsql.Insert(); // exe Insert Operation, return # inserted
row
Server.Transfer("Newsletter_Confirm.aspx");
} |
Back
|
| SELECT (Choose
from dropdownlist and append all corresponding info on appropriate
controls) void Bindata() /* binding data from
db to >= controls */
{
objcmd=new SqlCommand("select * from Employees",objconn);
objconn.Open();
objrdr=objcmd.ExecuteReader();
Employ.DataSource=objrdr;
//assign datasource
Employ.DataValueField="EmployeeID";
Employ.DataTextField="Name";
//field shown on dropdownlist
Employ.DataBind();
//binding data to dropdownlist
objrdr.Close(); //release reader object
}
void SelectEmployee (Object
s, EventArgs e)
{
objcmd=new SqlCommand("select * from Employees WHERE EmployeeID=@EmployeeID",
objconn);
objcmd.Parameters.Add("@EmployeeID" , Employ.SelectedItem.Value
); //assign employeeid from dropdownlist
objconn.Open();
objrdr=objcmd.ExecuteReader();
while(objrdr.Read()) //Display employee info on controls
{
Name.Text=(String)
objrdr["Name"];
Username.Text=(String) objrdr["Username"];
Address.Text=(String) objrdr["Address"];
City.Text=(String) objrdr["City"];
State.Text=(String) objrdr["State"];
Zip.Text=(String) objrdr["Zip"];
HomePhone.Text=(String) objrdr["Homephone"];
Extension.Text=(String) objrdr["Extension"];
Mobile.Text=(String) objrdr["Mobilephone"];
}
objrdr.Close();
objconn.Close();
btnupdate.Enabled=true;
}
<form runat="server>
<asp:DropDownList id="Employ"
runat="server" /> //Control of drowpdownlist
<asp:Button id="btnsubmit" text="Select
Employee" OnClick="SelectEmployee"
runat="server" /><P>
</form>
Back
|
UPDATE (All
info are in control, change info and click button to update )
void UpdateEmployee(Object
s, EventArgs e) //all controls already have values
{ objcmd=new SqlCommand("UPDATE Employees
SET Name=@Name , Username=@Username , Address=@Address, City=@City,
State=@State , Zip=@Zip, "+
" Homephone=@Homephone, Extension=@Extension, Mobilephone=@Mobilephone
WHERE EmployeeID=@EmployeeID", objconn);
/* Assign control text to variable used for SQL statement */
objcmd.Parameters.Add(
"@Name ", Name.Text );
objcmd.Parameters.Add( "@Username ", Username.Text);
objcmd.Parameters.Add( "@Address ", Address.Text);
objcmd.Parameters.Add( "@City " , City.Text);
objcmd.Parameters.Add( "@State " , State.Text);
objcmd.Parameters.Add( "@Zip " , Zip.Text);
objcmd.Parameters.Add( "@Homephone " , HomePhone.Text);
objcmd.Parameters.Add( "@Extension " , Extension.Text);
objcmd.Parameters.Add( "@Mobilephone " , Mobile.Text);
objcmd.Parameters.Add( "@EmployeeID " ,Employ.SelectedItem.Value);
objconn.Open();
objcmd.ExecuteNonQuery();
objconn.Close();
Response.Redirect("itself.aspx");
}
<form runat="Server">
<asp:Button id="btnupdate" text="Update
Employee" OnClick="UpdateEmployee"
runat="server" /> <P>
</form>
Back
|
DELETE
void DeleteEmployee(Object
s, EventArgs e)
{
objcmd=new SqlCommand("DELETE FROM Employees WHERE EmployeeID=@EmployeeID",
objconn);
objcmd.Parameters.Add("@EmployeeID" ,
lbemployee.SelectedItem.Value
);
objconn.Open();
objcmd.ExecuteNonQuery();
objconn.Close();
}
<form runat="server">
<asp:ListBox id="lbemployee"
runat="server" /><p>
<asp:Button id="btndelete" text="DeleteEmployee"
OnClick="DeleteEmployee"
runat="server" /> <P>
</form>
Back
|
Parameter Passing
void Bindata()
{
objconn.Open(); //open connection 1 time is enough
if(Request.QueryString["id"]
!=null)
{
objcmd=new SqlCommand("select * from Employees Where EmployeeID="+
Request.QueryString["id"]
,objconn);
objrdr=objcmd.ExecuteReader();
DtMyAddBook.DataSource=objrdr; //assign datasource
DtMyAddBook.DataBind();
}
else
{
objcmd=new SqlCommand("select * from Employees",objconn);
objrdr=objcmd.ExecuteReader();
MyAddBook.DataSource=objrdr; //assign datasource
MyAddBook.DataBind();
}
objrdr.Close(); //release
objconn.Close();
}
<form runat="server">
<asp:DataGrid>
<columns>
<asp:HyperLinkColumn DataTextField="Name"
// table column name to be displayed
DataNavigateUrlField="EmployeeID"
/* field name to be passed *, VERY important */
DataNavigateUrlFormatString="addressbook.aspx?id={0}
" /* varible holds EmployeeID , call itself..Page_Load */
HeaderText="NAME" />
</columns>
</asp:DataGrid>
</form>
Back
|
| |
| |
| |
| |
|
