TonyReba
asked on
ASP.NET how to read from a table and insert into a second table SQL Server ?
I have two aspx web forms in asp.net 3.5. In one form I am authentication users against a table which have information, I am using two fields to check : employee id and SS N,
Once the credentials are accepted, I take them to a survey page,
How Do I create a Stored Procedure or a Code in ADO.NET to read the values of several columns from the logged in userid, from this table ( empInfor) and Insert into a second table located at another sql server instance at Submit Button CLick?
Below is the code which checks the user
Once the credentials are accepted, I take them to a survey page,
How Do I create a Stored Procedure or a Code in ADO.NET to read the values of several columns from the logged in userid, from this table ( empInfor) and Insert into a second table located at another sql server instance at Submit Button CLick?
Below is the code which checks the user
protected void LogIn(object sender, ImageClickEventArgs e) { //Method that checks employee id and SSN against empInfo Table string strconn = WebConfigurationManager.ConnectionStrings["wfcdbConnectionString"].ConnectionString; using (SqlConnection conn = new SqlConnection(strconn)) { conn.Open(); string cmdStr = "select count(*) from [empInfo]"; cmdStr += " where ID='" + UserName.Text + "'"; cmdStr += " and RIGHT(SSN,4)='" + Password.Text + "'"; SqlCommand cmd = new SqlCommand(cmdStr, conn); int i = Convert.ToInt16(cmd.ExecuteScalar()); if (i == 0) { LtlLogin.Text = "<div id='lit1'> <p>Error: Incorrect login details provided.</p></div>"; Session["empCheck"] = false; } else { Session["empCheck"] = true; //Create a New Session Var Session["empId"] = UserName.Text; Response.Redirect("Main.aspx"); }
}
ASKER
Do I get the informatiln on login page? Or im better of uding the id to open a second connection on page 2 (insert). Can you provide some sample code on datasets. How to accomplish these.?
ASKER
Page 1 authenticates and connects to sql server1 page 2 inserts survey answets on sql server 2.
ASKER
what would be a better approach, to pass the id and open a new connection into the insert page, or to pass the session values from table 1 , (connection on page 1 only to pulll values)
and second page insert the session values plus the survey value fields?
Should I use a datareader or datatable or how do I perform the reading?
and second page insert the session values plus the survey value fields?
Should I use a datareader or datatable or how do I perform the reading?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats a good idea,
can you provide a sample code on how to select from one instance , set the parameters , and insert into the second instance?
can you provide a sample code on how to select from one instance , set the parameters , and insert into the second instance?
I can suggest you an outline of solution , you can verify syntax and use it as needed.
select from table
set parameters
insert into other table
select from table
set conn = Server.CreateObject("adodb.connection")
conn.open("DSN=TestServer;UID=sa;PWD=sa123x;DATABASE=EDI;")
set rs=Server.CreateObject("ADODB.recordset")
sql1 = "select * from table1";
rs.open sql1, conn
set parameters
column1 = rs.Fields("fieldname1").Value
.....
insert into other table
set conn1 = Server.CreateObject("adodb.connection")
conn1.open("DSN=TestServer;UID=sa;PWD=sa123x;DATABASE=EDI;")
set rs1=Server.CreateObject("ADODB.recordset")
sql2 = "insert into table2 values('"+column1 +"')";
rs.open sql2, conn1
ASKER
that looks more like a classic asp syntax, mine is asp.net c#
{
Check which user has logged in by using employeeID(assuming employee is the user)
Then use DataSet (Or DataTable) to get the what ever information you need to have from table 1 of SQL Server 1 PLUS store survey responses and store them in some Session variables
}
On Button Click Event()
{
Pass all these values (Session variable values) to insert/update query you have for Table 1 of SQL Server 2.
}