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

Really need some help on this

P: n/a
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
Jul 21 '05 #1
Share this Question
Share on Google+
5 Replies


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

Jul 21 '05 #2

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

Jul 21 '05 #3

P: n/a

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
Jul 21 '05 #4

P: n/a

"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
Jul 21 '05 #5

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

Jul 21 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.