Sending parameters to a stored procedure (VB.Net 2003 + SQL Server)

  • Thread starter Thread starter James
  • Start date Start date
J

James

I'm using Visual Basic.NET 2003 and SQL Server 2000. I'm attempting to pass
parameters to a stored procedure which will insert a record in a table.

The connection to SQL Server seems fine, but when my VB.Net app reaches the
ExecuteNonQuery() line I get an error:-

"An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred in system.data.dll
Additional information: System error."

I've used SQL Profiler to see what's reaching the server, and it seems that
the T-SQL string that's being executed is missing the '@'s before the
parameters of the stored procedure. This is the command which is being
sent:

exec spProcedure Title = 'Mr', Forename = 'Joe', Surname = 'Public',
Addressee = 'Mr J Q Public', Salutation = 'Joe'

and which fails - and if I attempt to run it directly in SQL Query Analyzer
it fails saying the stored procedure is expecting parameters.

If I modify the T-SQL to this:

exec spProcedure @Title = 'Mr', @Forename = 'Joe', @Surname = 'Public',
@Addressee = 'Mr J Q Public', @Salutation = 'Joe'

then it runs fine.

My VB.Net code follows - can anyone point out what I'm doing wrong? Why
isn't VB.Net sending the right T-SQL? How do I get SQL Server to accept the
parameters?

Thanks,

James




myConn = New SqlClient.SqlConnection("Initial Catalog=data;Data
Source=localhost;Integrated Security=SSPI;")
myConn.Open()

Dim cmdInsert As SqlClient.SqlCommand
cmdInsert = New SqlClient.SqlCommand("spProcedure", myConn)

With cmdInsert
.CommandType = CommandType.StoredProcedure

.Parameters.Add("Title", SqlDbType.VarChar, 50).Direction =
ParameterDirection.Input
.Parameters.Add("Forename", SqlDbType.VarChar, 50).Direction =
ParameterDirection.Input
.Parameters.Add("Surname", SqlDbType.VarChar, 50).Direction =
ParameterDirection.Input
.Parameters.Add("Addressee", SqlDbType.VarChar, 100).Direction =
ParameterDirection.Input
.Parameters.Add("Salutation", SqlDbType.VarChar, 100).Direction
= ParameterDirection.Input

.Parameters("Title").Value = "Mr"
.Parameters("Forename").Value = "Joe"
.Parameters("Surname").Value = "Public"
.Parameters("Addressee").Value = "Mr J Q Public"
.Parameters("Salutation").Value = "Joe"

.ExecuteNonQuery()

End With
 
James,

Have you tried using the "@" both when adding parameters and parameter values?

For example:

..Parameters.Add("@Title", SqlDbType.VarChar, 50)

..Parameters("@Title").Value = "Mr"

Kerry Moorman
 
Kerry Moorman said:
James,

Have you tried using the "@" both when adding parameters and parameter
values?

For example:

.Parameters.Add("@Title", SqlDbType.VarChar, 50)

.Parameters("@Title").Value = "Mr"
Click to expand...

This works - thanks for the tip!

James

PS What's the reason you can send parameters through a ADO.Net connection
without the initial @ if parameters always have to start with a @? Or is
that not generally true?
 
Parameter name is simply a string. Being a "parameter" doesn't preclude any
intelligence to the object. It simply says 'the sproc is going to expect a
parameter named X'. Therefore it matches the parameter name withe the sproc
parameter then pass that value. I have found that the parameters you pass
have to use the same name and capitalization as the expected parameters. If
you name the parameters they do not have to be in the same order, however if
you only use indexed then they have to be in the same order as the sproc
parameters.

Hope that helps..
 
Back
Top