By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Stored Procedure and SQLCommand

P: n/a
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)
Jan 6 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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)


Jan 6 '07 #2

P: n/a
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)


Jan 6 '07 #3

P: n/a
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)



Jan 6 '07 #4

P: n/a
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)




Jan 6 '07 #5

P: n/a
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)




Jan 7 '07 #6

P: n/a
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)
>
>




Jan 7 '07 #7

P: n/a
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)
>>
>>
>
>




Jan 7 '07 #8

P: n/a
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)
>>>
>>>
>>
>>
>
>




Jan 7 '07 #9

P: n/a
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)
>>>>
>>>>
>>>
>>>
>>
>>
>
>




Jan 7 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.