CODEWITHSUNDEEP
asp.netsqlvb.net
user1824547
user1824547

Reputation: 1

Copying data from one table to another (along with some other data)

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.

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

Upvotes: 0

Views: 903

Answers (1)

pawciu
pawciu

Reputation: 945

Maybe one of these will help:

Upvotes: 2

Related Questions