Solved

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

Posted on 2012-03-24
9
370 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
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!

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 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