Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-24
9
Medium Priority
?
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 1500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

618 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