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:
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
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');