WHERE statement syntax

[Stu]

Can anyone tell me if this statement is correct??
This is part of an UPDATE statement
"' WHERE drawer = " & myID1'" AND datetime = " & myID2

Also, How can I test to see if item was "not found"???

Thank you very much.

 

[Kevin Spencer]

I'm afraid that nobody can tell you if it is correct. What you posted is a
portion of a string declaration, which contains several variables
concatenated into it. Whether it's correct or not depends on several
factors:

1. What are the values of the variables?
2. What are the data types of the database columns that they are being used
with?

I CAN tell you that your string declaration is missing at least one "&"
character, between [& myID1'] and [" AND ..."], as well as the fact that the
single quote next to the variable name [ID1] is going to throw an error.

As for your second question, please define "item" and "not found" (as in
"not found by ...")

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Stu]

Here is the entire SQL:
Dim myConnString
Dim myConnection
Dim mySQL1
Dim mySQL2
Dim myRs
Dim myID1
Dim myID2

myConnString = Application
("pbbalancing_ConnectionString")
Set myConnection = Server.CreateObject
("ADODB.Connection")
myConnection.Open myConnString


Dim
myDrawer,myBegCash,myCashIn,myCashOut,myEndCash,mytotalcash
,mylongshort,myBalancingDate,myInputBy
myDrawer = Request.Form("Drawer")
myBegCash = Request.Form("BegCash")
myCashIn = Request.Form("CashIn")
myCashOut = Request.Form("CashOut")
myEndCash = Request.Form("EndCash")
mytotalcash = Request.Form("totalcash")
mylongshort = Request.Form("longshort")
myBalancingDate = Request.Form("BalancingDate")
myInputBy = Request.Form("InputBy")

myID1=Request.Form("Drawer")
myID2=Request.Form("BalancingDate")

mySQL1 = "UPDATE cashdrawer SET begcash = '"&_

Request.Form("BegCash") &_

"',cashin = '"&Request.Form
("CashIn") &_

"',cashout
= '"&Request.Form("CashOut") &_

"',endcash
= '"&Request.Form("EndCash") &_

"',totalcash
= '"&Request.Form("totalcash") &_

"',longshort
= '"&Request.Form("longshort") &_

"',datetime
= '"&Request.Form("BalancingDate") &_

"',inputby
= '"&Request.Form("InputBy") &_

"' WHERE drawer = " &
myID1'" AND datetime = " & myID2


myConnection.Execute(mySQL1)

"drawer" is text string & "datetime" is Date format.
ID1 & ID2 are text boxes from input form.
I'm also interested in determining if no record is updated
with this SQL1. Thank you.>

-----Original Message-----
I'm afraid that nobody can tell you if it is correct. What you posted is a
portion of a string declaration, which contains several variables
concatenated into it. Whether it's correct or not depends on several
factors:

1. What are the values of the variables?
2. What are the data types of the database columns that they are being used
with?

I CAN tell you that your string declaration is missing at least one "&"
character, between [& myID1'] and [" AND ..."], as well as the fact that the
single quote next to the variable name [ID1] is going to throw an error.

As for your second question, please define "item" and "not found" (as in
"not found by ...")

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

Can anyone tell me if this statement is correct??
This is part of an UPDATE statement
"' WHERE drawer = " & myID1'" AND datetime = " & myID2

Also, How can I test to see if item was "not found"???

Thank you very much.

.

 

[Kevin Spencer]

Thanks Stu. That makes everything a lot easier, except for one thing I
neglected to ask, which is what database you're using. This will affect the
SQL syntax for dates, but I'll just cover the gamut and you can decide based
upon your knowledge of the database.

In SQL, text values are delimited by 'single quotes' to indicate their data
type. Dates can vary, depending upon the database product, but generally,
they should also be enclosed with 'single quotes' - the exception is Access.
The latest versions of Access do, I believe, have support for single quotes
around dates, but until recently, the delimiter for datetime values in
Access is #pound signs#.

Therefore, the WHERE clause for your query might look something like the
following:

...." WHERE drawer = '" & myID1 & "' AND [datetime] = "' & myID2 & "'"

- or -

...." WHERE drawer = '" & myID1 & "' AND [datetime] = #" & myID2 & "#"

Of course, you have to make sure that the input date is a valid date (as
recognized by the computer) string, and is not empty.

Also, note the [square brackets] I put around the column called "datetime" -
I believe "datetime" is a reserved word. Putting square brackets around
column and table names prevents them from being confused by the computer
with reserved words, and also helps with database object names with spaces
in them.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

Here is the entire SQL:
Dim myConnString
Dim myConnection
Dim mySQL1
Dim mySQL2
Dim myRs
Dim myID1
Dim myID2

myConnString = Application
("pbbalancing_ConnectionString")
Set myConnection = Server.CreateObject
("ADODB.Connection")
myConnection.Open myConnString


Dim
myDrawer,myBegCash,myCashIn,myCashOut,myEndCash,mytotalcash
,mylongshort,myBalancingDate,myInputBy
myDrawer = Request.Form("Drawer")
myBegCash = Request.Form("BegCash")
myCashIn = Request.Form("CashIn")
myCashOut = Request.Form("CashOut")
myEndCash = Request.Form("EndCash")
mytotalcash = Request.Form("totalcash")
mylongshort = Request.Form("longshort")
myBalancingDate = Request.Form("BalancingDate")
myInputBy = Request.Form("InputBy")

myID1=Request.Form("Drawer")
myID2=Request.Form("BalancingDate")

mySQL1 = "UPDATE cashdrawer SET begcash = '"&_

Request.Form("BegCash") &_

"',cashin = '"&Request.Form
("CashIn") &_

"',cashout
= '"&Request.Form("CashOut") &_

"',endcash
= '"&Request.Form("EndCash") &_

"',totalcash
= '"&Request.Form("totalcash") &_

"',longshort
= '"&Request.Form("longshort") &_

"',datetime
= '"&Request.Form("BalancingDate") &_

"',inputby
= '"&Request.Form("InputBy") &_

"' WHERE drawer = " &
myID1'" AND datetime = " & myID2


myConnection.Execute(mySQL1)

"drawer" is text string & "datetime" is Date format.
ID1 & ID2 are text boxes from input form.
I'm also interested in determining if no record is updated
with this SQL1. Thank you.>

-----Original Message-----
I'm afraid that nobody can tell you if it is correct. What you posted is a
portion of a string declaration, which contains several variables
concatenated into it. Whether it's correct or not depends on several
factors:

1. What are the values of the variables?
2. What are the data types of the database columns that they are being used
with?

I CAN tell you that your string declaration is missing at least one "&"
character, between [& myID1'] and [" AND ..."], as well as the fact that the
single quote next to the variable name [ID1] is going to throw an error.

As for your second question, please define "item" and "not found" (as in
"not found by ...")

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

Can anyone tell me if this statement is correct??
This is part of an UPDATE statement
"' WHERE drawer = " & myID1'" AND datetime = " & myID2

Also, How can I test to see if item was "not found"???

Thank you very much.

.