|
I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table.
When calling the stored procedure from VB.NET, in the CommandText, can I
just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with
"INSERT_INTO_MYTABLE" then do the following :
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)
OleDbCommand2.Parameters("@Account").Value = "12345"
? Because if I have many parameters,it is a lot of work to set each
parameter like the above.
I tried it, but it gave me an error.
Thanks.
Dim OleDbCommand2 As New Data.SqlClient.SqlCommand
Dim OleDbConnection1 As New Data.SqlClient.SqlConnection
Dim m_daSQL As SqlClient.SqlDataAdapter
Dim m_dsSQL As DataSet
With OleDbConnection1
.ConnectionString = "...."
.Open()
End With
OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------CAN I set
it to INSERT_INTO_MYTABLE '12345' ?
OleDbCommand2.CommandType = CommandType.StoredProcedure
OleDbCommand2.Connection = OleDbConnection1
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)
OleDbCommand2.Parameters("@Account").Value = "12345"
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = OleDbCommand2
m_daSQL.Fill(m_dsSQL) | |
Share:
|
You can try it, but I don't think it will work. You need to
add the parameters one by one.
You may, however, be able to use AddWithValue, which reduces it
to one line. (It works w/SQLServer; I haven't tried it with the
OLEDBAdapter though.)
Robin S.
--------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message
news:el**************@TK2MSFTNGP06.phx.gbl...
>I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from
that table.
When calling the stored procedure from VB.NET, in the CommandText, can
I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with
"INSERT_INTO_MYTABLE" then do the following :
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)
OleDbCommand2.Parameters("@Account").Value = "12345"
? Because if I have many parameters,it is a lot of work to set each
parameter like the above.
I tried it, but it gave me an error.
Thanks.
Dim OleDbCommand2 As New Data.SqlClient.SqlCommand
Dim OleDbConnection1 As New Data.SqlClient.SqlConnection
Dim m_daSQL As SqlClient.SqlDataAdapter
Dim m_dsSQL As DataSet
With OleDbConnection1
.ConnectionString = "...."
.Open()
End With
OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------CAN I
set it to INSERT_INTO_MYTABLE '12345' ?
OleDbCommand2.CommandType = CommandType.StoredProcedure
OleDbCommand2.Connection = OleDbConnection1
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)
OleDbCommand2.Parameters("@Account").Value = "12345"
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = OleDbCommand2
m_daSQL.Fill(m_dsSQL)
| | |
Robin is right. Why are you using the OleDb namespace instead of the SQL
Server-specific SqlClient?
I assume you set the CommandType to StoredProcedure. You'll also need to
setup a Parameter to accept the new Identity value--unless you're returning
it as a row (which you shouldn't). The AddWithValue is handy but you can
also code
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar,
10).Value="12345"
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker www.betav.com/blog/billva www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message
news:el**************@TK2MSFTNGP06.phx.gbl...
>I am using VB.NET 2003 and SQL2000 database.
I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1
parameter (varchar(10)) and returns the identity column value from that
table.
When calling the stored procedure from VB.NET, in the CommandText, can I
just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with
"INSERT_INTO_MYTABLE" then do the following :
"
? Because if I have many parameters,it is a lot of work to set each
parameter like the above.
I tried it, but it gave me an error.
Thanks.
Dim OleDbCommand2 As New Data.SqlClient.SqlCommand
Dim OleDbConnection1 As New Data.SqlClient.SqlConnection
Dim m_daSQL As SqlClient.SqlDataAdapter
Dim m_dsSQL As DataSet
With OleDbConnection1
.ConnectionString = "...."
.Open()
End With
OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------CAN I set
it to INSERT_INTO_MYTABLE '12345' ?
OleDbCommand2.CommandType = CommandType.StoredProcedure
OleDbCommand2.Connection = OleDbConnection1
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10)
OleDbCommand2.Parameters("@Account").Value = "12345"
m_daSQL = New SqlClient.SqlDataAdapter
m_dsSQL = New DataSet
m_daSQL.SelectCommand = OleDbCommand2
m_daSQL.Fill(m_dsSQL)
| | |
Thank you, all.
I am using the SQLServer specific SQLClient, I just named my variable
incorrectly (I copied and paste from another code).
This is my stored procedure:
CREATE PROCEDURE INSERT_INTO_MYTABLE
@aCCOUNT varchar(10) = NULL
AS
insert into MYTABLE (Account) VALUES (@account)
select @@identity
Which it does returns a row containing the identity column value. You
mentioned that I should not do that, may I know the reason why ?
Also, if I want to setup a parameter to accept the new Identity value, I
assume my stored procedure will look like the following:
CREATE PROCEDURE INSERT_INTO_MYTABLE
@ID int output,
@aCCOUNT varchar(10) = NULL
AS
insert into MYTABLE (Account) VALUES (@account)
select @ID = @@identity
If my procedure is like the above, how can I get @ID in my VB.NET program ?
Thanks.
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message
news:Ow**************@TK2MSFTNGP04.phx.gbl...
Robin is right. Why are you using the OleDb namespace instead of the SQL
Server-specific SqlClient?
I assume you set the CommandType to StoredProcedure. You'll also need to
setup a Parameter to accept the new Identity value--unless you're
returning it as a row (which you shouldn't). The AddWithValue is handy but
you can also code
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar,
10).Value="12345"
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker www.betav.com/blog/billva www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message
news:el**************@TK2MSFTNGP06.phx.gbl...
>>I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with "INSERT_INTO_MYTABLE" then do the following : " ? Because if I have many parameters,it is a lot of work to set each parameter like the above. I tried it, but it gave me an error. Thanks.
Dim OleDbCommand2 As New Data.SqlClient.SqlCommand Dim OleDbConnection1 As New Data.SqlClient.SqlConnection Dim m_daSQL As SqlClient.SqlDataAdapter Dim m_dsSQL As DataSet
With OleDbConnection1 .ConnectionString = "...." .Open() End With
OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------CAN I set it to INSERT_INTO_MYTABLE '12345' ? OleDbCommand2.CommandType = CommandType.StoredProcedure OleDbCommand2.Connection = OleDbConnection1 OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10) OleDbCommand2.Parameters("@Account").Value = "12345"
m_daSQL = New SqlClient.SqlDataAdapter m_dsSQL = New DataSet m_daSQL.SelectCommand = OleDbCommand2 m_daSQL.Fill(m_dsSQL)
| | |
Sure, consider that returning single row is a far more expensive than
returning a ReturnValue (an integer) or an output parameter (most
datatypes). It also means you can use cheaper (faster) Command methods to
run the SP that don't add the overhead to deal with the inbound rowset.
Fetching the returned value is also easier--simply address the specific
Parameter.Value property. AFA using @@Identity... don't. It can lead to
problems as your database gets more sophisticated. Use SCOPE_IDENTITY()
instead--it's safer.
To fetch the returned value, setup an appropriate Parameter:
OleDbCommand2.Parameters.Add("@ID", SqlDbType.Int).Direction =
ParameterDirection.Output
Next, run the SP with cmd.ExecuteNonQuery (as it does not return a rowset)
and fetch the returned parameter.
intMyID = OleDbCommand2.Parameters("@ID").Value
This is detailed in my book...
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker www.betav.com/blog/billva www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message
news:O7**************@TK2MSFTNGP03.phx.gbl...
Thank you, all.
I am using the SQLServer specific SQLClient, I just named my variable
incorrectly (I copied and paste from another code).
This is my stored procedure:
CREATE PROCEDURE INSERT_INTO_MYTABLE
@aCCOUNT varchar(10) = NULL
AS
insert into MYTABLE (Account) VALUES (@account)
select @@identity
Which it does returns a row containing the identity column value. You
mentioned that I should not do that, may I know the reason why ?
Also, if I want to setup a parameter to accept the new Identity value, I
assume my stored procedure will look like the following:
CREATE PROCEDURE INSERT_INTO_MYTABLE
@ID int output,
@aCCOUNT varchar(10) = NULL
AS
insert into MYTABLE (Account) VALUES (@account)
select @ID = @@identity
If my procedure is like the above, how can I get @ID in my VB.NET program
?
Thanks.
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message
news:Ow**************@TK2MSFTNGP04.phx.gbl...
>Robin is right. Why are you using the OleDb namespace instead of the SQL Server-specific SqlClient? I assume you set the CommandType to StoredProcedure. You'll also need to setup a Parameter to accept the new Identity value--unless you're returning it as a row (which you shouldn't). The AddWithValue is handy but you can also code
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10).Value="12345"
hth -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) -----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message news:el**************@TK2MSFTNGP06.phx.gbl...
>>>I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with "INSERT_INTO_MYTABLE" then do the following : " ? Because if I have many parameters,it is a lot of work to set each parameter like the above. I tried it, but it gave me an error. Thanks.
Dim OleDbCommand2 As New Data.SqlClient.SqlCommand Dim OleDbConnection1 As New Data.SqlClient.SqlConnection Dim m_daSQL As SqlClient.SqlDataAdapter Dim m_dsSQL As DataSet
With OleDbConnection1 .ConnectionString = "...." .Open() End With
OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------CAN I set it to INSERT_INTO_MYTABLE '12345' ? OleDbCommand2.CommandType = CommandType.StoredProcedure OleDbCommand2.Connection = OleDbConnection1 OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10) OleDbCommand2.Parameters("@Account").Value = "12345"
m_daSQL = New SqlClient.SqlDataAdapter m_dsSQL = New DataSet m_daSQL.SelectCommand = OleDbCommand2 m_daSQL.Fill(m_dsSQL)
| | |
Bill is right (of course).
The problem with @@IDENTITY is that it returns the last
auto-increment value generated for your connection. So if you
have a stored procedure that (for example) writes a new
record and then it creates a record in an audit trail
table, you will get back the identity value from the audit
trail table, even if it is done using a trigger. Oops.
If you're working with SQLServer2000 or later, you should use
SCOPE_IDENTITY() instead, with one minor exception. If you
insert the new row using a stored procedure, but you want to
retrieve that value after calling the stored procedure,
SCOPE_IDENTITY() will return Null. According to Sql Server
Books Online, SCOPE_IDENTITY returns the last auto-increment
value generated in the current scope, and the stored procedure
is execute in a different scope.
If you are inserting new rows using stored procedures and you
want to retrieve the newly generated auto-increment value, you
should return this information using an output parameter,
as Bill has stated.
Robin S.
--------------------------------------
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...
Sure, consider that returning single row is a far more expensive than
returning a ReturnValue (an integer) or an output parameter (most
datatypes). It also means you can use cheaper (faster) Command methods
to run the SP that don't add the overhead to deal with the inbound
rowset. Fetching the returned value is also easier--simply address the
specific Parameter.Value property. AFA using @@Identity... don't. It
can lead to problems as your database gets more sophisticated. Use
SCOPE_IDENTITY() instead--it's safer.
To fetch the returned value, setup an appropriate Parameter:
OleDbCommand2.Parameters.Add("@ID", SqlDbType.Int).Direction =
ParameterDirection.Output
Next, run the SP with cmd.ExecuteNonQuery (as it does not return a
rowset) and fetch the returned parameter.
intMyID = OleDbCommand2.Parameters("@ID").Value
This is detailed in my book...
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker www.betav.com/blog/billva www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest
book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message
news:O7**************@TK2MSFTNGP03.phx.gbl...
>Thank you, all.
I am using the SQLServer specific SQLClient, I just named my variable incorrectly (I copied and paste from another code). This is my stored procedure: CREATE PROCEDURE INSERT_INTO_MYTABLE @aCCOUNT varchar(10) = NULL AS insert into MYTABLE (Account) VALUES (@account) select @@identity
Which it does returns a row containing the identity column value. You mentioned that I should not do that, may I know the reason why ? Also, if I want to setup a parameter to accept the new Identity value, I assume my stored procedure will look like the following: CREATE PROCEDURE INSERT_INTO_MYTABLE @ID int output, @aCCOUNT varchar(10) = NULL AS insert into MYTABLE (Account) VALUES (@account) select @ID = @@identity
If my procedure is like the above, how can I get @ID in my VB.NET program ?
Thanks.
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message news:Ow**************@TK2MSFTNGP04.phx.gbl...
>>Robin is right. Why are you using the OleDb namespace instead of the SQL Server-specific SqlClient? I assume you set the CommandType to StoredProcedure. You'll also need to setup a Parameter to accept the new Identity value--unless you're returning it as a row (which you shouldn't). The AddWithValue is handy but you can also code
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10).Value="12345"
hth -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) -----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message news:el**************@TK2MSFTNGP06.phx.gbl... I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with "INSERT_INTO_MYTABLE" then do the following : " ? Because if I have many parameters,it is a lot of work to set each parameter like the above. I tried it, but it gave me an error. Thanks.
Dim OleDbCommand2 As New Data.SqlClient.SqlCommand Dim OleDbConnection1 As New Data.SqlClient.SqlConnection Dim m_daSQL As SqlClient.SqlDataAdapter Dim m_dsSQL As DataSet
With OleDbConnection1 .ConnectionString = "...." .Open() End With
OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------> CAN I set it to INSERT_INTO_MYTABLE '12345' ? OleDbCommand2.CommandType = CommandType.StoredProcedure OleDbCommand2.Connection = OleDbConnection1 OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10) OleDbCommand2.Parameters("@Account").Value = "12345"
m_daSQL = New SqlClient.SqlDataAdapter m_dsSQL = New DataSet m_daSQL.SelectCommand = OleDbCommand2 m_daSQL.Fill(m_dsSQL) | | |
Fniles,
Bill and Robin are right (of course), but why are you using that awfull
auto-increment instead of a nice GUID as identifier.
The only counterpart is that you have to create that yourself
dim myKey as Guid = New Guid().
But than you get rid of most of your trouble you have and surely will get
with related tables when you use the auto increment identifier.
Just my thought,
Cor
"RobinS" <Ro****@NoSpam.yah.noneschreef in bericht
news:zr******************************@comcast.com. ..
Bill is right (of course).
The problem with @@IDENTITY is that it returns the last
auto-increment value generated for your connection. So if you
have a stored procedure that (for example) writes a new
record and then it creates a record in an audit trail
table, you will get back the identity value from the audit
trail table, even if it is done using a trigger. Oops.
If you're working with SQLServer2000 or later, you should use
SCOPE_IDENTITY() instead, with one minor exception. If you
insert the new row using a stored procedure, but you want to
retrieve that value after calling the stored procedure,
SCOPE_IDENTITY() will return Null. According to Sql Server
Books Online, SCOPE_IDENTITY returns the last auto-increment
value generated in the current scope, and the stored procedure
is execute in a different scope.
If you are inserting new rows using stored procedures and you
want to retrieve the newly generated auto-increment value, you
should return this information using an output parameter,
as Bill has stated.
Robin S.
--------------------------------------
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...
>Sure, consider that returning single row is a far more expensive than returning a ReturnValue (an integer) or an output parameter (most datatypes). It also means you can use cheaper (faster) Command methods to run the SP that don't add the overhead to deal with the inbound rowset. Fetching the returned value is also easier--simply address the specific Parameter.Value property. AFA using @@Identity... don't. It can lead to problems as your database gets more sophisticated. Use SCOPE_IDENTITY() instead--it's safer.
To fetch the returned value, setup an appropriate Parameter:
OleDbCommand2.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output Next, run the SP with cmd.ExecuteNonQuery (as it does not return a rowset) and fetch the returned parameter.
intMyID = OleDbCommand2.Parameters("@ID").Value
This is detailed in my book...
hth
-- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) -----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message news:O7**************@TK2MSFTNGP03.phx.gbl...
>>Thank you, all.
I am using the SQLServer specific SQLClient, I just named my variable incorrectly (I copied and paste from another code). This is my stored procedure: CREATE PROCEDURE INSERT_INTO_MYTABLE @aCCOUNT varchar(10) = NULL AS insert into MYTABLE (Account) VALUES (@account) select @@identity
Which it does returns a row containing the identity column value. You mentioned that I should not do that, may I know the reason why ? Also, if I want to setup a parameter to accept the new Identity value, I assume my stored procedure will look like the following: CREATE PROCEDURE INSERT_INTO_MYTABLE @ID int output, @aCCOUNT varchar(10) = NULL AS insert into MYTABLE (Account) VALUES (@account) select @ID = @@identity
If my procedure is like the above, how can I get @ID in my VB.NET program ?
Thanks.
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message news:Ow**************@TK2MSFTNGP04.phx.gbl... Robin is right. Why are you using the OleDb namespace instead of the SQL Server-specific SqlClient? I assume you set the CommandType to StoredProcedure. You'll also need to setup a Parameter to accept the new Identity value--unless you're returning it as a row (which you shouldn't). The AddWithValue is handy but you can also code
OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10).Value="12345"
hth -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) -----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message news:el**************@TK2MSFTNGP06.phx.gbl... >I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with "INSERT_INTO_MYTABLE" then do the following : " ? Because if I have many parameters,it is a lot of work to set each parameter like the above. I tried it, but it gave me an error. Thanks. > Dim OleDbCommand2 As New Data.SqlClient.SqlCommand Dim OleDbConnection1 As New Data.SqlClient.SqlConnection Dim m_daSQL As SqlClient.SqlDataAdapter Dim m_dsSQL As DataSet > With OleDbConnection1 .ConnectionString = "...." .Open() End With > OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------CAN I set it to INSERT_INTO_MYTABLE '12345' ? OleDbCommand2.CommandType = CommandType.StoredProcedure OleDbCommand2.Connection = OleDbConnection1 OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10) OleDbCommand2.Parameters("@Account").Value = "12345" > m_daSQL = New SqlClient.SqlDataAdapter m_dsSQL = New DataSet m_daSQL.SelectCommand = OleDbCommand2 m_daSQL.Fill(m_dsSQL) > > | | |
Cor,
I've not had any problems (yet) with auto-incrementing. Are you
talking about the case where you want to add a parent and its
children, and you have to get the auto-increment value
from the parent before you can use it in the children records?
And if you create your own GUID instead, that's not a problem
because you know what it is? Am I getting that right?
Robin S.
------------------------------------
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:Ok**************@TK2MSFTNGP02.phx.gbl...
Fniles,
Bill and Robin are right (of course), but why are you using that
awfull auto-increment instead of a nice GUID as identifier.
The only counterpart is that you have to create that yourself
dim myKey as Guid = New Guid().
But than you get rid of most of your trouble you have and surely will
get with related tables when you use the auto increment identifier.
Just my thought,
Cor
"RobinS" <Ro****@NoSpam.yah.noneschreef in bericht
news:zr******************************@comcast.com. ..
>Bill is right (of course).
The problem with @@IDENTITY is that it returns the last auto-increment value generated for your connection. So if you have a stored procedure that (for example) writes a new record and then it creates a record in an audit trail table, you will get back the identity value from the audit trail table, even if it is done using a trigger. Oops.
If you're working with SQLServer2000 or later, you should use SCOPE_IDENTITY() instead, with one minor exception. If you insert the new row using a stored procedure, but you want to retrieve that value after calling the stored procedure, SCOPE_IDENTITY() will return Null. According to Sql Server Books Online, SCOPE_IDENTITY returns the last auto-increment value generated in the current scope, and the stored procedure is execute in a different scope.
If you are inserting new rows using stored procedures and you want to retrieve the newly generated auto-increment value, you should return this information using an output parameter, as Bill has stated.
Robin S. --------------------------------------
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message news:%2****************@TK2MSFTNGP06.phx.gbl...
>>Sure, consider that returning single row is a far more expensive than returning a ReturnValue (an integer) or an output parameter (most datatypes). It also means you can use cheaper (faster) Command methods to run the SP that don't add the overhead to deal with the inbound rowset. Fetching the returned value is also easier--simply address the specific Parameter.Value property. AFA using @@Identity... don't. It can lead to problems as your database gets more sophisticated. Use SCOPE_IDENTITY() instead--it's safer.
To fetch the returned value, setup an appropriate Parameter:
OleDbCommand2.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output Next, run the SP with cmd.ExecuteNonQuery (as it does not return a rowset) and fetch the returned parameter.
intMyID = OleDbCommand2.Parameters("@ID").Value
This is detailed in my book...
hth
-- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) -----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message news:O7**************@TK2MSFTNGP03.phx.gbl... Thank you, all.
I am using the SQLServer specific SQLClient, I just named my variable incorrectly (I copied and paste from another code). This is my stored procedure: CREATE PROCEDURE INSERT_INTO_MYTABLE @aCCOUNT varchar(10) = NULL AS insert into MYTABLE (Account) VALUES (@account) select @@identity
Which it does returns a row containing the identity column value. You mentioned that I should not do that, may I know the reason why ? Also, if I want to setup a parameter to accept the new Identity value, I assume my stored procedure will look like the following: CREATE PROCEDURE INSERT_INTO_MYTABLE @ID int output, @aCCOUNT varchar(10) = NULL AS insert into MYTABLE (Account) VALUES (@account) select @ID = @@identity
If my procedure is like the above, how can I get @ID in my VB.NET program ?
Thanks.
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message news:Ow**************@TK2MSFTNGP04.phx.gbl... Robin is right. Why are you using the OleDb namespace instead of the SQL Server-specific SqlClient? I assume you set the CommandType to StoredProcedure. You'll also need to setup a Parameter to accept the new Identity value--unless you're returning it as a row (which you shouldn't). The AddWithValue is handy but you can also code > OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, 10).Value="12345" > hth -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- > "fniles" <fn****@pfmail.comwrote in message news:el**************@TK2MSFTNGP06.phx.gbl.. . >>I am using VB.NET 2003 and SQL2000 database. >I have a stored procedure called "INSERT_INTO_MYTABLE" that >accepts 1 parameter (varchar(10)) and returns the identity column >value from that table. >When calling the stored procedure from VB.NET, in the >CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead >of calling it with "INSERT_INTO_MYTABLE" then do the following : > " >? Because if I have many parameters,it is a lot of work to set >each parameter like the above. >I tried it, but it gave me an error. >Thanks. >> > Dim OleDbCommand2 As New Data.SqlClient.SqlCommand > Dim OleDbConnection1 As New Data.SqlClient.SqlConnection > Dim m_daSQL As SqlClient.SqlDataAdapter > Dim m_dsSQL As DataSet >> > With OleDbConnection1 > .ConnectionString = "...." > .Open() > End With >> > OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------> >CAN I set it to INSERT_INTO_MYTABLE '12345' ? > OleDbCommand2.CommandType = CommandType.StoredProcedure > OleDbCommand2.Connection = OleDbConnection1 > OleDbCommand2.Parameters.Add("@Account", >SqlDbType.VarChar, 10) > OleDbCommand2.Parameters("@Account").Value = "12345" >> > m_daSQL = New SqlClient.SqlDataAdapter > m_dsSQL = New DataSet > m_daSQL.SelectCommand = OleDbCommand2 > m_daSQL.Fill(m_dsSQL) >> >> > > | | |
Exactly, not that it is a problem, it is much easier to handle and
maintance. Nothing is in fact impossible.
Cor
"RobinS" <Ro****@NoSpam.yah.noneschreef in bericht
news:oK******************************@comcast.com. ..
Cor,
I've not had any problems (yet) with auto-incrementing. Are you
talking about the case where you want to add a parent and its
children, and you have to get the auto-increment value
from the parent before you can use it in the children records?
And if you create your own GUID instead, that's not a problem
because you know what it is? Am I getting that right?
Robin S.
------------------------------------
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:Ok**************@TK2MSFTNGP02.phx.gbl...
>Fniles,
Bill and Robin are right (of course), but why are you using that awfull auto-increment instead of a nice GUID as identifier.
The only counterpart is that you have to create that yourself dim myKey as Guid = New Guid().
But than you get rid of most of your trouble you have and surely will get with related tables when you use the auto increment identifier.
Just my thought,
Cor
"RobinS" <Ro****@NoSpam.yah.noneschreef in bericht news:zr******************************@comcast.com ...
>>Bill is right (of course).
The problem with @@IDENTITY is that it returns the last auto-increment value generated for your connection. So if you have a stored procedure that (for example) writes a new record and then it creates a record in an audit trail table, you will get back the identity value from the audit trail table, even if it is done using a trigger. Oops.
If you're working with SQLServer2000 or later, you should use SCOPE_IDENTITY() instead, with one minor exception. If you insert the new row using a stored procedure, but you want to retrieve that value after calling the stored procedure, SCOPE_IDENTITY() will return Null. According to Sql Server Books Online, SCOPE_IDENTITY returns the last auto-increment value generated in the current scope, and the stored procedure is execute in a different scope.
If you are inserting new rows using stored procedures and you want to retrieve the newly generated auto-increment value, you should return this information using an output parameter, as Bill has stated.
Robin S. --------------------------------------
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message news:%2****************@TK2MSFTNGP06.phx.gbl.. . Sure, consider that returning single row is a far more expensive than returning a ReturnValue (an integer) or an output parameter (most datatypes). It also means you can use cheaper (faster) Command methods to run the SP that don't add the overhead to deal with the inbound rowset. Fetching the returned value is also easier--simply address the specific Parameter.Value property. AFA using @@Identity... don't. It can lead to problems as your database gets more sophisticated. Use SCOPE_IDENTITY() instead--it's safer.
To fetch the returned value, setup an appropriate Parameter:
OleDbCommand2.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output Next, run the SP with cmd.ExecuteNonQuery (as it does not return a rowset) and fetch the returned parameter.
intMyID = OleDbCommand2.Parameters("@ID").Value
This is detailed in my book...
hth
-- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) -----------------------------------------------------------------------------------------------------------------------
"fniles" <fn****@pfmail.comwrote in message news:O7**************@TK2MSFTNGP03.phx.gbl... Thank you, all. > I am using the SQLServer specific SQLClient, I just named my variable incorrectly (I copied and paste from another code). This is my stored procedure: CREATE PROCEDURE INSERT_INTO_MYTABLE @aCCOUNT varchar(10) = NULL AS insert into MYTABLE (Account) VALUES (@account) select @@identity > Which it does returns a row containing the identity column value. You mentioned that I should not do that, may I know the reason why ? Also, if I want to setup a parameter to accept the new Identity value, I assume my stored procedure will look like the following: CREATE PROCEDURE INSERT_INTO_MYTABLE @ID int output, @aCCOUNT varchar(10) = NULL AS insert into MYTABLE (Account) VALUES (@account) select @ID = @@identity > If my procedure is like the above, how can I get @ID in my VB.NET program ? > Thanks. > "William (Bill) Vaughn" <bi**************@nwlink.comwrote in message news:Ow**************@TK2MSFTNGP04.phx.gbl.. . >Robin is right. Why are you using the OleDb namespace instead of the >SQL Server-specific SqlClient? >I assume you set the CommandType to StoredProcedure. You'll also need >to setup a Parameter to accept the new Identity value--unless you're >returning it as a row (which you shouldn't). The AddWithValue is >handy but you can also code >> >OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, >10).Value="12345" >> >hth >-- >____________________________________ >William (Bill) Vaughn >Author, Mentor, Consultant >Microsoft MVP >INETA Speaker >www.betav.com/blog/billva >www.betav.com >Please reply only to the newsgroup so that others can benefit. >This posting is provided "AS IS" with no warranties, and confers no >rights. >__________________________________ >Visit www.hitchhikerguides.net to get more information on my latest >book: >Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) >----------------------------------------------------------------------------------------------------------------------- >> >"fniles" <fn****@pfmail.comwrote in message >news:el**************@TK2MSFTNGP06.phx.gbl. .. >>>I am using VB.NET 2003 and SQL2000 database. >>I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts >>1 parameter (varchar(10)) and returns the identity column value from >>that table. >>When calling the stored procedure from VB.NET, in the CommandText, >>can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it >>with "INSERT_INTO_MYTABLE" then do the following : >> " >>? Because if I have many parameters,it is a lot of work to set each >>parameter like the above. >>I tried it, but it gave me an error. >>Thanks. >>> >> Dim OleDbCommand2 As New Data.SqlClient.SqlCommand >> Dim OleDbConnection1 As New Data.SqlClient.SqlConnection >> Dim m_daSQL As SqlClient.SqlDataAdapter >> Dim m_dsSQL As DataSet >>> >> With OleDbConnection1 >> .ConnectionString = "...." >> .Open() >> End With >>> >> OleDbCommand2.CommandText = "INSERT_INTO_MYTABLE" ------CAN >>I set it to INSERT_INTO_MYTABLE '12345' ? >> OleDbCommand2.CommandType = CommandType.StoredProcedure >> OleDbCommand2.Connection = OleDbConnection1 >> OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, >>10) >> OleDbCommand2.Parameters("@Account").Value = "12345" >>> >> m_daSQL = New SqlClient.SqlDataAdapter >> m_dsSQL = New DataSet >> m_daSQL.SelectCommand = OleDbCommand2 >> m_daSQL.Fill(m_dsSQL) >>> >>> >> >> > > | | |
Got it. Thanks for clarifying. I can see the validity of
your argument.
Robin S.
-------------------------------
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:ON***************@TK2MSFTNGP04.phx.gbl...
Exactly, not that it is a problem, it is much easier to handle and
maintance. Nothing is in fact impossible.
Cor
"RobinS" <Ro****@NoSpam.yah.noneschreef in bericht
news:oK******************************@comcast.com. ..
>Cor,
I've not had any problems (yet) with auto-incrementing. Are you talking about the case where you want to add a parent and its children, and you have to get the auto-increment value from the parent before you can use it in the children records?
And if you create your own GUID instead, that's not a problem because you know what it is? Am I getting that right?
Robin S. ------------------------------------
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message news:Ok**************@TK2MSFTNGP02.phx.gbl...
>>Fniles,
Bill and Robin are right (of course), but why are you using that awfull auto-increment instead of a nice GUID as identifier.
The only counterpart is that you have to create that yourself dim myKey as Guid = New Guid().
But than you get rid of most of your trouble you have and surely will get with related tables when you use the auto increment identifier.
Just my thought,
Cor
"RobinS" <Ro****@NoSpam.yah.noneschreef in bericht news:zr******************************@comcast.co m... Bill is right (of course).
The problem with @@IDENTITY is that it returns the last auto-increment value generated for your connection. So if you have a stored procedure that (for example) writes a new record and then it creates a record in an audit trail table, you will get back the identity value from the audit trail table, even if it is done using a trigger. Oops.
If you're working with SQLServer2000 or later, you should use SCOPE_IDENTITY() instead, with one minor exception. If you insert the new row using a stored procedure, but you want to retrieve that value after calling the stored procedure, SCOPE_IDENTITY() will return Null. According to Sql Server Books Online, SCOPE_IDENTITY returns the last auto-increment value generated in the current scope, and the stored procedure is execute in a different scope.
If you are inserting new rows using stored procedures and you want to retrieve the newly generated auto-increment value, you should return this information using an output parameter, as Bill has stated.
Robin S. --------------------------------------
"William (Bill) Vaughn" <bi**************@nwlink.comwrote in message news:%2****************@TK2MSFTNGP06.phx.gbl... Sure, consider that returning single row is a far more expensive than returning a ReturnValue (an integer) or an output parameter (most datatypes). It also means you can use cheaper (faster) Command methods to run the SP that don't add the overhead to deal with the inbound rowset. Fetching the returned value is also easier--simply address the specific Parameter.Value property. AFA using @@Identity... don't. It can lead to problems as your database gets more sophisticated. Use SCOPE_IDENTITY() instead--it's safer. > To fetch the returned value, setup an appropriate Parameter: > OleDbCommand2.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output Next, run the SP with cmd.ExecuteNonQuery (as it does not return a rowset) and fetch the returned parameter. > intMyID = OleDbCommand2.Parameters("@ID").Value > This is detailed in my book... > hth > -- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- > "fniles" <fn****@pfmail.comwrote in message news:O7**************@TK2MSFTNGP03.phx.gbl.. . >Thank you, all. >> >I am using the SQLServer specific SQLClient, I just named my >variable incorrectly (I copied and paste from another code). >This is my stored procedure: >CREATE PROCEDURE INSERT_INTO_MYTABLE >@aCCOUNT varchar(10) = NULL >AS >insert into MYTABLE (Account) VALUES (@account) >select @@identity >> >Which it does returns a row containing the identity column value. >You mentioned that I should not do that, may I know the reason >why ? >Also, if I want to setup a parameter to accept the new Identity >value, I assume my stored procedure will look like the following: >CREATE PROCEDURE INSERT_INTO_MYTABLE >@ID int output, >@aCCOUNT varchar(10) = NULL >AS >insert into MYTABLE (Account) VALUES (@account) >select @ID = @@identity >> >If my procedure is like the above, how can I get @ID in my VB.NET >program ? >> >Thanks. >> >"William (Bill) Vaughn" <bi**************@nwlink.comwrote in >message news:Ow**************@TK2MSFTNGP04.phx.gbl... >>Robin is right. Why are you using the OleDb namespace instead of >>the SQL Server-specific SqlClient? >>I assume you set the CommandType to StoredProcedure. You'll also >>need to setup a Parameter to accept the new Identity >>value--unless you're returning it as a row (which you >>shouldn't). The AddWithValue is handy but you can also code >>> >>OleDbCommand2.Parameters.Add("@Account", SqlDbType.VarChar, >>10).Value="12345" >>> >>hth >>-- >>____________________________________ >>William (Bill) Vaughn >>Author, Mentor, Consultant >>Microsoft MVP >>INETA Speaker >>www.betav.com/blog/billva >>www.betav.com >>Please reply only to the newsgroup so that others can benefit. >>This posting is provided "AS IS" with no warranties, and confers >>no rights. >>__________________________________ >>Visit www.hitchhikerguides.net to get more information on my >>latest book: >>Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) >>and Hitchhiker's Guide to SQL Server 2005 Compact Edition >>(EBook) >>----------------------------------------------------------------------------------------------------------------------- >>> >>"fniles" <fn****@pfmail.comwrote in message >>news:el**************@TK2MSFTNGP06.phx.gbl.. . >>>>I am using VB.NET 2003 and SQL2000 database. >>>I have a stored procedure called "INSERT_INTO_MYTABLE" that >>>accepts 1 parameter (varchar(10)) and returns the identity >>>column value from that table. >>>When calling the stored procedure from VB.NET, in the >>>CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" >>>instead of calling it with "INSERT_INTO_MYTABLE" then do the >>>following : >>> " >>>? Because if I have many parameters,it is a lot of work to set >>>each parameter like the above. >>>I tried it, but it gave me an error. >>>Thanks. >>>> >>> Dim OleDbCommand2 As New Data.SqlClient.SqlCommand >>> Dim OleDbConnection1 As New Data.SqlClient.SqlConnection >>> Dim m_daSQL As SqlClient.SqlDataAdapter >>> Dim m_dsSQL As DataSet >>>> >>> With OleDbConnection1 >>> .ConnectionString = "...." >>> .Open() >>> End With >>>> >>> OleDbCommand2.CommandText = >>>"INSERT_INTO_MYTABLE" ------CAN I set it to >>>INSERT_INTO_MYTABLE '12345' ? >>> OleDbCommand2.CommandType = CommandType.StoredProcedure >>> OleDbCommand2.Connection = OleDbConnection1 >>> OleDbCommand2.Parameters.Add("@Account", >>>SqlDbType.VarChar, 10) >>> OleDbCommand2.Parameters("@Account").Value = "12345" >>>> >>> m_daSQL = New SqlClient.SqlDataAdapter >>> m_dsSQL = New DataSet >>> m_daSQL.SelectCommand = OleDbCommand2 >>> m_daSQL.Fill(m_dsSQL) >>>> >>>> >>> >>> >> >> > > | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
reply
views
Thread by Nashat Wanly |
last post: by
|
2 posts
views
Thread by Dino L. |
last post: by
|
2 posts
views
Thread by Eli |
last post: by
|
3 posts
views
Thread by Bilbo |
last post: by
|
6 posts
views
Thread by Sam |
last post: by
|
1 post
views
Thread by Alan T |
last post: by
|
7 posts
views
Thread by Siv |
last post: by
|
4 posts
views
Thread by dawson |
last post: by
|
3 posts
views
Thread by dawson |
last post: by
|
2 posts
views
Thread by jed@auto-soft.co.za |
last post: by
| | | | | | | | | | |