Angie532
asked on
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
'**************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
what the error message you got ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This should be an INSERT not an update...?!?
ASKER
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
MsgBox ("Select LOOKUP_CODE From LOOKUP_VALUES where ucase(LOOKUP_VALUES.LOOKUP
CMD.CommandType = adCmdText
Set TierRS = CMD.Execute
'TierCount = TierRS.Fields.Item(0).Valu
MsgBox (CMD.CommandType)
'*************************
'*****************From here on, I don't know what to do!!!?!?!*****************
'*************************
' 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.Tex
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?
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
MsgBox ("Select LOOKUP_CODE From LOOKUP_VALUES where ucase(LOOKUP_VALUES.LOOKUP
CMD.CommandType = adCmdText
Set TierRS = CMD.Execute
'TierCount = TierRS.Fields.Item(0).Valu
MsgBox (CMD.CommandType)
'*************************
'*****************From here on, I don't know what to do!!!?!?!*****************
'*************************
' 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.Tex
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?