Stored Procedure and SQLCommand

  • Thread starter Thread starter fniles
  • Start date Start date
F

fniles

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)
 
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.
 
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)
 
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.
 
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)
-----------------------------------------------------------------------------------------------------------------------
 
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.
--------------------------------------
 
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
 
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.
 
Exactly, not that it is a problem, it is much easier to handle and
maintance. Nothing is in fact impossible.

Cor
 
Back
Top