@@fetch_status in trigger

in the trigger for update and insert i have the following cursor

declare crs cursor static local for
SELECT [id] FROM inserted

open crs
fetch next from crs into @.v1

print @.@.fetch_status

while @.@.fetch_status = 0
begin
print @.v1

fetch next from crs into @.v1
end

close crs
deallocate crs

the problem is that the @.@.fetch_status is now always -1 and I'm pretty sure that it worked some time ago and i can't figure what is changed.

any ideas ?
thnx.Why would you want to do this? What's the real trigger look like?

Can you post that code?

A cursor in a trigger would more than likely perform poorly...|||I use cursor in trigger cause i can have inserts/updates from multiple sources and i don't want to break the logic.

code in attach|||fixed.

cursor threshold was 0; changed to -1

HLEP..Sql Server UPDATE INNER JOIN QUERY ..

Im using an ADP to connect to a SQL Sqever DB.
In access it was really easy to say

Inner join on table1 and table2 and update columnA from table1 with
columnC from table2 where table1.key = table2.key and table2 columnB =
1 and table2 columnD = 4

I have tried all manner of beasts to get this thing to work..

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))

This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0' part which is really important

Any Help would be great...This way looks like it should work also but I get an error 'ADO Error:
HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
can only show one table in an update qurey?

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 =
(SELECT HARMFULEVENT
FROM HARMFULEVENT
WHERE (HARMFULEVENT.UNITID = 1 AND
HARMFULEVENT.LISTORDER = 0))
WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)|||SAME ADO ERROR WHEN I USE THIS ???

UPDATE dbo.GIS_EVENTS_TEMP
SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
FROM GIS_EVENTS_TEMP G INNER JOIN
HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
WHERE (H.UNITID = 1 AND H.LISTORDER = 0)|||(meyvn77@.yahoo.com) writes:
> Im using an ADP to connect to a SQL Sqever DB.
> In access it was really easy to say
> Inner join on table1 and table2 and update columnA from table1 with
> columnC from table2 where table1.key = table2.key and table2 columnB =
> 1 and table2 columnD = 4
>
> I have tried all manner of beasts to get this thing to work..
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)
> AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))
> This almost works but ignors the 'HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0' part which is really important
> Any Help would be great...

Unfortunately, it's not very easy to help if we don't know what
tables you have. The standard recommendation for this type of
problem is to post:

o CREATE TABLE statements of the tables nvolved. (Preferrably
cut down to the columns relevant to the problem.(
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of the business problem.

The two first points makes it easy to copy and paste into Query Analyzer,
so a tested solution can be developed. The third point makes it possible
to verify that the solution is correct. And the fourth point gives some
extra information which helps to understand the general problem.

> This way looks like it should work also but I get an error 'ADO Error:
> HARMFULEVENT Does not match a table in the query' ?? Is this cuz you
> can only show one table in an update qurey?
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 =
> (SELECT HARMFULEVENT
> FROM HARMFULEVENT
> WHERE (HARMFULEVENT.UNITID = 1 AND
> HARMFULEVENT.LISTORDER = 0))
> WHERE (CASEID = HARMFULEVENT.CRASHNUMBER)

No, but because you are referring to HARMFULEVENT outside the subquery.

> AME ADO ERROR WHEN I USE THIS ???
>
> UPDATE dbo.GIS_EVENTS_TEMP
> SET FSTHARM1 = HARMFULEVENT.HARMFULEVENT
> FROM GIS_EVENTS_TEMP G INNER JOIN
> HARMFULEVENT H ON G.CASEID = H.CRASHNUMBER
> WHERE (H.UNITID = 1 AND H.LISTORDER = 0)

Here you are mixing use of aliases and table name. Once you have
introduced an alias, you can not refer to the full table name in
the query.

--
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 don't think that you can write something like this:
SELECT HARMFULEVENT FROM HARMFULEVENT

.Update creates new record but all fields null

Hi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are null.
I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct.
if request.QueryString("add")<> "" then
Dim rsUpdateEntry
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3

rsUpdateEntry.AddNew

rsUpdateEntry.Fields("title1") = Request.Form("title1")
rsUpdateEntry.Fields("body1") = Request.Form("body1")
rsUpdateEntry.Fields("title2") = Request.Form("title2")
rsUpdateEntry.Fields("body2") = Request.Form("body2")
rsUpdateEntry.Fields("title3") = Request.Form("title3")
rsUpdateEntry.Fields("body3") = Request.Form("body3")
rsUpdateEntry.Fields("title4") = Request.Form("title4")
rsUpdateEntry.Fields("body4") = Request.Form("body4")
rsUpdateEntry.Fields("title5") = Request.Form("title5")
rsUpdateEntry.Fields("body5") = Request.Form("body5")
rsUpdateEntry.Fields("image1") = Request.Form("attach1")
rsUpdateEntry.Fields("image2") = Request.Form("attach2")
rsUpdateEntry.Fields("image3") = Request.Form("attach3")
rsUpdateEntry.Fields("image4") = Request.Form("attach4")
rsUpdateEntry.Fields("image5") = Request.Form("attach5")
rsUpdateEntry.Fields("country") = Request.Form("country")
rsUpdateEntry.Fields("dest_url") = Request.Form("dest_url")


rsUpdateEntry.Update

rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
end if
Thanks
MarkAt the risk of offending you, may I suggest that you instead consider a stored procedure? At first it require a bit more effort and thought, but in the long run, stored procedures are a sensible way to handle database activity (including updates, inserts, selects and deletes).

As for the code you've written, are you sure that the .Add is in the right place? It looks to me (though I don't write my code this way) like it should come after you have assigned all of the values.

Regards,

hmscott

.Net 2.0 and datetime

Hi,

Here's my problem.

So using a stored proc and some parameters I want to update a datetime field

So I define @.theDate as datetime in my stored proc

in my vb.net app I have

dbcmd.Parameters.AddWithValue("@.theDate", SelectedDate)

where SelectedDate is

Dim SelectedDate as Nullable(of Datetime)

Now if SelectDate is a value all is good with the world.

However if SelectDate is Null I get an error.

How do I pass a null date to the stored proc ?

You need to add code like this:

dbcmd.Parameters.AddWithValue("@.theDate", (SelectedDate==null) ? DbNull.Value : SelectedDate)

DbNull.Value is a null for the database.

**Update**

Hi
I'm working with SQL2000, and I defined the below structure:
MasterTable(No numeric(3),desc char(40)) No is PK
DetailTable(No numeric(3),code numeric(4),other char(40)) No&Code are PK
and I defined an Update Cascade for their relation.and I have an update
trigger on MasterTable.
My question:
when I update the MasterTable, which one occures first(the casecade
action,or update trigger on that)?
I would be grateful if somebody could give me a hint.
ThanksReferential Integrity frist, then the After trigger.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

**to trace what has happened**

Hi
In SQL2000, how can I find what has happened in my DB in particular date?
for example I insert a ne row in table1 and I update 2 rows in table2. now
how can find what did I perfome?
I don't want to monitor the changes simentanously, I want to refer to them
afre some times.
should I refer to .log file of my db?(if ye ,how?) or should I do
something else?
any help would be thanked.Hi,
SQL Server will not log the events by default. In this case probably you can
write triggers to audit the Delete/Insert and Update events.
Thanks
Hari
SQL Server MVP
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
> Hi
> In SQL2000, how can I find what has happened in my DB in particular date?
> for example I insert a ne row in table1 and I update 2 rows in table2. now
> how can find what did I perfome?
> I don't want to monitor the changes simentanously, I want to refer to them
> afre some times.
> should I refer to .log file of my db?(if ye ,how?) or should I do
> something else?
> any help would be thanked.|||you could enable c2 auditing as many gov types are forced to do.
mike.menard
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
> Hi
> In SQL2000, how can I find what has happened in my DB in particular date?
> for example I insert a ne row in table1 and I update 2 rows in table2. now
> how can find what did I perfome?
> I don't want to monitor the changes simentanously, I want to refer to them
> afre some times.
> should I refer to .log file of my db?(if ye ,how?) or should I do
> something else?
> any help would be thanked.|||I think it does it, cause when we set the database in full mode it somehow
save the changes so it can use it when it wants to restore the db based on
the last changes.
is it right?
in other words, if I want to know what has happened during a period of
time should I save the changes as a log file, or is there any place to
have it systematically?
thanks
On Mon, 18 Sep 2006 15:17:22 +0330, Hari Prasad
<hari_prasad_k@.hotmail.com> wrote:

> Hi,
> SQL Server will not log the events by default. In this case probably you
> can
> write triggers to audit the Delete/Insert and Update events.
> Thanks
> Hari
> SQL Server MVP
> "M" <rez1824@.yahoo.co.uk> wrote in message
> news:op.tf2mdvein9ig5y@.system109.parskhazar.net...
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||Have a look at these:
http://sqlserver2000.databases.aspf...erver-data.html
Andrew J. Kelly SQL MVP
"M" <rez1824@.yahoo.co.uk> wrote in message
news:op.tf33aps8n9ig5y@.system109.parskhazar.net...
>I think it does it, cause when we set the database in full mode it somehow
>save the changes so it can use it when it wants to restore the db based on
>the last changes.
> is it right?
> in other words, if I want to know what has happened during a period of
> time should I save the changes as a log file, or is there any place to
> have it systematically?
> thanks
> On Mon, 18 Sep 2006 15:17:22 +0330, Hari Prasad
> <hari_prasad_k@.hotmail.com> wrote:
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

***Update Trigger ***

Hi Group Members
Description of table ALPHA
col1int
col2int
col3int
Description of table BETA
tar1int
tar2int
tar3int
I would like create an update trigger for any update on ALPHA table
it should be reflected rightaway on BETA table.
Thanks and good luck
I'm assuming that col1 & tar1 are FK with a 1-to-1, and col2/3 & tar2/3
are the columns you want to carry the updates to - but something like
this should work:
CREATE TRIGGER utr_AlphaBeta ON ALPHA
FOR UPDATE
AS
BEGIN
UPDATEBETA
SETBETA.tar2 = INSERTED.col2,
BETA.tar3 = INSERTED.col3
FROMINSERTED,BETA
WHEREINSERTED.col1 = BETA.tar1
END
.....and good luck to you.
kevincristo@.gmail.com wrote:
> Hi Group Members
>
> Description of table ALPHA
> col1int
> col2int
> col3int
>
> Description of table BETA
> tar1int
> tar2int
> tar3int
> I would like create an update trigger for any update on ALPHA table
> it should be reflected rightaway on BETA table.
> Thanks and good luck
|||On 23 Mar 2006 12:49:37 -0800, kevincristo@.gmail.com wrote:

>Hi Group Members
>
>Description of table ALPHA
>col1int
>col2int
>col3int
>
>Description of table BETA
>tar1int
>tar2int
>tar3int
>I would like create an update trigger for any update on ALPHA table
>it should be reflected rightaway on BETA table.
>Thanks and good luck
Hi kevincristo,
Best solution, IMO:
DROP TABLE Beta
go
CREATE VIEW Beta
AS
SELECT col1 AS tar1, col2 AS tar2, col33 AS tar3
FROM Alpha
go
Hugo Kornelis, SQL Server MVP

***Update Trigger ***

Hi Group Members
Description of table ALPHA
col1 int
col2 int
col3 int
Description of table BETA
tar1 int
tar2 int
tar3 int
I would like create an update trigger for any update on ALPHA table
it should be reflected rightaway on BETA table.
Thanks and good luckI'm assuming that col1 & tar1 are FK with a 1-to-1, and col2/3 & tar2/3
are the columns you want to carry the updates to - but something like
this should work:
CREATE TRIGGER utr_AlphaBeta ON ALPHA
FOR UPDATE
AS
BEGIN
UPDATE BETA
SET BETA.tar2 = INSERTED.col2,
BETA.tar3 = INSERTED.col3
FROM INSERTED,BETA
WHERE INSERTED.col1 = BETA.tar1
END
....and good luck to you.
kevincristo@.gmail.com wrote:
> Hi Group Members
>
> Description of table ALPHA
> col1 int
> col2 int
> col3 int
>
> Description of table BETA
> tar1 int
> tar2 int
> tar3 int
> I would like create an update trigger for any update on ALPHA table
> it should be reflected rightaway on BETA table.
> Thanks and good luck|||On 23 Mar 2006 12:49:37 -0800, kevincristo@.gmail.com wrote:

>Hi Group Members
>
>Description of table ALPHA
>col1 int
>col2 int
>col3 int
>
>Description of table BETA
>tar1 int
>tar2 int
>tar3 int
>I would like create an update trigger for any update on ALPHA table
>it should be reflected rightaway on BETA table.
>Thanks and good luck
Hi kevincristo,
Best solution, IMO:
DROP TABLE Beta
go
CREATE VIEW Beta
AS
SELECT col1 AS tar1, col2 AS tar2, col33 AS tar3
FROM Alpha
go
Hugo Kornelis, SQL Server MVP

(Urgent) Update statement takes for ever to excecute

Hi, i am trying to Update some records in my table but the update statement is taking for ever ...this is my update Statements

UPDATE Statements..ParticipantFundBalancesSETAct1 = ACT_ID1,TotAct1 = TOT_ACT1,Act2 = ACT_ID2, TotAct2 = TOT_ACT2, Act3 = ACT_ID3, TotAct3 = TOT_ACT3,Act4 = ACT_ID4,TotAct4 = TOT_ACT4,Act5 = ACT_ID5,TotAct5 = TOT_ACT5,Act6 = ACT_ID6,TotAct6 = TOT_ACT6, Act7 = ACT_ID7, TotAct7 = TOT_ACT7,Act8 = ACT_ID8,TotAct8 = TOT_ACT8,Act9 = ACT_ID9,TotAct9 = TOT_ACT9,Act10 = ACT_ID10,TotAct10 = TOT_ACT10,Act11 = ACT_ID11,TotAct11 = TOT_ACT11,Act12 = ACT_ID12,TotAct12 = TOT_ACT12,Act13 = ACT_ID13,TotAct13 = TOT_ACT13,Act14 = ACT_ID14,TotAct14 = TOT_ACT14,Act15 = ACT_ID15,TotAct15 = TOT_ACT15,Act16 = ACT_ID16,TotAct16 = TOT_ACT16,Act17 = ACT_ID17,TotAct17 = TOT_ACT17,Act18 = ACT_ID18,TotAct18 = TOT_ACT18,/*Act19 = ACT_ID19,TotAct19 = TOT_ACT19,Act20 = ACT_ID20,TotAct20 = TOT_ACT20, */ OpeningUnits = UNIT_OP,OPricePerUnit = PRICE_OP,ClosingUnits = UNIT_CL,CPricePerUnit = PRICE_CL,AllocationPercent = ALLOC_PER1FROMStatements..ParticipantFundBalances pfbJOIN (Selectcp.PlanId,p.ParticipantId,@.PeriodId Period,CASE WHEN a.FUND_ID = 'LOAN' Then 0 ELSEf.FundId END FundId,a.ACT_ID1,a.TOT_ACT1,a.ACT_ID2,a.TOT_ACT2,a.ACT_ID3,a.TOT_ACT3,a.ACT_ID4,a.TOT_ACT4,a.ACT_ID5,a.TOT_ACT5,a.ACT_ID6,a.TOT_ACT6,a.ACT_ID7,a.TOT_ACT7,a.ACT_ID8,a.TOT_ACT8,a.ACT_ID9,a.TOT_ACT9,a.ACT_ID10,a.TOT_ACT10,a.ACT_ID11,a.TOT_ACT11,a.ACT_ID12,a.TOT_ACT12,a.ACT_ID13,a.TOT_ACT13,a.ACT_ID14,a.TOT_ACT14,a.ACT_ID15,a.TOT_ACT15,a.ACT_ID16,a.TOT_ACT16,a.ACT_ID17,a.TOT_ACT17,a.ACT_ID18,a.TOT_ACT18,/*a.ACT_ID19,a.TOT_ACT19,a.ACT_ID20,a.TOT_ACT20, */a.UNIT_OP,a.PRICE_OP,a.UNIT_CL,a.PRICE_CL,Cast(Rtrim(i.ALLOC_PER1)as decimal)as ALLOC_PER1FROMASDBF a-- Derive the unique PlanId from the Statements ClientPlan tableINNERJOIN Statements..ClientPlan cpON a.PLAN_NUM = cp.ClientPlanIdANDcp.ClientId = @.ClientId-- Derive the unique ParticipantId from the Statements Participant tableINNERJOIN Statements..Participant pON a.PART_ID = p.PartId

--Derive the unique FundID from the Statements Fund Table...

Left OuterJOIN Statements..Fund fONa.FUND_ID = f.CusipORa.FUND_ID = f.TickerORa.FUND_ID = f.ClientFundId-- get the allocation percent from the INVSRCLEFTOuter JOIN INVSRC iONa.FUND_ID = i.INV_IDANDa.PLAN_NUM = i.Plan_NumberANDa.PART_ID = i.PART_IDWHEREa.Import = 1)aON pfb.PlanId = a.PlanIdANDpfb.ParticipantId = a.ParticipantIdANDpfb.PeriodId = PeriodIdAND pfb.FundId = a.FundId

While i insert data in my table i am checking if there are any loans in the ASDBF table and if there i am inserting a 0 in the particular

i am trying to up date the with in 3 different plans in the same table..

any help will be appreciated.

Regards

Karen

Never mind i fixed it by doing a group by..

Regards

Karen

(URgent) My update statement is not working..

Hi..

I have inserted couple of data in a particular table which is as follows..

Portfolio Table

PortId PlanId PortfolioName PorfolioDescription ClientPortolioId

77117838BALPORT NULLNULL77217838HIGHGROW NULLNULL77317838MODGROW NULLNULL

My FundDBF is as follows

RowNumber FUND_ID f.ASSETDESC Import

20BALPORT Balanced True21MODGROW Moderate Growth True22HIGHGROW High Growth True

and this is my Update statement

UPDATE Statements..PlanPortfolioSETPlanId = pm.PlanId,PortfolioName = pm.FUND_ID, PortfolioDescription = pm.ASSETDESCFROMStatements..PlanPortfolio pJoin (SELECT DISTINCTp.PlanId,pd.FUND_ID,f.ASSETDESC ---pd.FUND_IDFROMPartDBF pdINNERJOIN Statements..ClientPlan pon pd.PLAN_NUM = p.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_IDWHERE pd.Import = 1AND NOT (pd.FUND_IDISNULLORLen(pd.FUND_ID) = 0ORpd.FUND_IDNOT IN (SELECTPortfolioNameFROMStatements..PlanPortfolio ppWherepp.PlanId = p.PlanId))) pmon p.PlanId = pm.PlanId

I am trying to put the above table with f,AssetDesc in the PorfolioDescription field..

Any help will be appreciated..

Regards

Karen

Can you provide the result of this query:

SELECT DISTINCT
p.PlanId,
pd.FUND_ID,
f.ASSETDESC ---pd.FUND_ID
FROM
PartDBF pd
INNERJOIN Statements..ClientPlan p
on pd.PLAN_NUM = p.ClientPlanId
INNERJOIN FundDBF f
on pd.FUND_ID = f.FUND_ID
WHERE
pd.Import = 1
AND NOT (
pd.FUND_IDISNULL
OR
Len(pd.FUND_ID) = 0
OR
pd.FUND_IDNOT IN (
SELECT
PortfolioName
FROM
Statements..PlanPortfolio pp
Where
pp.PlanId = p.PlanId
)
)

|||

Karen,

Not for nothin' but if those are real table and column names from your database, you are exposing a lot more than you should of what is obviously a very sensitive database.

|||

charles i have change the database Name.... and included dummy names...

|||

Dinakar,

when i ran that query the result set is empty..

Regards

Karen

|||

Sorry Karen, just making sure - I had a guy post a month ago who posted some stuff he shouldn't have and we wound up taking the thread down.

|||

so until the query I posted (you subquery) returns something your UPDATE will not work. Take the SELECT I posted and start with that..

|||

Dinakar ,

I updated the query bit to this

Update Statements..PlanPortfolioSET--SELECT Distinct PlanId = pd.PlanId, PortfolioName = pd.FUND_ID, PortfolioDescription = pd.ASSETDESCFROMStatements..PlanPortfolio pJoin (SELECT DISTINCTcp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROMPartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_ID/*AND NOT (pd.FUND_ID IS NULLORLen(pd.FUND_ID) = 0OR *//*AND NOT EXISTS (SELECTPortfolioName--PortfolioDescriptionFROMStatements..PlanPortfolio ppWherepp.PlanId = cp.PlanId) */--)) pdon p.PlanId = pd.PlanId

and when i run the select distinct part i get this

17841 BALPORT Balanced
17841 HIGHGROW High Growth

17841 MODGROW Moderate

which is correct but rem out the Select distinct and use an update it doesnt do any thing...

I dont know whats going wrong

|||

You just need to update the desctription.

Update Statements..PlanPortfolio
SET
--SELECT Distinct
PlanId = pd.PlanId,
PortfolioName = pd.FUND_ID,
PortfolioDescription = pd.ASSETDESC

Also, what does this return:

SELECT DISTINCT
cp.PlanId,
pd.FUND_ID,
f.ASSETDESC--pd.FUND_ID
FROM
PartDBF pd
INNERJOIN Statements..ClientPlan cp
on pd.PLAN_NUM = cp.ClientPlanId
INNERJOIN FundDBF f
on pd.FUND_ID = f.FUND_ID

|||

The Select distinct returns

17841 BALPORT Balanced
17841 HIGHGROW High Growth
17841 MODGROW Moderate

|||

So your subquery is returning 3 rows for same planId of 17841? which is different from planid of 17838 in the outer SELECT ?

|||

actually it is right cause i have delete plan Id 17838 and inserted a new plan..|||

Dinakar,

The reason its not updating it because for every result returned in the Select Distinct query has a different portfolioId... so if i just update the portfolio desicription its gonna do that for all the descriptions who planId matches ...

so do u have any ideas..

Regards

Karen

|||

Run these queries again:

SELECT DISTINCT cp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROM PartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_IDSELECT Distinct PlanId = pd.PlanId, PortfolioName = pd.FUND_ID, PortfolioDescription = pd.ASSETDESCFROM Statements..PlanPortfolio pJoin (SELECT DISTINCT cp.PlanId,pd.FUND_ID,f.ASSETDESC--pd.FUND_IDFROM PartDBF pdINNERJOIN Statements..ClientPlan cpon pd.PLAN_NUM = cp.ClientPlanIdINNERJOIN FundDBF fon pd.FUND_ID = f.FUND_ID) pdON p.PlanId = pd.PlanId

|||

Dinakar this is the result of both of my queries and they are the same

17842 BALPORT Balanced
17842 HIGHGROW High Growth
17842 MODGROW Moderate

(SQL Mobile)Can SELECT, but cannot INSERT/UPDATE. Why not?

I'm just getting started with using SQL Server Mobile while creating a handheld app using Visual Studio 2005. So far, I have succeeded in RETRIEVING data from the mobile database, but I don't understand why my INSERT nor UPDATE statements will not work. (I'm assuming they don't work, because after I run my code via the debugger, I oddly get no error message, but when I "open" the table in the Server Explorer, I do not see my expected results. Do I need to do some kind of "commit" or something? Here's my code. Thanks for any help! - Sue

-

'Create SQL statement:
Dim strSQL As String
strSQL = "INSERT INTO MyTable (col1, col2) VALUES ('yucky', 'poo')"

'Create DB connection:
Dim connDB As New System.Data.SqlServerCe.SqlCeConnection
connDB.ConnectionString = ("Data Source =" _
+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "\SueUWM.sdf;"))

'Create DB command:
Dim cmndDB As New System.Data.SqlServerCe.SqlCeCommand(strSQL, connDB)

'Open the connection:
connDB.Open()

'Execute the command:
Dim intRecordsAffected As Integer
Try
intRecordsAffected = cmndDB.ExecuteNonQuery()

If intRecordsAffected <> 1 Then
MessageBox.Show("Unable to insert into database.")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString, "Insert Error")
End Try

'close the DB connection:

connDB.Close()

-

I am so dumb! I just learned that the insert & update modifications are made on the .sdf file on the emulator, and therefore will *not* be visible to me in Visual Studio's Server Explorer (on the desktop)! - Sue