Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   ASP.Net (http://www.xtremevbtalk.com/asp-net/)
-   -   insert values into table from another table along with other values (http://www.xtremevbtalk.com/asp-net/325077-insert-values-table-table-values.html)

qmira 11-14-2012 10:32 AM

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! :)

JustinCase2 04-09-2014 06:41 AM

Bit hard to say
 
Not sure about this without seeing the actual datasource. But i think Your second attempt is the best one:

Code:

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"

However Your INSERT statement has 1 more Field than the SELECT statement ([compName] is declared twize in the insert but not the Select). Not sure if thats the problem but its the only one i see ATM.


All times are GMT -6. The time now is 12:46 PM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.