Solved

get id and Insert second table

Posted on 2011-09-02
5
264 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 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

9 Experts available now in Live!

Get 1:1 Help Now