Solved

get id and Insert second table

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
System.net.Sockets Error 5 38
VB.NET Textbox input validation 4 62
Get month and date in a format 4 44
Copying from a network share 3 20
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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