Solved

get id and Insert second table

Posted on 2011-09-02
5
265 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
  • 3
5 Comments
 
LVL 11

Accepted Solution

by:
SAMIR BHOGAYTA earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

15 Experts available now in Live!

Get 1:1 Help Now