SQLTeam.com | Weblogs | Forums

Importing Data to SQL Server from Excel File using VBA


#1

Hi Community,

I've been working on this for a couple days and I'm completely stuck. I'm trying to import data into my SQL Server Database from an Excel file using VBA. for the most part it works perfectly except for when there is a Foreign Key constraint issue.
I am receiving the following error: "Cannot insert the value Null into column BookingID, table ServiceRecords.dbo.Invoices; column does not allow nulls. INSERT fails.

In my 'INvoices' Table I have three foreign keys which are: UserID, SPUserID, and BookingID. I am not sure what I am doing wrong but it will not allow me to push the data from excel to my SQL table when there are foreign keys involved. Additionally I tried to just add a row using SSMS and received a similar issue as follows:

"Cannot insert the value NULL into column 'BookingID', table 'ServiceRecords.dbo.Invoices'; column does not allow nulls. Insert fails."

Provided below is my VBA code, my code for the table i created, and the code for trying to insert one row using SSMS, any help would be appreciated:

VBA:
Public Sub Push_Invoices()

Dim conn As New ADODB.Connection
Dim iRowNo As Integer
Dim sUserID, sSPUserID, sBookingID, sCompanyName, sInvoiceTotal, sCustomerFullName As String

With Sheets("Invoices")
        
    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=DESKTOP-SOBHASS\SQLEXPRESS;Initial Catalog=ServiceRecords;Integrated Security=SSPI;"
        
    'Skip the header row
    iRowNo = 2
        
    'Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = ""
        sCompanyName = .Cells(iRowNo, 2)
        sInvoiceTotal = .Cells(iRowNo, 3)
        sCustomerFullName = .Cells(iRowNo, 4)
        sUserID = .Cells(iRowNo, 5)
        sSPUserID = .Cells(iRowNo, 6)
        sBookingID = .Cells(iRowNo, 7)
            
        'Generate and execute sql statement to import the excel rows to SQL Server table
        conn.Execute "insert into dbo.Invoices (CompanyName, InvoiceTotal, CustomerFullName) values ('" & sCompanyName & "', '" & sInvoiceTotal & "', '" & sCustomerFullName & "')"
        iRowNo = iRowNo + 1
    Loop
        
    MsgBox "Invoices Exported Successfully."
        
    conn.Close
    Set conn = Nothing
         
End With

End Sub

SQL Code to create the Invoices Table:
Create Table Invoices
(
InvoiceID int Not Null Identity Primary Key,
CustomerFullName varchar(MAX) Not Null,
CompanyName varchar(MAX) Not Null,
InvoiceTotal varchar(MAX) Not Null,
UserID int Not Null References dbo.Customers(UserID),
SpUserID int Not Null References dbo.ServiceProviders(SpUserID),
BookingID int Not Null References dbo.Booking(BookingID)
);

SQL Code to INSERT a Row using SSMS:
Set identity_insert Invoices ON
INsert Into Invoices (InvoiceID, CustomerFullName, CompanyName, InvoiceTotal)

Values('45', 'abc', 'def', '756');


#2

Yes, that will throw error as you have a NOT NULL constraint defined on the BookingID column.If it is a Foreign Key why are you not inserting rows in the BookingID column?


#3

You have 7 columns in your table and all of which do not accept blank values, you are only inserting data into 4 columns which is why it is throwing you the error, you need to insert values for all columns.


#4

Thanks for the help- a couple points of clarification:

  • Correct me if i'm wrong and I must be, but I thought the purpose of a foreign key is that it should populate itself based primary key in the connecting table? Also when i try and insert rows into the foreign ID columns i get an error conflicting with the foreign key constraints

-When i try and insert values for all columns i get an error conflicting with foreign key constraints when i try and insert values into all the columns, and it was my understanding that those columns would auto-fill based on the primary key in the connecting table.


#5

No, it does not auto-fill. How will a Foreign key column know to which primary key column should it get mapped to.
It totally depends on your business.
I guess the error you are getting is due to inserting the value in the foreign key column which does not exist in the main table.