inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067
how i can i view the image?
pls clarify my doubt
satish
Hi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default...b;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:

> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command

create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')

when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067

how i can i view the image?

pls clarify my doubt

satishUse WRITETEXT/READTEXT instead of INSERT/SELECT to store / retrieve
There is quite a detailed explanation in Books Online

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141732028.000941.183610@.e56g2000cwe.googlegr oups.com...
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
>
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
>
> pls clarify my doubt
>
> satish|||satish (satishkumar.gourabathina@.gmail.com) writes:
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?

You have not inserted the the image into the table. You have inserted the
disk location of the image into the table. Run

SELECT convert(varchar(80), d) FROM aa

to see.

There is no way to insert data into a column directly from a file. The
normal way of loading image is write a program that reads the file,
and the passes the binary stream through a parameterised INSERT statement
in a client API. You can also convert the contents to a hexstring and
build an INSERT statement from that.

Conversly, to display the image you also need an application. If you
have stored an image in a table, a SELECT on that table in Query Analyzer
or Mgmt Studio will only display a long hex string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C7072756468766920
3030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/defaul...kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"satish" wrote:

> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C7072756468766920
3030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>sql

insert/update trigger

Tbl1 inserts 1 record(with some fields populated) in tbl2. then I need get values from tbl3 to populate the rest of the fields in tbl2(update the record).
tbl1 = tblallBag_data
tbl2 = tblBag_data
tbl3 = tblShipping_sched

I created a trigger in tbl1 to insert a record into tbl2 and it works fine.

CREATE TRIGGER trgtblBag_Data ON dbo.tbltblallBag_data
FOR INSERT
AS

INSERT INTO tblBag_data (work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity)
SELECT work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity
FROM inserted

How can I update tbl2?
Should I create another trigger to update tbl2?
Should I join the two tbls(tbl2 & tbl3) to find
@.work_ord_num = work_ord_num , @.work_ord_line_num = work_ord_line_num

Thanks for your help!tbl2 and tbl3 should be joined with inserted.

Insert/Update statements or Stored Procs

When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.
thanksStored procs...but who's going to write them?|||Use ADO from VB for insert and update|||Originally posted by Brett Kaiser
Stored procs...but who's going to write them?

wouldn't i just code the Insert/Update statement within the stored proc, then pass the value's to the stored proc. That sounds like alot of parameters to be dealing with for larger tables.|||Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.|||Originally posted by Brett Kaiser
Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.

Thanks Brett, one more quick question. Within the stored proc, i need to check if the record already exists before inserting or updating. Can you paste a small code sample to give me an idea of how i would ideally do that.

thanks alot|||I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP|||USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D'
GO

CREATE PROC mySproc99
@.Action Char(1)
, @.Col1 int
, @.Col2 char(1) = Null
AS
-- File: {\\tsstrv03\ESolutions}:
-- Date: May 1st, 2002
-- Author: Brett Kaiser
-- Server:
-- Database: TaxReconDB
-- Login: sa
-- Description: myTable99 Maint sproc
--
--
-- The stream will do the following:
--
-- 1.
--
-- Tables Used: myTable99
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- ------- ---- ------ ------ ------ ------
-- Ledger_Detail 76779 17160 KB 17040 KB 64 KB 56 KB
-- ATS_SignOff_Entity 3316 512 KB 504 KB 16 KB -8 KB
-- tblAcct_LedgerBalance 11691 3848 KB 3792 KB 8 KB 48 KB
--
--Change Log
--
-- UserId Date Description
-- ---- ----- ---------------------------
-- x002548 05/23/2002 1. Initial release
--
--
--

Declare @.error_out int, @.Result_Count int, @.Error_Message varchar(255), @.Error_Type int, @.Error_Loc int, @.RC int

SET NOCOUNT ON

SELECT @.rc = 0

BEGIN TRAN

IF @.Action NOT IN ('S','I','U','D')
BEGIN
SELECT @.Error_Loc = 1
SELECT @.Error_Message = 'Incorrect Request. Must be S,I,U or D. Paramter was: "' + @.Action + '"'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END

IF @.Action = 'S'
BEGIN
SELECT Col1, Col2 FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 2
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 2
SELECT @.Error_Message = 'myTable99 Returned zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'D'
BEGIN
DELETE FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 3
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 3
SELECT @.Error_Message = 'An Attempted DELETE from myTable99 affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'I'
BEGIN
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 4
SELECT @.Error_Message = 'An Attempted INSERT to myTable99 did not insert anything'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'U'
BEGIN
UPDATE myTable99 SET Col2=@.Col2 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 5
SELECT @.Error_Message = 'An Attempted UPDATE of myTable99 Affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

COMMIT TRAN

mySproc99_Exit:

SET NOCOUNT OFF

RETURN @.rc

mySproc99_Error:

ROLLBACK TRAN

IF @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
IF @.Error_Type = 50002

BEGIN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END

SELECT @.rc = -1

RAISERROR @.Error_Type @.Error_Message

GOTO mySproc99_Exit
GO

DECLARE @.RC int

EXEC @.RC = mySproc99 'X',1,'A'

SELECT @.RC

EXEC @.RC = mySproc99 'S',4

SELECT @.RC

EXEC @.RC = mySproc99 'I',5,'E'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'U',5,'F'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'D',5

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'I',4,'F'

SELECT @.RC
GO

DROP PROC mySproc99
GO
DROP TABLE myTable99
GO|||Originally posted by Pat Phelan
I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP
Actually, any communication with the server should be done through stored procedure, including SELECT.|||Originally posted by rdjabarov
Actually, any communication with the server should be done through stored procedure, including SELECT. I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||here's the man of so many virtues|||Originally posted by ms_sql_dba
here's the man of so many virtues

You sure s/he's a man?

Pat, you lost me...

we're talking about an app right? Not ad-hoc/dba maint issues? right?|||Originally posted by ms_sql_dba
here's the man of so many virtues Are you accusing me of having virtues ? I may wear many hats, but that is due to having a huge head. It has nothing to do with virtues of any kind!

-PatP|||Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)

suave is the only word I can think of...

You must be a ladies man....

:D

Does anyone use anything like the template posted..or is it 1 sproc per operation?|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?) Heck, I thought that confusion was a consequence of working with databases!

Nah, I don't really have any of those titles, but they sounded cool next to my actual titles (International super-spy, Bon-Vivant, and Ultra-cool geek about town). I'll try to behave better from now on!

-PatP|||Originally posted by Brett Kaiser
suave is the only word I can think of... Nope, Suave is one of our DataWarehousing consultants. He lives somewhere in Jersey and flies out to come play when we need him.
Originally posted by Brett Kaiser
You must be a ladies man.... Just one lady, although I do flirt outrageously. I used to send people around the bend when I'd dial our last TAM and ask "So how are you, other than obviously devastatingly gorgeous?" To which she'd often reply "Gee, you've just GOT to call more often."
Originally posted by Brett Kaiser
Does anyone use anything like the template posted..or is it 1 sproc per operation? Nope, to me that smacks of bad design. At least in my book, cross-tabs should be done on the client side or in the data warehouse, not from an OLTP system.

-PatP|||That's all VERY funny...

but what do you mean cross tabs?

And I definetly have to use that line...

Well, at least on the wife anyway...|||Originally posted by Brett Kaiser
but what do you mean cross tabs?
Whoops! Brain fart on my part, wrong thread!

-PatP|||International super-spy? You took my title!!! I demand it back...or a cig!|||You're really having trouble with the cig thing, but it is worth the fight. There aren't enough "bright boys" around, and we can't afford to loose any!

Anywho, the title isn't exclusive. When they awarded me the title, none of the previous users lost their permission to use it!

-PatP|||>For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand".

Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools. You better get your facts right...pb8 > 9704 would "compile" and give SQL error codes whenever embedded sql is given in code and it adds to ease of use for developers...[no doubt agrees for SP approch being the better one].

moreover, PB supports all 4 levels of dynamic SQL superbly and I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...

my 2 cents

WS [wizardofnet-at-yahoo]

Originally posted by Pat Phelan
I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||Originally posted by mell
I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...


[as he types falling out of chair]
Really?
[/as he types falling out of chair]|||Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN|||Originally posted by MCrowley
Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN

Ya lost me on that one...you mean make 4 out of the one I posted?

It's all a matter of methodolgy...

Pick 1 and stick eith it...no thinking involved...same thing for naming comventions...

make it so you don't have to look anything up...

But I like the part about not knowing the names of columns or tables...

must make for some very interesting code...no?|||Originally posted by mell
Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools.[wizardofnet-at-yahoo] 'splain dis one again for me... In the scenario I described, you don't have SELECT permissions, so you can't see any tables. You can't open the DataWindow painter. You can't generate any dynamic SQL...

What exactly can you do again?

-PatP|||Hey Lucy.....what did you did you do with the permissions this time?|||Yep. As near as I understand, when a procedure runs for the first time with it's first parameters, a query plan is born. SQL Server tries to use that query plan for each successive run of the stored procedure. Writing an all in one procedure is good if the procedure is not run very often, but for a website where a stored procedure can be run many many times, you don't want to wait around for the query optimizer to try to figure out it needs to recompile all of a sudden. Clear as mud?|||woooooosh...

and huh?

wouldn't the plan just stay in cache?

Got to get that internals book...|||Here is a classic example. Get on a server that has been around and been backing up databases regularly. Then make this stored procedure:

create procedure testproc (@.start int, @.end int)
as

select *
from msdb..backupset
where backup_set_id > @.start
and backup_set_id < @.end
go

Then run this:
testproc 1, 2

Get the execution plan, then run this:
testproc 1, 40000

and check out that execution plan

It should use the index for both, even though a table scan would be better for the second query.

EDIT: Hmm. Having trouble with the reverse of the logic in this example. I can not get the stored proc to do anything but use the index. Anyway, complex queries can get hit pretty hard by this fact. Something to keep in mind.|||Originally posted by Sammy_S
When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.

thanks

It's definitely better to use sprocs. In this way you separate the different layers in your application (something, which you may have missed to consider during the development). Later if you need changes you will need just to change the sprocs, without any modifications in the VB code. Also consider that the sprocs syntax is being validated during creation and they're compiled. So in all cases it's better to use them instead of raw hard-coded statements.

Martin Markov

Insert/Update sql commands not saving to DB

Here is my problem :
I issue an insert statement to the db. While I am getting a return value of 1 (1 row was affected) the values never show up into the db when I open the DB in access. However, I can see the data when it does an SQL select inside the program. So for instance, I do an insert into ORDER values (1, 12, 5.99). (1 = item ID, 12 = quantity, 5.99 = price). I then do a select * from Order, and I get those values back. When I open the DB in access, in between doing the insert and the select, I dont see the values there either. It is like it is making a temporary copy of the DB in memory during the execution only. When I close the program and re-F5, the data is no longer there. Maybe we need some kind of commit transaction? What am I doing wrong? I am using VB.Net 2005/MS Access 2003. Here is the relevant code :
Private m_Connection As OleDbConnection
''' <summary>
''' Defines the path to the database.
''' </summary>
''' <remarks></remarks>
#If CONFIG = "Debug" Then
Public Const DB_PATH As String = "DBs\DB_Test.mdb"
#ElseIf CONFIG = "Release" Then
Public Const DB_PATH As String = "DBs\DB_Production.mdb"
#End If
Sub connect(ByVal p_path As String) Implements IPartyDBase.connect
Dim connect_string As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & p_path
m_Connection = New OleDbConnection(connect_string)
m_Connection.Open()
End Sub
Sub someSub(ByVal stock As StockClass)
Dim tempString
Dim command As OleDbCommand
command = m_Connection.CreateCommand
command.CommandType = CommandType.Text
tempString = "Insert into Stock VALUES (" & stock.ID & ", "
tempString = tempString & stock.Quantity & ", "
tempString = tempString & stock.Price & ")"
Dim tempInt as Integer
command.CommandText = tempString
tempInt = command.ExecuteNonQuery
If Not tempInt = 1 Then
Throw New Exception("Bad addStockToDB into Stock " & tempInt)
End If
End Sub
Sub anotherSub
p_dbase.connect(PartyDBaseAccess.DB_PATH)
p_dbase.someSub()
p_dbase.close()
End Sub
Edit : During execution, looking under bin/debug/DBs, there is a copy of the database that has all the transactions I did during execution... but the actual DB isnt being updated/copied over.
Ok, the problem was that the path was not implicit, and it was overwriting the DB in /bin/debug/DBs... so changing the DB attributes to never copy worked, and opening the file in /bin/debug/DBs instead of the place where it was copying from.

Insert with values and select

Hi

Thnks for the time

I need to insert some values and select a value from another table to insert

insertinto products values(10,'proname','desc',(select modelfrom Products))

How can this be done.

Try:

insert into products(c1, ..., cn)

select 10, 'proname', 'desc', model

from dbo.products

go

AMB

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

you can do it the other way around

Code Snippet

insertinto products

select 10,'proname','desc', Model

from Products

|||

Hi

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Put the result of the "select" statement into a variable and use the variable.

declare @.model varchar(25)

set @.modele = (select model from products where producti = @.productid)

insert into products values(10, 'proname', 'desc', @.model)

go

AMB

sql

Insert with Parameters (SQL Server)

Hello, this is my code:

SqlCommand cmd =new SqlCommand("INSERT INTO Users (Username,Password) " +"VALUES ('@.username','@.password' ",new SqlConnection(my_ConnectionString)); cmd.Parameters.Add("@.username", SqlDbType.NVarChar, 50).Value = txtUsername.Text;cmd.Parameters.Add("@.password", SqlDbType.NVarChar, 50).Value = txtPassword.Text cmd.Connection.Open();cmd.ExecuteNonQuery();cmd.Connection.Close();

But in the database, the row inserted is exactly this:

"@.username" "@.password"

I mean, the parameters are not inserted :S

Please, tell me the error in the code...
Thank you so much,

Carlos.Placing single quotes around the parameters makes them be treated as literal strings. Remove the single quotes and you should have better luck.|||Thank you, now it works ;)

Carlos.

insert values of table and its dependencies

Hello,

I have 3 table
Table 1 : list of "whatever" program
Table 2: list of tasks for each program
Table 3: list of user for each task

When I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.

Is there a way to do that in sql?
I do not really want to go through each single task, then copy it with the new program, then get the @.@.identity of the inserted task and then assign the same user to the newly inserted task.

ThanksI am really stock with that, any idea?

Thanks very much|||

I am assuming something like this for a db structure...

TABLE 1:

ProjectID int identity(1,1)
ProjectName varchar(50)

TABLE 2:

TaskID int identity(1,1)
ProjectID int,
TaskName varchar(50)

TABLE 3:

UserTaskID int identity(1,1)
TaskID int
UserName varchar(50)

If so, you could do something like the following:

DECLARE

@.NewProjectint
DECLARE @.ProjectToCopyint
SET @.ProjectToCopy= 1

INSERT

INTO TABLE1
(
ProjectName
)
VALUES
(
'New Project Name'
)

SELECT

@.NewProject=SCOPE_IDENTITY()

INSERT

INTO TABLE2
(
ProjectID,
TaskName
)
SELECT @.NewProject, TaskNameFROM TABLE2WHERE ProjectID= @.ProjectToCopy
INSERTINTO TABLE3
(
TaskID
,UserName
)
SELECT t1.TaskID, UserName
FROM TABLE2 t
INNERJOIN TABLE3 u
ON t.TaskID= u.TaskID
INNERJOIN TABLE2 t1
ON t.TaskName= t1.TaskName
AND t1.ProjectID= @.NewProject
WHERE t.ProjectID= @.ProjectToCopy

What do you think?

Steve

|||Thanks, I will give it a try

insert values into both table at the same time using sql server 2005

hi all,

In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.

table 1:

tid--> int,identity,primary key

tname-->varchar(200)

table 2:

sid-->int,identity,primary key

tid-->fk (this tid is set as foreign key for the tid in table1)

now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..

pls help me..

thanks

swapna

Go For Stored Procedure for Insert...

SP Flow should be-

1. Start SQL Transaction

2. Insert into Table1

3. Get the Inserted INDENTITY value.

4.Insert into table 2

5. Commit Transaction Or Rollback transaction depending on the Error .

|||

You can create a stored procedure. There use will insert the record in the table 1 first and fetch the latest generated id value in table 1 using scope_identity and store it in a variable. Then you will insert the corresponding record in table 2 using the variable value.

For help on how to call a stored procedure from code visit http://forums.asp.net/t/1165758.aspx.

Feel free to ask for more help on this issue.

|||

Or you can create a trigger to insert the row into the second table.

It if must always happen the same way, the trigger would be a safer bet.

check out create trigger in the documentation.

sql

Insert values into 2 tables with one INSERT

Is this the appropriate way to insert information into a database with 2 tables (related with empID fields)

INSERT INTO table1
empID, first, last, dept, district
INSERT INTO table 2
className, classType, classDate

I am using ColdFusion to send the information to the database.

Thanks!Originally posted by helios76
Is this the appropriate way to insert information into a database with 2 tables (related with empID fields)

INSERT INTO table1
empID, first, last, dept, district
INSERT INTO table 2
className, classType, classDate

I am using ColdFusion to send the information to the database.

Thanks!

uhhhh...no

post the ddl for your tables...syntax is

INSERT INTO myTable (col1, col2, ect)
SELECT col1, col2, ect
FROM SomeOtherTable|||OK, full story of what I am trying to do.
Create a form that will go through ColdFusion to the database. This is how CFMX uses the SQL commands:

<cfquery name="AddEmployee" datasource="CompanyInfo">
INSERT INTO Employee
(Emp_ID,FirstName,LastName, Dept_ID,Contract)
VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName# ', #Form.Dept_ID#,'#Form.Contract#')
</cfquery>

The #Form.Emp_ID# is how CFMX knows what was entered on the HTML form and then sends it to the database.

What my goal is, is to send data to 2 tables that are linked by Emp_ID in the same database.|||Originally posted by helios76
OK, full story of what I am trying to do.
Create a form that will go through ColdFusion to the database. This is how CFMX uses the SQL commands:

<cfquery name="AddEmployee" datasource="CompanyInfo">
INSERT INTO Employee
(Emp_ID,FirstName,LastName, Dept_ID,Contract)
VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName# ', #Form.Dept_ID#,'#Form.Contract#')
</cfquery>

The #Form.Emp_ID# is how CFMX knows what was entered on the HTML form and then sends it to the database.

What my goal is, is to send data to 2 tables that are linked by Emp_ID in the same database.

You could add another insert in batch like this:

<cfquery name="AddEmployee" datasource="CompanyInfo">
INSERT INTO Employee
(Emp_ID,FirstName,LastName, Dept_ID,Contract)
VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName# ', #Form.Dept_ID#,'#Form.Contract#')
INSERT INTO Employee2
(Emp_ID,FirstName,LastName, Dept_ID,Contract)
VALUES (#Form.Emp_ID#,'#Form.FirstName#','#Form.LastName# ', #Form.Dept_ID#,'#Form.Contract#')
</cfquery>

Insert values in Primary key field

Hi,

Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.

Thanks in advance for any reply.

I've found the answer myself if anyone interested!

SET IDENTITY_INSERT table_name ON \g

Insert values from file

Just in general terms (I will look up the specifics), how would I go
about doing this?
There is value.csv list containing values (000, 001, 002, 003, etc . .
.). There is a table named Attrib. Each value in value.csv needs an
Insert Into Attrib.
Basically I want to perform the following:
Insert into Attrib
values ('A', 'B', 'C', '000')
Insert into Attrib
values ('A', 'B', 'C', '001')
Insert into Attrib
values ('A', 'B', 'C', '002')
With something like 2000 values in value.csv, I don't know the smartest
way to go about this.
Thanks
-tomTom,
Using DTS (Data Transformation Services) would be the easiest way to do
this. DTS is covered in the SQL Books Online and additional information can
be found at:
http://www.sqldts.com/
HTH
Jerry
"tom" <tomfeldsten@.hotmail.com> wrote in message
news:1129133965.223074.121090@.z14g2000cwz.googlegroups.com...
> Just in general terms (I will look up the specifics), how would I go
> about doing this?
> There is value.csv list containing values (000, 001, 002, 003, etc . .
> .). There is a table named Attrib. Each value in value.csv needs an
> Insert Into Attrib.
> Basically I want to perform the following:
> Insert into Attrib
> values ('A', 'B', 'C', '000')
> Insert into Attrib
> values ('A', 'B', 'C', '001')
> Insert into Attrib
> values ('A', 'B', 'C', '002')
> With something like 2000 values in value.csv, I don't know the smartest
> way to go about this.
> Thanks
> -tom
>|||Look at:
http://www.databasejournal.com/feat...cle.php/3331881
There are samples for opening this in a query.
Then you should go with:
INSERT INTO SomeTable
SELECT <columnlist> from <Openrowsetquery>
HTH, Jens Suessmeyer.|||Thanks to the both of you. Good hints for the direction I need to go.
-tom|||And a third one: "sp_addlinkedserver" with a little help from "schema.ini".
Look up both in Books Online.
ML

Insert Values Error

Hey Guys:

I am trying to create a form, and then insert the values entered by a user into a sql database, I have enclosed the page code below. Everything works except the data is not being inserted into the database, and i keep getting the default message in my error message section. I took this right from the quick start tutorial and started working with it, and keep getting an error.

I believe the error is located in the INSERT statement

<%@. Page Language="vb" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<html
<script language="VB" runat="server"
 Dim MyConnection As SqlConnection
 Sub Page_Load(Sender As Object, E As EventArgs)
 MyConnection = New SqlConnection("server=localhost;database=planetauction;uid=planetauction;pwd=bean13")
 If Not (IsPostBack)
 BindGrid()
 Page.DataBind()
 End If
 End Sub
 Sub AddAuthor_Click(Sender As Object, E As EventArgs)
 Page.Validate()
 If Not Page.IsValid
 Return
 End If
 Dim DS As DataSet
 Dim MyCommand As SqlCommand
 If txtLastName.Value = ""
 Message.InnerHtml = "ERROR: Null values not allowed for Author ID, " & _
 "Name or Phone"
 Message.Style("color") = "red"
 BindGrid()
 Return
 End If
 Dim InsertCmd As String = "insert into users (txtLastName) values (@.lastname)"
 MyCommand = New SqlCommand(InsertCmd, MyConnection)
 MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
 MyCommand.Parameters("@.lastname").Value = txtLastName.Value
 MyCommand.Connection.Open()
 Try
 MyCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the " & _
 "same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
 "the fields are correctly filled out"
 End If
 Message.Style("color") = "red"
 End Try
 MyCommand.Connection.Close()
 BindGrid()
 End Sub
 Sub BindGrid()
 Dim MyCommand As SqlDataAdapter = new SqlDataAdapter( _
 "select * from users", MyConnection)
 Dim DS As DataSet = new DataSet()
 MyCommand.Fill(DS, "Users")
 MyDataGrid.DataSource=DS.Tables("Users").DefaultView
 MyDataGrid.DataBind()
 End Sub
</script
<body style="font: 10pt verdana"
 <form runat="server" ID="Form1"
 <h3><font face="Verdana">Inserting a Row of Data</font></h3
 <table width="95%">
 <tr>
 <td valign="top"
 <ASP:DataGrid id="MyDataGrid" runat="server"
 Width="700"
 BackColor="#ccccff"
 BorderColor="black"
 ShowFooter="false"
 CellPadding=3
 CellSpacing="0"
 Font-Name="Verdana"
 Font-Size="8pt"
 HeaderStyle-BackColor="#aaaadd"
 EnableViewState="false"
 /
 </td>
 <td valign="top"
 <table style="font: 8pt verdana">
 <tr>
 <td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New Author:</td>
 </tr>
 <tr>
 <td nowrap>Last Name: </td>
 <td>
 <input type="text" id="txtLastName" runat="server" NAME="txtLastName"><br
 </td>
 </tr>
 <tr>
 <td></td>
 <td style="padding-top:15">
 <input type="submit" OnServerClick="AddAuthor_Click" value="Add Author" runat="server" ID="Submit1" NAME="Submit1">
 </td>
 </tr>
 <tr>
 <td colspan="2" style="padding-top:15" align="center">
 <span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
 </td>
 </tr>
 </table
 </td>
 </tr>
 </table
 </form
</body>
</html>

Put a more specific error message in while debugging:

Try
 MyCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the " & _
 "same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
 "the fields are correctly filled out. Exception: " & Exp.ToString()
 End If
 Message.Style("color") = "red"
 End Try

|||Mr. Reily:

Thanks for your advice. I added the detailed error message and here is what I got:

ERROR: Could not add record, please ensure the fields are correctly filled out. Exception: System.Data.SqlClient.SqlException: Invalid column name 'txtLastName'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.reference3_aspx.AddAuthor_Click(Object Sender, EventArgs E) in http://localhost/planetauction/reference3.aspx:line 47

I really think the error is in the insert statement. I need to put either double or single quotes around something in the insert statement, right now the database believes the value is referring to a column name, rather than inserting that data into the column specified.

Let me know what u think.

Thanks|||Thanks guys, got it all figured out.

It was the insert statment that was screwed up. For anyone else with the same problem, I simply mixed up the values, and the column names in the sql insert statements.

For Reference:

 Dim InsertCmd As String = "insert into users (COLUMNNAME1, COLUMNNAME2) values (@.FIRST SQL PARAMETER LISTED BELOW, SECOND SQL PARAMETER LISTED BELOW, etc)"
 MyCommand = New SqlCommand(InsertCmd, MyConnection)
 MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
 MyCommand.Parameters("@.lastname").Value = txtLastName.Value
 MyCommand.Connection.Open()

insert Values and selected Items

Can this be done?

I have a procedure where I have Values and Selected Table items that have to be inserted into a nother table that require them to be stored in the same record to the data correctly placed?

This is a simple example.If this is u r requirement its ok. Other than that plz reply to me.

insert into y select * from x

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

sql

insert value into 2 columns,1 col. should take data from another table & 2nd random

Hi,

I have table which has 2 columns
username
password

i want to insert username values from by copying data from another table whereas password shd be randomly generated

Thank You

Quote:

Originally Posted by parshupooja

Hi,

I have table which has 2 columns
username
password

i want to insert username values from by copying data from another table whereas password shd be randomly generated


Thank You

Are you looking at doing this directly in MySQL, or do you have a thin/fat client you would like to do this through? I don't believe there is a random text function in MySQL, but many programming languages have ways to accomplish it.|||i am working SQL Server 2005. yes need to do it directly

Quote:

Originally Posted by ilearneditonline

Are you looking at doing this directly in MySQL, or do you have a thin/fat client you would like to do this through? I don't believe there is a random text function in MySQL, but many programming languages have ways to accomplish it.

INSERT UPDATE Trigger Question

How would I write a trigger that updates the values of a Description
column to upper case for even IDs and to lower case for odd IDs? I
need this trigger to fire for INSERT and UPDATE events.Hi

Try something like:

CREATE TABLE Test ( id int not null identity (1,1), Description char(10) )

CREATE TRIGGER Test_Insert ON Test FOR INSERT AS
UPDATE TEST SET Description = CASE Id%2 WHEN 0 THEN UPPER(Description) ELSE
LOWER (Description) END

INSERT INTO TEST ( Description ) VALUES ('One')
INSERT INTO TEST ( Description ) VALUES ('Two')
INSERT INTO TEST ( Description ) VALUES ('Three')
INSERT INTO TEST ( Description ) VALUES ('four')
INSERT INTO TEST ( Description ) VALUES ('five')
INSERT INTO TEST ( Description ) VALUES ('SIX')
INSERT INTO TEST ( Description ) VALUES ('SEVEN')

SELECT * from Test

John

<imani_technology@.yahoo.com> wrote in message
news:f9208446.0309011615.5f269625@.posting.google.c om...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.|||imani_technology@.yahoo.com wrote in message news:<f9208446.0309011615.5f269625@.posting.google.com>...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.

You might want to consider formatting the text on the client, when you
retrieve it from the database - presentation tasks don't really belong
in a database. But if you want to do it using a trigger, something
like this should work (assuming your 'ID' is an integer key column):

create trigger ATR_UI_MyTable
on dbo.MyTable after insert, update
as
update dbo.MyTable
set DescriptionColumn =
case i.IDColumn % 2
when 1 then lower(i.DescriptionColumn)
when 0 then upper(i.DescriptionColumn)
end
from dbo.MyTable t
join inserted i
on t.IDColumn = i.IDColumn

Simon

Insert Triggers

I have written an Insert Trigger to examine newly inserted records and set some values. However, each time a record is inserted, all records are checked. How can I make the trigger work only on newly inserted records?Within the trigger, you can access a view called INSERTED that shows only the rows that are being inserted by the statement that launched the trigger. You can use the INSERTED view (probably via a JOIN) to limit the number of rows you are affecting in your underlying table.

-PatP|||my telepathic usb port is clogged...can you post the trigger...

probably take us a few minutes...

DDL would be nice as well

and pat's correct(what again? say it ain't so...)|||CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select tblGroup.Id as DataRecordId,
1 as TaskNum,
"Ready" as Status,
tblUsers.Id as UserId,
getdate() as StartDateTime
from tblGroup, tblUsers, tblVendors where (tblGroup.I_Field3=tblVendors.OdissVendorId)
And (tblGroup.I_Field6 Is Null OR tblGroup.I_Field6='0')
And (tblUsers.WFID=1)

..a little complex. the check for tblGroup.I_Field6 is necessitated because all records are being checked - this where clause could be stripped off if only new records were being checked.|||Something like this would do it:

CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
if exists (select 1 from inserted)
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select i.Id, 1, 'Ready', u.Id, getdate()
from inserted i
inner join tblVendors v
on i.I_Field3=v.OdissVendorId
inner join tblUsers u
on (u.WFID=1)|||thanx..will try this.

INSERT table (column1, column2) (@variable, '100')

Generally, you can't use a variable in this way, correct?
INSERT table (column1, column2) VALUES (@.variable, '100')
Do I need to be learning about stored procedures to make something like
this work? Do I need to restructure the insert statement?
Thanks
-tomThat should work, otherwise use the SELCT syntax:
INSERT table (column1, column2)
SELECT @.variable, '100'
HTH, Jens Suessmeyer.|||Tom,
Yes you can use variables in a Table Insert.
create table dbo.Test
(Col1 varchar(10),
Col2 varchar(10)
)
Go
declare @.Test varchar(10)
set @.Test = 'Test'
Insert into dbo.Test (col1, col2)
Values (@.Test, 'Test2')
Go
select * From dbo.Test
Go
Drop dbo.Test
Go
HTH
Barry|||Sorry I meant ...
Drop Table dbo.Test
Go
!
Barry|||tom wrote:
> Generally, you can't use a variable in this way, correct?
> INSERT table (column1, column2) VALUES (@.variable, '100')
I don't see anything wrong with this, given that @.variable is declared and
defined.

> Do I need to be learning about stored procedures to make something
> like this work?
I don't understand the question. It should work as is. Run this script to
see:
create table #temp (
column1 int,
column2 varchar(3))
declare @.variable int
set @.variable=200
INSERT INTO #temp (column1, column2)
VALUES (@.variable, '100')
SELECT * FROM #temp
drop table #temp

> Do I need to restructure the insert statement?
>
No, what is making you think you have to?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Tom,
The INSERT would work but a proc would be better.
Examples to work with:
CREATE TABLE TESTTABLE101
(COLUMN1 VARCHAR(10) NOT NULL,
COLUMN2 VARCHAR(10) NOT NULL)
GO
DECLARE @.VARIABLE VARCHAR(10)
SET @.VARIABLE = 'WHATEVER'
INSERT TESTTABLE101
VALUES(@.VARIABLE, '100')
GO
SELECT * FROM TESTTABLE101
GO
CREATE PROC USP_TESTPROC
@.VARIABLE VARCHAR(10)
AS
INSERT TESTTABLE101
VALUES(@.VARIABLE, '100')
GO
EXEC USP_TESTPROC 'WHATEVER2'
GO
SELECT * FROM TESTTABLE101
GO
--DROP PROC USP_TESTPROC
--DROP TABLE TESTTABLE101
HTH
Jerry
"tom" <tomfeldsten@.hotmail.com> wrote in message
news:1129834047.688572.283250@.o13g2000cwo.googlegroups.com...
> Generally, you can't use a variable in this way, correct?
> INSERT table (column1, column2) VALUES (@.variable, '100')
> Do I need to be learning about stored procedures to make something like
> this work? Do I need to restructure the insert statement?
> Thanks
> -tom
>|||Sure, why not.
All Inserts/Updates/Deletions in databases where I work are done through
stored procedures.
There are exceptions but usually they are one-time jobs by the database DBA,
tested first on a database copy before they are committed to the production
database.
Even Selects go through SPs. Nobody has direct access to tables and queries
except the database DBA and programmers.
I bet quite a few people in the newsgroup here would agree on this method.
So, to answer your second question, YES... learn about stored procedures.
"tom" <tomfeldsten@.hotmail.com> wrote in message
news:1129834047.688572.283250@.o13g2000cwo.googlegroups.com...
> Generally, you can't use a variable in this way, correct?
> INSERT table (column1, column2) VALUES (@.variable, '100')
> Do I need to be learning about stored procedures to make something like
> this work? Do I need to restructure the insert statement?
> Thanks
> -tom
>|||> Do I need to restructure the insert statement?

>No, what is making you think you have to?
Is it possible that adding double quotes and single quotes changed the
outcome? I adding quotes thus: " ' {first double quotes and then
single quotes} and it worked. Specifically, this works: VALUES
('100', '" @.variable "'), though you can't tell the order in that text.
I'm using QA.
Thank you everyone for your incredibly quick responses.
-tom|||tom wrote:
>
> Is it possible that adding double quotes and single quotes changed the
> outcome? I adding quotes thus: " ' {first double quotes and then
> single quotes} and it worked. Specifically, this works: VALUES
> ('100', '" @.variable "'), though you can't tell the order in that
> text. I'm using QA.
>
Did you run my script? Why are you messing around with double quotes? Are
you trying to create a dynamic sql statement? If so, see
http://www.sommarskog.se/dynamic_sql.html
My suggestion would be to not use dynamic sql.
You are prolonging this process by failing to give us enough information.
Show us the code needed to cause the error you are getting. Oh yeah! And
tell us what error you are getting ...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> Show us the code needed to cause the error you are getting. Oh yeah! And
>tell us what error you are getting ...
Here it is . ..
set nocount on
declare fix_cursor cursor
for select ID from mock
declare @.thiskey char(10)
open fix_cursor
fetch next from fix_cursor into @.thiskey
while @.@.FETCH_STATUS = 0
begin
insert into tasks_full (taskid, taskactkey)
values ('" @.thiskey "', (select max(actkey) from actions_full where
actid = '"@.thiskey"'))
fetch next from fix_cursor into @.thiskey
end
close fix_cursor
deallocate fix_cursor
For this statement I get the following error:
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '" @.thiskey "'.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near ')'.
-tom