none
How To insert Selected Values From 4 Sql tables Into a 'New Table' Using vb.net Application? RRS feed

  • Question

  • I have 4 sql Tables ;dbstage1,dbstage2,dbstage3,dbstage4
    like it looks in pictures;

    stage1dbStage2dbstage3dbstage4


    I want to Create a New Table named 'DbStages' with the 4 Tables values and this query :

     Select  (DbStage1.CID) As CID 
    ,(DbStage1.Villes)As Villes ,(DbStage1.Candidats) AsCandidats
    ,ISNULL(DbStage1.Points,0) As Stage1
    ,ISNULL(DbStage2.Points,0) As Stage2
    ,ISNULL(DbStage3.Points,0) As Stage3
    ,ISNULL(DbStage4.Points,0) As Stage4 
    from((DbStage1 FULL OUTER JOIN DbStage2 ON DbStage1.CID=DbStage2.CID) FULL OUTER JOIN DbStage3  ON DbStage2.CID=DbStage3.CID)FULL OUTER JOIN DbStage4 ON dbStage3.CID=DbStage4. CID Order by DbStage1.CID


    which will looking like this picture:

    dbStages

     My Question is how to insert selected values into the New Table 'DbStages' using a visual basic application?

    So , Thank you very Much,

    Best Regards ,Bajtitou.





    • Edited by Bajtitou Thursday, April 19, 2018 7:16 PM
    Thursday, April 19, 2018 9:44 AM

Answers

  • It seems my replies make no sense. 

    I don't know what you're doing. Assuming you made the new table with SQL manager explorer (whatever its name has in your version)

    Then the code can be this (quick and dirty done)

    Imports System.Data.SqlClient
    Public Class Form1 'whatever you use"'
        Private Sub FillNewTable()
            Dim JoinedTable As New DataTable
            Using ad As New SqlDataAdapter("YourJoinSQL", "yourconnectionstring")
                ad.Fill(JoinedTable)
            End Using
            Dim insertstring = "Insert into MyNewTable values (x,y,z) from (@x, @y, @z)"
            Using con As New SqlConnection("yourconnectionstring")
                con.Open()
                Using com As New SqlCommand(insertstring)
                    For Each row As DataRow In JoinedTable.Rows
                        com.Parameters.Clear()
                        For Each column In JoinedTable.Columns
                            com.Parameters.Clear()
                            com.Parameters.AddWithValue("@x", row.Item("x"))
                            com.Parameters.AddWithValue("@y", row.Item("y"))
                            com.Parameters.AddWithValue("@z", row.Item("z"))
                        Next
                        com.ExecuteNonQuery()
                    Next
                End Using
            End Using
        End Sub
    End Class


    Success
    Cor


    • Edited by Cor Ligthert Friday, April 20, 2018 9:56 AM
    • Marked as answer by Bajtitou Friday, April 20, 2018 9:40 PM
    Friday, April 20, 2018 9:55 AM

All replies

  • Create an SQL insert statement with parameters and use the SQLClient executenonquery command.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

    With this information it should be a very simple job to make the code which every beginner can do. 


    Success
    Cor

    Thursday, April 19, 2018 10:17 AM
  • For SQL-Server (doing this in MS-Access the concept is the same yet different methods would be needed, Oracle is a bit more complicated) you can create the table structure first or have the structure already created.

    Note that all the scripts below for VB.NET require a connection and command

    I created the following in SQL-Server Management Studio as an example. Execute with ExcuteNonQuery

    Dim createTable As String =
            <SQL>
            CREATE TABLE [dbo].[Karen](
    	        [CustomerIdentifier] [INT] IDENTITY(1,1) NOT NULL,
    	        [CompanyName] [NVARCHAR](40) NOT NULL,
    	        [ContactName] [NVARCHAR](30) NULL,
    	        [ContactIdentifier] [INT] NULL,
    	        [ContactTypeIdentifier] [INT] NULL,
    	        [Street] [NVARCHAR](60) NULL,
    	        [City] [NVARCHAR](15) NULL,
    	        [PostalCode] [NVARCHAR](10) NULL,
    	        [CountryIdentfier] [INT] NULL,
    	        [Phone] [NVARCHAR](24) NULL,
    	        [ModifiedDate] [DATETIME2](7) NULL
            ) ON [PRIMARY]
            </SQL>.Value

    If you want to total replace the data if the table exists first using ExecuteNonQuery

    DROP TABLE [dbo].[Karen]

    If you don't want to overwrite data, for VB.NET ignore the BEGIN/END and use Cint(cmd.ExecuteScalar)

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Karen')
    BEGIN
      PRINT 'Table Exists - append'
    END
    For pushing data to the new table look at MERGE or BULK INSERT.


     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, April 19, 2018 11:02 AM
    Moderator
  • My Question is how to insert selected values into the New Table 'DbStages' using a visual basic application?

    So , Thank you very Much,

    Best Regards ,Bajtitou.

    Hi Bajtitou,

    You said that you want to insert selected value into new Table "DbStages" using vb.net, can you tell me where do you select value, and how do you select value? Here is about insert value into sql database, you can take a look firstly.

    https://stackoverflow.com/questions/12634516/how-can-i-insert-data-into-sql-server-using-vbnet

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 20, 2018 7:29 AM
    Moderator
  • For SQL-Server (doing this in MS-Access the concept is the same yet different methods would be needed, Oracle is a bit more complicated) you can create the table structure first or have the structure already created.

    Note that all the scripts below for VB.NET require a connection and command

    I created the following in SQL-Server Management Studio as an example. Execute with ExcuteNonQuery

    Dim createTable As String =
            <SQL>
            CREATE TABLE [dbo].[Karen](
    	        [CustomerIdentifier] [INT] IDENTITY(1,1) NOT NULL,
    	        [CompanyName] [NVARCHAR](40) NOT NULL,
    	        [ContactName] [NVARCHAR](30) NULL,
    	        [ContactIdentifier] [INT] NULL,
    	        [ContactTypeIdentifier] [INT] NULL,
    	        [Street] [NVARCHAR](60) NULL,
    	        [City] [NVARCHAR](15) NULL,
    	        [PostalCode] [NVARCHAR](10) NULL,
    	        [CountryIdentfier] [INT] NULL,
    	        [Phone] [NVARCHAR](24) NULL,
    	        [ModifiedDate] [DATETIME2](7) NULL
            ) ON [PRIMARY]
            </SQL>.Value

    If you want to total replace the data if the table exists first using ExecuteNonQuery

    DROP TABLE [dbo].[Karen]

    If you don't want to overwrite data, for VB.NET ignore the BEGIN/END and use Cint(cmd.ExecuteScalar)

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Karen')
    BEGIN
      PRINT 'Table Exists - append'
    END
    For pushing data to the new table look at MERGE or BULK INSERT.


    HI,      

    I tried with this samples 

    Imports System.Data 
    Imports System.Data.SqlClient
    Public Class  MyOperations
    Public Property ConfigurationManager As Object
    '--**''--
    Public Function CreateTable(ByVal TableName As String) As
    Boolean        
            Try
                   Using con As New SqlConnection(ConnectionString2)
                   Using cmd As New SqlCommand With {.Connection = con,.CommandText = TableStructure(TableName)}
                   If TableExists(TableName) Then
                   Return False 
                   End If
                   con.Open()
                   cmd.ExecuteNonQuery()
                   Return True
                   End Using
                   End Using
            Catch ex As Exception
            Return False
            End Try
            End Function 
          '--**--
    Public Function StageTable(ByVal TableName As String) As String 
              ' Dim tableName As String = "dbStages"
                Dim StageTable As String =
                <sql>
                    CREATE TABLE <%= TableName %>
                    (
                           [ID] [INT] IDENTITY(1,1) NOT NULL,
                           [CID] [NCHAR](10) NOT NULL,
                           [Villes] [NVARCHAR](30) NULL,
                           [Candidats] [INT] NULL,
                           [Stage1] [INT] NULL,
                           [Stage2] [INT] NULL,
                           [Stage3] [INT] NULL,
                           [Stage4] [INT] NULL
                           ) ON [PRIMARY]
                          </sql>.Value
                          Return StageTable 
                          End Function
                  '--'**--''
                   Public Function TableExists(ByVal TableName As String) As Boolean
                Dim cmdText As String =
                ("select * from DCO.dbo where name=\’" + (TableName + "\’")) 
                 Dim Result As Boolean = False 
                 Using con As New SqlConnection(ConnectionString2) con.Open()
                 Using sqlConnection As SqlConnection = New SqlConnection(ConnectionString2)
                 sqlConnection.Open()
                 Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection) 
                 Using reader As SqlDataReader = sqlCmd.ExecuteReader
                  End Using
                  End Using
                  End Using
                  Return Result
                  End Using
                  End Function
    
                    '-**-**
              Public Function DropTable(ByVal TableName As String) As Boolean
    
             Dim Result As Boolean = False
    
                 Try
                      Dim con As New SqlConnection(ConnectionString2)
                      Dim cmd As New SqlCommand With {.CommandText = "DROP TABLE " & TableName, .Connection = con}
                      con.Open()
                      cmd.ExecuteNonQuery()
                      con.Close()
    
                    Result = True
                    Catch ex As SqlException
                    Return False
                    End Try
    
                    Return Result
                    End Function

    in Form1 load:

    Option Strict On
    Public Class Form1
    
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
       Dim ops As New MyOperations 
       ops1.CreateTable("dbStage")
       End Sub


    So What is wrong in this samples.

    Best Regards Bajtitou.





    • Edited by Bajtitou Friday, April 20, 2018 1:59 PM
    Friday, April 20, 2018 9:25 AM
  • Create an SQL insert statement with parameters and use the SQLClient executenonquery command.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx

    With this information it should be a very simple job to make the code which every beginner can do. 


    Success
    Cor

    Hi,

    Thank you for your answer ,

    these pages  from (/msdn.microsoft.com) are Very interesting.

    Best Regards Bajtitou.

    Friday, April 20, 2018 9:30 AM
  • My Question is how to insert selected values into the New Table 'DbStages' using a visual basic application?

    So , Thank you very Much,

    Best Regards ,Bajtitou.

    Hi Bajtitou,

    You said that you want to insert selected value into new Table "DbStages" using vb.net, can you tell me where do you select value, and how do you select value? Here is about insert value into sql database, you can take a look firstly.

    https://stackoverflow.com/questions/12634516/how-can-i-insert-data-into-sql-server-using-vbnet

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi, thank you for your answer ,

    my question is about how to insert the selected values with statement:

    Select (DbStage1.CID) As CID ,(DbStage1.Villes)As Villes ,(DbStage1.Candidats) AsCandidats ,ISNULL(DbStage1.Points,0) As Stage1 ,ISNULL(DbStage2.Points,0) As Stage2 ,ISNULL(DbStage3.Points,0) As Stage3 ,ISNULL(DbStage4.Points,0) As Stage4 from((DbStage1 FULL OUTER JOIN DbStage2 ON DbStage1.CID=DbStage2.CID) FULL OUTER JOIN DbStage3

    ON DbStage2.CID=DbStage3.CID)FULL OUTER JOIN DbStage4 ON dbStage3.CID=DbStage4. CID

    Order by DbStage1.CID

    Best regards, Bajtitou.
    Friday, April 20, 2018 9:45 AM
  • It seems my replies make no sense. 

    I don't know what you're doing. Assuming you made the new table with SQL manager explorer (whatever its name has in your version)

    Then the code can be this (quick and dirty done)

    Imports System.Data.SqlClient
    Public Class Form1 'whatever you use"'
        Private Sub FillNewTable()
            Dim JoinedTable As New DataTable
            Using ad As New SqlDataAdapter("YourJoinSQL", "yourconnectionstring")
                ad.Fill(JoinedTable)
            End Using
            Dim insertstring = "Insert into MyNewTable values (x,y,z) from (@x, @y, @z)"
            Using con As New SqlConnection("yourconnectionstring")
                con.Open()
                Using com As New SqlCommand(insertstring)
                    For Each row As DataRow In JoinedTable.Rows
                        com.Parameters.Clear()
                        For Each column In JoinedTable.Columns
                            com.Parameters.Clear()
                            com.Parameters.AddWithValue("@x", row.Item("x"))
                            com.Parameters.AddWithValue("@y", row.Item("y"))
                            com.Parameters.AddWithValue("@z", row.Item("z"))
                        Next
                        com.ExecuteNonQuery()
                    Next
                End Using
            End Using
        End Sub
    End Class


    Success
    Cor


    • Edited by Cor Ligthert Friday, April 20, 2018 9:56 AM
    • Marked as answer by Bajtitou Friday, April 20, 2018 9:40 PM
    Friday, April 20, 2018 9:55 AM
  • In regards to "So What is wrong in this samples.", seriously, how would I know without having the ability to run the code?

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 20, 2018 10:07 AM
    Moderator
  • It seems my replies make no sense. 

    I don't know what you're doing. Assuming you made the new table with SQL manager explorer (whatever its name has in your version)

    Hi,

    Thank you very much , 

    your Replies are very helpfull,

    this is what I'm looking for and here is the result:

    Private Sub FillNewTable1() Dim sql As String = "Select (DbStage1.CID) As CID ,(DbStage1.Villes)As Villes ,(DbStage1.Candidats) As Candidats ,ISNULL(DbStage1.Points,0) As Stage1 ,ISNULL(DbStage2.Points,0) As Stage2 ,ISNULL(DbStage3.Points,0) As Stage3 ,ISNULL(DbStage4.Points,0) As Stage4 from((DbStage1 FULL OUTER JOIN

    DbStage2 ON DbStage1.CID=DbStage2.CID) FULL OUTER JOIN DbStage3 On DbStage2.CID= DbStage3.CID)

    FULL OUTER JOIN DbStage4 On dbStage3.CID=DbStage4. CID Order by DbStage1.CID"

    Using con As New SqlConnection(cs2) Dim dt As New DataTable Using ad As New SqlDataAdapter(sql, con) ad.Fill(dt) Dim insertstring = "update DbStages set CID=@x,Villes=@y,Candidats=@z,Stage1=@a,Stage2=@b,Stage3=@c,Stage4=@d" Using com As New SqlCommand(insertstring, con) con.Open() For Each row As DataRow In dt.Rows com.Parameters.Clear() For Each column In dt.Columns com.Parameters.Clear() com.Parameters.AddWithValue("@x", row.Item("CID")) com.Parameters.AddWithValue("@y", row.Item("Villes")) com.Parameters.AddWithValue("@z", row.Item("Candidats")) com.Parameters.AddWithValue("@a", row.Item("Stage1")) com.Parameters.AddWithValue("@b", row.Item("stage2")) com.Parameters.AddWithValue("@c", row.Item("stage3")) com.Parameters.AddWithValue("@d", row.Item("Stage4")) Next com.ExecuteNonQuery() Next End Using End Using End Using MessageBox.Show("Successfully Update", "Table", MessageBoxButtons.OK, MessageBoxIcon.Information) 'con.Close() End Sub

    this samples work very well all cilumns where inserted,

    But there is a probleme whith update when i change the statement :

    Dim insertstring = "Insert into DbStages (CID,Villes,Candidats,Stage1,Stage2,Stage3,Stage4)

    Values (@x, @y, @z,@a,@b,@c,@d)"

    By this :

    Dim Updatstring = "Update DbStages set CID=@x,Villes=@y,Candidats=@z,Stage1=@a,Stage2=@b

    ,Stage3=@c,Stage4=@d"

    I get wrong result.

    I have to add some thing like  Where villes =@somewhere ....,,

    but I want to Update all columns in the same time.

    thank you very much,

    best regards ,Bajtitou.


    • Edited by Bajtitou Friday, April 20, 2018 10:20 PM
    Friday, April 20, 2018 9:52 PM
  • In regards to "So What is wrong in this samples.", seriously, how would I know without having the ability to run the code?


    Hi, 

    I want to say if the code to cach Table Exists was correct or no? 

    this part:

    Public Function TableExists(ByVal TableName As String) As Boolean
        Dim cmdText As String =
                ("select * from DCO.dbo where name=\’" + (TableName + "\’"))
        Dim Result As Boolean = False
        Using con As New SqlConnection(ConnectionString2) con.Open()
                 Using sqlConnection As SqlConnection = New SqlConnection(ConnectionString2)
                sqlConnection.Open()
                Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection)
                    Using reader As SqlDataReader = sqlCmd.ExecuteReader
                    End Using
                End Using
            End Using
            Return Result
        End Using
    End Function

    Thank you very Much,

    Best regards Bajtitou.

    Friday, April 20, 2018 10:01 PM
  • In regards to "So What is wrong in this samples.", seriously, how would I know without having the ability to run the code?


    Hi, 

    I want to say if the code to cach Table Exists was correct or no? 

    this part:

    Public Function TableExists(ByVal TableName As String) As Boolean
        Dim cmdText As String =
                ("select * from DCO.dbo where name=\’" + (TableName + "\’"))
        Dim Result As Boolean = False
        Using con As New SqlConnection(ConnectionString2) con.Open()
                 Using sqlConnection As SqlConnection = New SqlConnection(ConnectionString2)
                sqlConnection.Open()
                Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection)
                    Using reader As SqlDataReader = sqlCmd.ExecuteReader
                    End Using
                End Using
            End Using
            Return Result
        End Using
    End Function

    Thank you very Much,

    Best regards Bajtitou.

    I would not post the code if it didn't work as presented yet you have altered how the connect string should be setup by using \.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, April 21, 2018 1:11 AM
    Moderator
  • In regards to "So What is wrong in this samples.", seriously, how would I know without having the ability to run the code?


    Hi, 

    I want to say if the code to cach Table Exists was correct or no? 

    this part:

    Public Function TableExists(ByVal TableName As String) As Boolean
        Dim cmdText As String =
                ("select * from DCO.dbo where name=\’" + (TableName + "\’"))
        Dim Result As Boolean = False
        Using con As New SqlConnection(ConnectionString2) con.Open()
                 Using sqlConnection As SqlConnection = New SqlConnection(ConnectionString2)
                sqlConnection.Open()
                Using sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConnection)
                    Using reader As SqlDataReader = sqlCmd.ExecuteReader
                    End Using
                End Using
            End Using
            Return Result
        End Using
    End Function

    Thank you very Much,

    Best regards Bajtitou.

    I would not post the code if it didn't work as presented yet you have altered how the connect string should be setup by using \.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Hello,

    Thank you very much for your assistance and help.

    Best Regards, Bajtitou.

    Sunday, April 22, 2018 7:59 AM