Database Update and ASP

[nicks60610]

I am trying to do something that defies all logic according to my ASP code, hehehe. I have a database which I am trying to update a field in, but this field is only tied together to another table via a date field and the field I need to update is a comments field.

Basically this:

Page1: Fill out a form and submit to the DB, the DB updates the fields and the date field is the same in table 1 and 2 as they are related.

Page2: I want to update the comments field in table 2 but when I try to tell the DB that the date_created field is where it should filter, it tells me it is an invalid field.

Table 1 creates the date field (entered_date)

Table 2 takes this date and puts it in the (date_created) field.

When I do my update statement I do this:

objConn.execute ("Update EQ_EVENTS_COMMENTS set REMARKS = '"& fldRemarks & "' WHERE Date_Created = ('" & entered_date & "')

But since the 2 fields are from two different tables it does not know what to do. I even tried carrying the value over in a session string, but it still did not want to.

Any ideas???

 

[shaddow]

are you using OLEDB or ODBC connection?

________
George, M

 

[nicks60610]

ADODB - here is the connection string:

Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.Open "Provider=MSDAORA.1;Password=xxxxxx;User ID=xxxxxx;Data Source=xxxxxxxxxxxxxx;Persist Security Info=True;"

 

[shaddow]

Ok looks like ODBC for me,
Try this query

objConn.execute "Update EQ_EVENTS_COMMENTS set REMARKS = '"& fldRemarks & "' WHERE Date_Created = '" & entered_date & "'

if doesnt work will be better if you display few fields of both table and the related fields to each other.

________
George, M

 

[nicks60610]

Still does not want to cooperate :-( , at least I don't get any mismatch errors on the table and fields.

Here are the fields in the table EQ_EVENTS_COMMENTS:

Plant
Date_created
Time_created
Remarks

These are the fields in the table WO_WORK_ORDER (this is where the date entered field is updating both tables:

Plant
Wo_base
Wo_task
Wo_subtask
etc
etc
etc
Entered_Date


Then I just noticed that in the table EQ_EVENTS, there is a field called Date_Created and Time_Created, so these 3 tables are more than likely all related with those fields?

 

[shaddow]

It seems to me that those 2 tables shoulw have the plant as the relation between them.
I think the problem here it's what you want to do and what is the flow of the operation.
I mean what you do before updating those tables. I still dont knw why you would relate 2 tables only by a date... there could be more then same date and then will be problems.

I presume that you see some records from WO_WORK_ORDER first and then you add events and comments after.

________
George, M

 

[nicks60610]

Yes I know what you mean, I also think that this database was written with the thought in mind that no one would use web screens to update/insert all these statements all in one sweep. The app is a commercial app with an oracle backend and when you do this through the app you first create the work order, then when you close it, the event screen is invoked and you can add the remarks, etc. The only problem is if I link it to the plant field it would update all closed work orders since they are using the same plant #.

Thanks for your help though,

 

[DavidAkers]

Silly question, but is the Date_created field a date field? Or could it be a text field?

-David

 

[nicks60610]

It is a date field, so I have to somehow capture the date/time that the event is created on screen 1 and have it &quot;tucked away&quot; on screen 2 so I can filter it in on screen 3 so that the remarks field is updated...seems like it should be easy but I think the DB designer who designed this table was smoking something that day to link a critical field with a date/time field <grin>

 

[nicks60610]

Update:

I was able to catch the 2 time/date stamps on my page1 using this:

Set objRs = Server.CreateObject(&quot;ADODB.Recordset&quot

objRs.Open &quot;SELECT * FROM EQ_EVENTS WHERE PLANT = '854' and WO_BASE = '&quot;& Session(&quot;Var1&quot&&quot;'&quot; , objConn

w_created = (ObjRs.Fields(&quot;DATE_CREATED&quot.Value)

Set objRS = Nothing

Set objRs1 = Server.CreateObject(&quot;ADODB.Recordset&quot
objRs1.Open &quot;SELECT * FROM EQ_EVENTS WHERE PLANT = '854' AND WO_BASE = '&quot;& Session(&quot;Var1&quot&&quot;'&quot;, objConn


w_created2 = (objRs1.fields(&quot;TIME_CREATED&quot.Value)

Then storing the 2 in a session value:

session.Contents(&quot;var3&quot = w_created
session.Contents(&quot;var4&quot = w_created2

On Page 2:

objConn.Execute (&quot;Update EQ_EVENTS_COMMENTS set remarks = 'NICKLAS' WHERE PLANT = '854' and DATE_CREATED = TO_DATE('&quot;& Session(&quot;MyVar3&quot&&quot;','MM/DD/YYYY') and TIME_CREATED = '&quot;& Session(&quot;MyVar4&quot&&quot;'&quot

I did a response.write on each of these pages and the time matches to the millisecond, but the field &quot;REMARKS&quot; is still not being updated, my colleague thought it is in the WHERE statement, that it does not like something in it and therefore does not update, but the table only has 4 fields and I am touching each of them, any ideas???

 

[gjconely]

Are you returning any errors. Could what you are trying to update REMARKS with contain any 's in it? If so, do a replacestring(,&quot;'&quot;,&quot;''&quot command and fix that. Also, if you are working with DATE fields use a command like
UPDATE ??? SET ????=???? WHERE DATE LIKE '????'

The only problem again, is that you can have more then one entry per millisecond, so you might want to use more information for matching. Dont be scarred to add a UNIQUEID value to the tables - Add it as the last field so that you don't have to worry about the other programmers calling field #s not fieldnames in their code.


Greg Conely

http://www.ImageMentor.net

 

[nicks60610]

The issue is that this is not my DB but a commercial package with an application built on it, we are only creating the web screens as an added value to the functionality, otherwise I would have added a more unique value, I am not sure what the vendor was thinking of when they designed this table :- / .

No errors returned, but I will try the update statement you sent me, that may get it working : - ) , thanks!!

The field &quot;REMARKS&quot; is a mixed field so it can take any character and number without any issue. Let me try the update statement first and go from there - thanks again!!