|
|
You're in the
sql server programming
group:SQL 2005 Output Clause To VB6 Variable
sql server programming:
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.
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 [quoted text, click to view] "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. >
Lovely Angel For You (lovely_angel_for_you@yahoo.com) writes:
[quoted text, click to view] > 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_injection, 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.mspx Books Online for SQL Server 2000 at
Erland , If SQL injection is not an issue because this is not web
application and because i examine parameters in client carefully before concatenating them in a string to construct the dynamic SQL would that still be bad for the query plan? because i simply find Exec ContatenatedStringSQL much practical than sp_executesql , its not just the parameter difference that user might or might not select at client its also filter range between them, example table Orders and OrderDetails TransactionDate (= , between , > , < , >= , <=) this applies to any date column StoreID (= , <>) ItemStyle (= , Like , Not Like ) , applies to any varchar column Qty (= , < , > , <= , >=) , applies to any numerical col and so on, user might choose one or more from those parameters to filter report output , AND might choose different filter ranges like described up so you have many many possibilities, if all those possibilities are put in the SP with executesql , it is simply too complex and time consuming but constructing the SQL in client is far easier , i read your article about dynamic SQL but still can't figure out how it can be easier to maintain and construct ?! assuming SQL injection is not an issue as i said. Thanks Bassam -- Thank you [quoted text, click to view] "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. >
Bassam (bassam@nileprojects.com) writes:
[quoted text, click to view] > Erland , If SQL injection is not an issue because this is not web > application SQL Injection can be an issue in Windows applications as well, not the least if the application uses application roles or something else that elevates privileges beyond the user's own. [quoted text, click to view] > and because i examine parameters in client carefully Which is a mess you don't have to deal with if you use parameterised statements. [quoted text, click to view] > before concatenating them in a string to construct the dynamic SQL would > that still be bad for the query plan? The problem with inlined parameters is that each query will get its own entry in the plan cache, so each query will be compiled, and the cache will be filled with things that are never reused. [quoted text, click to view] > because i simply find Exec ContatenatedStringSQL much practical than > sp_executesql , its not just the parameter difference that user might or > might not select at client its also filter range between them, > example But that does not preclude the use of parameterised statements. You can still build the string dynamically, and this is often necessary when the user needs to be able to search on different conditions. [quoted text, click to view] > i read your article about dynamic SQL but still can't figure out how it > can be easier to maintain and construct ?! assuming SQL injection is not > an issue as i said. You may also want to read my article on dynamic search conditions, http://www.sommarskog.se/dyn-search.html. You can compare the code of search_orders_1 and search_orders_2 and see which one you find cleaner. Yes, that is stored procedure, but the reasoning applies to SQL generated in client code as well. -- 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.mspx Books Online for SQL Server 2000 at
Thank you for your answers Erland, i will carfully read the article and
return to discussion if any Bassam [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns99A6BCC0777DDYazorman@127.0.0.1... > Bassam (bassam@nileprojects.com) writes: >> Erland , If SQL injection is not an issue because this is not web >> application > > SQL Injection can be an issue in Windows applications as well, not the > least if the application uses application roles or something else that > elevates privileges beyond the user's own. > >> and because i examine parameters in client carefully > > Which is a mess you don't have to deal with if you use parameterised > statements. > >> before concatenating them in a string to construct the dynamic SQL would >> that still be bad for the query plan? > > The problem with inlined parameters is that each query will get its own > entry in the plan cache, so each query will be compiled, and the cache > will be filled with things that are never reused. > >> because i simply find Exec ContatenatedStringSQL much practical than >> sp_executesql , its not just the parameter difference that user might or >> might not select at client its also filter range between them, >> example > > But that does not preclude the use of parameterised statements. You can > still build the string dynamically, and this is often necessary when > the user needs to be able to search on different conditions. > >> i read your article about dynamic SQL but still can't figure out how it >> can be easier to maintain and construct ?! assuming SQL injection is not >> an issue as i said. > > You may also want to read my article on dynamic search conditions, > http://www.sommarskog.se/dyn-search.html. You can compare the code > of search_orders_1 and search_orders_2 and see which one you find > cleaner. Yes, that is stored procedure, but the reasoning applies to > SQL generated in client code as well. > > > -- > 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.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
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. [quoted text, click to view] "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. >
Mark Yudkin (DoNotContactMe@boingboing.org) writes:
[quoted text, click to view] > 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 [quoted text, click to view] > 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.mspx Books Online for SQL Server 2000 at
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |