Solved

get id and Insert second table

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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