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