God Dam VB crap

Orlie

This is what I have...

strSQL = "Update RoomFile SET RoomStatus = '" & NewRoomStatus & "'"
strSQL = strSQL & " WHERE RoomName = " & grdEnquiry.Text

The error is wrecking my head! I'm sure it's quite simple but I can't figure it out!

Run-time error '3075'

Syntax error (missing operator) in query expression 'RoomName = Alpine View'.

error is on the dbHilton.Execute (strSQL)

[CrimsonGhost]

strSQL = "Update RoomFile SET RoomStatus = '" & NewRoomStatus & "'"
strSQL = strSQL & " WHERE RoomName = '" & grdEnquiry.Text & "'"

Or failing that immediately after do a
msgbox strSQL
And see exactly what the SQL statement is and post that up here.

Enygma

You need to wrap the Alpine View part in quotes.

i.e.

strSQL = "Update RoomFile SET RoomStatus = '" & NewRoomStatus & "'"
strSQL = strSQL & " WHERE RoomName = '" & grdEnquiry.Text & "'"

Don't forget you also need to escape any ' characters that might be in there already.

Enygma

beat me to it

watty

I try to do as much as possible with SQL Server stored procedures that take a parameter.

This can be "faked" in Access/Jet by creating a "report" that takes a parameter. Honest.

Advantages:

Easier to debug
Runs much faster (Oracle, DB2, MDSExx, MS SQL etc)
You can make changes on server without recompiling and reinstalling the application.

[CrimsonGhost]

Originally posted by Enygma
beat me to it

We both posted at 11.48 so it must have been only by seconds.

Kernel32

Using Stored Procs also helps with SQL injection attacks.

What if you were using SQL Server, connecting using sa or equivalent(which happens a lot amazingly) and grdEnquiry.Text contains the following...

'; shutdown with nowait --

Thats just one example, dymanic sql is generally evil.

bonkey

Originally posted by LurkingIcon
This can be "faked" in Access/Jet by creating a "report" that takes a parameter. Honest.

Don't you mean by using a Query, rather than a Report?

jc

watty

Yep I meant query!

(Shows how often I use Access rather than "real" SQL).

Orlie

Thanks guys! Sorted it!

Talliesin

Function SQLEscape(Str as String) As String
	SQLEscape = Replace$(Str, "'", "''")
End Function
strSQL = "Update RoomFile SET RoomStatus = '" & SQLEscape(NewRoomStatus) & "'"
strSQL = strSQL & " WHERE RoomName = " & SQLEscape(grdEnquiry.Text)

Not escapeing possible ' characters is buggy at best, insecure at worse, both most of the time.

Enygma

Is there anything like Java's PreparedStatement in VB?

bonkey

Originally posted by Enygma
Is there anything like Java's PreparedStatement in VB?

Sure.

If you're using ADO - which would be the most common - then it would be a Command object.

ADO.Net will have OleDbCommand, OdbcCommand, etc.

Outside that, both RDO and DAO both have an equivalent as well, but buggered if I can remember what they're called.

jc