How to pass sqlparameter from front end to sqlserver2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Respected sir,
i am using vb.net as front end and sqlserver2008 as backend
and i m using stored procedure to send data. as follows

VB

 Dim param(10) As SqlParameter
 param(0) = New SqlParameter("@Company_Name", _Company_Name)
 param(1) = New SqlParameter("@Flat", _Flat)
 param(2) = New SqlParameter("@Area", _Area)
 param(3) = New SqlParameter("@Street", _Street)
 param(4) = New SqlParameter("@City", _City)
 param(5) = New SqlParameter("@State", _State)
 param(6) = New SqlParameter("@Pin", _Pin)
 param(7) = New SqlParameter("@Phone", _Phone)
 param(8) = New SqlParameter("@Fax", _Fax)
 param(9) = New SqlParameter("@E_Mail", _Email)
 param(10) = New SqlParameter("@Web_Site", _Web)

objdal.ExecuteDatasetOnly("SP_I_Vendors", param)

and stored procedure as follows

SQL

alter PROC SP_I_Vendors
(
	@Company_Name nvarchar(100),
	@Flat nvarchar(30),
	@Area nvarchar(30),
	@Street nvarchar(30),
	@City nvarchar(30),
	@State nvarchar(30),
	@Pin nvarchar(10),
	@Phone nvarchar(20),
	@Fax nvarchar(20),
	@E_Mail nvarchar(30),
	@Web_Site nvarchar(20),
	@ContactPerson nvarchar(20)
	)
AS
BEGIN
 INSERT INTO Vendors
  (
   Company_Name,Flat,Area,Street,City,State,Pin,
   Phone,Fax,E_Mail,Web_Site,ContactPerson,CreatedOn
   )
  VALUES
  (
      @Company_Name,@Flat,@Area,@Street,@City,@State,@Pin,
	@Phone,@Fax,@E_Mail,@Web_Site,@ContactPerson,GETDATE()
   )
end

but i got error at front side that,
"Procedure or function SP_I_Vendors expect parameter @Company_Name which was not supplied"

when i cross check with removing first column(@Company_Name)from the sqlquery still i get same error with
"Procedure or function SP_I_Vendors expect parameter @Flat which was not supplied"
means first parameter shows error everytime..

I have been trying to solve above problem since 2 days but not getting solution..
Please help me...
Thank you,

Posted 20-Oct-13 8:12am

Sunil Bansode

Add a Solution

Comments

Ranjan.D 20-Oct-13 17:46pm

There could be some issue with ExecuteDatasetOnly. Did you write any custom code? Did you tried doing single step debug?

Sunil Bansode 21-Oct-13 0:49am

thank you sir..for reply me..
Yes i have tried single step debug too..but as soon as cmd.ExecuteNonQuery() statement debug that time above error comes.

I have paste code for more information of function

Public Function ExecuteDatasetOnly(ByVal SpName As String, ByVal parameters() As SqlParameter) As Integer
Try

Call OpenConnection()
ds = New DataSet

cmd.CommandText = SpName
cmd.CommandType = CommandType.Text
Add_Parameters_In_Command(parameters)
Return cmd.ExecuteNonQuery()

Catch ex As Exception
Throw ex
Finally
CloseConnection()
End Try

End Function

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Accepted Answer · 2013-10-20T11:50:00

Well,

USE [cpqaAnswers]
GO

CREATE TABLE [cpqa].[tbl_SB_Vendors](
    Company_Name nvarchar(100),
	Flat nvarchar(30),
	Area nvarchar(30),
	Street nvarchar(30),
	City nvarchar(30),
	State nvarchar(30),
	Pin nvarchar(10),
	Phone nvarchar(20),
	Fax nvarchar(20),
	E_Mail nvarchar(30),
	Web_Site nvarchar(20),
	ContactPerson nvarchar(20),
	CreatedOn nvarchar(666)
	)

CREATE PROC [cpqa].[sp_SB_SP_I_Vendors]
(
	@Company_Name nvarchar(100),
	@Flat nvarchar(30),
	@Area nvarchar(30),
	@Street nvarchar(30),
	@City nvarchar(30),
	@State nvarchar(30),
	@Pin nvarchar(10),
	@Phone nvarchar(20),
	@Fax nvarchar(20),
	@E_Mail nvarchar(30),
	@Web_Site nvarchar(20),
	@ContactPerson nvarchar(20),
	@CreatedOn nvarchar(666)
	)
AS
BEGIN
 INSERT INTO [cpqa].[tbl_SB_Vendors]
  (
   Company_Name,Flat,Area,Street,City,[State],Pin,
   Phone,Fax,E_Mail,Web_Site,ContactPerson,CreatedOn
   )
  VALUES
  (
      @Company_Name,@Flat,@Area,@Street,@City,@State,@Pin,
	@Phone,@Fax,@E_Mail,@Web_Site,@ContactPerson,GETDATE()
   )
end

EXECUTE [cpqa].[sp_SB_SP_I_Vendors] 'a', 'b', 'c', 'd' ,'e' ,'f', 'g', 'h', 'i', 'j' ,'k', 'l', 'm'

SELECT [Company_Name]
      ,[Flat]
      ,[Area]
      ,[Street]
      ,[City]
      ,[State]
      ,[Pin]
      ,[Phone]
      ,[Fax]
      ,[E_Mail]
      ,[Web_Site]
      ,[ContactPerson]
      ,[CreatedOn]
  FROM [cpqaAnswers].[cpqa].[tbl_SB_Vendors]
GO

Seems to work, so ....

Company_Name	Flat	Area	Street	City	State	Pin	Phone	Fax	E_Mail	Web_Site	ContactPerson	CreatedOn
a	b	c	d	e	f	g	h	i	j	k	l	Oct 20 2013  2:43PM