• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Loop Through Reader, Select Data, Insert into table.

I'm working on a Console Application that is pulling data from other systems and databases.  I would like to
1. have the datareader loop through a table and select the distinct employee ID
2. as each employee ID is read, I will pass the ID as a parameter and read and retrieve data from another table.
3. populate the third table with the new data.  

I would appreciate any help you could provide to help me get this project done sooner.
Thanks in advance.

Any samples would be appreciated.
0
Annette Wilson, MSIS
Asked:
Annette Wilson, MSIS
  • 9
  • 5
  • 2
  • +2
1 Solution
 
Pratima PharandeCommented:
       Dim datacommand As New SqlCommand
        Dim reader As SqlDataReader

        datacommand = New SqlCommand
        datacommand.Connection = New SqlConnection("Coonection string here")
        datacommand.Connection.Open()
        datacommand.CommandText = "select ID, col1,col2 from table1"
        reader = datacommand.ExecuteReader()

        If reader.Read() Then
          Dim myconnection As SqlConnection
      Dim mycommand As SqlCommand
myconnection = New SqlConnection("Coonection string here")
      myconnection.Open()
         mycommand = New SqlCommand("insert into table2(ID) values (" & reader("id") & ")", myconnection)
                     mycommand.ExecuteNonQuery()
      myconnection.Close()
      

        End If

        reader.Close()
        datacommand.Connection.Close()

refer

http://www.developerbarn.com/net-development/606-sqldatareader-read-next-record.html
http://www.dreamincode.net/forums/topic/66695-insert-data-into-sql-server-in-vbnet-by-user-input/
http://msdn.microsoft.com/en-us/library/aa720188(v=vs.71).aspx
0
 
sonawanekiranCommented:
Dim objDR As SqlClient.SqlDataReader
Dim objCommand As SqlClient.SqlCommand
Dim ConnectionString As String = "YOUR CONNECTION STRING HERE"
Dim objConnection As SqlClient.SqlConnection
Dim ssql As String
       
objConnection = New SqlClient.SqlConnection(ConnectionString)
ssql = "SELECT * FROM someTable"
       
If objConnection.State <> ConnectionState.Open Then
 objConnection.Open()
End If
objCommand = New SqlClient.SqlCommand(ssql, objConnection)
objDR = objCommand.ExecuteReade(CommandBehavior.CloseConnection)
objCommand = Nothing
      
If objDR.HasRows Then
      While objDR.Read()
            ' do your code here
            ' the following gives access
            ' to the table's field:
            ' objDR.Item("someField")
        ' Insert statement goes here
      End While
End If
objDR.Close()
objDR = Nothing
0
 
x77Commented:
I think the easy method is a unique sentence that inserts data on Table2 Like:

   Insert into Table2(id) Select distinct id from Table1

If Table2 has rows then:

   Insert into Table2(id) ( Select id from Table1 minus Select Id from Table2)
   
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
x77Commented:
Sorry i do´nt note that there are two DataBases.
Note that you can use DabaBase link.

You says "datareader loop through a table and select the distinct employee ID"
But your sentence do´nt use distinct clause, then you can need a Dictionary or HashTable to get Distinct Id

  Dim D as new Dictionary (of object, boolean)  , v as object      
       ....
 on Reader Loop:
     if D.TryGetValue( Id , v) = false then D.add( Id, true)

After Reader Loop build a loop to enumerate dictionary and insert on Table 2.

     for each Id in D.Keys
        Insert into Table2(v) ...
     next          
     


0
 
Annette Wilson, MSISAuthor Commented:
Thank you for the quick response. Ill work with the code and get right back to you.
0
 
Annette Wilson, MSISAuthor Commented:
After working with my API for a while, I see that each call that I make, I will only bring back a single value each time.  So...

I was wondering if you could tell me how I can loop through table1 and supply an employeeID for each API call and populate table1 with the returned value until all rows in table one has been processed.

Portion of my API Code is below:

I want to use a method "GetEmployeeID" to keep feeding the API until all rows in table 1 is complete.

I also want to populate table1 with the EmpMgrName where employeeID = employeeID each time.

 
Dim strQry As String = "SELECT GetAPIEmpMgrNameCall"
        strQry += "FROM apTable WHERE IDNo = " & GetEmployeeID()
        Dim intErr As Integer = API.QueryGT(hQry, strQry)
        If (intErr <> 0) Then
            Console.WriteLine("Error Obtaining Employee Manager Name")
            Return
        End If

        EmpMgrName = API.StrValue(hQry, 0)

Open in new window

0
 
CodeCruiserCommented:
Can you show us your complete code?
0
 
Annette Wilson, MSISAuthor Commented:
Here's an encripted version of what I'm working with.

I will have about 5 API Calls.  Each call has to update a table... final step for a database email that I have set up.
Module EData
    
    Public Sub MgrName(ByVal IDNo As String)
        Dim MgrName As String

        Dim sAI As MyAPIStuff.MyAPIStuff = New MyAPIStuff.MyAPIStuff()
        sAI.Commence()

        Dim sAIODBCSource As String = System.Configuration.ConfigurationManager.AppSettings("Connect").ToString()
        Dim uNm As String = System.Configuration.ConfigurationManager.AppSettings("TheAccount").ToString()
        Dim pwrd As String = System.Configuration.ConfigurationManager.AppSettings("ThePassword").ToString()
        Dim intConn As Integer = sAI.AmOpenConnection(sAIODBCSource & Chr(0), uNm & Chr(0), _
                                                     pwrd & Chr(0))

        Dim errMsg As String = sAI.LtErrMg(intConn)
        If (errMsg.Length > 0) Then
            Console.WriteLine("API Connection = " & intConn & _
                     "  Error Message: " & sAI.LtErrMg(intConn))
        End If
        If (intConn = 0) Then Return

        sAI.UptLogSlot(intConn)
        Dim lLoginId As Long = sAI.Log(intConn)

        Dim hQry As Integer = sAI.QryCrte(intConn)
        If (hQry = 0) Then
            Console.WriteLine("hQry = 0")
            Return
        End If

        Dim strQry As String = "SELECT cf_Get_Employee_Manager_Name "
        strQry += "My API Query = " & GetEmployeeID()
        Dim intErr As Integer = sAI.QryGt(hQry, strQry)
        If (intErr <> 0) Then
            Console.WriteLine("Error Obtaining Employee Manager Name")
            Return
        End If

        MgrName = sAI.GtFdStrVal(hQry, 0)



        sAI.Release(hQry)
        intConn = sAI.CloseConn(intConn)
        sAI = Nothing
    End Sub


    Public Function GetEmployeeID()
        Dim EmployeeID As String = " "
        Try

            Dim conn As SqlConnection = New SqlConnection(AppConfig.MYConnectionString)
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT EmployeeID from Table"
            conn.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader()
            If (rd.Read() = True) Then
                GetEmployeeID = rd("EmployeeID")
            End If
            rd.Close()
            conn.Close()


        Catch ex As Exception
            Console.WriteLine("Error Retrieving Employee Number")
        End Try

        Return EmployeeID

    End Function

End Module

Open in new window

0
 
CodeCruiserCommented:
That code is to retrieve manager name right? I thought your question was about "Loop Through Reader, Select Data, Insert into table."!
0
 
Annette Wilson, MSISAuthor Commented:
The "Public Function GetEmployeeID" has to loop through the table and for each employee number, the employee manager is pulled using the API.  The manager has to be saved into the table at the same row that the employee number was pulled.

Hope that explains what I'm trying to achieve.  
Sorry for the mess.

I'm currently working through and trying to use the Entity Framework.  I think it can handle the looping through a list of employeeID and efficiently save that string to the database.
0
 
CodeCruiserCommented:
Try following code



Module EData
    
    Public Function MgrName(ByVal IDNo As Integer) As Integer
        Dim MgrName As String

        Dim sAI As MyAPIStuff.MyAPIStuff = New MyAPIStuff.MyAPIStuff()
        sAI.Commence()

        Dim sAIODBCSource As String = System.Configuration.ConfigurationManager.AppSettings("Connect").ToString()
        Dim uNm As String = System.Configuration.ConfigurationManager.AppSettings("TheAccount").ToString()
        Dim pwrd As String = System.Configuration.ConfigurationManager.AppSettings("ThePassword").ToString()
        Dim intConn As Integer = sAI.AmOpenConnection(sAIODBCSource & Chr(0), uNm & Chr(0), _
                                                     pwrd & Chr(0))

        Dim errMsg As String = sAI.LtErrMg(intConn)
        If (errMsg.Length > 0) Then
            Console.WriteLine("API Connection = " & intConn & _
                     "  Error Message: " & sAI.LtErrMg(intConn))
        End If
        If (intConn = 0) Then Return

        sAI.UptLogSlot(intConn)
        Dim lLoginId As Long = sAI.Log(intConn)

        Dim hQry As Integer = sAI.QryCrte(intConn)
        If (hQry = 0) Then
            Console.WriteLine("hQry = 0")
            Return
        End If

        Dim strQry As String = "SELECT cf_Get_Employee_Manager_Name "
        strQry += "My API Query = " & IDNo
        Dim intErr As Integer = sAI.QryGt(hQry, strQry)
        If (intErr <> 0) Then
            Console.WriteLine("Error Obtaining Employee Manager Name")
            Return
        End If

        MgrName = sAI.GtFdStrVal(hQry, 0)



        sAI.Release(hQry)
        intConn = sAI.CloseConn(intConn)
        sAI = Nothing
    End Sub


    Public Sub AddManagers()
        Dim Manager As String
        Dim EmpID AS Integer
        Try

            Dim conn As SqlConnection = New SqlConnection(AppConfig.MYConnectionString)
            Dim cmd As SqlCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT EmployeeID from Table"
            conn.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader()
            Dim con2 As New SqlConnection(AppConfig.MyConnectionString)
            con2.Open()
            Dim cmd2 As New SqlCommand
            cmd2.Connection = con2
            While rd.Read() 
                EmpID = rd("EmployeeID")
                Manager = MgrName(EmpID)
                cmd2.CommandText = "Update Table Set Manager = '" & Manager & "' Where EmployeeID=" & EmpID
                cmd2.ExecuteNonQuery()
            End While
            rd.Close()
            conn.Close()
            con2.Close()

        Catch ex As Exception
            Console.WriteLine("Error Retrieving Employee Number")
        End Try

    End Sub

End Module

Open in new window

0
 
Annette Wilson, MSISAuthor Commented:
This looks beautiful! Thanks.  I'll try it and get back to you.
0
 
Annette Wilson, MSISAuthor Commented:
Sorry for the delay in responding.  I was having trouble with the API dll.
After evaluating the code, I see that I was not clear in my explanation.  

The Public Function MgrName Has to first read the Manager ID from the table Normal SQL Connection.

This then supplies the correct parameter that will be passed through the API to get the Manager Name

Then the name is added to the same table at the same row.   Since the employee number is distinct, I could say something like (ByRef IDNo As String, ByVal FuncManID As String)?
0
 
CodeCruiserCommented:
You have a table of employees with a column containing the managerID and now you want to add another column with manager name but populate it automatically using managerID?
0
 
Annette Wilson, MSISAuthor Commented:
CodeCruiser,  The API function will allow me to pass in the employeeID I thought that I had to pass in managerID.

I am working on populating one table from multiple sources and systems.  I was able to work with Entity framework and loop through a list of EmployeeID  from that table.  Now I am trying to figure out how to save the EmployeeManagerName to the table where EmployeeID = the EmployeeID that I pass in.  Please see the following:

I created the following two methods in a repository class.
Public Function GetEmployeeID() As System.Linq.IQueryable(Of Table1)
        Dim dataContext = New MyEntities()
        Dim EmpManName As String = " "
        Dim EmployeeIdList = (From n In dataContext.Table1 Select n.EmployeeID).ToList
        For Each n In EmployeeIdList
            GetManID(n.ToString, EmpManName)
        Next n
        Return EmployeeIdList

    End Function

    Public Function GetManID(ByVal id, ByVal EmpManName)
        Dim dataContext = New MyEntities()
        Dim ManName = From n In dataContext.Table1
                    Where n.EmployeeID = id
                    Select n.FunctionalMgrName.FirstOrDefault

        Return ManName

    End Function


My program has API methods that retrieve the the manager name from another system as I pass in the employeeID.

How do I save the string to Table1 

Here's the end of the API method that I am working on:

 Return FuncMgrName = MyAPI.APIStrValue(hQry, 0)

        Dim repository As New CasualDataRepository
        Dim SavMan = repository.GetManID(EmployeeID, FuncMgrName)
        repository.SaveChanges()

Open in new window

0
 
CodeCruiserCommented:
>I am working on populating one table from multiple sources and systems.
Does it have to be done in code? Can be done in SQL. You can join multiple tables to get your desired resultset in SQL.
0
 
Annette Wilson, MSISAuthor Commented:
I have to work with this in code because of the secured system.
I almost got it.  

I am going to take out the loop from here and call this function in code... to do this, I have to change this from list and just return the employeeID because I am getting an invalid cast:

Public Function GetEmployeeID() As System.Linq.IQueryable(Of Table1)
        Dim dataContext = New MyEntities()
        Dim EmpManName As String = " "
        Dim EmployeeIdList = (From n In dataContext.Table1 Select n.EmployeeID).ToList
       
        Return EmployeeIdList

    End Function


Here's a part of what I have to process in code:
Dim repository As New MyDataRepository
        repository.GetEmployeeID()
        For Each n In repository.GetEmployeeID

            Dim strQry As String = "SELECT HRMangerName"
            strQry += "FROM amEmplDept WHERE IDNo = " & n.EmployeeID
            Dim intErr As Integer =MyAPI.QueryGet(hQry, strQry)
            If (intErr <> 0) Then
                Console.WriteLine("Error Obtaining Employee HR Manager Name")
                Return
            Else
                HRMgrName = MyAPI.FieldStrValue(hQry, 0)
                repository.GetTalentManID(n.EmployeeID, HRMgrName)
                repository.SaveChanges()

            End If
0
 
Annette Wilson, MSISAuthor Commented:
It took a while to get this solution to work correctly due to version and compatibility issues between the API and the version of .net framework it would work with.  I ended up using a version of this solution after I rewrote it using Visual Studio 2005.  

Thank you very much for your assistance.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 9
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now