Solved

ASP.NET how to read from a table and insert into a second table SQL Server ?

Posted on 2012-03-24
9
366 Views
Last Modified: 2012-04-17
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

       }
0
Comment
Question by:TonyReba
  • 5
  • 2
  • 2
9 Comments
 
LVL 10

Expert Comment

by:himanshut
ID: 37762294
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
 
LVL 9

Author Comment

by:TonyReba
ID: 37762755
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
 
LVL 9

Author Comment

by:TonyReba
ID: 37762758
Page 1 authenticates and connects to sql server1 page 2 inserts survey answets on sql server 2.
0
 
LVL 10

Expert Comment

by:himanshut
ID: 37764103
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Author Comment

by:TonyReba
ID: 37764158
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
 
LVL 12

Accepted Solution

by:
Anuradha Goli earned 500 total points
ID: 37765307
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
 
LVL 9

Author Comment

by:TonyReba
ID: 37765964
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
 
LVL 12

Expert Comment

by:Anuradha Goli
ID: 37806313
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
 
LVL 9

Author Comment

by:TonyReba
ID: 37806511
that looks more like a classic asp syntax, mine is asp.net c#
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now