More Info: Stored Procedure Security Question

Dear Group

I have found that table A had SELECT permissions for 'Public' but not table
B.
Giving 'Public' SELECT permissions on table B did the trick.

HOWEVER, I don't want anyone to be able to do a direct SELECT on table A or
B but only give them access to the data by using the stored procedures. Is
there any way this can be set up?

Thanks for your efforts!
Have a nice day!

Martin

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message news:...
> Dear Group
> I'm having two stored procedures, sp_a and sp_b
> Content of stored procedure A:
> CREATE PROCEDURE dbo.sp_a
> SELECT * FROM a
> GO
> Content of stored procedure B:
> CREATE PROCEDURE dbo.sp_b
> SELECT * FROM b
> GO
> I have created a user that has execute permissions for both procedures.
> When I run procedure A, all works fine but when running procedure B I'm
> getting an error saying that the user must have SELECT permissions on
> table B.
> Both tables are owned by dbo, and the security role for the user doesn't
> has any SELECT permission on table a and b.
> I'd be grateful if anyone could point me in a direction why this error
> might come up for procedure B but not for A,
> with a possible solution without giving the user SELECT permissions.
> Thanks very much for your help!
> MartinMartin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
>> Content of stored procedure A:
>> CREATE PROCEDURE dbo.sp_a
>> SELECT * FROM a
>> GO
>>
>> Content of stored procedure B:
>> CREATE PROCEDURE dbo.sp_b
>> SELECT * FROM b
>> GO
> I have found that table A had SELECT permissions for 'Public' but not
> table B. Giving 'Public' SELECT permissions on table B did the trick.
> HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
> or B but only give them access to the data by using the stored
> procedures. Is there any way this can be set up?

I have a strong feeling that you are not telling us the full story,
because what you have described is the typical usage of ownership
chaining, and users should indeed be able to access the data in the
tables through the stored procedures.

Is there by chance some dynamic SQL involved?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I also responded to your previous thread. As Erland said, this should work
as long as the objects are in the same database. If in different databases,
you'll need to enable cross-database chaining and the databases need to have
the same owner in order to maintain an unbroken ownership chain for
dbo-owned objects.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:cg53pg$rbo$1@.sparta.btinternet.com...
> Dear Group
> I have found that table A had SELECT permissions for 'Public' but not
table
> B.
> Giving 'Public' SELECT permissions on table B did the trick.
> HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
or
> B but only give them access to the data by using the stored procedures. Is
> there any way this can be set up?
> Thanks for your efforts!
> Have a nice day!
> Martin
>
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:...
> > Dear Group
> > I'm having two stored procedures, sp_a and sp_b
> > Content of stored procedure A:
> > CREATE PROCEDURE dbo.sp_a
> > SELECT * FROM a
> > GO
> > Content of stored procedure B:
> > CREATE PROCEDURE dbo.sp_b
> > SELECT * FROM b
> > GO
> > I have created a user that has execute permissions for both procedures.
> > When I run procedure A, all works fine but when running procedure B I'm
> > getting an error saying that the user must have SELECT permissions on
> > table B.
> > Both tables are owned by dbo, and the security role for the user doesn't
> > has any SELECT permission on table a and b.
> > I'd be grateful if anyone could point me in a direction why this error
> > might come up for procedure B but not for A,
> > with a possible solution without giving the user SELECT permissions.
> > Thanks very much for your help!
> > Martin|||Thanks for your help guys!
Well, as Erland suspected, I haven't given you the full story as I
thought it doesn't matter but as I found out the hard way, it was
indeed dynamic SQL that caused the problem.

Erland, please don't tell me off for using dynamic SQL! LOL
I've read your wonderful fantastic guides and obbey all rules on
dynamic SQL but although I'm not happy myself, I think I really can't
avoid it this time.
However, if you'd offer to have a look at my script and just tell me
whether it can be done without dynamic SQL then this would make me
very happy!

Anyway, I solved the permission problem by basing the stored
procedures that contain the dynamic SQL on Views and implementing row
level security in those.

Thanks again for your efforts!
Have a nice day!

Martin

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<07JVc.8691$3O3.3742@.newsread2.news.pas.earthlink.n et>...
> I also responded to your previous thread. As Erland said, this should work
> as long as the objects are in the same database. If in different databases,
> you'll need to enable cross-database chaining and the databases need to have
> the same owner in order to maintain an unbroken ownership chain for
> dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:cg53pg$rbo$1@.sparta.btinternet.com...
> > Dear Group
> > I have found that table A had SELECT permissions for 'Public' but not
> table
> > B.
> > Giving 'Public' SELECT permissions on table B did the trick.
> > HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
> or
> > B but only give them access to the data by using the stored procedures. Is
> > there any way this can be set up?
> > Thanks for your efforts!
> > Have a nice day!
> > Martin
> > "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:...
> > > Dear Group
> > > > I'm having two stored procedures, sp_a and sp_b
> > > > Content of stored procedure A:
> > > CREATE PROCEDURE dbo.sp_a
> > > SELECT * FROM a
> > > GO
> > > > Content of stored procedure B:
> > > CREATE PROCEDURE dbo.sp_b
> > > SELECT * FROM b
> > > GO
> > > > I have created a user that has execute permissions for both procedures.
> > > When I run procedure A, all works fine but when running procedure B I'm
> > > getting an error saying that the user must have SELECT permissions on
> > > table B.
> > > > Both tables are owned by dbo, and the security role for the user doesn't
> > > has any SELECT permission on table a and b.
> > > I'd be grateful if anyone could point me in a direction why this error
> > > might come up for procedure B but not for A,
> > > with a possible solution without giving the user SELECT permissions.
> > > > Thanks very much for your help!
> > > > Martin
>|||Martin (theintrepidfox@.hotmail.com) writes:
> Erland, please don't tell me off for using dynamic SQL! LOL
> I've read your wonderful fantastic guides and obbey all rules on
> dynamic SQL but although I'm not happy myself, I think I really can't
> avoid it this time.
> However, if you'd offer to have a look at my script and just tell me
> whether it can be done without dynamic SQL then this would make me
> very happy!

Well, there are cases where dynamic SQL is the best solution and there
are cases where dynamic SQL is a really poor choice.

The whole message of http://www.sommarskog.se/dyn-search.html is that
for dynamic search conditions is "use dynamic SQL, if you can handle
the security issues". If you can make it with views, then you should
be fine.

Beware though, that a very skilled person can be able to cram out
information from a view for row-based security that he is not supposed
to have access to. It is not that he can actually get to see the rows,
but he can make conclusions from query plans statistical IO and such.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks very much Erland!

>It is not that he can actually get to see the rows,
> but he can make conclusions from query plans statistical IO and such.

Do you know any source with more information on this view security issue?
What harm can it do? It's a CRM app, not a top secret military app.

Thanks for your efforts!

M

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns954FEB97D2796Yazorman@.127.0.0.1>...
> Martin (theintrepidfox@.hotmail.com) writes:
> > Erland, please don't tell me off for using dynamic SQL! LOL
> > I've read your wonderful fantastic guides and obbey all rules on
> > dynamic SQL but although I'm not happy myself, I think I really can't
> > avoid it this time.
> > However, if you'd offer to have a look at my script and just tell me
> > whether it can be done without dynamic SQL then this would make me
> > very happy!
> Well, there are cases where dynamic SQL is the best solution and there
> are cases where dynamic SQL is a really poor choice.
> The whole message of http://www.sommarskog.se/dyn-search.html is that
> for dynamic search conditions is "use dynamic SQL, if you can handle
> the security issues". If you can make it with views, then you should
> be fine.
> Beware though, that a very skilled person can be able to cram out
> information from a view for row-based security that he is not supposed
> to have access to. It is not that he can actually get to see the rows,
> but he can make conclusions from query plans statistical IO and such.|||Martin (theintrepidfox@.hotmail.com) writes:
> Do you know any source with more information on this view security issue?
> What harm can it do? It's a CRM app, not a top secret military app.

As long as you don't let SQL Server MVP Steve Kass anywhere near the
database, I think your data is fairly safe. :-) That is, Steve Kass was
the one who discovered this issue, and to exploit you would need to
a query tool like Query Analyzer, and you would probably have to have
some knowledge about the schema. And you need a very good understanding
of SQL Server. Finally a good dosis of patience is good for the task.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monthly Reports

Hi,
Actually I am generating report by stored procedure by passing two parameters like START date and END date.
Now my problem is I need to generate report for every month seperately that fall in range of START date and END date.
thanks
IndiraIndira,
Group by Date, and when creating the group, if you use a date, Crystal will, by default, give the grouping options:
the date, in a dropdown
the order to sort by, in a dropdown,
the grouping frequency, in a dropdown (defaults to "for each day.")
change the last one to "for each month."
Crystal will then produce your summaries for each calendar month within the range.
You can then add a page break after this date group, to start anew page with a new month.
That's how you'd produce one report covering many months, aggregated by each month.
YOur question says you want to produce separate monthly reports.
If they are paper copies, you just restart your page numbering for each new group, print the report out, and then distribute the separate months pages as separate reports.
If you want to produce separate files, you will need to outline which method you want to use to distribute the separate reports, because thismight require code via VB or C etc...

Dave

Month/Year comparison against a date

Hi,

Is there a way to get the last day of a month, given a date in a datetime variable?

I have a stored procedure that accepts a datetime parameter. I need to find the last day of the month for that parameter value. For example, if the stored procedure is passed the datetime value, '6/13/2007', I need to be able to get from that '6/30/2007.'

Here's the bigger picture: The sp actually takes two datetime parameters (unfortunately, I don't have access to change the user interface). The sp needs to select records between the month/years of those dates, not including the first but including the second. For example, if the user specifies the following dates:

6/7/2006
7/15/2007

the sp needs to select all records that come after 6/30/2006 and on or before 7/31/2007.

I've tried this: ("ReportDate" is the name of the datetime field in a table in the sp and "@.StartDate" and "@.EndDate" are the datetime parameters in the sp)

Month(ReportDate) > Month(@.StartDate) And Year(ReportDate) >= Year(@.StartDate) And
Month(ReportDate) <= Month(@.EndDate) And Year(ReportDate) <= Year(@.EndDate)

But this returns fewer records than when I enter 6/30/2006 and 7/31/2007 as the parameters and just compare dates like this:

ReportDate > @.StartDate And ReportDate <= @.EndDate

Thank you.

Something like this:

Code Snippet

WHERE ( ReportDate >= dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )
)

You do NOT want to enclose ReportDate in a function since that will most likely result in not using indexing.

|||

WHERE ( ReportDate > dateadd( month, datediff( month, 0, @.StartDate ) , -1 )

AND ReportDate < dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )
)

I just edited the previous reply to make it more close to your requirement..

|||

I don't think that is exactly 'right'. (I did make the assumption that the OP wanted ONLY JULY 2007 dates.)

Follow this code:

Code Snippet

DECLARE
@.StartDate datetime,
@.EndDate datetime

SELECT
@.StartDate = '06/7/2007',
@.EndDate = '7/15/2007'

-- Arnie's Variation
-- Starting at midnight, 7/1/2007, Ending at midnight, 8/1/2007
SELECT
'Arnie',
dateadd( month, datediff( month, 0, @.StartDate ) + 1, 0 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1, 0 )

-- Mandip's Variation
-- Starting at midnight, 5/31/2007, ending at midnight, 8/1/2007
SELECT
'Mandip',
dateadd( month, datediff( month, 0, @.StartDate ) , -1 ),
dateadd( month, datediff( month, 0, @.EndDate ) + 1,0 )

-- OP Requested
-- the sp needs to select all records that come
-- after 6/30/2006 and on or before 7/31/2007

-- -
Arnie 2007-07-01 00:00:00.000 2007-08-01 00:00:00.000

Mandip 2007-05-31 00:00:00.000 2007-08-01 00:00:00.000

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.codecomments.com ***
Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:

>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks, Sue. This looks like what I need!
*** Sent via Developersdex http://www.codecomments.com ***

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.developersdex.com ***Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:
>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.developersdex.com ***

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.codecomments.com ***Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:

>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.codecomments.com ***|||Thanks, Sue. This looks like what I need!
*** Sent via Developersdex http://www.codecomments.com ***

Monitoring disk space

I am trying to write a script to properly monitor disk space for MSSQL
server.
Is there a stored procedure that tells me the separate space usage of
the database and the log?
The procedure sp_spaceusage seems to give an "overall" figure.
However, is it not possible (albeit unwise) that the user could create
the log on a separate filesystem, maybe even with a fixed file size,
so the log could potentially run out of space whilst there is still
space available overall.
Any thoughts?
thanks,
NeilConsider a combination of DBCC SQLPERF(LOGPSACE) and DBCC SHOWFILESTATS. The later is not documented
so use at your own risk...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:3631594f-2ecb-44db-8dd5-c0e1346ed7e4@.m34g2000hsb.googlegroups.com...
>I am trying to write a script to properly monitor disk space for MSSQL
> server.
> Is there a stored procedure that tells me the separate space usage of
> the database and the log?
> The procedure sp_spaceusage seems to give an "overall" figure.
> However, is it not possible (albeit unwise) that the user could create
> the log on a separate filesystem, maybe even with a fixed file size,
> so the log could potentially run out of space whilst there is still
> space available overall.
> Any thoughts?
> thanks,
> Neil|||> Consider a combination of DBCC SQLPERF(LOGPSACE) and DBCC SHOWFILESTATS. The later is not documented
> so use at your own risk...
>
Thanks, that got it.
I am using DBCC SQLPERF(LOGSPACE) and a query on the SYSFILES table.

Monitor the daily performance report of SQL 2000.

What are the most necessary item to be checked to monitor
the daily performance report of SQL 2000.
Any list of system Stored procedure? Any list of tables.
If so please specify.
Thanks in advane.
AnandThanks for your reply.
>--Original Message--
>Anand,
>the first (and best) place to start looking is
>http://www.sql-server-performance.com
>--
>I hope this helps
>regards
>Greg O MCSD
>SQL Scribe Documentation Builder
>Document any SQL server database in minutes
>Programmers love it, DBA dream of it
>AGS SQL Scribe download a 30 day trial today
>http://www.ag-software.com/ags_scribe_index.asp
>
>"gurus" <gurusanand@.yahoo.com> wrote in message
>news:065201c35199$1ca11230$a501280a@.phx.gbl...
>> What are the most necessary item to be checked to
monitor
>> the daily performance report of SQL 2000.
>> Any list of system Stored procedure? Any list of tables.
>> If so please specify.
>> Thanks in advane.
>> Anand
>
>.
>

Monitor SQL Statements

I would like to monitor any non stored procedure activity on a sql
server (2000 and 2005). Mainly I need to watch for ad hoc sql from
applications and for people running sql from query analyzer.
I think that Profiler will do this, but I have concerns about how much
overhead that will cause. At times, the server is very busy and I have
SLA in place that would cause me to pay a penalty for slow response
time.
Any suggestions?
Jim
St LouisFirst off you should never use Profiler for traces on busy systems. You can
simply use TRACE and send the results to a binary file on a disk attached
locally to the server. Then you can copy them to another machine for
processing or viewing. The easiest way (if you are not familiar with that)
to do that is to briefly use profiler. Set up the events and columns and
filters exactly how you want it. Then run the trace and immediately hit
stop. Then go to File - Script Trace - For 2000. This will generate a tsql
script that you can use to run a trace without the overhead of profiler.
Follow the instructions in the script to add the filename and adjust the
maxfilesize etc. Look up sp_tracecreate in booksonline for more details. If
the sp's are being run as RPC's and not batches you can just look for Batch
Completed events and ignore all the sps.
Andrew J. Kelly SQL MVP
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1150981788.688853.289590@.u72g2000cwu.googlegroups.com...
>I would like to monitor any non stored procedure activity on a sql
> server (2000 and 2005). Mainly I need to watch for ad hoc sql from
> applications and for people running sql from query analyzer.
> I think that Profiler will do this, but I have concerns about how much
> overhead that will cause. At times, the server is very busy and I have
> SLA in place that would cause me to pay a penalty for slow response
> time.
> Any suggestions?
> Jim
> St Louis
>|||First off you should never use Profiler for traces on busy systems. You can
simply use TRACE and send the results to a binary file on a disk attached
locally to the server. Then you can copy them to another machine for
processing or viewing. The easiest way (if you are not familiar with that)
to do that is to briefly use profiler. Set up the events and columns and
filters exactly how you want it. Then run the trace and immediately hit
stop. Then go to File - Script Trace - For 2000. This will generate a tsql
script that you can use to run a trace without the overhead of profiler.
Follow the instructions in the script to add the filename and adjust the
maxfilesize etc. Look up sp_tracecreate in booksonline for more details. If
the sp's are being run as RPC's and not batches you can just look for Batch
Completed events and ignore all the sps.
Andrew J. Kelly SQL MVP
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1150981788.688853.289590@.u72g2000cwu.googlegroups.com...
>I would like to monitor any non stored procedure activity on a sql
> server (2000 and 2005). Mainly I need to watch for ad hoc sql from
> applications and for people running sql from query analyzer.
> I think that Profiler will do this, but I have concerns about how much
> overhead that will cause. At times, the server is very busy and I have
> SLA in place that would cause me to pay a penalty for slow response
> time.
> Any suggestions?
> Jim
> St Louis
>

Monitor DiskSpace

I have discovered that there is an extended procedure called xp_fixeddrives, which can check how much space you have on your SQL Server's drives, it works fine. Can some one tell me what can I use to monitor the above (space used on drive) on servers that do not have SQL at all. Remember I want to call that from my SQL NOT from VBWMI - Windows Management Instrumentation is what you need.
This provides you with the ability to monitor all sorts of metrics in a server which are outside of the realm of SQL Server. The notation for addressing the components takes some getting used to, but if you're writing a serious app, it's worth the effort.
Useful link : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/using_wmi.asp

Try searching from www.alltheweb.com or other for +WMI +<whatever language you use>

Enjoy :)

Money, SQL Server and C#

Argggg...

Please help.

I need to store money in SQL Server.
I am using C# and a stored procedure to insert.

How do you accomplish this? Specifically what datatype should the money variable be in C# during the insert? It's initial value is a string as it is entered from a text field.

I have been trying for over an hour now to simply insert money into SQL Server using C# and have it stored as a money type!

Thanks

Try using decimal. hth

|||

I did get it working using decimal.. thanks!

Decimal rate = Convert.ToDecimal(txtRate.Text);

Stored procedure looks like this.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sp_UpdateHorsepowerRate

@.HorsePower int,
@.Rate money,
@.UserId uniqueidentifier,
@.EmployerId int

AS

INSERT INTO EmployerHorsePower (HorsePower, Rate, UserId, EmployerId) VALUES (@.HorsePower, @.Rate, @.UserId, @.EmployerId)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Rob

|||

I would recommend that you not begin your stored procedures with "sp" (refer to:http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp).

Also, when setting up the SqlParameter be sure that you consider the precision and scale. For themoney datatype, it would be precision=19, scale = 4.

money in stored procedure

Hi there,

I have a table products with product_price money(8) field. I have a stored procedure,

CREATE procedure p_dat_update_product(
@.product_id int,
@.product_price decimal
)
as
set nocount on

declare
@.msg varchar(255) -- error message holder

-- remove any leading/trailing spaces from parameters
select @.product_id = ltrim(rtrim(@.product_id))
select @.product_price = ltrim(rtrim(@.product_price))

-- turn [nullable] empty string parameters into NULLs
if (@.product_id = N'') select @.product_id = null
if (@.product_price = N'') select @.product_price = null

-- start the validation
if @.product_id is null
begin
select @.msg = 'The value for variable p_dat_update_product.@.product_id cannot be null!'
goto ErrHandler
end

-- execute the query
if exists (select 'x' from product where product_id = @.product_id)
begin
update dbo.product
set
product_price = @.product_price
where product_id = @.product_id
end

if (@.@.ERROR <> 0) goto Errhandler

return
ErrHandler:
raiserror 30001 @.msg
return
GO

And a class function

public void Edit_Product(string product_id,string product_price)
{

decimal dec_product_price = decimal.Parse(product_price);

SqlDataServ oMisDb = new SqlDataServ("LocalSuppliers");
oMisDb.AddParameter("@.product_id", product_id);
oMisDb.AddParameter("@.product_price", dec_product_price.ToString());
oMisDb.ExecuteCmd("p_dat_update_product");
}

But i keep getting this error,

Error converting data type nvarchar to numeric

oMisDb.ExecuteCmd("p_dat_update_product");

I do not know what to do and solve this. Is there something that i have done wrong.

thanks in advance.

PS: still learning aspx and ms sql

Hi

the stored proc accepts int and decimal

priteshchandra:

CREATE procedure p_dat_update_product(
@.product_id int,
@.product_price decimal
)

while you are passing string

priteshchandra:

public void Edit_Product(string product_id,string product_price)

so you need to cast them to appropriate datatypes

priteshchandra:

oMisDb.AddParameter("@.product_id", product_id);
oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

should be

oMisDb.AddParameter("@.product_id", convert.toint32(product_id));
oMisDb.AddParameter("@.product_price", convert.toDecimal(dec_product_price));

hope that helps.

thanks,

satish.

|||

I tired ur solution but got this error.

Compiler Error Message:CS0103: The name 'convert' does not exist in the current context

oMisDb.AddParameter("@.product_price", convert.toDecimal(dec_product_price));

|||

aargh my friend

Convert.ToDecimal() , do you've .net documentation/MSDN or not?

its straight forward naming convention of C#.

thanks,

satish.

|||

I think he meant Decimal.Parse() and Int32.Parse()?

|||

Motley:

I think he meant Decimal.Parse() and Int32.Parse()?

yes infact TryParse would be efficient.

|||

Just replace this line:

oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

with this:

oMisDb.AddParameter("@.product_price", dec_product_price);

because you already converted it to decimal here:

decimal dec_product_price = decimal.Parse(product_price);

|||

kipo:

Just replace this line:

oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

with this:

oMisDb.AddParameter("@.product_price", dec_product_price);

because you already converted it to decimal here:

decimal dec_product_price = decimal.Parse(product_price);

I tired this before and i kept getting this error,

CS1502:The best overloaded method match for'MISUtilities.DataServices.SqlDataServ.AddParameter(string, string)'has some invalid arguments

Source Error:

Line 359: oMisDb.AddParameter("@.product_price", dec_product_price);

|||

satish_nagdev:

aargh my friend

Convert.ToDecimal() , do you've .net documentation/MSDN or not?

its straight forward naming convention of C#.

thanks,

satish.

Sory, i do not have MSDN. am using Express Edition for now since me learning .NET

|||

I believe you are looking for this syntax:

oMdb.Parameters.Add("@.param",SqlDbType.Int).Value={something}

or

oMdb.Parameters.AddWithValue("@.param", {something})

of course, replace {something} with a constant, variable, etc.

|||

After working to finding a solution, i kept failing. eventually i came up with this.

i changed the field from money to nvarchar and everything worked fine on wards.

question:

replacing money with nvarchar, will it affect my data in any way already stored?

|||

Hi Pritesh,

you said money datatype and in actual post i could see you are accepting decimal datatype. i think that is the actual problem.

regarding replacing money with nvarchar is not a good idea. there might be cases when you need to work on numeric (money) so you'll do conversion back and forth to money and varchar.

so in your table the column is of type money right? if yes modify your sp to accept money instead of decimal and in c# code use overloaded parameter constructor and pass datatype like sqlint etc

thanks,

satish.

|||

well, i tired placing money in the stored procedure but i kept getting error that money is not valid in sp.