Update certain NULL field

azae
04-17-2004, 03:37 AM
I am doing something on client server..

I only can add record.,
Only certain field is fill ("Time_In"), ("User_Nm"), ("Computer_ID")

But I cannot do the ("time_out") and ("consumed")

this program uses 2 winsock communication for sending data to update database in server.

the code is as follow..
-------Client.vbp----------------
Private Sub Form_Unload(Cancel As Integer)

Winsock1.SendData "disconnect||" & tmpMatrik & "||" & Winsock1.LocalHostName
Winsock1.Close

End Sub
------------------------

--------------Server.vbp-----------
Case "disconnect"
Matrik1 = vntArray(1)
Machine1 = vntArray(2)
date2 = Format(Now, "dd/mm/yyyy")


'----------I only want this protion to be update in database
timeout = Format(Now, "long time")
consume = timeout - timein
'------------------------------------------------------

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
Set db = OpenDatabase(App.Path & "\dbProject.mdb")
Set rs = db.OpenRecordset("Select * from TRANSACTION where Date = '" & date2 & "' and Matrik_No = '" & Matrik1 & "' and Machine_ID = '" & Machine1 & "'")

db.Execute ("Update TRANSACTION set Time_Out = #" & timeout & "#, Time_Consumed = #" & consume & "# where Date = #" & date1 & "# and Matrik_No ='" & Matrik1 & "' and Machine_Id ='" & Machine1 & "'")
rs.Close
db.Close
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''
End Select
-----------------------------------------


these only some part of my program.. I cannot give all because is very huge..

Oh, I use DAO method.

Is there anything wrong to SQL statement I wrote. I does not promt error message.
please help me...
I really working on this for a long time.. But my lecturer wants result.. not just see me doing...
Please help me check and tell me how I can fix it.

Thanks

Shurik12
04-17-2004, 07:07 AM
Hi,

What are the datatypes of the fields Time_Out and Time_Consumed and what are the parameters' values you are trying to pass?
Also it would be nice to know the error number/description

Shurik.

azae
04-18-2004, 02:45 AM
I already defined as "short date" in database. I use Ms Access...
The problem is taht there is no error message.
Once client is login success, the database in server will will record time_in, student_name, pc_id
leaving time_out, and consume to be null..

I tried to do UPDATE set ...... WHERE student_name......
after connection close.
But no reaction.

Hi,

What are the datatypes of the fields Time_Out and Time_Consumed and what are the parameters' values you are trying to pass?
Also it would be nice to know the error number/description

Shurik.

Shurik12
04-18-2004, 03:59 AM
So if I understand correctly you want to update Time_Out and Time_Consumed fields after a user logs in (naturally without changing the rest of the fields for this given user)? Is it what you're trying to achieve?

azae
04-18-2004, 09:17 PM
The fields was NULL after user login..
These fields suppose will automatic update after the user in client logout if it find matching...

I did not set primary key or relationship yet. I do it to make sure it work first.

I wonder why cannot, because on client, it send data before Winsock1.close
In addition it has no error of connection protocol.

I assume is fault on SQL for "Update ... Set ... Where ....."
I am new on this, that why I really dunno how to find mistake... The sooner I discaver the problem, the sooner I find way to fix it.

PS ..this is my final year project

azae
04-20-2004, 01:32 AM
I tried several times, but the fields do not automatic update.
There is not wrong with the client.vbp because if no connection it will promt error code "40006" or "40020" "Wrong protocol for current state"

It is the database. (I think)
The SQL is no error, the database is Access in 97 format.
The file refference is DAO 3.5

I believe someone here must have done what I am doing now.
Kindly tell me where is the mistake, because it is important for me to think solution...
Any opinion will be appreciated...

Shurik12
04-20-2004, 01:42 AM
I just had a look at you first post:



Set rs = db.OpenRecordset("Select * from TRANSACTION where Date = '" & date2 & "' and Matrik_No = '" & Matrik1 & "' and Machine_ID = '" & Machine1 & "'")

db.Execute ("Update TRANSACTION set Time_Out = #" & timeout & "#, Time_Consumed = #" & consume & "# where Date = #" & date1 & "# and Matrik_No ='" & Matrik1 & "' and Machine_Id ='" & Machine1 & "'")
rs.Close



And don't see a reason for opening the recordset in this context...You open it, then try to execute the sql against the connection object and then close the recordset....

As for tyhe UPDATE sql itself, please do te following



Dim sSQL As String

sSQL= "Update TRANSACTION set Time_Out = #" & timeout & "#, Time_Consumed = #" & consume & "# where Date = #" & date1 & "# and Matrik_No ='" & Matrik1 & "' and Machine_Id ='" & Machine1 & "'"

Debug.Print sSQL

db.Execute (sSQL)



Now press CTRL+G and copy the content of the immediate window here so that one try to see how the sql looks like after the parameters have been passed.



SHurik.

azae
04-24-2004, 02:44 AM
Thanks Shurik for your time for checking and correction.

I know something now at least., But this havent solve my problem yet.
The result is still the same.
It is good that it having error message. At least I know where problem is.
Hmmm.... look like I need to know a bit more for me able to develop it as stated in planning stage...

Wish me luck. Maybe I got solution at the last minute.
May God bless me....

Azae

MKoslof
04-24-2004, 08:07 AM
Well, can you list out the data types defined for each field in this update statement? If you go to the table where you defined all these fields, are they all strings except for two date fields? If you have any numeric data types, we need to change your UPDATE syntax.

And, DATE is a reserved word, this will effect your where clause. Put brackets, [], around it [Date]..this is also part of your problem.

azae
04-25-2004, 02:49 AM
can I send u the database for u?
It a lot easy for both ways communication. It is MsAccess 97 format.

MKoslof
04-25-2004, 09:03 AM
No just tell me the data TYPES for each field in the INSERT statment. And, did you put brackets around the [Date] field like I suggested before? This is a reserved word.

azae
04-26-2004, 12:20 AM
after second consideration, I think is foolish to send like that.
I think better to list out as you said. These how I define in database
TRANSACTION table

Date : Date/Time (short date)
Matrik_No : Text
Time_In : Date/Time (short time)
Time_Out : Date/Time (short time)
Time_Consumed : Date/Time (short time)
Machine_Id : Text

Database is in MsAccess 97 format.

In VB, this is how I define the variable,
Dim date1 As Date
Dim timein As Date
Dim date2 As Date
Dim timeout As Date
Dim consume As Date
Dim Matrik As String
Dim Machine As String
Dim Matrik1 As String
Dim Machine1 As String
Dim str As String
Dim sSQL As String

You mention about putting bracket. Actually I really dont understand.

MKoslof
04-26-2004, 07:56 AM
look carefully at the WHERE clause ([Date])



Dim sSQL As String

sSQL= "Update TRANSACTION set Time_Out = #" & timeout & "#, Time_Consumed = #" & consume & "# WHERE [Date] = #" & date1 & "# AND Matrik_No ='" & Matrik1 & "' AND Machine_Id ='" & Machine1 & "'"

Debug.Print sSQL

db.Execute sSQL

azae
04-28-2004, 01:43 AM
Dear MKoslof
Thanks for the suggestion. However, the result is the same as before.
I think I must do several test on it to know where is the problem.

I done in client.vbp, the event Form_Unload is in working the way I want it to be. I am worry about the server side on whether it receive correct message from the client.

The time_in can be insert fine, but the remaining 2 fields meant for updating later, give me extra headache.
I do some checking first. Any dicovery I maybe announce here.

Azae

Shurik12
04-28-2004, 02:06 AM
Hi again,

could you check post #7 (use your latest sql string ) and try to do that trick with Debug.Print. This might shed some light on the problem.

Shurik.

MKoslof
04-28-2004, 07:34 AM
I believe both Shurik and I have included Debug.Print statements in our code samples :). Please tell us the string out-putted to the debug window.

azae
05-02-2004, 02:27 AM
Sorry for late reply,

I was discuss my problems with my supervisor regarding the problem in hoping the presentation will not be tough on me. Meanwhile I do some add ups, at least show them I done a lot of things.

Thanks for your help, but so sorry I really dun understand what Shurik and MKoslof want me to do.

To be frank, I am very new in VB, but I really tried my best.

Dennis DVR
05-02-2004, 02:49 AM
they want you to do this


Dim sSQL As String

sSQL= "Update TRANSACTION set Time_Out = #" & timeout & "#, Time_Consumed = #" & consume & "# WHERE [Date] = #" & date1 & "# AND Matrik_No ='" & Matrik1 & "' AND Machine_Id ='" & Machine1 & "'"

Debug.Print sSQL 'This will print the value of sSQL in the immidiate window

db.Execute sSQL


and the Debug.Print sSQL will print the value of sSQL in your immidiate window
of your Visual Basic and copy it and paste it here to see the exact value of sSQL

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum