Is possible to transfer data from one table into an empty table along with a other data? Is it possible to do that with a single SQL statement? What I've tried so far produces no errors but the data from the original table is not transferred to the empty table.
是否可以将数据从一个表传输到空表以及其他数据?是否可以使用单个SQL语句执行此操作?到目前为止我所尝试的没有产生任何错误,但原始表中的数据不会传输到空表。
I've include my code below and I'd appreciate any advice. I suspect there's a problem with the database connection, but I am still new to programming and I can't see the problem.
我在下面提供了我的代码,我很感激任何建议。我怀疑数据库连接有问题,但我还是新编程,我看不出问题。
Imports System.Data.SqlClient
Public Class uploadSuccess
Inherits System.Web.UI.Page
Dim con As SqlConnection
Dim cmd As SqlCommand
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
con = New SqlConnection("Data Source=127.0.0.1; User Id=user;Password=pass;Initial Catalog=catalog;")
con.Open()
cmd = con.CreateCommand()
cmd.Connection = con
Dim intCount As Int32
Dim getReceipt As Int32
Dim femtocellrow As Int32
Dim noreceipt As Int32
Dim username As String
Dim client As String
Dim comName As String
noreceipt = Session("recNo")
username = Session("username")
comName = Session("compName")
'Response.Write("noreceipt" & noreceipt)
cmd.CommandText = "SELECT COUNT(*) AS RETURNCOUNT FROM dbo.tempTable"
intCount = cmd.ExecuteScalar
' Response.Write("count " & intCount)
cmd.CommandText = "SELECT noDevices FROM dbo.Receipt WHERE receiptNo= @noreceipt"
cmd.Parameters.AddWithValue("@noreceipt", noreceipt)
getReceipt = cmd.ExecuteScalar
'Response.Write("receipt " & getReceipt)
cmd.CommandText = "SELECT COUNT(*) AS RETURNCOUNT FROM dbo.femtocell WHERE receiptNo= @norec"
cmd.Parameters.AddWithValue("@norec", noreceipt)
femtocellrow = cmd.ExecuteScalar
'Response.Write("femrow " & femtocellrow)
cmd.CommandText = "SELECT clientID FROM dbo.Receipt WHERE companyName=@comName"
cmd.Parameters.AddWithValue("@comName", comName)
client = cmd.ExecuteScalar
'Response.Write("client " & client)
con.Close()
Dim rowsAffected As Integer = 0
Dim myConnectionString As String = "Data Source=192.168.18.30; User Id=sa;Password=google;Initial Catalog=femtocell;"
Dim myConnection As New SqlConnection(myConnectionString)
If (femtocellrow < getReceipt) & (intCount < femtocellrow) Then
'Dim myQuery As String = "SELECT ([uploadID],[clientID],[receiptNo],[compName],[state],[town],[district],[siteAddress],[latitude],[longitude],[type],[serialNo],[man],[model],[pwr],[appNo],[manAntenna],[modelAntenna],[height],[gain],[emission],[bhaul],[strucType],[covType],[cov],[spectBand],[txFreq],[rxFreq],[bw],[regFee],[uspArea],[commDate],[compName]) INTO dbo.femtocell (username,client,noreceipt,comName,[State],[Town],[District],[Site_Address],[Latitude],[Longitude],[Equipment_Type],[Equipment_Serial_No],[Equipment_Man],[Equipment_Model],[Equipment_Pwr_Mw],[Equipment_App_No],[Antenna_Man],[Antenna_Model],[Antenna_Height_m],[Antenna_Gain_Db],[antenna_emission],[Bhaul],[Struc_Type],[Cov_Type],[Cov_m],[Spect_Band],[Tx_Freq_MHz],[Rx_Freq_MHz],[Bw_KHz],[Reg_Fee_RM],[USP_area],[Comm_Date]) FROM dbo.tempTable"
' Dim myQuery As String = "INSERT INTO dbo.femtocell ([uploadID],[clientID],[receiptNo],[compName],[state],[town],[district],[siteAddress],[latitude],[longitude],[type],[serialNo],[man],[model],[pwr],[appNo],[manAntenna],[modelAntenna],[height],[gain],[emission],[bhaul],[strucType],[covType],[cov],[spectBand],[txFreq],[rxFreq],[bw],[regFee],[uspArea],[commDate],[compName]) VALUES (SELECT 'username','client','noreceipt','comName',[State],[Town],[District],[Site_Address],[Latitude],[Longitude],[Equipment_Type],[Equipment_Serial_No],[Equipment_Man],[Equipment_Model],[Equipment_Pwr_Mw],[Equipment_App_No],[Antenna_Man],[Antenna_Model],[Antenna_Height_m],[Antenna_Gain_Db],[antenna_emission],[Bhaul],[Struc_Type],[Cov_Type],[Cov_m],[Spect_Band],[Tx_Freq_MHz],[Rx_Freq_MHz],[Bw_KHz],[Reg_Fee_RM],[USP_area],[Comm_Date]FROM dbo.tempTable"
Dim myQuery As String = "INSERT INTO dbo.femtocell ([state],[town],[district],[siteAddress],[latitude],[longitude],[type],[serialNo],[man],[model],[pwr],[appNo],[manAntenna],[modelAntenna],[height],[gain],[emission],[bhaul],[strucType],[covType],[cov],[spectBand],[txFreq],[rxFreq],[bw],[regFee],[uspArea],[commDate]) SELECT [State],[Town],[District],[Site_Address],[Latitude],[Longitude],[Equipment_Type],[Equipment_Serial_No],[Equipment_Man],[Equipment_Model],[Equipment_Pwr_Mw],[Equipment_App_No],[Antenna_Man],[Antenna_Model],[Antenna_Height_m],[Antenna_Gain_Db],[antenna_emission],[Bhaul],[Struc_Type],[Cov_Type],[Cov_m],[Spect_Band],[Tx_Freq_MHz],[Rx_Freq_MHz],[Bw_KHz],[Reg_Fee_RM],[USP_area],[Comm_Date]FROM dbo.tempTable"
Dim myCommand As New SqlCommand(myQuery, myConnection)
Try
myConnection.Open()
rowsAffected = myCommand.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.Message)
Finally
myConnection.Close()
End Try
Else
Dim message As String = "Number of devices submitted is invalid."
Dim sb As New System.Text.StringBuilder()
sb.Append("alert('")
sb.Append(message)
sb.Append("');")
ClientScript.RegisterOnSubmitStatement(Me.GetType(), "alert", sb.ToString())
End If
End Sub
End Class
1 个解决方案
#1
2
Maybe one of these will help:
也许其中一个会有所帮助:
-
SQL SERVER - 将数据从一个表插入另一个表 - INSERT INTO SELECT - SELECT INTO TABLE sqlauthority.com
-
copy all rows of a table to another table
*.com将表的所有行复制到另一个表*.com
-
C# – Bulk copying data into MS SQL Server with DataTables
myadventuresincoding.wordpress.comC# - 使用DataTables myadventuresincoding.wordpress.com将数据批量复制到MS SQL Server中
#1
2
Maybe one of these will help:
也许其中一个会有所帮助:
-
SQL SERVER - 将数据从一个表插入另一个表 - INSERT INTO SELECT - SELECT INTO TABLE sqlauthority.com
-
copy all rows of a table to another table
*.com将表的所有行复制到另一个表*.com
-
C# – Bulk copying data into MS SQL Server with DataTables
myadventuresincoding.wordpress.comC# - 使用DataTables myadventuresincoding.wordpress.com将数据批量复制到MS SQL Server中