Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Insert rows into a database table

Posted on 2005-03-06
4
Medium Priority
?
141 Views
Last Modified: 2010-05-02
Hi Everyone, can you tell me what I'm doing wrong?  This is the code I wrote to insert the rows that meet a criteria  (RECORDS WITHOUT ERRORS...) into the database, specifically into the COMPANY table...

'**************THIS PART OF THE CODE WORKS PERFECTLY, I ADDED IT JUST BECAUSE IT'S PART OF THE LOOP
'*****THE CODE THAT DOESN'T WORK IS AFTER THE "ELSE" WHEN I TRY TO ADD THE VALUES TO THE DATABASE***
Do While Index > 1
    'company file
    dCompany.Show
   
    If ((Len(oSheet.Cells(Index, 1).Value) = 0) And (Len(oSheet.Cells(Index, 2).Value) = 0) And (Len(oSheet.Cells(Index, 3).Value) = 0) And (Len(oSheet.Cells(Index, 4).Value) = 0) And (Len(oSheet.Cells(Index, 5).Value) = 0) And (Len(oSheet.Cells(Index, 6).Value) = 0) And (Len(oSheet.Cells(Index, 7).Value) = 0) And (Len(oSheet.Cells(Index, 8).Value) = 0) And (Len(oSheet.Cells(Index, 9).Value) = 0) And (Len(oSheet.Cells(Index, 10).Value) = 0) And (Len(oSheet.Cells(Index, 11).Value) = 0) And (Len(oSheet.Cells(Index, 12).Value) = 0) And (Len(oSheet.Cells(Index, 13).Value) = 0)) _
    And ((Len(oSheet.Cells(Index, 14).Value) = 0) And (Len(oSheet.Cells(Index, 15).Value) = 0) And (Len(oSheet.Cells(Index, 16).Value) = 0) And (Len(oSheet.Cells(Index, 17).Value) = 0) And (Len(oSheet.Cells(Index, 18).Value) = 0) And (Len(oSheet.Cells(Index, 19).Value) = 0) And (Len(oSheet.Cells(Index, 20).Value) = 0) And (Len(oSheet.Cells(Index, 21).Value) = 0) And (Len(oSheet.Cells(Index, 22).Value) = 0) And (Len(oSheet.Cells(Index, 23).Value) = 0) And (Len(oSheet.Cells(Index, 24).Value) = 0) And (Len(oSheet.Cells(Index, 25).Value) = 0)) Then
         Exit Do
    Else
if(records have errors) then
 'pseudocode: put the records with errors into an Excel Spreadsheet

'**************THE CODE ABOVE WORKS GREAT, THE CODE BELOW GIVES ME AN ERROR!***************
'**************HERE STARTS THE CODE THAT DOESN'T WORK***********************************
            'If Company = "0" Then
            CMD.CommandText = "Insert Into COMPANY (NAME, PUBLIC_IND, TICKER, INDUSTRY_ID, SECTOR_ID, SUB_SECTOR_ID, TIER, ACTIVE, PIPELINE, " & _
                                            "ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, PHONE1, PHONE2, FAX, WEBSITE, BANKER1_ID, " & _
 "BANKER2_ID, REVENUES, RESEARCH, CREATED_BY, CREATED_DTTM) " & _
                                          "Values " & _
                                            "('" & oSheet.Cells(Index, 1).Value & "', '" & oSheet.Cells(Index, 2).Value & "', '" & oSheet.Cells(Index, 3).Value & "', '" & oSheet.Cells(Index, 4).Value & "', '" & oSheet.Cells(Index, 5).Value & "', '" & oSheet.Cells(Index, 6).Value & "', '" & oSheet.Cells(Index, 7).Value & "', '" & oSheet.Cells(Index, 8).Value & "', '" & oSheet.Cells(Index, 9).Value & "', '" & oSheet.Cells(Index, 10).Value & "', '" & oSheet.Cells(Index, 11).Value & "','" & oSheet.Cells(Index, 12).Value & "', '" & oSheet.Cells(Index, 13).Value & "', '" & oSheet.Cells(Index, 14).Value & "','" & oSheet.Cells(Index, 15).Value & "', '" & oSheet.Cells(Index, 16).Value & "', '" & oSheet.Cells(Index, 17).Value & "','" & oSheet.Cells(Index, 18).Value & "', '" & oSheet.Cells(Index, 19).Value & "', '" & oSheet.Cells(Index, 20).Value & "','" & oSheet.Cells(Index, 21).Value & "', '" & oSheet.Cells(Index, 22).Value & "', '" & oSheet.Cells(Index, 23).Value & "'," '" _
                                             & oSheet.Cells(Index, 24).Value & "', '" & oSheet.Cells(Index, 25).Value & "')"
                   
' ELSE IF                  
Index = Index + 1
Loop                                          
Angie
0
Comment
Question by:Angie532
  • 2
4 Comments
 
LVL 5

Expert Comment

by:fadloun
ID: 13470975
what the error message you got ?
0
 
LVL 28

Accepted Solution

by:
vinnyd79 earned 2000 total points
ID: 13470982
How about something like this?


' add a refence to Microsoft ActiveX Data Objects Library:

Dim DataBase As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
DataBase = "C:\Pipeline.mdb"

' open Database Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & DataBase & "'"
cn.CursorLocation = adUseClient
cn.Open

' open recordset
Set rs = New ADODB.Recordset
rs.Open "Select * From Company", cn, adOpenKeyset, adLockOptimistic

'........

'**************THIS PART OF THE CODE WORKS PERFECTLY, I ADDED IT JUST BECAUSE IT'S PART OF THE LOOP
'*****THE CODE THAT DOESN'T WORK IS AFTER THE "ELSE" WHEN I TRY TO ADD THE VALUES TO THE DATABASE***
Do While Index > 1
   'company file
   dCompany.Show
   
   If ((Len(oSheet.Cells(Index, 1).Value) = 0) And (Len(oSheet.Cells(Index, 2).Value) = 0) And (Len(oSheet.Cells(Index, 3).Value) = 0) And (Len(oSheet.Cells(Index, 4).Value) = 0) And (Len(oSheet.Cells(Index, 5).Value) = 0) And (Len(oSheet.Cells(Index, 6).Value) = 0) And (Len(oSheet.Cells(Index, 7).Value) = 0) And (Len(oSheet.Cells(Index, 8).Value) = 0) And (Len(oSheet.Cells(Index, 9).Value) = 0) And (Len(oSheet.Cells(Index, 10).Value) = 0) And (Len(oSheet.Cells(Index, 11).Value) = 0) And (Len(oSheet.Cells(Index, 12).Value) = 0) And (Len(oSheet.Cells(Index, 13).Value) = 0)) _
   And ((Len(oSheet.Cells(Index, 14).Value) = 0) And (Len(oSheet.Cells(Index, 15).Value) = 0) And (Len(oSheet.Cells(Index, 16).Value) = 0) And (Len(oSheet.Cells(Index, 17).Value) = 0) And (Len(oSheet.Cells(Index, 18).Value) = 0) And (Len(oSheet.Cells(Index, 19).Value) = 0) And (Len(oSheet.Cells(Index, 20).Value) = 0) And (Len(oSheet.Cells(Index, 21).Value) = 0) And (Len(oSheet.Cells(Index, 22).Value) = 0) And (Len(oSheet.Cells(Index, 23).Value) = 0) And (Len(oSheet.Cells(Index, 24).Value) = 0) And (Len(oSheet.Cells(Index, 25).Value) = 0)) Then
        Exit Do
   Else
if(records have errors) then
'pseudocode: put the records with errors into an Excel Spreadsheet

'**************THE CODE ABOVE WORKS GREAT, THE CODE BELOW GIVES ME AN ERROR!***************
'**************HERE STARTS THE CODE THAT DOESN'T WORK***********************************
           'If Company = "0" Then
           
    ' add new record to Table
rs.AddNew
rs.Fields("NAME").Value = oSheet.Cells(Index, 1).Value
rs.Fields("PUBLIC_IND").Value = oSheet.Cells(Index, 2).Value
rs.Fields("TICKER").Value = oSheet.Cells(Index, 3).Value
' etc.....
rs.Update
         
' ELSE IF
Index = Index + 1
Loop


'..........

' close recordset
rs.Close
Set rs = Nothing

' close connection
cn.Close
Set cn = Nothing
End Sub
0
 

Author Comment

by:Angie532
ID: 13496137
This should be an INSERT not an update...?!?
0
 

Author Comment

by:Angie532
ID: 13498975
I found out that some of those values like Tier and Active are numeric because in the database, the name or string is not inserted only the code and using this code there's a LOOKUP values table where next to the code there's the name.  So I have to match the excel name to the name in the LOOK UP table and get the code and insert the code into the database...
I can get the code, I'm just finished that part of the code, but I dont' know how to insert it into the database... Can you help me?

Here's my code so far.  This is for the TIER field.
   
Dim Valid_Record
Dim Invalid_Fields
Valid_Record = True
Invalid_Fields = ""
   
       '*****************TIER LOOKUP******************************************************
       Dim TierCount
       Dim TierRS As recordset
       Set CMD.ActiveConnection = Conn
       Dim TierName As String
       'Make the Tier names (from excel and the DB) upper case so you can compare them! (Ucase)
       TierName = UCase(oSheet.Cells(k, 7).Value)
       'GET THE CODE FOR THE LOOKUP VALUES
       CMD.CommandText = "Select LOOKUP_CODE From LOOKUP_VALUES where ucase(LOOKUP_VALUES.LOOKUP_DESC) = " & "'" & TierName & "'" & " AND LOOKUP_VALUES.LOOKUP_NAME ='CMP_TIER';"
MsgBox ("Select LOOKUP_CODE From LOOKUP_VALUES where ucase(LOOKUP_VALUES.LOOKUP_DESC) = " & "'" & TierName & "'" & " AND LOOKUP_VALUES.LOOKUP_NAME ='CMP_TIER';")
       CMD.CommandType = adCmdText
       Set TierRS = CMD.Execute
       'TierCount = TierRS.Fields.Item(0).Value
       MsgBox (CMD.CommandType)
'********************************************************************************
'*****************From here on, I don't know what to do!!!?!?!*******************************
'**************************Pseudocode*********************************************
      ' If(CMD.CommandType does not equal a valid code) then
Valid_Record = False
else
'insert the code value into the database...
            CMD.CommandText = "Insert Into COMPANY (NAME, PUBLIC_IND, TICKER, INDUSTRY_ID, SECTOR_ID, SUB_SECTOR_ID, TIER, ACTIVE, PIPELINE, " & _
                                            "ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, PHONE1, PHONE2, FAX, WEBSITE, BANKER1_ID, " & _
 "BANKER2_ID, REVENUES, RESEARCH, CREATED_BY, CREATED_DTTM) " & _
                                          "Values " & _
(??!?!?!?!?!?!?!!??!?!)
end if
TierRS.Close
Set TierRS = Nothing


Here's how my cowerker did the Industry table:
'CMD.CommandText = "Insert Into INDUSTRY (INDUSTRY, CREATED_BY, CREATED_DTTM) Values ('" & Replace(txtAddIndustry.Text, "'", "''") & "','" & GetCurrentUserName() & "',NOW());"
But he used strings and Dates, how do U do numbers??!?  I dont' really understand how to do an insert...
Why use Replace?  And how do you know how many "" or '' and && to use?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month14 days, 18 hours left to enroll

578 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