Kevin Spencer
4/2/2004 7:36:00 PM
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.
"Stu" <sarmstrong@townfin.com> wrote in message
news:14b6001c418e0$ac0bc550$a601280a@phx.gbl...
> 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.
> >
> >"Stu" <sarmstrong@townfin.com> wrote in message
> >news:1769601c418d0$435a1da0$a501280a@phx.gbl...
> >> 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.
> >>
> >
> >
> >.
> >