insert values into table from another table along with other values
insert values into table from another table along with other values
insert values into table from another table along with other values
insert values into table from another table along with other values
insert values into table from another table along with other values
insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values
insert values into table from another table along with other values insert values into table from another table along with other values
insert values into table from another table along with other values
Go Back  Xtreme Visual Basic Talk > > > insert values into table from another table along with other values


Reply
 
Thread Tools Display Modes
  #1  
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
  #2  
Old 04-09-2014, 07:41 AM
JustinCase2 JustinCase2 is offline
Junior Contributor
 
Join Date: Apr 2006
Posts: 324
Default 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.
Reply With Quote
Reply

Tags
copying table, vb.net


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
insert values into table from another table along with other values
insert values into table from another table along with other values
insert values into table from another table along with other values insert values into table from another table along with other values
insert values into table from another table along with other values
insert values into table from another table along with other values
insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values insert values into table from another table along with other values
insert values into table from another table along with other values
insert values into table from another table along with other values
 
insert values into table from another table along with other values
insert values into table from another table along with other values
 
-->