SQL to insert data (Syntax)

I need to insert data to table:  HomepageIcons

The problem is with the first item ("FirmId") , which its value comes from the result of the following sql:

Select firmid from HomepageIcons where IconName = 'Cases'  --- this value should be inserted in the table row along with the rest of the values as listed below (note that 'image' value has quotes in there, is the syntax right ?  

---

Firmid = ?  (This is the value that comes from the result of the sql above)
IconName = 'Basic Training'
Linkto = 'frames.asp?leftframe=leftframe.asp&mainframe=BT/Basictraining.asp'
row = '4'
iconorder = '99'
superuseravailable = '1'
adminavailable = '1'
image = '<img src="../Images/iconusersguide.gif" border=0>'
Linktoleftframe = 'BT/Basictraining.asp'

What I need is the full sql to insert all this values  :)

Thx for the help :)
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jekautzCommented:
Your SQL SELECT statement looks correct, but it retrieves values from a table.  It does not put them into a table.

If you need to insert these values, then you need to use the INSERT statement, like this:

INSERT INTO HomepageIcons
VALUES (firmid, iconname, linkto, row, icondorder, superuseravailable, adminavailable, image, linktoleftframe);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pooja Katiyar VermaCommented:
Insert into HomepageIcons  
 Firmid,
 IconName,
 Linkto,
 row,
 iconorder,'99'
 superuseravailable,
 adminavailable,
 image,
 Linktoleftframe )
Select
Firmid ,
'Basic Training',
'frames.asp?leftframe=leftframe.asp&mainframe=BT/Basictraining.asp',
'4',
'99',
'1',
'1',
'<img src="../Images/iconusersguide.gif" border=0>',
'BT/Basictraining.asp'
from HomepageIcons where HomepageIcons ;
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
i would use a parameterized query and a stored procedure:

        set cmd = Server.CreateObject("ADODB.Command")
        sql = "exec sp_insertHomePageIcons ?, ?, ?, ?, ?, ?, ?, ?"
        with cmd
		    .ActiveConnection = conn      '-- make sure you explicitly open your connection somewhere beforehand
		    .CommandText = sql
		    .Parameters.Append  .CreateParameter( "iconName", adVarchar, adParamInput, 50, iconName )
		    .Parameters.Append  .CreateParameter( "linkTo", adVarchar, adParamInput, 50, linkTo )
		    .Parameters.Append  .CreateParameter( "row", adInteger, adParamInput, , row )
		    .Parameters.Append  .CreateParameter( "iconOrder", adVarchar, adParamInput, 50, iconOrder )
		    .Parameters.Append  .CreateParameter( "superUser", adInteger, adParamInput, , superUserAvailable )
		    .Parameters.Append  .CreateParameter( "admin", adInteger, adParamInput, , adminAvailable )
		    .Parameters.Append  .CreateParameter( "image", adVarchar, adParamInput, 50, image )
		    .Parameters.Append  .CreateParameter( "linkToLeftFrame", adVarchar, adParamInput, 50, linkToLeftFrame )
			.Execute
	    end with

Open in new window


and your stored procedure would look like the following:

CREATE   PROCEDURE [dbo].[sp_insertHomePageIcons] 
	@iconName varchar( 50 ),
	@linkName varchar( 50 ), 
	@row int,
	@iconOrder varchar( 50 ),
	@superUser int,
	@admin int,
	@image varchar( 50 ),
	@linkToLeftFrame varchar( 50 ),
as
begin 
	set nocount on;
	
	declare @firmID int;
	select @firmID = firmid from HomepageIcons where IconName = 'Cases';
	
	Insert into HomepageIcons ( Firmid, IconName, Linkto, row, iconorder, superuseravailable, adminavailable, image, Linktoleftframe )
	values( @iconName, @linkName, @row, @iconOrder, @superUser, @admin, @image, @linkToLeftFrame );
end

Open in new window


you should perform checks on each value passed to the stored procedure, make sure the variables are have valid data, otherwise you'll get an error. you'll also need to download and include the file adovbs.inc, which you can get here:

http://www.4guysfromrolla.com/webtech/code/adovbs.txt

finally, make sure your column types match up to the stored procedure, otherwise you'll again get errors. you'll need to specify the the correct data types and sizes in order for it work. I STRONGLY recommend having a look at this EE article that explains parameterized queries:

http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/A_3626-ASP-Classic-Using-Parameterized-Queries.html
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AleksAuthor Commented:
Thanks .. I will check it out tomorrow  morning !
0
AleksAuthor Commented:
I am trying the following:

---

INSERT  INTO HomepageIcons
        ( Firmid ,
          IconName ,
          Linkto ,
          row ,
          iconorder ,
          superuseravailable ,
          adminavailable ,
          image ,
          Linktoleftframe
        )
VALUES  ( '1' ,
          'Basic Training' ,
          'frames.asp?leftframe=leftframe.asp&mainframe=BT/Basictraining.asp' ,
          '4' ,
          '99' ,
          '1' ,
          '1' ,
          '<img src='../Images/iconusersguide.gif' border=0>' ,
          'BT/Basictraining.asp'
        )

---

I get an error on the code that has the '  it breaks the code, how can I insert a :  '  ?

line:             '<img src='../Images/iconusersguide.gif' border=0>' ,
0
AleksAuthor Commented:
Tired this but didn't work:

 '<img src='''../Images/iconusersguide.gif''' border=0>' ,

Tried this didn't work:

 '''<img src='../Images/iconusersguide.gif' border=0>''' ,
0
AleksAuthor Commented:
:)
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you use the code examples i gave, you do not need to worry about single quotes or escaping any other charactors
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.