Solved

How to add Parameters to a SQL INSERT statement

Posted on 2007-11-30
5
1,652 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 200 total points
ID: 20384632
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
ID: 20384742


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
ID: 20386672
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
ID: 20397083
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
ID: 31412000
Thank you.  I see what I did wrong.  The devil is in the details, isn't it?
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

635 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