从一个表中读取数据并加载到另一个表中 [英] Read data from one table and load into another

问题描述

我需要从一个表(tblEvaluationChecklist)中读取数据，然后将数据和一些用户输入的文本一起加载到另一个表(tblEvaluationQuestions)中.由于某种原因，它无法正常工作.谁能帮助我或将我推向更好的方向.

其他信息:两个表都存在于同一SQL 2008数据库中.我正在使用VS2010.我现在正在学习vb.net，但已将SQL Server与msaccess一起使用，而这以前是通过MSAccess中的追加表查询完成的.

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

推荐答案

由于您使用的是.NET 4框架，因此LInQ to SQL将是最佳且简单的解决方案.下面的一些代码用于解决您的问题.

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.

// 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();

您可以为该任务使用单个SQL语句，并且该语句易于实现且不易使用



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

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.

我将看一下在存储过程中进行的所有操作

在此链接中查看从其他表插入数据
插入示例(Transact-SQL) [配置参数和参数数据类型(ADO .NET) [ ^ ]

页面底部有一个很好的例子

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

这篇关于从一个表中读取数据并加载到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！