[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Invalid column name

Posted on 2016-08-27
5
Medium Priority
?
165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 18

Accepted Solution

by:
vasto earned 1000 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 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 1000 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 22
ID: 41773077
>INSERT INTO Agency ([ImageName], [Image]) SELECT 'Glencoe', * ...

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

650 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