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
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");             } 

Open in new window

       }
LVL 9
TonyRebaAsked:
Who is Participating?
 
Anuradha GoliConnect With a Mentor Systems Development / Support SpecialistCommented:
The best way I can think of to accomplish this is via sp_addlinkedserver. You need to make sure that whatever account you use to add the link (via sp_addlinkedsrvlogin) has permissions to the table you're joining, but then once the link is established, you can call the server by name, i.e.:

SELECT *
FROM server1table
INNER JOIN server2.database.dbo.server2table ON .....

So can write single storedprocedure to retrieve data from one instance and insert into other instance.

Hope this helps.
0
 
himanshutCommented:
On Page_Load():
{
 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.
}
0
 
TonyRebaAuthor Commented:
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.?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
TonyRebaAuthor Commented:
Page 1 authenticates and connects to sql server1 page 2 inserts survey answets on sql server 2.
0
 
TonyRebaAuthor Commented:
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?
0
 
TonyRebaAuthor Commented:
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?
0
 
Anuradha GoliSystems Development / Support SpecialistCommented:
I can suggest you an outline of solution , you can verify syntax and use it as needed.

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

Open in new window


set parameters
column1 = rs.Fields("fieldname1").Value
.....

Open in new window


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

Open in new window

0
 
TonyRebaAuthor Commented:
that looks more like a classic asp syntax, mine is asp.net c#
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.