473,221 Members | 1,797 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

Stored Procedure and SQLCommand

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
9 4109
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
2
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have...
3
by: Bilbo | last post by:
I have a a headscratcher here: I have a form that when submitted should do 2 things when a user enters data and then clicks the Add button. Here goes: 1. Call a stored procedure called...
6
by: Sam | last post by:
I had created stored procedure at SQL Server 2000 and how do I call it via ASP.Net using VB Language? CREATE PROCEDURE STK As if exists (select * from dbo.sysobjects where id =...
1
by: Alan T | last post by:
Is there a sample code I can use to call a stored procedure which has input and output paramerts ?
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
4
by: dawson | last post by:
Hello, when I execute the following stored procedure in MSVS 2005, it request the three paramaters and returns the correct value/row from the database as expected: CREATE PROCEDURE...
3
by: dawson | last post by:
Hello, from the code below, how do I add/send a parameter to the stored procedure? SqlConnection conn = null; conn = new...
2
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.