Solved

How to add Parameters to a SQL INSERT statement

Posted on 2007-11-30
5
1,638 Views
Last Modified: 2010-04-21
I have a Stored Procedure I'm trying to use to add records from an ASP.Net 2.0/VB webform into a SQL Server 2000 database.

This seems to be working (no errors so far):
        cmdTest.Parameters.Add(New SqlParameter("@txtContactContract", Data.SqlDbType.VarChar, 10))
        cmdTest.Parameters("@txtContactContract").Value = txtContactContractTitle.Text

but I want to shorten it to something like:
cmdTest.Parameters.Add(New SqlParameter("@txtContactContract", SqlDbType.VarChar).Value = txtContactContract.Text)

but I get this error:
The SqlParameterCollection only accepts non-null SqlParameter type objects, not Boolean objects.

What did I do wrong?

ALTER Procedure sp_SYEP_Employers_NewRecord

		@txtEmployerName varchar(50), 

		@ddlOrganizationType varchar(50), 

		@txtFedID varchar(50), 

		@txtContactContract varchar(50), 

		@txtContactContractTitle varchar(50), 

		@txtCompanyStreet varchar(50), 

		@txtCompanyCity varchar(50), 

		@txtCompanyState varchar(50), 

		@txtCompanyZip varchar(50), 

		@txtCompanyContractContactTelephone varchar(50), 

		@txtCompanyContractContactFAX varchar(50), 

		@txtCompanyContractContactCell varchar(50), 

		@txtCompanyContractContactEmail varchar(50), 

		@txtContactProgram varchar(50), 

		@txtContactProgramTitle varchar(50), 

		@txtContactProgramAddress varchar(50), 

		@txtContactProgramCity varchar(50),	

		@txtContactProgramState varchar(50), 

		@txtContactProgramZip varchar(50), 

		@txtContactProgramPhone varchar(50), 

		@txtContactProgramFAX varchar(50),

		@txtContactProgramCell varchar(50), 

		@txtContactProgramEmail varchar(50), 

		@txtWorksitesRequestedNumber varchar(50), 

		@ddlOrgHasContract varchar(50), 

		@ddlConfirmUnderstand varchar(50)

 

AS

 

SET NOCOUNT ON

 

INSERT Employers(

	txtEmployerName, ddlOrganizationType, txtFedID, txtContactContract, txtContactContractTitle, 

	txtCompanyStreet, txtCompanyCity, txtCompanyState, txtCompanyZip, txtCompanyContractContactTelephone, 

	txtCompanyContractContactFAX, txtCompanyContractContactCell, txtCompanyContractContactEmail, 

	txtContactProgram, txtContactProgramTitle, txtContactProgramAddress, txtContactProgramCity, 

	txtContactProgramState, txtContactProgramZip, txtContactProgramPhone, txtContactProgramFAX, 

	txtContactProgramCell, txtContactProgramEmail, txtWorksitesRequestedNumber, ddlOrgHasContract, 

	ddlConfirmUnderstand, ApplicationDateEmployer

	) 

VALUES (@txtEmployerName, @ddlOrganizationType, @txtFedID, @txtContactContract, @txtContactContractTitle, 

	@txtCompanyStreet, @txtCompanyCity, @txtCompanyState, @txtCompanyZip, @txtCompanyContractContactTelephone, 

	@txtCompanyContractContactFAX, @txtCompanyContractContactCell, @txtCompanyContractContactEmail, 

	@txtContactProgram, @txtContactProgramTitle, @txtContactProgramAddress, @txtContactProgramCity,	

	@txtContactProgramState, @txtContactProgramZip, @txtContactProgramPhone, @txtContactProgramFAX,

	@txtContactProgramCell, @txtContactProgramEmail, @txtWorksitesRequestedNumber, @ddlOrgHasContract, @ddlConfirmUnderstand,getdate()

	)

 

Select	SCOPE_IDENTITY() priKeyEmployers

 

RETURN

Open in new window

0
Comment
Question by:megnin
5 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 200 total points
Comment Utility
That should be:
cmdTest.Parameters.Add(New SqlParameter("@txtContactContract", SqlDbType.VarChar)).Value = txtContactContract.Text

0
 
LVL 10

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 100 total points
Comment Utility


http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_22942748.html


this is the solution which i gave before some time

here questios is some what different but the solution would be same for your problem

thanx
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 200 total points
Comment Utility
it should be

Parameters.Add returns a type of Parameter, so you can use it directly
        cmdTest.Parameters.Add(New SqlParameter("@txtContactContract", Data.SqlDbType.VarChar, 10))
like
        cmdTest.Parameters.Add(New SqlParameter("@txtContactContract", Data.SqlDbType.VarChar, 10)).Value = txtContactContractTitle.Text
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
Thank you very much MikeToole and imitchie!  
Conceptinfotech, thank you for your comment also, but since I am just learning, the direct answers were much more helpful.
0
 
LVL 1

Author Closing Comment

by:megnin
Comment Utility
Thank you.  I see what I did wrong.  The devil is in the details, isn't it?
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now