[Bort]

I'm working on a project which allows people to sign in when they enter a building, and sign out when they leave, kind of like a visitor register. I have an electronic signature pad for the actual signing. That bit is working fine. I have a 'Save' button which saves details of people signing in or out depending on which RadioButton is selected (radIn or radOut). The signing in part works fine, everything gets saved to a newly created record in my access database. Signing out, however, doesn't seem to work at all.

Can someone please take a look at my code below and tell me what I'm missing, and also that my SQL statement for finding and updating the record is correct.

		ElseIf radOut.Checked = True Then

			'SQL Query to retrieve the signer's last record
			Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\Documents and Settings\PC\My Documents\Visual Studio 2005\Projects\Register v7.0\Register v7.0\Reg v2.mdb'")
			Dim SQLQuery As String = "UPDATE Time MAX(EntryID) SET TimeOut = " & TimeString & " and SignatureOutData = " & SigString & " WHERE FirstName = " & cmbName.Text & " and TimeOut = NULL"
			MessageBox.Show(SQLQuery)
			Dim SignOut As New OleDbCommand(SQLQuery, myConnection)

			Me.TimeTableAdapter1.Update(Reg_v2DataSet.Time)

			'Confirms save
			MessageBox.Show("Saved!")

			'saves final information to database
			Reg_v2DataSet.AcceptChanges()

The line "MessageBox.Show(SQLQuery)" gives the correct information for the variables used, but I'm not entirely sure about the SQL query's format.

Thanks for taking a look guys.
Bort

[vbnetskywalker]

Update statement is in the next format :

"Update TableName Set 
	FieldName1= Value1 ,
	FieldName2= Value2 ,
	FieldName3= Value3 ,
	FieldName4= Value4 ,
	.
	."

notice : your fault was when you put (and) between fields instead of ( , )
and I'm not pretty sure about the name of the table you put (and what is this Max ... take a deep look at the name of the table)
so reformat your (SQL) statement , becase I didin't write it for you this was a format only.

by the way this is not a (Query) this is a (Command) because it doesn't have a returned value.
(Insert , Update, Delete ) are called (NonQuery Commands).

I hpoe you make it this time , good luck.

[Bort]

Thanks for getting back to me about this.

The table name is Time, and the MAX(EntryID) should really be part of the Where part of the command (I think). What it is trying to do is find a record in the Time table with the highest entry ID (MAX(EntryID)) where the first name matches that on the form, and the signature out data is blank, then update that field with the needed details.

I'll take a look at the format and see if changing it helps.

Bort

Edit:

I've now changed the SQL command to read "UPDATE Time SET TimeOut = " & TimeString & " , SignatureOutData = " & SigString & " WHERE MAX(EntryID) and FirstName = " & cmbName.Text & " and TimeOut = NULL" but it still does not work. I also tried changing the 'and's in the WHERE part of the command to ',' too, but this did no good. Trying to list it like you showed in your example didn't do anything either, just caused more errors because it only took the first line as the command and ignored the rest. I could have used _ to make it look at the next few lines, but it was just as easy to have the entire command on one line.

Been trying to set up the UPDATE statement again, this time with different formatting.

			Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Application.StartupPath & "\Reg v2.mdb'")
			Dim SQLCmd As New OleDbCommand
			Dim SQLStr As String = "UPDATE [Time] SET TimeOut = '" & TimeString & "' , SignatureOutData = '" & SigString & "' WHERE Date = '" & DateTime.Today & "' and FirstName = '" & cmbName.Text & "' and TimeOut = ''"

			myConnection.Open()
			SQLCmd.Connection = myConnection
			SQLCmd.CommandText = SQLStr
			SQLCmd.ExecuteNonQuery()

			myConnection.Close()

This is what comes of spending all day working on a really annoying problem...

This post has been edited by Bort: 16 June 2008 - 07:47 AM

[Bort]

So guessing no-one here can help me?

[Jayman]

It would be helpful if you include any error message that you are getting during compile or runtime.

Also, describe exactly what is happening verses what should be happening.

[baavgai]

I'm at a bit of a loss at to what you're aiming for here, so I'm going to guess.

First, EntryID is the identity column, and primary key of table Time? So, you want to update for this EntryId?

SELECT EntryID
	FROM [Time]
	WHERE FirstName=@FirstName and TimeOut is NULL

So, your update command would look something like this:

UPDATE [Time]
	SET TimeOut=@TimeOut, SignatureOutData=@SignatureOutData
	WHERE EntryID in (
		SELECT EntryID
			FROM [Time]
			WHERE FirstName=@FirstName and TimeOut is NULL
	)

Am I with you so far?

If so, then here's some code that should work:

				
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\Documents and Settings\PC\My Documents\Visual Studio 2005\Projects\Register v7.0\Register v7.0\Reg v2.mdb'")
Dim cmd As OleDbCommand = conn.CreateCommand()

cmd.CommandText = "UPDATE [Time]" _
		& " SET TimeOut=" & TimeString & ", SignatureOutData=" & SigString  _
		& " WHERE EntryID in (" _
		& " SELECT EntryID" _
		& " FROM [Time]" _
		& " WHERE FirstName='" & cmbName.Text & "'" _
		& " and TimeOut is NULL" _
		& " )"
Try
	cmd.Connection.Open()
	cmd.ExecuteNonQuery()
Finally
	cmd.Connection.Close()
End Try

Hope this helps.

[Bort]

Hi Jayman, Baavgai,

Thanks for your responses.

Jayman, I was not getting any error messages. The dataset was not updating to the database, leaving blank columns where there should have been data.

Baavgai, I tried your update command but on the line 'cmd.ExecuteNonQuery' I get an error saying 'Syntax error (missing operator) in query expression '13:27:52'.'. The query expression is TimeString. There was one slight change I made to your update command though. In the SELECT part of the statement, I changed EntryID to MAX(EntryID) in order to find the single, most recent record which matches the rest of the criteria. On the whole though, you have what I am trying to do worked out pretty well.

Bort

[Bort]

Never mind. Problem is solved. All I needed were some single quote marks in the SQL statement.

[newkt477]

BORT,

I AM GETTING THE SIMILAR PROBLEM. COULD YOU PLEASE TELL ME WHERE DID YOU ADDED SINGLE QUOTES?

THANKS