Stored procedure parameter prob

G

Guest

Hi,

This is my stored procured. i try to pass the parametre like this. i am
getting error.
any one please tell me how to pass the parameter?

vb code:
..Connection = New OleDbConnection(strConn)
..Connection.Open()
..CommandText = "sp_RegShow1"
..Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
..Parameters.Item(2).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")


thanks
bala

Stored procedure:

CREATE PROCEDURE [dbo].[sp_RegShow1]
(@FirstName_1 [nvarchar](50),
@LastName_1 [nvarchar](50)
)
AS
select * from tbleusers where firstname=@FirstName_1 and lastname=@LastName_1
GO
 
K

Ken Tucker [MVP]

Hi,

Try this.

..Parameters.Add("@FirstName",Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~"))
..Parameters.Add("@LastName" , Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~"))


Ken
------------------

Hi,

This is my stored procured. i try to pass the parametre like this. i am
getting error.
any one please tell me how to pass the parameter?

vb code:
..Connection = New OleDbConnection(strConn)
..Connection.Open()
..CommandText = "sp_RegShow1"
..Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
..Parameters.Item(2).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")


thanks
bala

Stored procedure:

CREATE PROCEDURE [dbo].[sp_RegShow1]
(@FirstName_1 [nvarchar](50),
@LastName_1 [nvarchar](50)
)
AS
select * from tbleusers where firstname=@FirstName_1 and
lastname=@LastName_1
GO
 
G

Guest

Hi,

Thanks for the reply.

i tried like this but still i am getting error.
err msg:
procedure sp_regshow1 'expects parameter '@Firstname_1 which is not supplied.

..Parameters.Add("@FirstName_1", Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~"))
.Parameters.Add("@LastName_2", Replace(Trim(Mid(cboCustName.Text,
InStr(cboCustName.Text, "-") + 2)), "'", "~"))

thanks
bala
 
C

Cor Ligthert

Bala,

Do you add the parameters in the same sequence as in the SQL string.
That is a difference between OleDb and SQLClient.

I hope this helps?

Cor

"Bala"
Hi,

Thanks for the reply.

i tried like this but still i am getting error.
err msg:
procedure sp_regshow1 'expects parameter '@Firstname_1 which is not
supplied.

.Parameters.Add("@FirstName_1", Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~"))
.Parameters.Add("@LastName_2", Replace(Trim(Mid(cboCustName.Text,
InStr(cboCustName.Text, "-") + 2)), "'", "~"))

thanks
bala

Ken Tucker said:
Hi,

Try this.

..Parameters.Add("@FirstName",Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~"))
..Parameters.Add("@LastName" , Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~"))


Ken
------------------

Hi,

This is my stored procured. i try to pass the parametre like this. i am
getting error.
any one please tell me how to pass the parameter?

vb code:
..Connection = New OleDbConnection(strConn)
..Connection.Open()
..CommandText = "sp_RegShow1"
..Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
..Parameters.Item(2).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")


thanks
bala

Stored procedure:

CREATE PROCEDURE [dbo].[sp_RegShow1]
(@FirstName_1 [nvarchar](50),
@LastName_1 [nvarchar](50)
)
AS
select * from tbleusers where firstname=@FirstName_1 and
lastname=@LastName_1
GO
Click to expand...
Click to expand...
 
H

Harry Strybos

Bala said:
Hi,

This is my stored procured. i try to pass the parametre like this. i am
getting error.
any one please tell me how to pass the parameter?

vb code:
.Connection = New OleDbConnection(strConn)
.Connection.Open()
.CommandText = "sp_RegShow1"
.Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
.Parameters.Item(2).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")


thanks
bala

Stored procedure:

CREATE PROCEDURE [dbo].[sp_RegShow1]
(@FirstName_1 [nvarchar](50),
@LastName_1 [nvarchar](50)
)
AS
select * from tbleusers where firstname=@FirstName_1 and
lastname=@LastName_1
GO
Click to expand...

I think it should be:

.Parameters.Item(0).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
.Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
 
G

Guest

Hi Harry,

i tried your way.but i got this error.

Invalid index 0 for this oledbparameterCollection with count=0
any idea?

thanks
bala


Harry Strybos said:
Bala said:
Hi,

This is my stored procured. i try to pass the parametre like this. i am
getting error.
any one please tell me how to pass the parameter?

vb code:
.Connection = New OleDbConnection(strConn)
.Connection.Open()
.CommandText = "sp_RegShow1"
.Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
.Parameters.Item(2).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")


thanks
bala

Stored procedure:

CREATE PROCEDURE [dbo].[sp_RegShow1]
(@FirstName_1 [nvarchar](50),
@LastName_1 [nvarchar](50)
)
AS
select * from tbleusers where firstname=@FirstName_1 and
lastname=@LastName_1
GO
Click to expand...

I think it should be:

.Parameters.Item(0).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
.Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
Click to expand...
 
J

Jim Hughes

Don't forget to set the CommandType!

..CommandText = "sp_RegShow1"
..CommandType = CommandType.StoredProcedure

I also do not see where you are adding Parameters to the command object.

..Parameters.Add(New OleDBParameter("@FirstName_1",
Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~"))

Not to nitpick, but do not prefix your proc names with sp_ or xp_, it forces
SQL Server to look in the Master DB first, then the default DB for that
connection.


Bala said:
Hi Harry,

i tried your way.but i got this error.

Invalid index 0 for this oledbparameterCollection with count=0
any idea?

thanks
bala


Harry Strybos said:
Bala said:
Hi,

This is my stored procured. i try to pass the parametre like this. i am
getting error.
any one please tell me how to pass the parameter?

vb code:
.Connection = New OleDbConnection(strConn)
.Connection.Open()
.CommandText = "sp_RegShow1"
.Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
.Parameters.Item(2).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")


thanks
bala

Stored procedure:

CREATE PROCEDURE [dbo].[sp_RegShow1]
(@FirstName_1 [nvarchar](50),
@LastName_1 [nvarchar](50)
)
AS
select * from tbleusers where firstname=@FirstName_1 and
lastname=@LastName_1
GO
Click to expand...

I think it should be:

.Parameters.Item(0).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
.Parameters.Item(1).Value = Replace(Trim(Mid(cboCustName.Text, 1,
InStr(cboCustName.Text, "-") - 1)), "'", "~")
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

passing parameters to sql sp 1
vb.net and sql server 2005 stored procedure very slow 5
How do you pass parameters to a stored procedure 2
sqlDataSource UpdateQuery withe stored procedure and parameters? 4
Passing Optional Parameter to Stored Procedure in VS VB.net 2
ADO: how to execute SQL Server stored procedure and get return val 1
Passing values to Stored Procedure 1
Entity Framework Return Value from Stored Procedure - Getting Error 1

Top