Really need some help on this

  • Thread starter Michael S. Kolias
  • 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
 
G

Guest

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...
 
W

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 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...
 
F

Fredrik Wahlgren

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
 
M

Michael S. Kolias

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...
 

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

Need help with code on website (noob) 2
Need Help With Custom Profile Providers 0
Error Message could not find a non-generic method 6
SQL Output Paramter problem 1
Interesting Stored Procedure Problem.. 3
Only one table shows up with the information 2
Pass UserID instead of Username to other pages after logged on, ASP.NET 2.0 1
help please on insert statement into Access from webpage ! 0

Members online

Total: 37 (members: 2, guests: 35)
Robots: 442

Forum statistics

Threads
473,767
Messages
2,569,573
Members
45,046
Latest member
Gavizuho

Latest Threads

Top