Really need some help on this

  • Thread starter Thread starter Michael S. Kolias
  • Start date Start date
M

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
 
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 said:
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
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Jeph Axxe said:
Why is that ?

Truncated
Click to expand...

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
 
Thanx for the tips everyone. really appreciate it.

Michael S. Kolias said:
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
Click to expand...
 
Be careful about cross posting, it annoys quite a few people that
frequent these news groups.


Its silly, but it took me a while to find the syntax to add a param and
set its value in one line

This call returns a reference to the newly created param....
oCmd.Parameters.Add("@username", SqlDbType.VarChar, 16)

So you can type :
oCmd.Parameters.Add("@username", VarChar, 16).Value = myValue

HTH,
-eric
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
Click to expand...
 
Back
Top