Inserting value from one table to another

[gi11ies]

Hi

This is probably pretty simple, but any help would be great. I'm trying to insert the user ID of all users in a user table into another table.

My simple select statment gets the users IDs ....

Dim nicConn As OleDbConnection
Dim nicConn1 As String
nicConn1 = ConfigurationSettings.AppSettings("connString1")
nicConn = New OleDbConnection(nicConn1)
Dim nicSQL as String = "select user_id from users"
Dim nicCmd as New OleDbDataAdapter(nicSQL, nicConn)
Dim nicDS As DataSet=New DataSet()
nicCmd.Fill(nicDS,"Users")

From the dataset I want to put each user ID into into the other table. So Im guessing its

For each row in the users dataset
Insert into the table the user ID
Next

If some one could point me in the right direction to do this, it would be great !!!

Gillies

 

[ca8msm]

You don't have to do it like that (unless you have some other functions to do that we are unaware of). You can simply run one SQL command e.g.

strSQL = "INSERT INTO TABLE1 (USERID) SELECT USERID FROM USERS"

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[gi11ies]

Thanks, that will do it - but I didnt explain myself properly (sorry) ... the tables are in seperate databases ... to add to the confusion. The full code I have so far is.

Sub btnAddAll_click (Sender As Object, e As EventArgs )
If Not Page.IsPostBack then
Dim nicConnA As OleDbConnection
Dim nicConn2 As String
nicConn2 = ConfigurationSettings.AppSettings("connString1")
nicConnA = New OleDbConnection(nicConn2)
Dim nicSQL2 as String = "select * from users"
Dim nicCmd2 as New OleDbDataAdapter(nicSQL2, nicConnA)
Dim nicDS2 As DataSet=New DataSet()
nicCmd2.Fill(nicDS2,"AllUsers")

Dim userRow as DataRow

For Each userRow in AllUsers.Tables[0].Rows
Dim nicAddConn2 As OleDbConnection
Dim nic3Conn As String
nic3Conn = ConfigurationSettings.AppSettings("connString4")
nicAddConn2 = New OleDbConnection(nic3Conn)
nicAddConn2.Open
Dim nicSQL3 as String = "insert into recipients (message_id, to_id, status, message_date, message_from, message_from_dept) values ('" & Request.QueryString("msgID") & "', '" & ?????????? & "','Pending','" & Request.QueryString("msgDate") & "','" & Request.QueryString("msgFrom") & "','" & Session("department") & "')"
Dim nicCmd3 as New OleDbCommand(nicSQL3, nicAddConn2)
nicCmd3.ExecuteNonQuery()
nicAddConn2.Close
Next
Response.Redirect("msgs_confirm.aspx")
End If
End Sub

I am not sure if this For Loop I have for the dataset will work, and am not sure what to put for ????????? in the insert statement.

If anyone can point out what I should do, it would be great!

Gillies

 

[ca8msm]

If you just want to extract data from a row within your DataRow, you can just use "userrow.Item(0)" (where zero is the index of the column that contains the relevant data).



____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.