Really need some help on this

[Michael S. Kolias]

I have a stored procedure that I call from an aspx page but for some weird
reason i get this error message:
Procedure 'sp_insert_customer' expects parameter '@username', which was not
supplied.

Here is my stored proc
CREATE PROCEDURE dbo.sp_insert_customer

@username varchar(16),
@password varchar(34),
@email varchar(128),
@firstName varchar(50),
@lastName varchar(50),
@company varchar(50),
@address varchar(50),
@address2 varchar(50),
@city varchar(50),
@state varchar(50),
@zip varchar(50),
@country char(2),
@telephone varchar(50),
@cellPhone varchar(50),
@fax varchar(50)

AS

INSERT INTO CUSTOMERS

(username, [password], email, first_name, last_name, company, address,
address2, city, state_province, zip_postal, country, telephone,
cell_phone, fax)

VALUES

(@username, @password, @email, @firstName, @lastName, @company, @address,
@address2, @city, @state, @zip, @country, @telephone, @cellPhone, @fax)

GO

And here is the funtion I am calling it from
Public Function insert() As Boolean
Dim oCmd As SqlCommand

Try
oCn.Open()
oCmd = oCn.CreateCommand

With oCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_insert_customer"
With .Parameters
.Add("@username", SqlDbType.VarChar, 16, m_username)
.Add("@password", SqlDbType.VarChar, 34, m_password)
.Add("@email", SqlDbType.VarChar, 128, m_email)
.Add("@firstName", SqlDbType.VarChar, 50, m_firstName)
.Add("@lastName", SqlDbType.VarChar, 50, m_lastName)
.Add("@company", SqlDbType.VarChar, 50, m_company)
.Add("@address", SqlDbType.VarChar, 50, m_address)
.Add("@address2", SqlDbType.VarChar, 50, m_address2)
.Add("@city", SqlDbType.VarChar, 50, m_city)
.Add("@state", SqlDbType.VarChar, 50, m_state)
.Add("@zip", SqlDbType.VarChar, 50, m_zip)
.Add("@country", SqlDbType.Char, 2, m_country)
.Add("@telephone", SqlDbType.VarChar, 50, m_telephone)
.Add("@cellPhone", SqlDbType.VarChar, 50, m_cellPhone)
.Add("@fax", m_fax)
End With
End With

oCmd.Prepare()
oCmd.ExecuteNonQuery()

Catch ex As Exception
m_lastError = ex.Message
Return False

Catch sqlEx As Exception
m_lastError = sqlEx.Message
Return False

Finally
oCmd.Dispose()
oCn.Close()
End Try

Return True
End Function
Now the bizzare thing is if I modify the parameter list of the .Add function
everything works ok. where is the problem???????
Here is the working version of that function.

Public Function insert() As Boolean
Dim oCmd As SqlCommand

Try
oCn.Open()
oCmd = oCn.CreateCommand

With oCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_insert_customer"
With .Parameters
.Add("@username", m_username)
.Add("@password", m_password)
.Add("@email", m_email)
.Add("@firstName", m_firstName)
.Add("@lastName", m_lastName)
.Add("@company", m_company)
.Add("@address", m_address)
.Add("@address2", m_address2)
.Add("@city", m_city)
.Add("@state", m_state)
.Add("@zip", m_zip)
.Add("@country", m_country)
.Add("@telephone", m_telephone)
.Add("@cellPhone", m_cellPhone)
.Add("@fax", m_fax)
End With
End With

oCmd.Prepare()
oCmd.ExecuteNonQuery()

Catch ex As Exception
m_lastError = ex.Message
Return False

Catch sqlEx As Exception
m_lastError = sqlEx.Message
Return False

Finally
oCmd.Dispose()
oCn.Close()
End Try

Return True
End Function

[Tu-Thach]

The first version does not work because you never pass in the value of the
parameters. The fourth parameter is the source column, not the value.

Tu-Thach

"Michael S. Kolias" wrote:

I have a stored procedure that I call from an aspx page but for some weird
reason i get this error message:
Procedure 'sp_insert_customer' expects parameter '@username', which was not
supplied.

Here is my stored proc
CREATE PROCEDURE dbo.sp_insert_customer

@username varchar(16),
@password varchar(34),
@email varchar(128),
@firstName varchar(50),
@lastName varchar(50),
@company varchar(50),
@address varchar(50),
@address2 varchar(50),
@city varchar(50),
@state varchar(50),
@zip varchar(50),
@country char(2),
@telephone varchar(50),
@cellPhone varchar(50),
@fax varchar(50)

AS

INSERT INTO CUSTOMERS

(username, [password], email, first_name, last_name, company, address,
address2, city, state_province, zip_postal, country, telephone,
cell_phone, fax)

VALUES

(@username, @password, @email, @firstName, @lastName, @company, @address,
@address2, @city, @state, @zip, @country, @telephone, @cellPhone, @fax)

GO

And here is the funtion I am calling it from
Public Function insert() As Boolean
Dim oCmd As SqlCommand

Try
oCn.Open()
oCmd = oCn.CreateCommand

With oCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_insert_customer"
With .Parameters
.Add("@username", SqlDbType.VarChar, 16, m_username)
.Add("@password", SqlDbType.VarChar, 34, m_password)
.Add("@email", SqlDbType.VarChar, 128, m_email)
.Add("@firstName", SqlDbType.VarChar, 50, m_firstName)
.Add("@lastName", SqlDbType.VarChar, 50, m_lastName)
.Add("@company", SqlDbType.VarChar, 50, m_company)
.Add("@address", SqlDbType.VarChar, 50, m_address)
.Add("@address2", SqlDbType.VarChar, 50, m_address2)
.Add("@city", SqlDbType.VarChar, 50, m_city)
.Add("@state", SqlDbType.VarChar, 50, m_state)
.Add("@zip", SqlDbType.VarChar, 50, m_zip)
.Add("@country", SqlDbType.Char, 2, m_country)
.Add("@telephone", SqlDbType.VarChar, 50, m_telephone)
.Add("@cellPhone", SqlDbType.VarChar, 50, m_cellPhone)
.Add("@fax", m_fax)
End With
End With

oCmd.Prepare()
oCmd.ExecuteNonQuery()

Catch ex As Exception
m_lastError = ex.Message
Return False

Catch sqlEx As Exception
m_lastError = sqlEx.Message
Return False

Finally
oCmd.Dispose()
oCn.Close()
End Try

Return True
End Function
Now the bizzare thing is if I modify the parameter list of the .Add function
everything works ok. where is the problem???????
Here is the working version of that function.

Public Function insert() As Boolean
Dim oCmd As SqlCommand

Try
oCn.Open()
oCmd = oCn.CreateCommand

With oCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_insert_customer"
With .Parameters
.Add("@username", m_username)
.Add("@password", m_password)
.Add("@email", m_email)
.Add("@firstName", m_firstName)
.Add("@lastName", m_lastName)
.Add("@company", m_company)
.Add("@address", m_address)
.Add("@address2", m_address2)
.Add("@city", m_city)
.Add("@state", m_state)
.Add("@zip", m_zip)
.Add("@country", m_country)
.Add("@telephone", m_telephone)
.Add("@cellPhone", m_cellPhone)
.Add("@fax", m_fax)
End With
End With

oCmd.Prepare()
oCmd.ExecuteNonQuery()

Catch ex As Exception
m_lastError = ex.Message
Return False

Catch sqlEx As Exception
m_lastError = sqlEx.Message
Return False

Finally
oCmd.Dispose()
oCn.Close()
End Try

Return True
End Function

[William \(Bill\) Vaughn]

And don't prefix your SP name with "sp_" unless you don't care about
performance.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
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.
__________________________________

"Tu-Thach" <Tu*****@discussions.microsoft.com> wrote in message
news:53**********************************@microsof t.com...

The first version does not work because you never pass in the value of the
parameters. The fourth parameter is the source column, not the value.

Tu-Thach

"Michael S. Kolias" wrote:

I have a stored procedure that I call from an aspx page but for some
weird
reason i get this error message:
Procedure 'sp_insert_customer' expects parameter '@username', which was
not
supplied.

Here is my stored proc
CREATE PROCEDURE dbo.sp_insert_customer

@username varchar(16),
@password varchar(34),
@email varchar(128),
@firstName varchar(50),
@lastName varchar(50),
@company varchar(50),
@address varchar(50),
@address2 varchar(50),
@city varchar(50),
@state varchar(50),
@zip varchar(50),
@country char(2),
@telephone varchar(50),
@cellPhone varchar(50),
@fax varchar(50)

AS

INSERT INTO CUSTOMERS

(username, [password], email, first_name, last_name, company, address,
address2, city, state_province, zip_postal, country, telephone,
cell_phone, fax)

VALUES

(@username, @password, @email, @firstName, @lastName, @company,
@address,
@address2, @city, @state, @zip, @country, @telephone, @cellPhone, @fax)

GO

And here is the funtion I am calling it from
Public Function insert() As Boolean
Dim oCmd As SqlCommand

Try
oCn.Open()
oCmd = oCn.CreateCommand

With oCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_insert_customer"
With .Parameters
.Add("@username", SqlDbType.VarChar, 16, m_username)
.Add("@password", SqlDbType.VarChar, 34, m_password)
.Add("@email", SqlDbType.VarChar, 128, m_email)
.Add("@firstName", SqlDbType.VarChar, 50, m_firstName)
.Add("@lastName", SqlDbType.VarChar, 50, m_lastName)
.Add("@company", SqlDbType.VarChar, 50, m_company)
.Add("@address", SqlDbType.VarChar, 50, m_address)
.Add("@address2", SqlDbType.VarChar, 50, m_address2)
.Add("@city", SqlDbType.VarChar, 50, m_city)
.Add("@state", SqlDbType.VarChar, 50, m_state)
.Add("@zip", SqlDbType.VarChar, 50, m_zip)
.Add("@country", SqlDbType.Char, 2, m_country)
.Add("@telephone", SqlDbType.VarChar, 50, m_telephone)
.Add("@cellPhone", SqlDbType.VarChar, 50, m_cellPhone)
.Add("@fax", m_fax)
End With
End With

oCmd.Prepare()
oCmd.ExecuteNonQuery()

Catch ex As Exception
m_lastError = ex.Message
Return False

Catch sqlEx As Exception
m_lastError = sqlEx.Message
Return False

Finally
oCmd.Dispose()
oCn.Close()
End Try

Return True
End Function
Now the bizzare thing is if I modify the parameter list of the .Add
function
everything works ok. where is the problem???????
Here is the working version of that function.

Public Function insert() As Boolean
Dim oCmd As SqlCommand

Try
oCn.Open()
oCmd = oCn.CreateCommand

With oCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_insert_customer"
With .Parameters
.Add("@username", m_username)
.Add("@password", m_password)
.Add("@email", m_email)
.Add("@firstName", m_firstName)
.Add("@lastName", m_lastName)
.Add("@company", m_company)
.Add("@address", m_address)
.Add("@address2", m_address2)
.Add("@city", m_city)
.Add("@state", m_state)
.Add("@zip", m_zip)
.Add("@country", m_country)
.Add("@telephone", m_telephone)
.Add("@cellPhone", m_cellPhone)
.Add("@fax", m_fax)
End With
End With

oCmd.Prepare()
oCmd.ExecuteNonQuery()

Catch ex As Exception
m_lastError = ex.Message
Return False

Catch sqlEx As Exception
m_lastError = sqlEx.Message
Return False

Finally
oCmd.Dispose()
oCn.Close()
End Try

Return True
End Function

[Jeph Axxe]

Why is that ?

William (Bill) Vaughn wrote:

And don't prefix your SP name with "sp_" unless you don't care about
performance.

--
An artist is someone who produces things that
people don't need to have but that he - for some
reason - thinks it would be a good idea to give them.
Andy Warhol

[Fredrik Wahlgren]

"Jeph Axxe" <he***@metal.dude> wrote in message
news:34*************@individual.net...

Why is that ?

Truncated

Here's why. Snipped from http://vyaskn.tripod.com/object_naming.htm

" If you are using Microsoft SQL Server, never prefix your stored procedures
with 'sp_', unless you are storing the procedure in the master database. If
you call a stored procedure prefixed with sp_, SQL Server always looks for
this procedure in the master database. Only after checking in the master
database (if not found) it searches the current database. "

For the same reason, don't prefix your sp's with "xp_"

/ Fredrik

[Michael S. Kolias]

Thanx for the tips everyone. really appreciate it.

"Michael S. Kolias" <mi***@alspaconsulting.com> wrote in message
news:YN********************@speakeasy.net...

I have a stored procedure that I call from an aspx page but for some weird
reason i get this error message:
Procedure 'sp_insert_customer' expects parameter '@username', which was not supplied.

Here is my stored proc
CREATE PROCEDURE dbo.sp_insert_customer

@username varchar(16),
@password varchar(34),
@email varchar(128),
@firstName varchar(50),
@lastName varchar(50),
@company varchar(50),
@address varchar(50),
@address2 varchar(50),
@city varchar(50),
@state varchar(50),
@zip varchar(50),
@country char(2),
@telephone varchar(50),
@cellPhone varchar(50),
@fax varchar(50)

AS

INSERT INTO CUSTOMERS

(username, [password], email, first_name, last_name, company, address,
address2, city, state_province, zip_postal, country, telephone,
cell_phone, fax)

VALUES

(@username, @password, @email, @firstName, @lastName, @company, @address,
@address2, @city, @state, @zip, @country, @telephone, @cellPhone, @fax)

GO

And here is the funtion I am calling it from
Public Function insert() As Boolean
Dim oCmd As SqlCommand

Try
oCn.Open()
oCmd = oCn.CreateCommand

With oCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_insert_customer"
With .Parameters
.Add("@username", SqlDbType.VarChar, 16, m_username)
.Add("@password", SqlDbType.VarChar, 34, m_password)
.Add("@email", SqlDbType.VarChar, 128, m_email)
.Add("@firstName", SqlDbType.VarChar, 50, m_firstName)
.Add("@lastName", SqlDbType.VarChar, 50, m_lastName)
.Add("@company", SqlDbType.VarChar, 50, m_company)
.Add("@address", SqlDbType.VarChar, 50, m_address)
.Add("@address2", SqlDbType.VarChar, 50, m_address2)
.Add("@city", SqlDbType.VarChar, 50, m_city)
.Add("@state", SqlDbType.VarChar, 50, m_state)
.Add("@zip", SqlDbType.VarChar, 50, m_zip)
.Add("@country", SqlDbType.Char, 2, m_country)
.Add("@telephone", SqlDbType.VarChar, 50, m_telephone)
.Add("@cellPhone", SqlDbType.VarChar, 50, m_cellPhone)
.Add("@fax", m_fax)
End With
End With

oCmd.Prepare()
oCmd.ExecuteNonQuery()

Catch ex As Exception
m_lastError = ex.Message
Return False

Catch sqlEx As Exception
m_lastError = sqlEx.Message
Return False

Finally
oCmd.Dispose()
oCn.Close()
End Try

Return True
End Function
Now the bizzare thing is if I modify the parameter list of the .Add function everything works ok. where is the problem???????
Here is the working version of that function.

Public Function insert() As Boolean
Dim oCmd As SqlCommand

Try
oCn.Open()
oCmd = oCn.CreateCommand

With oCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_insert_customer"
With .Parameters
.Add("@username", m_username)
.Add("@password", m_password)
.Add("@email", m_email)
.Add("@firstName", m_firstName)
.Add("@lastName", m_lastName)
.Add("@company", m_company)
.Add("@address", m_address)
.Add("@address2", m_address2)
.Add("@city", m_city)
.Add("@state", m_state)
.Add("@zip", m_zip)
.Add("@country", m_country)
.Add("@telephone", m_telephone)
.Add("@cellPhone", m_cellPhone)
.Add("@fax", m_fax)
End With
End With

oCmd.Prepare()
oCmd.ExecuteNonQuery()

Catch ex As Exception
m_lastError = ex.Message
Return False

Catch sqlEx As Exception
m_lastError = sqlEx.Message
Return False

Finally
oCmd.Dispose()
oCn.Close()
End Try

Return True
End Function