View Single Post
 
Old 11-14-2012, 11:32 AM
qmira qmira is offline
Newcomer
 
Join Date: Nov 2012
Posts: 1
Default insert values into table from another table along with other values

hi, I would really appreciate it if someone could help me with this. I know that copying data from one table into another table is pretty easy to do. But i can't figure out how to to copy data from one table to another, but also including some values that aren't in the original table. I'm wondering if it is possible to do it with a single sql statement. And fyi, i'm a newbie with asp.net vb.. So sorry if there are some major problems in my coding. Its my first time doing a program with vb.net. so i've pasted below my coding. can you tell me whats wrong with it? apparently when i run it, there's no error. But the data isn't transferring into the new table.

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=192.168.18.30; User Id=sa;Password=google;Initial Catalog=femtocell;")
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




Thanks!
Reply With Quote