Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

get id and Insert second table

Posted on 2011-09-02
5
Medium Priority
?
272 Views
Last Modified: 2012-05-12
I have a partial of my code below. It works fine on #1. I know it is not a store procedure. I need to leave it this way (business reason). However, I want to add #2


      1. Read Excel file and get values on each row and insert to mytable_1
      
          Dim rs As New ADODB.Recordset
          Dim rs2 As New ADODB.Recordset
          Dim cn As New ADODB.Connection

        rs.LockType = ADODB.LockTypeEnum.adLockOptimistic
        rs.CursorType = ADODB.CursorTypeEnum.adOpenKeyset

        cn.Open("myconnection)
        rs.Open("select * from table_1 where myclientref = 'XXXXXX'", cn)
         
        rs.Fields("fieldnum1").Value = fieldnum1
      rs.Fields("fieldnum2").Value = fieldnum2
      rs.Fields("fieldnum3").Value = fieldnum3
      etc...      

        rs.Update()
      
      2. I want to get the ID above and insert to a second table with the id and other field from the same Excel row


      sample data
        
      Insert to table1
      fieldnum1
      fieldnum2
      fieldnum3
      
      Insert to table2 with same id above
      fieldnum4  < this comes from Excel also
      fieldnum5  < same
0
Comment
Question by:VBdotnet2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 11

Accepted Solution

by:
SAMIR BHOGAYTA earned 2000 total points
ID: 36474606
Yes, it is possible you have to add data in first table through stored procedure and in that stored procedure you have to return @@Identity column and get into one

Dim RetVal As Integer = variable and in the second query you have to pass the this RetVal like id. You have also use the stored procedure for inserting second record into the other table.
0
 

Author Comment

by:VBdotnet2005
ID: 36474668
#1 above is not a store procedure. It just insert t table.
0
 

Author Comment

by:VBdotnet2005
ID: 36474676
with recordset above, is there a way to return @@Identity  ?
0
 

Author Comment

by:VBdotnet2005
ID: 36474757
after   rs.Update()

Can i use this to get IDENTIT?

Private Function GetIdentity(ByRef cnn As SqlConnection) As Integer
Dim oCmd As New SqlCommand("SELECT @@IDENTITY", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function
0
 
LVL 19

Expert Comment

by:Shahan Ayyub
ID: 36475793
Select @@Identity works when you have a table having a column with IDENTITY = TRUE and you have inserted a record then in that case you will receive that column's value of inserted record which have IDENTITY=TRUE

Please provide more detail regarding your table, if possible...
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

722 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