Wednesday, March 21, 2012

Please help me I am stuck!

I have a few question on ASP.net accessing database. Can you please help me??
The situation is like this:
There are 2 listbox, listboxA and listboxb. ListboxA contains all the available interviewer's names. Then listboxb contains the selected interviewer's name. Now I need to extract all the name from listboxb and find their corresponding ID in a table call interviewer then insert their corresponding ID to another table call performance. How am I suppose to do that? Secondly is: For example i got 2 sql statement, how do I execute them at the same time, in other words is there any codes that can insert a data into multiple tables and multiple rows to a database?
Can you please help me with it?
I am really stuck on this.here is the code:

I used button press event to insert the ID's from the listb.


if(!Page.IsPostBack)
{
SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM tblPerson",myConnection);
DataSet ds = new DataSet();
ad.Fill(ds,"tblPerson");

myBox.DataSource = ds;
myBox.DataTextField = "Name";
myBox.DataValueField = "PersonID";
myBox.DataBind();

}
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.myConnection = new System.Data.SqlClient.SqlConnection();
//
// myConnection
//
this.myConnection.ConnectionString = "workstation id=GEEK;packet size=4096;integrated security=SSPI;initial catalog=DBP" +
"erson;persist security info=False";
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void Button1_Click(object sender, System.EventArgs e)
{
foreach(ListItem l in myBox.Items)
{
SqlCommand myCommand = new SqlCommand("ADDPERSON",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

myCommand.Parameters.Add(new SqlParameter("@.p_ID",SqlDbType.Int,4));
myCommand.Parameters["@.p_ID"].Value = Convert.ToInt32(l.Value);

myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

}
}


Thanks alot... but I don't really know how to use StoredProcedure.
Where did you initialise the "ADDPERSON" ?
Is there any website I can look up for reference?
Sorry to trouble you, but thanks alot
Creating Stored Procedures and User-Defined Functions;

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbt7/html/dvhowcreatingstoredprocedure.asp

Using Stored Procedures with a Command

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp
Here is the stored procedure that I have used:


CREATE PROCEDURE [ADDPERSON]

@.p_ID int

AS

INSERT INTO tblDeveloper(PersonID) VALUES(@.p_ID);
GO

0 comments:

Post a Comment