Read data from one table and load into another

Question

0.00/5 (No votes)

See more:

I need to read data from one table (tblEvaluationChecklist) and then load the data along with some user entered text into another table (tblEvaluationQuestions). For some reason it is not working. Can anyone help me out or push me in a better direction.

Other information: Both tables exist in the same SQL 2008 database. I am using VS 2010. I am just now learning vb.net, but have used SQL server with msaccess and this used to be done with an Append Table Query in MSAccess.

Dim strConnection As String = ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString
Dim objConnection As New SqlConnection(strConnection)
Dim strConnectionLoad As String = ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString
Dim objConnectionLoad As New SqlConnection(strConnectionLoad)
Dim strSQL As String
Dim strSQLLoad As String
Dim SQLCmd As New SqlCommand() 'The SQL Command
Dim SQLCmdLoad As New SqlCommand()
Dim SQLdr As SqlDataReader        '
objConnection.ConnectionString = strConnection     'Set the Connection String
objConnection.Open() 'Open the connection
objConnectionLoad.ConnectionString = strConnection     'Set the Connection String
objConnectionLoad.Open() 'Open the connection
Try
    'Get Checklist data from tblEvaluationChecklist
    strSQL = "SELECT [Checklist] ,[Item] ,[Objective] ,[ProgramGroup] FROM tblEvaluation " & _
            " WHERE Checklist = '" & ddChecklist.Text & "' and ProgramGroup = '" & CType(Session.Item("SessionProgramGroup"), String) & "'"
    SQLCmd = New SqlCommand() 'The SQL Command
    SQLCmd.Connection = objConnection 'Sets the Connection to use with the SQL Command
    SQLCmd.Parameters.Clear()
    SQLCmd.CommandText = strSQL
    'Load data into tblEvaluationQuestions
    strSQLLoad = "INSERT INTO tblEvaluationQuestions ([EvaluationID] " & _
           " ,[PID] " & _
           " ,[Checklist] " & _
           " ,[Item] " & _
           " ,[Objective]) " & _
        "VALUES " & _
           " (@EvaluationID " & _
           " ,@PID " & _
           " ,@Checklist " & _
           " ,@Item " & _
           " ,@Objective) "
    SQLCmdLoad = New SqlCommand() 'The SQL Command
    SQLCmdLoad.Connection = objConnectionLoad 'Sets the Connection to use with the SQL Command
    SQLCmdLoad.Parameters.Clear()
    SQLCmdLoad.CommandText = strSQL
    SQLdr = SQLCmd.ExecuteReader 'Gets Data
    If SQLdr.HasRows Then
        Do While SQLdr.Read()
            SQLCmdLoad.Parameters.Clear()
            SQLCmdLoad.Parameters.AddWithValue("@EvaluationID", txtAssessmentID.text)
            SQLCmdLoad.Parameters.AddWithValue("@PID", txtPID.Text)
            SQLCmdLoad.Parameters.AddWithValue("@Checklist", SQLdr("Checklist"))
            SQLCmdLoad.Parameters.AddWithValue("@Item", SQLdr("Item"))
            SQLCmdLoad.Parameters.AddWithValue("@Objective", SQLdr("Objective"))
            SQLCmdLoad.ExecuteNonQuery()
        Loop
    End If
    SQLdr.Close()
    objConnection.Close()
    objConnectionLoad.Close()
Catch ex As Exception
    lblAlert.Text = lblAlert.Text + " | " + "Error with updating Personel Evaluation, Contact the Quaility Control Manager"
    lblAlert.Visible = True
End Try

Posted 6-Oct-11 16:18pm

Andy Morris

Updated 6-Oct-11 23:02pm

Simon_Whale

v2

Add a Solution

4 solutions

Solution 1

Since you are using .NET 4 framework, LInQ to SQL will be the best and simple solution. Below few code is used to resolve your problem.

C#

// Fetching source table content
var SourceTable = 
from tbl1 in dbContext.Table
select tbl1;

// Loading destination table content with customized data
DestTable dest = new DestTable
{
    column1 = SourceTable.Column1 + "customdata",
    column2 = SourceTable.Column2 + "customdata",

    columnm = SourceTable.Columnm + "customdata"
}

// Updating in the destination database table
dbContext.DestTables.InsertOnSubmit(dest);
dbContext.SubmitChanges();

Posted 6-Oct-11 17:39pm

Ganesan Senthilvel

Comments

Dalek Dave 7-Oct-11 5:47am

Good Call.

Ganesan Senthilvel 7-Oct-11 10:02am

Thanx Dalek

Solution 3

I would look at doing all of this in a stored procedure

Have a look at the Inserting Data from Other Tables from this link

INSERT Examples (Transact-SQL)[^]

How to execute Stored Procedures from VB.NET Configuring Parameters and Parameter Data Types (ADO.NET)[^]

There is a great example at the bottom of the page

Posted 6-Oct-11 22:54pm

Simon_Whale

Comments

Dalek Dave 7-Oct-11 5:47am

Good Links.

Simon_Whale 7-Oct-11 5:50am

Thanks Dave

Solution 4

Check the below links.

http://www.sqlteam.com/article/using-select-to-insert-records[^]

http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/[^]

Posted 6-Oct-11 23:38pm

Toniyo Jackson

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bala Selvanayagam · Accepted Answer · 2011-10-06T22:33:00

Solution 2

you can use a single SQL statement for this task and its easy to implement and less compliecated

http://www.techonthenet.com/sql/insert.php[^]

look at the second example of this page where a record can be selected and inserted into an another table on one statement.

Posted 6-Oct-11 22:33pm

Bala Selvanayagam

Comments

Andy Morris 7-Oct-11 16:47pm

Thanks, this worked. Just in case someone else needs an example:
strSQL = "INSERT INTO tblEvaluationQuestions ([EvaluationID] " & _
" ,[PID] " & _
" ,[Checklist] " & _
" ,[Item] " & _
" ,[Objective]) " & _
"SELECT " & _
" '" & txtAssessmentID & "' as EvaluationID" & _
" ,'" & txtPID.Text & "' as PID" & _
" , Checklist " & _
" , Item " & _
" , Objective FROM tblEvaluation" & _
" WHERE checklist = '" & ddChecklist.Text & "' and ProgramGroup = '" & CType(Session.Item("SessionProgramGroup"), String) & "'"

Bala Selvanayagam 9-Oct-11 5:07am

Andy, please accept and vote for the answer, if you are happy