April 3, 2008 at 9:24 am
Shamsudheen,
Thank you so much for your prompt reply, I have to fix this issue urgently. Here is my stored proc
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[DS_GetProductsByFilter]
@FieldName nVarChar(20),
@FieldValue nVarChar(50)
AS
SET NOCOUNT ON
DECLARE @Where VARCHAR(200)
DECLARE @sqlcmd VARCHAR(3000)
IF @FieldName = 'Show All'
SET @Where = ''
ELSE
IF @FieldName = 'Title'
SET @Where = ' WHERE ' + @FieldName +
' LIKE ' + CHAR(39) + '%' + @FieldValue + '%' +
CHAR(39)
ELSE
SET @Where = ' WHERE ' + @FieldName +
' = ' + CHAR(39) + @FieldValue + CHAR(39)
SET @sqlcmd = 'Select PM.itemcode, isbn,title,cost,
price,mfgsuggestedprice,altprice, PD.Discount,validcost,
PM.VendorID, ProductType,PD.SchemeID,PM.description,
sectioncode,distributioncategory,itemalias,lastpurchaseorder,
Keywords,lastvouchernumber,allowregisterpricing,
allowregistertitle,reorderable,lastinvoiceno,PriceMethod,
CostBasis,PurchaseFromVendorID,reclassfromitemcode,
Internetitem,internetminqty,internetmaxqty,UpdateQOH,
PD.VendorItemNo,PD.ASIN , Note
FROM ProductMaster PM
LEFT OUTER JOIN ProductMasterDist PD ON PM.ItemCode = PD.ItemCode '
+ @Where + ' ORDER BY PM.itemcode'
EXEC (@sqlcmd)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
GRANT EXECUTE ON DS_GetProductsByFilter
Thanks,
Ram.
April 3, 2008 at 9:38 am
This proc almost certainly isn't the problem. Your problem is with the insert/update.
Your PK violation isn't coming from a select statement (unless it's something tangential, like you aren't selecting the PK column(s) so you don't have them when you try to update).
April 3, 2008 at 9:39 am
Keith,
Like I wrote earlier, if I fill the primary key column in the grid and then click on row above of below the current row then it gives me "row cannot be located for updating". If I don't fill the primary key and leave it null, then it gives me "Violation of primary key constraint".
Thanks,
Ram.
April 3, 2008 at 10:03 am
Yes, I read what you wrote earlier. I'm just saying I don't believe it's how you populated your grid that's the problem, it's the insert/update. I don't think it's doing what you expect it to do. Did you try disabling the PK to see what gets inserted?
April 3, 2008 at 10:38 am
There is no way I can disable the PK, the DBA will kill me.
April 3, 2008 at 10:43 am
ramadesai108 (4/3/2008)
Keith,Like I wrote earlier, if I fill the primary key column in the grid and then click on row above of below the current row then it gives me "row cannot be located for updating". If I don't fill the primary key and leave it null, then it gives me "Violation of primary key constraint".
Thanks,
Ram.
Notice it said "cannot be located for updating", operative word UPDATING. It doesn't understand that you're attempting to do an insert, it's being treated as an update. That's where you need to start.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 11:21 am
Matt,
This is exactly what I thought, but since this is automatic, I don't know what to do. The reason for me to use ADODC is so that I can search easily and sort quickly. I have over 4 million records to display otherwise I would not use ADODC. Any suggestions are welcome from all.
Thanks again.
Ram.
April 4, 2008 at 12:35 am
ramadesai108 (4/3/2008)
Shamsudheen,Thank you so much for your prompt reply, I have to fix this issue urgently. Here is my stored proc
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[DS_GetProductsByFilter]
@FieldName nVarChar(20),
@FieldValue nVarChar(50)
AS
SET NOCOUNT ON
DECLARE @Where VARCHAR(200)
DECLARE @sqlcmd VARCHAR(3000)
IF @FieldName = 'Show All'
SET @Where = ''
ELSE
IF @FieldName = 'Title'
SET @Where = ' WHERE ' + @FieldName +
' LIKE ' + CHAR(39) + '%' + @FieldValue + '%' +
CHAR(39)
ELSE
SET @Where = ' WHERE ' + @FieldName +
' = ' + CHAR(39) + @FieldValue + CHAR(39)
SET @sqlcmd = 'Select PM.itemcode, isbn,title,cost,
price,mfgsuggestedprice,altprice, PD.Discount,validcost,
PM.VendorID, ProductType,PD.SchemeID,PM.description,
sectioncode,distributioncategory,itemalias,lastpurchaseorder,
Keywords,lastvouchernumber,allowregisterpricing,
allowregistertitle,reorderable,lastinvoiceno,PriceMethod,
CostBasis,PurchaseFromVendorID,reclassfromitemcode,
Internetitem,internetminqty,internetmaxqty,UpdateQOH,
PD.VendorItemNo,PD.ASIN , Note
FROM ProductMaster PM
LEFT OUTER JOIN ProductMasterDist PD ON PM.ItemCode = PD.ItemCode '
+ @Where + ' ORDER BY PM.itemcode'
EXEC (@sqlcmd)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
GRANT EXECUTE ON DS_GetProductsByFilter
Thanks,
Ram.
Ram,
in your query there is two table ProductMaster PM and ProductMasterDist PD and in which table you trying to insert or update
April 4, 2008 at 5:36 am
First of all you need to understand few basic stuff -
1> Select statements don't update/insert into tables
2> A primary key is unique and cannot have repetitive values
3> There is a code segment with on_click where there is a function call or a procedure call which does the update/insert stuff
4> You don't have any problem with your select procedure
5> If you change the values in an existing record, you trigger an update
6> If you click on a new cell, a new record should be created with PK+1(if the PK column is identity) or else you need to specify a new value which is not present in the column already
Your problem shows that probably your primary key column is not an identity. Hence, you need to provide a new value.
But, to complicate things, you have a join in the select list, and it is most probable that if you cannot code the insert/update section, you cannot update/insert using a true grid, as this involves multiple insert/updates. If this enlightens you, the problem should be solved, else you should post the problem on you programming language forum. There seems to be a bit of gap in the understanding of how your true grid works.
You can check out the following sites for further info -
related stuff -
http://delphi.about.com/od/usedbvcl/l/aa032503a.htm
http://www.mpassociates.gr/software/catalog/prog/dataccess.html
Please make sure you go through the part -
If your queries don’t span multiple tables (a common case in many applications), using ComponentOne Query becomes a matter of "point and click."
and
You can create multi-table views combining information stored in different tables with the Schema Designer.
Note: Views cannot be updated.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply