Trying to call a stored proc but some times don't want to have values
inserted in some fields.
Hi
I am rewriting a VBS script which called a stored proc in a SQL server
db. The proc takes a number of values both char and floats.
Sometime it is a requirement that the stored proc will not be given a
values for some of the floats.
This works in VBS with a Null. Like this:
With objCmd
.Parameters("@fundID").Value = strTAFundID
.Parameters("@year_d").Value = Year
.Parameters("@mnth_d").Value = Month
.Parameters("@turnover_ratio").Value =
IIf(Trim(Sheet1.Cells(i, 5)) = "N/A", Null, FNum(Trim(Sheet1.Cells(i,
5)), 0, True))
.Parameters("@market_cap").Value =
IIf(Trim(Sheet1.Cells(i, 6)) = "N/A", Null, FNum(Trim(Sheet1.Cells(i,
6)), 1, True))
.Parameters("@pe_ratio").Value =
IIf(Trim(Sheet1.Cells(i, 7)) = "N/A", Null, FNum(Trim(Sheet1.Cells(i,
7)), 1, True))
.Parameters("@standard_deviation").Value =
IIf(Trim(Sheet1.Cells(i, 8)) = "N/A", Null, FNum(Trim(Sheet1.Cells(i,
8)), 2, True))
End With
objCmd.Execute
But when I try to do the same in .NET, the value '0.0 is always
placed in the null value fields.
I have tried creating a string with the full sql statement placing
null, DBNull, blank space etc, all with the same result.
This is my latest attempt in C#:
SqlCommand cmdLoadFundStatistics = new
SqlCommand("sp_pcom_LoadFundStatistics_TEST", this.dbConn);
cmdLoadFundStatistics.CommandType = CommandType.StoredProcedure;
// fund id
cmdLoadFundStatistics.Parameters.Add("@fundID", SqlDbType.Char, 10);
cmdLoadFundStatistics.Parameters["@fundID"].Value = fundid;
// year number
cmdLoadFundStatistics.Parameters.Add("@year_d", SqlDbType.SmallInt,
10);
cmdLoadFundStatistics.Parameters["@year_d"].Value = year_d;
// month number
cmdLoadFundStatistics.Parameters.Add("@mnth_d", SqlDbType.SmallInt,
10);
cmdLoadFundStatistics.Parameters["@mnth_d"].Value = mnth_d;
// trunover ratio
cmdLoadFundStatistics.Parameters.Add("@turnover_ra tio",
SqlDbType.Float, 10);
if(tratio != null)
cmdLoadFundStatistics.Parameters["@turnover_ratio"].Value = tratio;
else
cmdLoadFundStatistics.Parameters["@turnover_ratio"].Value =
System.DBNull.Value;
// market cap
cmdLoadFundStatistics.Parameters.Add("@market_cap" , SqlDbType.Float,
10);
if(cap != null)
cmdLoadFundStatistics.Parameters["@market_cap"].Value = cap;
else
cmdLoadFundStatistics.Parameters["@market_cap"].Value =
System.DBNull.Value;
ETC..............................................
I would really appreciate any help here. I cannot change the database;
pages pulling from these tables behave differently if the value
'0.0' is found as apposed to no vale present.
Many thanks (see proc below)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.sp_pcom_LoadFundStatistics_TEST
@fundID char(10),
@year_d smallint,
@mnth_d smallint,
@turnover_ratio float,
@market_cap float,
@pe_ratio float,
@standard_deviation float
AS
IF EXISTS(SELECT 1 FROM t_mnd_pct_TEST WHERE mutl_fund_id = @fundID AND
mnth_d = @mnth_d and year_d = @year_d)
UPDATE t_mnd_pct_TEST
SET turnover_ratio = @turnover_ratio,
market_cap = @market_cap,
pe_ratio = @pe_ratio,
standard_deviation = @standard_deviation
WHERE mutl_fund_id = @fundID AND mnth_d = @mnth_d and year_d =
@year_d
ELSE
BEGIN
--Need to ensure that t_mkt record exists for fund and date due to
foreign key restriction
IF NOT EXISTS(SELECT 1 FROM t_mkt_TEST WHERE mutl_fund_id = @fundID
AND mnth_d = @mnth_d and year_d = @year_d)
INSERT INTO t_mkt_TEST(mutl_fund_id, mnth_d, year_d)
VALUES (@fundID, @mnth_d, @year_d)
INSERT INTO t_mnd_pct_TEST(mutl_fund_id, mnth_d, year_d,
turnover_ratio, market_cap, pe_ratio, standard_deviation)
VALUES(@fundID, @mnth_d, @year_d, @turnover_ratio, @market_cap,
@pe_ratio, @standard_deviation)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 1 2315
Hi,
Phil Mc wrote: But when I try to do the same in .NET, the value '0.0 is always placed in the null value fields.
I'd have expected a SQL execution erorr instead -- makes me wonder what else
may be going on -- but try the following changes to your C# code and stored
procedure (only relevant parts reproduced, comments added):
// removed:
// cmdLoadFundStatistics.Parameters.Add("@turnover_ra tio", SqlDbType.Float,
10);
//
// We can add and assign value in one line
if(tratio != null)
cmdLoadFundStatistics.Parameters.Add("@turnover_ra tio",
SqlDbType.Float).Value = tratio;
// removed:
// else
// cmdLoadFundStatistics.Parameters["@turnover_ratio"].Value =
System.DBNull.Value;
//
// Instead of adding parameter with DBNull.Value for Value, we don't
// add it at all.
if(cap != null)
cmdLoadFundStatistics.Parameters.Add("@market_cap" ,
SqlDbType.Float).Value = cap;
Give the same treatment to any other optional parameters. Now, on to the
stored procedure:
ALTER PROCEDURE dbo.sp_pcom_LoadFundStatistics_TEST
@fundID char(10),
@year_d smallint,
@mnth_d smallint,
@turnover_ratio float = null, /* give parameter default value of null
*/
@market_cap float = null,
@pe_ratio float,
@standard_deviation float
AS
/* whatever */
If that doesn't help, make sure you don't have default value constraints for
these columns.
--
Chris Priede This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: schumacker |
last post by:
Hi everyone!
I am working with Delphi v7 and MS SQLServer.
I am trying to insert data in a table with a SQL sentence. Some of the
fields of my table are type char or varchar, and they can have...
|
by: Tim Marshall |
last post by:
Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:
A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several...
|
by: Eric Land |
last post by:
Help! I'm trying to call a parameterized stored proc in ASP.NET in VB. I am
creating a command object and creating a parametr list, and assigning a value
from a session variable (this is working)...
|
by: CsharpGuy |
last post by:
I took over an web app (C#) were the developer put everything in a has table
then called a method to execute a stored procedure, now I'm running into some
issues were if I do an update and a NULL...
|
by: Paul M |
last post by:
Hi All,
I'm currently writing a z/OS DB2 Stored Proc in C, using an example from the
IBM Stored Procedure guide (SG24-7083-00). The database calls to read and
update the database work...
|
by: bobh |
last post by:
Hi All,
I have this query which updates a field based on the result of an IIF
statement. The table is on SQLServer and I'm linked to it and it has
many records and will take Access a very long...
|
by: adjo |
last post by:
I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It...
|
by: raghuvendra |
last post by:
Hi
I have a jsp page with 4 columns: namely Category name , Category order, Input field and a submit button.
All these are aligned in a row. And Each Category Name has its corresponding Category...
|
by: pinky22 |
last post by:
I am calling SSIS package from a .Net windows UI. Both SSIS & .Net app are created in 2008. The SSIS package is stored in file system. When I ran .Net app I got error-
The package failed to...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| | |