Solved

Invalid column name

Posted on 2016-08-27
5
29 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
Comment Utility
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
Comment Utility
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 18
Comment Utility
>INSERT INTO Agency ([ImageName], [Image]) SELECT 'Glencoe', * ...

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

Expert Comment

by:Olaf Doschke
Comment Utility
* 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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 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

12 Experts available now in Live!

Get 1:1 Help Now