Solved

Invalid column name

Posted on 2016-08-27
5
46 Views
Last Modified: 2016-08-27
Using SQL Server 2014

I have created a test database for testing where I wish to store a images for various departments.  When running a query to insert the image I get the Invalid column name error.  

The error:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'AgencyID'.



Database Name: ImageTest
Table Name: Agency
Fields AgencyID
Field: ImageName
Field: Image


USE ImageTest

INSERT INTO Agency ([ImageName], [Image])
SELECT 'Glencoe', *
FROM OPENROWSET(BULK N'D:\TestImage\Glencoe.jpg', SINGLE_BLOB) image
where AgencyID = 1;

The way I want to insert the image is based on the AgencyID field.  So I can insert multiple images into the table based on the AgencyID Value.    That way I can pull them into a Crystal Report based on the AgencyID Field when the report is run.  

I have attached  the script to build the database and tables.
ImageTest.sql
0
Comment
Question by:MikeM670
5 Comments
 
LVL 18

Accepted Solution

by:
vasto earned 250 total points
ID: 41773021
remove
"where AgencyID = 1;"

the script contains just the commands which will create the database . What is the structure of table Agency ?
If you have AgencyID then the insert should look like this
INSERT INTO Agency ([AgencyID],[ImageName], [Image])
SELECT 1, 'Glencoe', image.*
FROM OPENROWSET(BULK N'D:\TestImage\Glencoe.jpg', SINGLE_BLOB) image

Open in new window

0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 250 total points
ID: 41773061
A where condition on an insert makes no sense, insert creates new records, you specify all data going into a new record, so there is no sense in checking whether some field has some value, you specify all the values.

WHERE would only make sense in an UPDATE statement, but then you would set all records with agencyid=1 to one and the smae picture. An inset just specifies new data, if the new data comes from a source table, you may apply a WHERE clause to that, but then the WHERE clause is part of the SELECT query on that source data, to limit which records you copy. WHERE always filters data, it doesn't set or specify data.

Bye, Olaf.
0
 
LVL 19
ID: 41773077
>INSERT INTO Agency ([ImageName], [Image]) SELECT 'Glencoe', * ...

* needs to be specified. * means All Fields
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41773082
* is not the problem, that is - as vasto put it more clearly - just the one blob field in this case image.*, since it has no name from the OPENRWOSET, it's a convenient wa to specify it.

The problem is simply, that the where clause is not about the image nor any other table, it's about a field of the table you insert into, and since you add new data to that target table there is no sense in applying a filter WHERE clause to that target tables column.

Bye, Olaf.
0
 

Author Closing Comment

by:MikeM670
ID: 41773180
It looks like I was not really clear in my questions initially but I was able to figure out how to do it with all your help.  

The example vasto gave that worked inserted a new row with the agencyid value of 1.  I already had a agencyid of 1 (in fact the table I had was agencies 1-4 in it with the rest of the table fields empty.  I wanted to add the image to the existing agencyid that was equal to 1.  

I redid the table Agency and added a primary key to the AgencyID Field so I would not have duplicate AgencyID's in the table and also added a ImageURL field to try the other method of image storage/retrieval.

The query I ended up using was this one.


USE ImageTest

INSERT INTO Agency ([AgencyID],[Name],[ImageName],[Image],[ImageURL])
SELECT 1, 'Glencoe', 'Glencoe', image.*,''
FROM OPENROWSET(BULK N'D:\TestImage\Glencoe.jpg', SINGLE_BLOB) image

I thank both of you for the help on solving this.

Mike
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now