Insert rows into a database table

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
Angie532Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fadlounCommented:
what the error message you got ?
0
vinnyd79Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angie532Author Commented:
This should be an INSERT not an update...?!?
0
Angie532Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.