ADO Stored Procedure Parameter Error With DB2
Jul 1, 2007
Hey all,
I ran into a little snag when writing a VBScript app for work that parses a text file and places that data into a DB2 V8.1 database via OLE DB ADO calls and a stored procedure with parameters.
I have found that I can call a stored procedure w/o parameters but as soon as I one that takes some I get the following error.
"arguments are of the wrong type, are out of acceptable range"
To try to troubleshoot it, I tried applying each parameter property individually. I noticed that when I didn't provide a direction, I received a different error (Don't have the exact one but it had something to do with an IBM OLE DB property error).
Because the VBScript error was on the line (mycmd.commandtype = adCmdStoredProc) I replaced the global constant with a literal (IE: 4). No fix.
I'm going to try to replace all of the global constants with their literal values but I also found this on the IBM site...
1: SQLProcedureColumns is used by many applications to
determine the direction of stored procedure parameters. The
ODBC driver is returning an ordinal of 1 for all the
parameters.
This causes problems with applications that use this
information. For example a stored procedure call using
Microsoft ActiveX Data Objects (ADO) and the adCmdStoredProc
syntax will return one of the following error messages:
3265 Item cannot be found in the collection corresponding to the
requested name or ordinal.
3001 Arguments are of the wrong type, are out of acceptable
range, or are in conflict with one another.
SQLProcedureColumns is also returning a "BUFFER_LENGTH" value
for numeric data types that is 1 byte too large. The default C
data type is character so this value should be display size
-without an extra byte for null.
I'm guessing this is the solution to my issue but I figured I'd put this out there just in case I was missing something.
If you have any other suggestions, please chime in.
View 5 Replies
ADVERTISEMENT
Jul 1, 2004
Hi,
I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@dtmWarrantyStart', which was not supplied" even though I supplied it in the code
which is
Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@dtmWarrantyStart", SqlDbType.datetime, 8)
ParamdtmWarrantyStart.Value = dtmWarrantyStart
myCommand.Parameters.Add(ParamdtmWarrantyStart)
below is the stored procedure.
create Proc sp_AddBoard(@BrandID int,
@strPcName varchar(50),
@bitAccounted bit,
@dtmAccounted datetime,
@dtmWarrantyStart datetime,
@dtmWarrantyEnd datetime,
-- @strDescription varchar(500),
@intStatus int,
@ModelNo varchar(50),
@intMemorySlots int,
@intMemSlotTaken int,
@intAgpSlots int,
@intPCI int,
@bitWSound bit,
@bitWLan bit,
@bitWVideo bit,
@dtmAcquired datetime,
@stat bit output,
@intFSB int) as
if not exists(select strPcName from tblBoards where strPcName=@strPcName)
begin
insert into tblBoards
(BrandID, strPcName, bitAccounted,
dtmAccounted, dtmWarrantyStart,
dtmWarrantyEnd, --strDescription,
intStatus,
ModelNo, intMemorySlots, intMemSlotTaken,
intAgpSlots, intPCI, bitWLan,
bitWVideo, dtmAcquired,intFSB,bitWSound)
values
(@BrandID,@strPcName,@bitAccounted,
@dtmAccounted,@dtmWarrantyStart,
@dtmWarrantyEnd,--@strDescription,
@intStatus,
@ModelNo,@intMemorySlots,@intMemSlotTaken,
@intAgpSlots,@intPCI,@bitWLan,
@bitWVideo,@dtmAcquired,@intFSB,@bitWSound)
end
else
begin
set @stat=1
end
The table is also designed to accept nulls on that field but still same error occured.
Please help
View 1 Replies
View Related
Sep 18, 2014
Stored procedure displays the follwing error.
Procedure or function 'getFavoriteList' expects parameter '@category_name', which was not supplied.
I have code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getFavoriteList]
@customer_id int,
@category_name varchar(200) OUTPUT
[Code] ....
View 6 Replies
View Related
Dec 7, 2007
I keep getting this error on one of my pages when trying to do an update to the table via a stored procedure:
*System.Data.SqlClient.SqlException: Procedure or function 'sp_u_contractor_references' expects parameter '@p_contractorrfid', which was not supplied
I have verified that I am adding this parameter and that the spelling is correct. I have also made sure that the value is not NULL.
I'm confused as to what else the problem could be. Any ideas?
View 5 Replies
View Related
Sep 19, 2005
Can anyone explain what's wrong with this code which is supposed to populate a dropdownlist using a parametised stored procedure and SqlDataAdapter?
objCmd = new SqlCommand("bs_GetActivityValueTypes", objConn.GetConnection()); objCmd.Parameters.Add("@scid", SqlDbType.Int);objCmd.Parameters["@scid"].Value = Convert.ToInt32(Request.QueryString["scorecard"].ToString()); objAdapter = new SqlDataAdapter(objCmd);objAdapter.Fill(dsInitData, "tblValueTypes");
comboResultType.DataSource = dsInitData.Tables["tblValueTypes"]; comboResultType.DataValueField = "Type_ID";comboResultType.DataTextField = "Type_Desc";comboResultType.DataBind(); comboResultType.Items.Insert(0, new ListItem("- Select a value type -", ""));Basically, if I remove the parameter from the code and the stored procedure it works fine, but when I add the parameter back I get an "Incorrect syntax near 'bs_GetActivityValueTypes'" error at line: objAdapter.Fill(dsInitData, "tblValueTypes"); This makes no sense to me because I know the stored procedure is well formed and I've used almost identical code elsewhere. All variables are declared further up the code. Someone help please!
View 3 Replies
View Related
Dec 19, 2007
Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:
CREATE PROCEDURE spprodsales
@productid int
AS
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
RETURN
And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :
select SalesOrderID,OrderQty,UnitPrice,ProductID
FROM Sales.SalesOrderDetail
Where ProductID IN (@productid)
So, can anyone please help me out possibly using the same stored procedure as above.
Thanks,
Kripa
View 5 Replies
View Related
Jan 29, 2015
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
View 9 Replies
View Related
Feb 19, 2007
Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
View 3 Replies
View Related
Jul 2, 2007
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
View 1 Replies
View Related
Mar 26, 2007
Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
Thanks Nickdel68
View 5 Replies
View Related
Feb 12, 2008
I have this update procedure that updates and encrypts a credit card column. I added the pass phrase to a table and now I'm trying to use that pass phrase in my update procedure.
When I run the update from the app the error is that the procedure is not getting the @Phrase parameter.
Can anyone see why this might be?ALTER PROCEDURE [dbo].[UpdatePayment]
@paymentID int,
@PaymentDate datetime,
@TenderTypeID int,
@PaymentAmount money,
@CreditCardType int,
@ExpirationDate datetime,
@ccNumber nvarchar(50),
@Phrase nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
SET @Phrase = (SELECT Phrase FROM tblSpecialStuff)
UPDATE tblReceipts SET
PaymentDate=@PaymentDate,
TenderTypeID=@TenderTypeID,
AmountPaid=@PaymentAmount,
CreditCardType=@CreditCardType,
ExpirationDate=@ExpirationDate,
ccNumber = (CASE
WHEN @CreditCardType > 0 THEN
EncryptByPassPhrase(@Phrase,@ccNumber)
ELSE @ccNumber
END)
END
View 1 Replies
View Related
Dec 23, 2003
hi,
i need to run a stored procedure from an asp page that will import data into a temporary table.
i am having trouble with passing the parameter from the Exec command (currently developing in QA). It works ok where i want to use a value such as 'PC', or 'printer' or 'laptop' where the import sql has a where clause of '... = <<parameter>>...'.
I want however to be able to pass it a value such as ['printer','laptop','pc']so that the procedure will collect the data into the temp table with an '...in <<parameter>>'
this falls over and retrieves zero rows. the parameter is passed to the sp correctly as i have used a print command to display it, it comes back as 'pc',printer','laptop'. It appears to be the ' in ' that is not parsing the parameter.
can anyone help please?
TIA
View 3 Replies
View Related
Sep 3, 1999
Hello!
I have a problem when i want to construct a stored procedure. I want to pass a parameter with customerid's. The problem is that I donīt know how many customerId's I send to the stored procedure, because the user select the customerId's from a selectbox. The SQL string I want to excute looks like this
SELECT CustomerName FROM Customer WHERE CustomerId IN (199, 301, 408... )
How should I create the stored procedure so I put the customerId values in a parameter, like the procedure below? (I know that this not will work)
CREATE PROCEDURE rpt_PM2000_test(@SCustomerId varChar)
AS
BEGIN
SELECT Customer
FROM Customer
WHERE CustomerId IN (@sCustomerId)
END
/Fredrik
View 3 Replies
View Related
Apr 22, 2007
In the Create portion of a stored procedure, does the line in bold mean
that if the parameter @RegoDate is not provided, then use the current datetime?
CREATE PROCEDURE spGetRegoDetail
@Owner nvarchar(20),
@RegoDate datetime = getdate
AS ...
Thanks in advance,
G11DB
View 2 Replies
View Related
Mar 4, 2004
Hi,
I have an Access front end/MSDE2000 backend system. There is a form that has a subform which is based on the results of a stored procedure. Both my subform and mainform contain intOrderID ( which is the field i want to link both of them with ).
I would like to have the subform's data updated when changing records in the mainform. In Access, this was simple ( link child and master fields ), but not with MSDE2000 as a back end ( ADO connection thing...)
I think i have to create something to filter the results of the stored procedure, but i dont like the fact the the entire dataset needs to be transmitted over the network ( most of the time, users will only be creating new records, or reviewing recently created ones )
I'm really not sure as to how to accomplish this... Any ideas?
thanks!
View 10 Replies
View Related
Jun 16, 2007
HiI'm trying to alter my stored procedure to take a parameter for theDatabase Name, but as usual the syntax is killing me.Thanks for any helpDennis'--------------------------------------------------------------------------*--------------------------------Before - This Works without a paramater'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE [MyClientDatabase] ON PRIMARY( NAME = N'MyClientDatabase',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'MyClientDatabase_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAMyClientDatabase_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_AS'--------------------------------------------------------------------------*--------------------------------After - This Doesn't work with a parameter'--------------------------------------------------------------------------*--------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_CreateNewClientDb]ASCREATE DATABASE @ClientDBName ON PRIMARY( NAME = N@ClientDBName,FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '.mdf' ,SIZE = 11264KB ,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB )LOG ON( NAME = N'@ClientDBName' + '_log',FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATA@ClientDBName' + '_log.ldf' ,SIZE = 1024KB ,MAXSIZE = 2048GB ,FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASMsg 102, Level 15, State 1, Procedure sp_CreateNewClientDb, Line 4Incorrect syntax near '@ClientDBName'.
View 5 Replies
View Related
Dec 7, 2007
Hi all,
I'm trying to create a SP with a parameter for server.
CREATE PROCEDURE dbo.sp_Testing
@server ???????
AS
SELECT * from @server
GO
Does anyone know how to do what i'm trying here..???
I don't know the type of the parameter...is it just simply server? or is there another specific name for it???
thanks,
View 6 Replies
View Related
Apr 27, 2007
I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e)
{
/*variables need for the update of the old staff record
and the creation of the new staff record.
*/
BMSUser bmsUser = (BMSUser)Session["bmsUser"];
int staffid = Convert.ToInt32(bmsUser.CmsStaffID);
int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]);
string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text);
int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value);
SqlConnection conn = new SqlConnection(connect);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_BMS_MoveStaffMember";
cmd.Parameters.Add("@OldStaffProfileID", SqlDbType.Int);
cmd.Parameters["@OldStaffProfileID"].Precision = 9;
cmd.Parameters["@OldStaffProfileID"].Scale = 0;
cmd.Parameters["@OldStaffProfileID"].Value = oldStaffProfileID;
cmd.Parameters.Add("@NewManagerProfileID", SqlDbType.Int);
cmd.Parameters["@NewManagerProfileID"].Precision = 9;
cmd.Parameters["@NewManagerProfileID"].Scale = 0;
cmd.Parameters["@NewManagerProfileID"].Value = newMangerProfileID;
cmd.Parameters.Add("@LastModifiedBy", SqlDbType.Int);
cmd.Parameters["@LastModifiedBy"].Precision = 9;
cmd.Parameters["@LastModifiedBy"].Scale = 0;
cmd.Parameters["@LastModifiedBy"].Value = staffid;
SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sp);
lbl_ERROR.Text = Convert.ToString(sp);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
conn.Close();
My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null.
Here is the parts of my stored proc returning the value...
DECLARE @OLDManagerReturn numeric(9)
SELECT @OLDManagerReturn = ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear
RETURN @OLDManagerReturn
View 5 Replies
View Related
Jun 19, 2007
In the SqlDbType enumeration there is no value for the new (max) types, only varchar. If Im passing a large string, it will get cut off at 8K. So how do I specify my varchar parameter as being of the max type ?
View 1 Replies
View Related
Jul 20, 2007
Hi,This works:CREATE PROCEDURE MyProc @Date smalldatetime = '2005-01-01'AS... But this does not CREATE PROCEDURE MyProc @Date smalldatetime = GETDATE()AS... I'm talking about sql2005. Can anyone help how to overcome this?
View 3 Replies
View Related
Aug 2, 2007
How do I assign a temp value to a parameter in a stored procedure?
I am trying like so:
Alter PROCEDURE ap_Insert_Pricing_ListPrice @partNumber varchar = "CQ2"AS SELECT PartNumber FROM Pricing WHERE (PartNumber = @partNumber)GO
What is the correct way to assign a temp value for testing purposes in SQL Server 2000 Query Analyzer?
When I try:
Alter PROCEDURE ap_Insert_Pricing_ListPrice @partNumber varchar
AS
Set @partNumber = "10-AF40-N04B-JZ"SELECT PartNumber FROM Pricing WHERE (PartNumber = @partNumber)GO
I get error:
Invalid column name '10-AF40-N04B-JZ'.
View 2 Replies
View Related
Aug 19, 2007
I have two stored procedures one generates an output parameter that I then use in the second stored procedure.
1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4
5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14
15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut"
17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20
21
22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " "
30 myCommand.Parameters("@IDFieldOut").Value = 031
32 myCommand.ExecuteNonQuery()33
34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35
If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas
View 6 Replies
View Related
Feb 3, 2008
I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record. The procedure works fine, but I'm unable to get that return value in my code. Here's what I have:
IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL
DROP PROCEDURE dbEvent
GO
CREATE PROCEDURE
@Name varchar(200)
,@Location varchar(200)
AS
INSERT INTO Event
(
NAME
, LOCATION
)
VALUES
(
NAME
, @LOCATION
)
SELECT SCOPE_IDENTITY()
And my code behind: public Int64 Insert(SqlConnection Conn)
{
try
{
using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output;
Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName;
Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location;
if (Conn.State != ConnectionState.Open) Conn.Open();
Command.ExecuteNonQuery();
Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString());
return _requestId;
}
I'm getting the error that I have "Too many arguments specified" in my Insert() method. When I test the procedure in Query Analyzer, it works fine and returns the correct value.
Any suggestions? Don't I need to declare that return value in my .NET code as an output parameter?
View 2 Replies
View Related
Dec 9, 2003
This works:
WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN ('2003','2004','2005'))
This doesn't:
WHERE
WHERE ltrim(str((DATEPART(yyyy, dbo.Media_Tracking_Ad_History.ADDATE))) IN (@strYears))
@strYears will work if I only pass a single value such as 2003. I've tried every combination of single and double quotes I can think of to pass multiple values but nothing works. Any suggestions?
View 4 Replies
View Related
Mar 31, 2004
Hey huys, I got this stored procedure. First of all: could this work?
--start :)
CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0'
AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values
(@EmployeeID, @loginType, @domainUserID)
GO
--end :)
then I got this VB.Net code in my ASP.net page....
---begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed
End Sub
---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
View 2 Replies
View Related
Apr 19, 2004
I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI
The syntax I am trying to use is:
MyStoredProcedure.Parameters.Add(New SqlParameter("@Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@pdi").value = -1
When I do my ExecuteNonQuery I get error 8114
What am I doing wrong?
View 9 Replies
View Related
Jun 22, 2005
Hi all,
this is an easy one for those who know.
Why do you use two '@' symbols in front of a variable in a stored procedure?
Thanks
Tryst
View 8 Replies
View Related
Aug 1, 2005
What i want is to add by parameter a Where clause and i can not find how to do it!CREATE PROCEDURE [ProcNavigate]( @id as int, @whereClause as char(100))ASSelect field1, field2 from table1 Where fieldId = @id /*and @WhereClause */GOany suggestion?
View 1 Replies
View Related
Sep 19, 2005
Hi I have an if clause in my code to add the final parameter value to send to the database.
If Page.User.IsInRole("MICMS") Then
cmdCheckUser.Parameters.Add("@C_ID", 0)
Else
cmdCheckUser.Parameters.Add("@C_ID", Session("C_ID"))
End If
If the user is in the role, the error is triggered saying that @C_ID
is expected by the stored procedure. If i then change the value from 0
to 10, the stored procedure works fine.Is there any reason that the stored procedure is failing when the value 0 is used and not when any other value is used?Thanking you in advance.
View 1 Replies
View Related
Mar 9, 2006
How can I pass a parameter to a stored procedure using Visual Web Developer 2005? I have created a SQLDataSource that calls the SP.
Thanks
--R
View 1 Replies
View Related
Apr 20, 2006
I have a SQL Server 2005 Stored Procedure that takes a parameter to sort on:
-----------------------------------------------------------------
ALTER PROCEDURE [dbo].[GetAllApplications_Sorted]
@sortType varchar(25)
AS
SET NOCOUNT ON;
SELECT aev.ApplicationID,
aev.ApplicationName,
aev.EventMessage,
aev.MachineID,
aev.MachineName,
aev.EventLevel,
ellu.EventImg,
ellu.EventLevelID,
dbo.GetMaxEventLevelByApplicationID(aev.ApplicationID) AS maxEventLevel
FROM dbo.ApplicationEventView aev, dbo.EventLevelLookUp ellu
WHERE ellu.EventLevelID=dbo.GetMaxEventLevelByApplicationID(aev.ApplicationID)
AND aev.EventLevel=ellu.EventLevel
ORDER BY ellu.EventLevelID + ' ' + @sortType
-----------------------------------------------------------------
Problem is I keep getting this error:
Conversion failed when converting the varchar value 'desc' to data type int.
And I can't figure out why...
Thanks,
Doug
View 1 Replies
View Related
Jun 2, 2006
If you have a table called Person and has ID, Age, Gender and Name. And you have a stored procedure:
CREATE PROCEDURE [dbo].[GetName]@Age TINYINT,@Gender TINYINTASBEGIN SELECT Name FROM Person WHERE Age = @Age AND Gender = @Gender END
You want some times to send @Age as null to get all ages (or in other words, you are not concerned with the age) or @Gender as null to get all genders.
A way to do that is to build your query string dynamically and execute it with EXEC command. However, this is a bad solution because you are losing most of the SP benefits and writting longer and more complicated procedures, specially that you have a lot of parameters!
Another solution that I thought of is having your select like this: SELECT Name FROM Person WHERE Age = ISNULL(@Age,Age) AND Gender = ISNULL(@Gender, Gender)
However, I noticed that when the value of the column is NULL, you will get false results, obviously because you are using = operator in comparing a value with null while you should be using IS operator, so this method did not work.
I am wondering if any one has a good solution for this.
Adam Tibi
View 2 Replies
View Related
Jun 13, 2006
This should be relatively easy but for some reason it isn't. I'm trying to simply add parameters to a stored procedure that performs a simple input and I can't do it... I keep getting an error that the parameters are not found when I am explicitly stating them. I could do this with VB ASP.NET 1.x but with all these radical changes with 2.0, I'm pulling my hair out.... I can get to work if I declare a sqlStatement in the code but don't want to go that route (but will if there is no other choice) Any help would be great:
Code:
Dim cmd As New SqlDataSource
cmd.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
cmd.InsertParameters.Add("@firstName", txtFirstName.Text)
cmd.InsertParameters.Add("@lastName", txtLastName.Text)
cmd.InsertParameters.Add("@address1", txtAddress1.Text)
cmd.InsertParameters.Add("@address2", txtaddress2.Text)
cmd.InsertParameters.Add("@city", txtCity.Text)
cmd.InsertParameters.Add("@state", ddlState.SelectedItem.Value)
cmd.InsertParameters.Add("@zipCode", txtZipCode.Text)
cmd.InsertParameters.Add("@telephone", txtTelephone.Text)
cmd.InsertParameters.Add("@email", txtEmail.Text)
cmd.InsertParameters.Add("@agegroup", ddlAgeGroup.SelectedItem.Value)
cmd.InsertParameters.Add("@birthday", txtBirthday.Text)
cmd.InsertParameters.Add("@emailnotification", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@magazine", rbEmail.SelectedItem.Value)
cmd.InsertParameters.Add("@question", txtquestion.Text)
cmd.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"
cmd.InsertCommand = "sp_insertCustomer"
cmd.Insert()
Stored Procedure:
CREATE PROCEDURE dbo.sp_insertCustomer @firstName nchar(30),@lastName nchar(30),@address1 nchar(50),@address2 nchar(50),@city nchar(30),@state nchar(2),@zipcode nchar(10),@telephone nchar(10),@email nchar(50),@ageGroup int,@birthday dateTime,@emailNotification int,@magazine int,@question varchar(1000)
AS
INSERT tblCustomer
(firstName,lastName,address1,address2,city,state,zipCode,telephone,email,ageGroup,birthday,emailNotification,magazine,question)
Values(@firstName,@lastName,@address1,@address2,@city,@state,@zipcode,@telephone,@email,@ageGroup,@birthday,@emailNotification,@magazine,@question)
ERROR:
Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'sp_insertCustomer' expects parameter '@firstName', which was not supplied.
Source Error:
Line 24: cmd.InsertCommand = "sp_insertCustomer"Line 25: Line 26: cmd.Insert()Line 27: Line 28:
View 5 Replies
View Related
