?
Solved

Insert rows into a database table

Posted on 2005-03-06
4
Medium Priority
?
140 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
[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
  • 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 9 hours left to enroll

752 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