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

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 :)
0
Aleks
Asked:
Aleks
3 Solutions
 
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
 
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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