Asp Forum - SQL 2005 Output Clause To VB6 Variable - microsoft.public.sqlserver.programming

(Lovely Angel For You)

9/9/2007 5:30:00 AM

Hi,

I am using Visual basic 6 with SQL 2005. In one particular query, I
need to update 3 different tables, dependent on input.

So, the Primary ID for FirstTable, need to be refernced in other Two
Tables.

I searched a bit on the internet and found that SQL2005, has Output
Clause. However I am not sure how to bring the Inserted.ID to
VisualBasic 6 variable.

My Query is:

StrSQL = StrSQL & "INSERT INTO People (username, phone, city)
OutPut Inserted.ID AS CURID VALUES ("
StrSQL = StrSQL & "'" & LCase$(UserName.Text) & "', "
StrSQL = StrSQL & "'" & LCase$(phone.Text) & "', "
StrSQL = StrSQL & "'" & LCase$(city.Text) & "')"
CONN.Execute StrSQL
CONN.Close

Now how to get Inserted.ID to Visual Basic Variable. So the following
can be executed

VBVariable = Inserted.ID

If city.text = "MyCity" Then
StrSQL = StrSQL & "INSERT INTO Table2 (peopleid, zip, country)
VALUES ("
StrSQL = StrSQL & " & VBVariable & ", "
StrSQL = StrSQL & "'" & LCase$(zip.Text) & "', "
StrSQL = StrSQL & "'" & LCase$(country.Text) & "')"
CONN.Execute StrSQL
CONN.Close
End If

Any info on that will be highly appreciated.

Best Wishes
Lovely

________________________________________________________________________________

Posted In multiple groups as the issue is about two different
developement softwares.

4 Answers

Uri Dimant

9/9/2007 6:48:00 AM

Hi
I have always used an OUTPUT clause to INSERT data into a staging/temporary
tables. From there you can easily SELECT the ID.However I assume your ID is
an INDENTITY property , right? If it is you can wrap your insert into stored
proccedure ( do that ASAP as you are under SQL Injection risk) and use
SELECT SCOPE_IDENTITY() to return ther value

"Lovely Angel For You" <lovely_angel_for_you@yahoo.com> wrote in message
news:1189315824.378122.20450@k79g2000hse.googlegroups.com...
> Hi,
>
> I am using Visual basic 6 with SQL 2005. In one particular query, I
> need to update 3 different tables, dependent on input.
>
> So, the Primary ID for FirstTable, need to be refernced in other Two
> Tables.
>
> I searched a bit on the internet and found that SQL2005, has Output
> Clause. However I am not sure how to bring the Inserted.ID to
> VisualBasic 6 variable.
>
> My Query is:
>
> StrSQL = StrSQL & "INSERT INTO People (username, phone, city)
> OutPut Inserted.ID AS CURID VALUES ("
> StrSQL = StrSQL & "'" & LCase$(UserName.Text) & "', "
> StrSQL = StrSQL & "'" & LCase$(phone.Text) & "', "
> StrSQL = StrSQL & "'" & LCase$(city.Text) & "')"
> CONN.Execute StrSQL
> CONN.Close
>
> Now how to get Inserted.ID to Visual Basic Variable. So the following
> can be executed
>
> VBVariable = Inserted.ID
>
> If city.text = "MyCity" Then
> StrSQL = StrSQL & "INSERT INTO Table2 (peopleid, zip, country)
> VALUES ("
> StrSQL = StrSQL & " & VBVariable & ", "
> StrSQL = StrSQL & "'" & LCase$(zip.Text) & "', "
> StrSQL = StrSQL & "'" & LCase$(country.Text) & "')"
> CONN.Execute StrSQL
> CONN.Close
> End If
>
> Any info on that will be highly appreciated.
>
> Best Wishes
> Lovely
>
> ________________________________________________________________________________
>
> Posted In multiple groups as the issue is about two different
> developement softwares.
>

Erland Sommarskog

9/9/2007 8:54:00 AM

Lovely Angel For You (lovely_angel_for_you@yahoo.com) writes:
> I searched a bit on the internet and found that SQL2005, has Output
> Clause. However I am not sure how to bring the Inserted.ID to
> VisualBasic 6 variable.
>
> My Query is:
>
> StrSQL = StrSQL & "INSERT INTO People (username, phone, city)
> OutPut Inserted.ID AS CURID VALUES ("
> StrSQL = StrSQL & "'" & LCase$(UserName.Text) & "', "
> StrSQL = StrSQL & "'" & LCase$(phone.Text) & "', "
> StrSQL = StrSQL & "'" & LCase$(city.Text) & "')"
> CONN.Execute StrSQL
> CONN.Close
>
> Now how to get Inserted.ID to Visual Basic Variable. So the following
> can be executed

First of all, you need to learn to use parameterised statements. This
is essential for several reasons. What if there is a single quote in
any of the input fields? A naive will only get a syntax error. A
malicious user can use it to make the application run code you did
not intend it to. Parameterised statements also uses the plan cache
in SQL Server more efficiently.

I have a quick example on
http://www.sommarskog.se/dynamic_sql.html#SQL..., you will
need to scroll down a bit to see it.

As for using the OUTPUT clause, you could do:

INSERT People(username, phone, city)
OUTPUT Inserted.ID
VALUES (?, ?, ?)

In VB you run the query as it was a SELECT query. (Some books on client
programming appears to distinguish between action queries and read
queries, but that is hogwash. Your query is a typical example of one
that is both.) Thus:

SET rs = cmd.Execute StrSQL
VBVariable rs.Fields(0)
SET rs = Nothing

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...

Mark Yudkin

9/10/2007 6:07:00 PM

The OUTPUT clause in SQL2005 has absolutely nothing whatsoever to do with
retrieving data into an application. It is used to capture results into
another TABLE. To retrieve that information you can use a normal SELECT.

I suspect that what you actually want is to obtain the value of an IDENTITY
column, although you didn't say so. To do this, retrieve the result of
SELECT @@IDENTITY.

And finally, use ? parameter markers instead of creating code that is
demanding the launch of an SQL injection attack to cause havoc and
corruption.

"Lovely Angel For You" <lovely_angel_for_you@yahoo.com> wrote in message
news:1189315824.378122.20450@k79g2000hse.googlegroups.com...
> Hi,
>
> I am using Visual basic 6 with SQL 2005. In one particular query, I
> need to update 3 different tables, dependent on input.
>
> So, the Primary ID for FirstTable, need to be refernced in other Two
> Tables.
>
> I searched a bit on the internet and found that SQL2005, has Output
> Clause. However I am not sure how to bring the Inserted.ID to
> VisualBasic 6 variable.
>
> My Query is:
>
> StrSQL = StrSQL & "INSERT INTO People (username, phone, city)
> OutPut Inserted.ID AS CURID VALUES ("
> StrSQL = StrSQL & "'" & LCase$(UserName.Text) & "', "
> StrSQL = StrSQL & "'" & LCase$(phone.Text) & "', "
> StrSQL = StrSQL & "'" & LCase$(city.Text) & "')"
> CONN.Execute StrSQL
> CONN.Close
>
> Now how to get Inserted.ID to Visual Basic Variable. So the following
> can be executed
>
> VBVariable = Inserted.ID
>
> If city.text = "MyCity" Then
> StrSQL = StrSQL & "INSERT INTO Table2 (peopleid, zip, country)
> VALUES ("
> StrSQL = StrSQL & " & VBVariable & ", "
> StrSQL = StrSQL & "'" & LCase$(zip.Text) & "', "
> StrSQL = StrSQL & "'" & LCase$(country.Text) & "')"
> CONN.Execute StrSQL
> CONN.Close
> End If
>
> Any info on that will be highly appreciated.
>
> Best Wishes
> Lovely
>
> ________________________________________________________________________________
>
> Posted In multiple groups as the issue is about two different
> developement softwares.
>

Erland Sommarskog

9/10/2007 10:04:00 PM

Mark Yudkin (DoNotContactMe@boingboing.org) writes:
> The OUTPUT clause in SQL2005 has absolutely nothing whatsoever to do with
> retrieving data into an application. It is used to capture results into
> another TABLE. To retrieve that information you can use a normal SELECT.

Try this:

CREATE TABLE #temp (ident int IDENTITY,
alfa char(4) NOT NULL)
INSERT #temp (alfa)
OUTPUT inserted.ident
VALUES ('beta')
go
DROP TABLE #temp

> I suspect that what you actually want is to obtain the value of an
> IDENTITY column, although you didn't say so. To do this, retrieve the
> result of SELECT @@IDENTITY.

scope_identity() is better, in case the table you insert to have a trigger
which inserts into a second table with an identity column. @@identity
will give you the value for the second table.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...

microsoft.public.sqlserver.programming

SQL 2005 Output Clause To VB6 Variable

(Lovely Angel For You)

Uri Dimant

Erland Sommarskog

Mark Yudkin

Erland Sommarskog

x Login to ForumsZone