I'm stumped at this point and I'm tired of trying things so I'mposting in hopes of some guru with a sharp eye. I have anasp.net app running on a local Win XP Pro box. Within the app,I call a SPROC and pass the info to the SQL box and it hiccups. I'll drop VS.NET into debug mode and step through the SPROCcreation and copy those values into Query Analyzer to get alittle more info. When I run the Query Analyer that data worksjust fine. What?! Here is the relevant info:
------ASP.NET CODE---------------------
Dim parameterCustID As SqlParameter = NewSqlParameter("@customerID", SqlDbType.Int, 4)
parameterCustID.Value = customerID
orderCommand.Parameters.Add(parameterCustID)
Dim parameterDateOrdered As SqlParameter = NewSqlParameter("@dateOrdered", SqlDbType.DateTime, 8)
parameterDateOrdered.Value = Today
orderCommand.Parameters.Add(parameterDateOrdered)
Dim parameterProductOrdered As SqlParameter = NewSqlParameter("@productOrdered", SqlDbType.VarChar, 50)
parameterProductOrdered.Value = productOrdered
orderCommand.Parameters.Add(parameterProductOrdere d)
Dim parameterQuantity As SqlParameter = NewSqlParameter("@quantity", SqlDbType.SmallInt, 2)
parameterQuantity.Value = CInt(quantity)
orderCommand.Parameters.Add(parameterQuantity)
Dim parameterCost As SqlParameter = New SqlParameter("@cost",SqlDbType.SmallMoney, 4)
parameterCost.Value = price
orderCommand.Parameters.Add(parameterCost)
Dim parameterComments As SqlParameter = NewSqlParameter("@comments", SqlDbType.NVarChar, 255)
parameterComments.Value = Trim(txtComments.Text)
orderCommand.Parameters.Add(parameterComments)
Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum",SqlDbType.VarChar, 50)
parameterCCNum.Value = Trim(txtCardNumber.Text)
orderCommand.Parameters.Add(parameterCCNum)
Dim parameterExpDate As SqlParameter = NewSqlParameter("@ExpDate", SqlDbType.VarChar, 50)
parameterExpDate.Value = expDate
orderCommand.Parameters.Add(parameterExpDate)
Dim parameterCCV As SqlParameter = New SqlParameter("@CCV",SqlDbType.VarChar, 3)
parameterCCV.Value = Trim(Issue.Text)
orderCommand.Parameters.Add(parameterCCV)
Dim parameterorderID As SqlParameter = NewSqlParameter("@orderID", SqlDbType.Int, 4)
parameterorderID.Direction = ParameterDirection.Output
orderCommand.Parameters.Add(parameterorderID)
orderCommand.ExecuteNonQuery()
' Return the OrderID
Dim orderID As Integer = parameterCustomerID.Value
--------------------------
-------SPROC in SQL 2K-------------------
CREATE Procedure addOrder
(
@customerID int,
@dateOrdered datetime,
@productOrdered varchar(50),
@quantity smallint,
@cost smallmoney,
@comments nvarchar(255),
@CCNum varchar(50),
@ExpDate varchar(50),
@CCV varchar(3),
@OrderID int OUTPUT
)
AS
/* Create the Order header */
INSERT INTO psiOrders
(
customerID,
dateOrdered,
productOrdered,
quantity,
cost,
comments,
CCNum,
ExpDate,
CCV
)
VALUES
(
@customerID,
@dateOrdered,
@productOrdered,
@quantity,
@cost,
@comments,
@CCNum,
@ExpDate,
@CCV
)
SELECT
@OrderID = @@Identity
GO
--------------------------
-----Query Analyzer Data---------------------
INSERT INTO psiOrders
(
customerID,
dateOrdered,
productOrdered,
quantity,
cost,
comments,
CCNum,
ExpDate,
CCV
)
VALUES
(
40,
9/22/2004,
'SSK Scooter Kit',
1,
$ 99.00,
'',
'5555555555555555',
'06-2007',
'182'
)
SELECT
@@Identity
GO
--------------------------
Any ideas?
--------------------------------
From: Chad Micheal Lawson
-----------------------
Posted by a user from .NET 247 ( http://www.dotnet247.com/)
<Id>BqE+WdnJs0uNaGpvrOAVZw==</Id> 4  1805 
When you declared the command object did you set the CommandType =
CommandType.StoredProcedure?
Also you may want to run a sqltrace and watch Stored Procedure class.
What's the exact error and line that it fails on?
"Chad Micheal Lawson via .NET 247" wrote: I'm stumped at this point and I'm tired of trying things so I'm posting in hopes of some guru with a sharp eye. I have an asp.net app running on a local Win XP Pro box. Within the app, I call a SPROC and pass the info to the SQL box and it hiccups. I'll drop VS.NET into debug mode and step through the SPROC creation and copy those values into Query Analyzer to get a little more info. When I run the Query Analyer that data works just fine. What?! Here is the relevant info:
------ASP.NET CODE---------------------
Dim parameterCustID As SqlParameter = New SqlParameter("@customerID", SqlDbType.Int, 4)
parameterCustID.Value = customerID
orderCommand.Parameters.Add(parameterCustID)
Dim parameterDateOrdered As SqlParameter = New SqlParameter("@dateOrdered", SqlDbType.DateTime, 8)
parameterDateOrdered.Value = Today
orderCommand.Parameters.Add(parameterDateOrdered)
Dim parameterProductOrdered As SqlParameter = New SqlParameter("@productOrdered", SqlDbType.VarChar, 50)
parameterProductOrdered.Value = productOrdered
orderCommand.Parameters.Add(parameterProductOrdere d)
Dim parameterQuantity As SqlParameter = New SqlParameter("@quantity", SqlDbType.SmallInt, 2)
parameterQuantity.Value = CInt(quantity)
orderCommand.Parameters.Add(parameterQuantity)
Dim parameterCost As SqlParameter = New SqlParameter("@cost", SqlDbType.SmallMoney, 4)
parameterCost.Value = price
orderCommand.Parameters.Add(parameterCost)
Dim parameterComments As SqlParameter = New SqlParameter("@comments", SqlDbType.NVarChar, 255)
parameterComments.Value = Trim(txtComments.Text)
orderCommand.Parameters.Add(parameterComments)
Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum", SqlDbType.VarChar, 50)
parameterCCNum.Value = Trim(txtCardNumber.Text)
orderCommand.Parameters.Add(parameterCCNum)
Dim parameterExpDate As SqlParameter = New SqlParameter("@ExpDate", SqlDbType.VarChar, 50)
parameterExpDate.Value = expDate
orderCommand.Parameters.Add(parameterExpDate)
Dim parameterCCV As SqlParameter = New SqlParameter("@CCV", SqlDbType.VarChar, 3)
parameterCCV.Value = Trim(Issue.Text)
orderCommand.Parameters.Add(parameterCCV)
Dim parameterorderID As SqlParameter = New SqlParameter("@orderID", SqlDbType.Int, 4)
parameterorderID.Direction = ParameterDirection.Output
orderCommand.Parameters.Add(parameterorderID)
orderCommand.ExecuteNonQuery()
' Return the OrderID
Dim orderID As Integer = parameterCustomerID.Value
--------------------------
-------SPROC in SQL 2K-------------------
CREATE Procedure addOrder
(
@customerID int,
@dateOrdered datetime,
@productOrdered varchar(50),
@quantity smallint,
@cost smallmoney,
@comments nvarchar(255),
@CCNum varchar(50),
@ExpDate varchar(50),
@CCV varchar(3),
@OrderID int OUTPUT
)
AS
/* Create the Order header */
INSERT INTO psiOrders
(
customerID,
dateOrdered,
productOrdered,
quantity,
cost,
comments,
CCNum,
ExpDate,
CCV
)
VALUES
(
@customerID,
@dateOrdered,
@productOrdered,
@quantity,
@cost,
@comments,
@CCNum,
@ExpDate,
@CCV
)
SELECT
@OrderID = @@Identity
GO
--------------------------
-----Query Analyzer Data---------------------
INSERT INTO psiOrders
(
customerID,
dateOrdered,
productOrdered,
quantity,
cost,
comments,
CCNum,
ExpDate,
CCV
)
VALUES
(
40,
9/22/2004,
'SSK Scooter Kit',
1,
$ 99.00,
'',
'5555555555555555',
'06-2007',
'182'
)
SELECT
@@Identity
GO
--------------------------
Any ideas?
--------------------------------
From: Chad Micheal Lawson
-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)
<Id>BqE+WdnJs0uNaGpvrOAVZw==</Id>
Hi Chad:
Just one idea. You might want to try
SqlCommandBuilder.DeriveParameters once, just to compare the
parameters it would select and compare them to the types and sizes you
have.
HTH,
--
Scott http://www.OdeToCode.com
On Thu, 23 Sep 2004 17:27:07 -0700, Chad Micheal Lawson via .NET 247
<an*******@dotnet247.com> wrote: I'm stumped at this point and I'm tired of trying things so I'm posting in hopes of some guru with a sharp eye. I have an asp.net app running on a local Win XP Pro box. Within the app, I call a SPROC and pass the info to the SQL box and it hiccups. I'll drop VS.NET into debug mode and step through the SPROC creation and copy those values into Query Analyzer to get a little more info. When I run the Query Analyer that data works just fine. What?! Here is the relevant info:
------ASP.NET CODE---------------------
Dim parameterCustID As SqlParameter = New SqlParameter("@customerID", SqlDbType.Int, 4)
parameterCustID.Value = customerID
orderCommand.Parameters.Add(parameterCustID)
Dim parameterDateOrdered As SqlParameter = New SqlParameter("@dateOrdered", SqlDbType.DateTime, 8)
parameterDateOrdered.Value = Today
orderCommand.Parameters.Add(parameterDateOrdere d)
Dim parameterProductOrdered As SqlParameter = New SqlParameter("@productOrdered", SqlDbType.VarChar, 50)
parameterProductOrdered.Value = productOrdered
orderCommand.Parameters.Add(parameterProductOrder ed)
Dim parameterQuantity As SqlParameter = New SqlParameter("@quantity", SqlDbType.SmallInt, 2)
parameterQuantity.Value = CInt(quantity)
orderCommand.Parameters.Add(parameterQuantity)
Dim parameterCost As SqlParameter = New SqlParameter("@cost", SqlDbType.SmallMoney, 4)
parameterCost.Value = price
orderCommand.Parameters.Add(parameterCost)
Dim parameterComments As SqlParameter = New SqlParameter("@comments", SqlDbType.NVarChar, 255)
parameterComments.Value = Trim(txtComments.Text)
orderCommand.Parameters.Add(parameterComments)
Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum", SqlDbType.VarChar, 50)
parameterCCNum.Value = Trim(txtCardNumber.Text)
orderCommand.Parameters.Add(parameterCCNum)
Dim parameterExpDate As SqlParameter = New SqlParameter("@ExpDate", SqlDbType.VarChar, 50)
parameterExpDate.Value = expDate
orderCommand.Parameters.Add(parameterExpDate)
Dim parameterCCV As SqlParameter = New SqlParameter("@CCV", SqlDbType.VarChar, 3)
parameterCCV.Value = Trim(Issue.Text)
orderCommand.Parameters.Add(parameterCCV)
Dim parameterorderID As SqlParameter = New SqlParameter("@orderID", SqlDbType.Int, 4)
parameterorderID.Direction = ParameterDirection.Output
orderCommand.Parameters.Add(parameterorderID)
orderCommand.ExecuteNonQuery()
' Return the OrderID
Dim orderID As Integer = parameterCustomerID.Value
--------------------------
-------SPROC in SQL 2K-------------------
CREATE Procedure addOrder
(
@customerID int,
@dateOrdered datetime,
@productOrdered varchar(50),
@quantity smallint,
@cost smallmoney,
@comments nvarchar(255),
@CCNum varchar(50),
@ExpDate varchar(50),
@CCV varchar(3),
@OrderID int OUTPUT
)
AS
/* Create the Order header */
INSERT INTO psiOrders
(
customerID,
dateOrdered,
productOrdered,
quantity,
cost,
comments,
CCNum,
ExpDate,
CCV
)
VALUES
(
@customerID,
@dateOrdered,
@productOrdered,
@quantity,
@cost,
@comments,
@CCNum,
@ExpDate,
@CCV
)
SELECT
@OrderID = @@Identity
GO
--------------------------
-----Query Analyzer Data---------------------
INSERT INTO psiOrders
(
customerID,
dateOrdered,
productOrdered,
quantity,
cost,
comments,
CCNum,
ExpDate,
CCV
)
VALUES
(
40,
9/22/2004,
'SSK Scooter Kit',
1,
$ 99.00,
'',
'5555555555555555',
'06-2007',
'182'
)
SELECT
@@Identity
GO
--------------------------
Any ideas?
--------------------------------
From: Chad Micheal Lawson
-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)
<Id>BqE+WdnJs0uNaGpvrOAVZw==</Id>
>' Return the OrderID Dim orderID As Integer = parameterCustomerID.Value <<<wrong paramter???
Dim orderID As Integer = parameterOrderID.Value
I find creating individual variables for each parameter error prone.
This is how I do it:
cmd.Parameters.Add("@EmpID", SqlDbType.Int).Value = empID
cmd.Parameters.Add("@WeekEnd", SqlDbType.SmallDateTime).Value =
weekend
cmd.Parameters.Add("@TimeSheetID", SqlDbType.Int).Direction =
ParameterDirection.Output
...
Dim TimeSheetID As Integer =
CType(cmd.Parameters("@TimeSheetID").Value, Integer)
HTH,
Greg
"Chad Micheal Lawson via .NET 247" <an*******@dotnet247.com> wrote in
message news:%2****************@TK2MSFTNGP10.phx.gbl...
I'm stumped at this point and I'm tired of trying things so I'm posting in
hopes of some guru with a sharp eye. I have an asp.net app running on a
local Win XP Pro box. Within the app, I call a SPROC and pass the info to
the SQL box and it hiccups. I'll drop VS.NET into debug mode and step
through the SPROC creation and copy those values into Query Analyzer to get
a little more info. When I run the Query Analyer that data works just fine.
What?! Here is the relevant info:
------ASP.NET CODE---------------------
Dim parameterCustID As SqlParameter = New SqlParameter("@customerID",
SqlDbType.Int, 4)
parameterCustID.Value = customerID
orderCommand.Parameters.Add(parameterCustID)
Dim parameterDateOrdered As SqlParameter = New SqlParameter("@dateOrdered",
SqlDbType.DateTime, 8)
parameterDateOrdered.Value = Today
orderCommand.Parameters.Add(parameterDateOrdered)
Dim parameterProductOrdered As SqlParameter = New
SqlParameter("@productOrdered", SqlDbType.VarChar, 50)
parameterProductOrdered.Value = productOrdered
orderCommand.Parameters.Add(parameterProductOrdere d)
Dim parameterQuantity As SqlParameter = New SqlParameter("@quantity",
SqlDbType.SmallInt, 2)
parameterQuantity.Value = CInt(quantity)
orderCommand.Parameters.Add(parameterQuantity)
Dim parameterCost As SqlParameter = New SqlParameter("@cost",
SqlDbType.SmallMoney, 4)
parameterCost.Value = price
orderCommand.Parameters.Add(parameterCost)
Dim parameterComments As SqlParameter = New SqlParameter("@comments",
SqlDbType.NVarChar, 255)
parameterComments.Value = Trim(txtComments.Text)
orderCommand.Parameters.Add(parameterComments)
Dim parameterCCNum As SqlParameter = New SqlParameter("@CCNum",
SqlDbType.VarChar, 50)
parameterCCNum.Value = Trim(txtCardNumber.Text)
orderCommand.Parameters.Add(parameterCCNum)
Dim parameterExpDate As SqlParameter = New SqlParameter("@ExpDate",
SqlDbType.VarChar, 50)
parameterExpDate.Value = expDate
orderCommand.Parameters.Add(parameterExpDate)
Dim parameterCCV As SqlParameter = New SqlParameter("@CCV",
SqlDbType.VarChar, 3)
parameterCCV.Value = Trim(Issue.Text)
orderCommand.Parameters.Add(parameterCCV)
Dim parameterorderID As SqlParameter = New SqlParameter("@orderID",
SqlDbType.Int, 4)
parameterorderID.Direction = ParameterDirection.Output
orderCommand.Parameters.Add(parameterorderID)
orderCommand.ExecuteNonQuery()
' Return the OrderID
Dim orderID As Integer = parameterCustomerID.Value
--------------------------
-------SPROC in SQL 2K-------------------
CREATE Procedure addOrder
(
@customerID int,
@dateOrdered datetime,
@productOrdered varchar(50),
@quantity smallint,
@cost smallmoney,
@comments nvarchar(255),
@CCNum varchar(50),
@ExpDate varchar(50),
@CCV varchar(3),
@OrderID int OUTPUT
)
AS
/* Create the Order header */
INSERT INTO psiOrders
(
customerID,
dateOrdered,
productOrdered,
quantity,
cost,
comments,
CCNum,
ExpDate,
CCV
)
VALUES
(
@customerID,
@dateOrdered,
@productOrdered,
@quantity,
@cost,
@comments,
@CCNum,
@ExpDate,
@CCV
)
SELECT
@OrderID = @@Identity
GO
--------------------------
-----Query Analyzer Data---------------------
INSERT INTO psiOrders
(
customerID,
dateOrdered,
productOrdered,
quantity,
cost,
comments,
CCNum,
ExpDate,
CCV
)
VALUES
(
40,
9/22/2004,
'SSK Scooter Kit',
1,
$ 99.00,
'',
'5555555555555555',
'06-2007',
'182'
)
SELECT
@@Identity
GO
--------------------------
Any ideas?
--------------------------------
From: Chad Micheal Lawson
-----------------------
Posted by a user from .NET 247 ( http://www.dotnet247.com/)
<Id>BqE+WdnJs0uNaGpvrOAVZw==</Id>
P.S.
SELECT @OrderID = @@Identity
can cause your problems (if you have triggers), better to use:
SELECT @OrderID = SCOPE_IDENTITY()
Greg
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Punisher |
last post by:
Ok I'm trying to reference a specific node in a tree.
TreeNode nodeParentChannel = treeview1.Nodes;
It bugs out with this error: Input string was not in a correct format
Obviously 1.2 is not...
|
by: The Crow |
last post by:
if the reason for error is simple, excuse me. but i cant find what the
reason is. Here is the snippet :
string script = String.Format(@"<script src='common.js'...
|
by: phmyhn |
last post by:
I have two web pages, one is viewlarger.aspx, another one is
shoppingcart.aspx. On the viewlarger.aspx, when clicking "add to cart"
image button, the sub appends the id (passed from another page...
|
by: John Chorlton |
last post by:
I've been attempting to pass a chunk of data back from a child Windows form using public properties on the form and have been getting some odd errors. I wanted to return a row of data to avoid...
|
by: dan |
last post by:
I am using VB.NET 2003 and SQL Server 2000.
The program uses ADO.NET .
The following instruction:
Me.cd_insertDataRecord.ExecuteNonQuery()
throws this exception:
">>>ProcessDataRecord/...
|
by: James Pose |
last post by:
Convert.Int32 fails for "0" but works fine for all other numbers.
This does not work on one machine when "0" it works fine on other machines
Convert.ToInt32(“0”);
gives exception {"Input...
|
by: Pieter Coucke |
last post by:
Hi,
When a user types a non numeric-value in a numeric column in a DataGridView,
and he tries to leave the cell, he gets this "Input string was not in a
correct format."-exception.
Is there a...
|
by: Jen |
last post by:
One user of my application is experiencing an exception "input string not in
correct format". But it makes no sense where it is occurring. It is
occurring when a string from a textbox ("172") is...
|
by: muaddubby |
last post by:
Hi
I'm running into a scenario with a DataSet that has a schema loaded into it, tries to then load data (that matches the schema), and fails with a "Input string was not in a correct format"...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing,...
| |
