CODEWITHSUNDEEP
sqlt-sqlfilestream
wafw1971
wafw1971

Reputation: 361

Filestream uploading issues

I am trying to insert a picture into a database using Filestream, I have created the table and columns and have created a Insert statement, but I keep getting the following error message:

Msg 515, Level 16, State 2, Line 2 Cannot insert the value NULL into column 'id', table 'Racing.dbo.Course_Info'; column does not allow nulls. INSERT fails. The statement has been terminated.

Column 1 - id - Unique identifier Column 2 - RacecourseID - this needs to be 11 Column 3 - filestream_data

So can you tell me whats wrong with my insert statement?

INSERT INTO Course_Info (RacecourseID, filestream_data)
VALUES ('11', (SELECT * 
   FROM OPENROWSET(BULK N'C:\Users\Administrator\Desktop\Pictures for Database\Racecourses\Cheltenham-Racecourse.jpg', SINGLE_BLOB) as rs))

Thanks

Wayne

Upvotes: 0

Views: 147

Answers (2)

SWeko
SWeko

Reputation: 30912

The error says it all: The unique identifier of the row is not nullable, and you are not specifying it in the column list. Another problem is that you are not setting the RacecourseID.

To set those two values, modify your query like:

INSERT INTO Course_Info (id, RacecourseID, filestream_data)
SELECT newid(), 11, * FROM OPENROWSET(BULK N'filename.jpg', SINGLE_BLOB) as rs))

The newid() will generate a fresh unique identifier for each inserted row, and the constant 11 will be used for the RacecourseID field.

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

I suspect you are going to kick yourself here.

USE Racing
INSERT INTO Course_Info(RaceCourseID,filestream_data)
SELECT 11, * FROM OPENROWSET(BULK N'C:\Users\Administrator\Desktop\Pictures for Database\Racecourses\Cheltenham-Racecourse.jpg', SINGLE_BLOB) rs

would be one way. Got to read the error messages RaceCourseID, cannot be null, means you have to put one in.

Upvotes: 1

Related Questions