Solved

Loop Through Reader, Select Data, Insert into table.

Posted on 2011-09-11
18
256 Views
Last Modified: 2012-05-12
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
Comment
Question by:Annette Wilson, MSIS
  • 9
  • 5
  • 2
  • +2
18 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36521038
       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
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36521479
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
 
LVL 15

Expert Comment

by:x77
ID: 36521491
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
 
LVL 15

Expert Comment

by:x77
ID: 36521534
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
 

Author Comment

by:Annette Wilson, MSIS
ID: 36522155
Thank you for the quick response. Ill work with the code and get right back to you.
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 36524088
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36526299
Can you show us your complete code?
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 36526551
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36532030
That code is to retrieve manager name right? I thought your question was about "Loop Through Reader, Select Data, Insert into table."!
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Annette Wilson, MSIS
ID: 36532553
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 36532657
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
 

Author Comment

by:Annette Wilson, MSIS
ID: 36533049
This looks beautiful! Thanks.  I'll try it and get back to you.
0
 

Author Comment

by:Annette Wilson, MSIS
ID: 36546312
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36570152
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
 

Author Comment

by:Annette Wilson, MSIS
ID: 36571645
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36575986
>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
 

Author Comment

by:Annette Wilson, MSIS
ID: 36576467
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
 

Author Closing Comment

by:Annette Wilson, MSIS
ID: 37845781
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now