Solved

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

Posted on 2012-03-24
9
364 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

16 Experts available now in Live!

Get 1:1 Help Now