SQL Update statement

Trying to find and update specific records in an Access database

Page 1 of 1

8 Replies - 60126 Views - Last Post: 19 July 2008 - 02:49 PM Rate Topic: -----

#1 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 422
  • View blog
  • Posts: 3,032
  • Joined: 18-September 06

SQL Update statement

Post icon  Posted 11 June 2008 - 08:20 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Update statement

#2 vbnetskywalker  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 98
  • Joined: 08-June 08

Re: SQL Update statement

Posted 12 June 2008 - 09:33 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 422
  • View blog
  • Posts: 3,032
  • Joined: 18-September 06

Re: SQL Update statement

Posted 16 June 2008 - 02:17 AM

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

Was This Post Helpful? 1
  • +
  • -

#4 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 422
  • View blog
  • Posts: 3,032
  • Joined: 18-September 06

Re: SQL Update statement

Posted 24 June 2008 - 06:13 AM

So guessing no-one here can help me?
Was This Post Helpful? 0
  • +
  • -

#5 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: SQL Update statement

Posted 24 June 2008 - 08:53 AM

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.
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5882
  • View blog
  • Posts: 12,760
  • Joined: 16-October 07

Re: SQL Update statement

Posted 24 June 2008 - 09:43 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 422
  • View blog
  • Posts: 3,032
  • Joined: 18-September 06

Re: SQL Update statement

Posted 25 June 2008 - 06:01 AM

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
Was This Post Helpful? 0
  • +
  • -

#8 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 422
  • View blog
  • Posts: 3,032
  • Joined: 18-September 06

Re: SQL Update statement

Posted 27 June 2008 - 06:04 AM

Never mind. Problem is solved. All I needed were some single quote marks in the SQL statement.
Was This Post Helpful? 0
  • +
  • -

#9 newkt477  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 18-July 08

Re: SQL Update statement

Posted 19 July 2008 - 02:49 PM

BORT,

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

THANKS
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1