• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Invalid column name

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
MikeM670
Asked:
MikeM670
2 Solutions
 
vastoCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
>INSERT INTO Agency ([ImageName], [Image]) SELECT 'Glencoe', * ...

* needs to be specified. * means All Fields
0
 
Olaf DoschkeSoftware DeveloperCommented:
* 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
 
MikeM670Author Commented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now