Updates with datetime in Where clause

PB 8.0.3 / MSS2000 / OleDB connectivity

I am updating a datawindow from within interactive PB. What am I doing
incorrectly? Below shows my update and the error I recieve.....

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQLSTATE = 42000
Microsoft OLE DB Provider for SQL Server
Line 1: Incorrect syntax near '12'.

No changes made to database.

UPDATE dp21.tf SET day01 = 7 WHERE pp_end_date = 12/20/02 12:00 am
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Obviously, the date in the Where clause is lacking the single quotes it
needs. Here is my DBParm profile for PB...

// Profile DP21 2000 Dev
SQLCA.DBParm =
"PROVIDER='SQLOLEDB',DATASOURCE='ITPR1SRV35\I1',PROVIDERSTRING='Database=DP2
1',PBCatalogOwner='dbo',LCID=1033,DateTimeFormat='m/d/yy h:mm
am/pm',DateFormat='yyyy-mm-dd',TimeFormat='hh:mm'"

The help says NOT to define single-quotes for interactive PB, and PB handles
it. But I am getting this error. And when I try to add the quotes myself, PB
dies and kicks me out!

Any help would be appreciated.. particularly before I throw my computer
across the room!

Jason Lipman



0
Jason
2/4/2003 9:20:15 PM
sybase.powerbuilder.database 9855 articles. 2 followers. Follow

2 Replies
495 Views

Similar Articles

[PageSpeed] 10
Get it on Google Play
Get it on Apple App Store

For what its worth .. We were using a different DB Access Method but same DB
backend.

When using datetime columns as a part of a primary key in DBLIB we needed to
add DateTimeAllowed = 1 to the DBParm.

Try configuring DBLIB  (aka MS-SQL Native), check that box on the Syntax
tab, and see if the datawindow can update properly.

If so, try using that setting in the OLEDB DBParm .. If it doesn't work I'll
double your money back on this free advice.

I have always found it problematic to use datetime as a row identifier.  I
like to use an identity value as the primary key and leave datetime as data.

"Jason Lipman" <Jason.Lipman@state.sd.us> wrote in message
news:9xnwgXJzCHA.59@forums.sybase.com...
> PB 8.0.3 / MSS2000 / OleDB connectivity
>
> I am updating a datawindow from within interactive PB. What am I doing
> incorrectly? Below shows my update and the error I recieve.....
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> SQLSTATE = 42000
> Microsoft OLE DB Provider for SQL Server
> Line 1: Incorrect syntax near '12'.
>
> No changes made to database.
>
> UPDATE dp21.tf SET day01 = 7 WHERE pp_end_date = 12/20/02 12:00 am
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Obviously, the date in the Where clause is lacking the single quotes it
> needs. Here is my DBParm profile for PB...
>
> // Profile DP21 2000 Dev
> SQLCA.DBParm =
>
"PROVIDER='SQLOLEDB',DATASOURCE='ITPR1SRV35\I1',PROVIDERSTRING='Database=DP2
> 1',PBCatalogOwner='dbo',LCID=1033,DateTimeFormat='m/d/yy h:mm
> am/pm',DateFormat='yyyy-mm-dd',TimeFormat='hh:mm'"
>
> The help says NOT to define single-quotes for interactive PB, and PB
handles
> it. But I am getting this error. And when I try to add the quotes myself,
PB
> dies and kicks me out!
>
> Any help would be appreciated.. particularly before I throw my computer
> across the room!
>
> Jason Lipman
>
>
>


0
David
2/5/2003 12:45:15 PM
Try:

UPDATE dp21.tf SET day01 = 7 WHERE pp_end_date = '12/20/02 12:00:00' ;

or

UPDATE [dp21].[tf] SET day01 = 7 WHERE pp_end_date = '12/20/02 12:00:00' ;

or

UPDATE tf SET day01 = 7 WHERE pp_end_date = '12/20/02 12:00:00' ;

I am assuming that the table name is "tf" and the table owner is "dp21".
SQL Server assumes that dots separate the database, owner and table names.
In embedded power script the single quotes are required.

The alternative is:

datetime dt
dt = datetime( date( "12/20/2002" ), time( "12:00:00" ))

update tf set day01 = 7 where pp_end_date = :dt ;


"David Rawheiser" <rawhide58@hotmail.com> wrote in message
news:BTrqwcRzCHA.198@forums.sybase.com...
> For what its worth .. We were using a different DB Access Method but same
DB
> backend.
>
> When using datetime columns as a part of a primary key in DBLIB we needed
to
> add DateTimeAllowed = 1 to the DBParm.
>
> Try configuring DBLIB  (aka MS-SQL Native), check that box on the Syntax
> tab, and see if the datawindow can update properly.
>
> If so, try using that setting in the OLEDB DBParm .. If it doesn't work
I'll
> double your money back on this free advice.
>
> I have always found it problematic to use datetime as a row identifier.  I
> like to use an identity value as the primary key and leave datetime as
data.
>
> "Jason Lipman" <Jason.Lipman@state.sd.us> wrote in message
> news:9xnwgXJzCHA.59@forums.sybase.com...
> > PB 8.0.3 / MSS2000 / OleDB connectivity
> >
> > I am updating a datawindow from within interactive PB. What am I doing
> > incorrectly? Below shows my update and the error I recieve.....
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > SQLSTATE = 42000
> > Microsoft OLE DB Provider for SQL Server
> > Line 1: Incorrect syntax near '12'.
> >
> > No changes made to database.
> >
> > UPDATE dp21.tf SET day01 = 7 WHERE pp_end_date = 12/20/02 12:00 am
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
> > Obviously, the date in the Where clause is lacking the single quotes it
> > needs. Here is my DBParm profile for PB...
> >
> > // Profile DP21 2000 Dev
> > SQLCA.DBParm =
> >
>
"PROVIDER='SQLOLEDB',DATASOURCE='ITPR1SRV35\I1',PROVIDERSTRING='Database=DP2
> > 1',PBCatalogOwner='dbo',LCID=1033,DateTimeFormat='m/d/yy h:mm
> > am/pm',DateFormat='yyyy-mm-dd',TimeFormat='hh:mm'"
> >
> > The help says NOT to define single-quotes for interactive PB, and PB
> handles
> > it. But I am getting this error. And when I try to add the quotes
myself,
> PB
> > dies and kicks me out!
> >
> > Any help would be appreciated.. particularly before I throw my computer
> > across the room!
> >
> > Jason Lipman
> >
> >
> >
>
>


0
Tyler
2/6/2003 12:28:22 AM
Reply:

Similar Artilces:

Update not updating database
In my sql server database I am using the detailsview. All my database calls work perfectly (SELECT, INSERT, DELETE) except the update. The update command passes without error but does not change the values in the database. It recognises the new values and the old values but does not change the database values. I am not doing eanything special but using the standard calls with the standard detailsview and everything else works.  Any ideas?   Code below <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="95%" AutoGene...

DateTime problem in updating database
I put this into script section. It works fine when the database field is text, but not if the field is a date. How should I modify this code ?   Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) e.Command.Parameters("OrderNumber").Value = TextBox1.Text    I tried e.Command.Parameters("OrderNumber").Value = TextBox1.DateTime But not work. Please help. ThanksThanks in advance for the help. I will try to credit the ones who helped but most important is we really do sincer...

Update dateTime.now into the database
I am having trouble updating a DateTime.Now from the textbox. Also I can populate it into the field if there is a datetime in there. The only way I can do it is by doing a autopostback by deleting the text and update it twice then it will update. Also I am a newbie in this and coding too.  I am using VB.  Here is my code:             <asp:TemplateField HeaderText="myDate" SortExpression="myDate">    <EditItemTemplate>          &nb...

Update datetime on sql database
 I am trying to update a datetime table in my sql database, Can anyone help me format it correct. I am getting error incorrect syntex near 12 and it is 12:51 so the time is not formated correctly for sql to accept My code using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using web = System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Net; using System.Text; using System.IO; using System.Text.RegularExpres...

Updating datetime fields in the database
Hi. I really need assistance with an update problem. I have a statement that takes a datetime time item (set to null) and places it in the buffer for a datetime column (using the SetItem function). If this statement is commented out, the following explicit update() does not fail. With the line not commented, I get the database error 'cannot convert a date/time item to int'. I have looked in PB at how the database items are defined and everything looks good. I have also removed the item from the datawindow and added it again (it is defined as a datetime field in the forma...

DataSet rows being deleted, but after the update , the sql database is not updated. The delete rows still in the database.
 Stepping thru the code with the debugger shows the dataset rows being deleted.   After executing the code, and getting to the page presentation. Then I stop the debug and start the page creation process again ( Page_Load ).    The database still has the original deleted dataset rows. Adding rows works, then updating works fine, but deleting rows, does not seem to work.   The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.     cDependChildTA.Fill(cDependChildDs._ClientDependentC...

updates, updates, updates, NO MORE!
Name: Andreas Email: unbekanntatjokerdotms Product: Firefox Summary: updates, updates, updates, NO MORE! Comments: Hi ! if you want to develop a fine webbrowser, maybe you nee a new management. To release a new buggy version every two weeks is not very professional. Test your software before the release and let the user download a stable browser. this update-orgy since 2.0.0 used me to use Opera instead of FF. We haved use FF in our Company, but we can't deploy a new version every week to +200 Windows-clients, so in our company we've decided to return to IE, ...

updates, updates, updates
Holy Crap, Batman! You'd think I was running Windows XP and it's patch Tuesday. So far this week - new kernel (2.6.27.10), 2 new browsers (Opera 9.63 and Firefox 3.05) and a new plugin (Flash 10.0.156.3). I'm about ready to try one od the BSD's! vince wrote: > Holy Crap, Batman! You'd think I was running Windows XP and it's patch > Tuesday. > > So far this week - new kernel (2.6.27.10), 2 new browsers (Opera 9.63 > and Firefox 3.05) and a new plugin (Flash 10.0.156.3). I'm about ready > to try one od the BSD's! I take ...

updates, updates, updates
Name: hans jensen Email: hansjensen44_athotmaildotcom Product: Firefox Summary: updates, updates, updates Comments: why is it everytime i go online firefox has new updates, 3 -4 times every day, its never ending..........whats all the problems??? please email me and let me know, ty,.........Hans Jensen Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.2) Gecko/20070219 Firefox/2.0.0.2 ...

Updates, updates and more updates
Name: pirhan Email: pirhan_chanathotmaildotcom Product: Firefox Summary: Updates, updates and more updates Comments: Hello, Normally I use Safari, but my iBook is out of commission for a while. So, I am forced to use a PC to which I would much rather use Firefox than Internet Exploder. However, as much I enjoy not using a Microsoft product, and use a product that works decently, I am severely annoyed about the constant updates I am put through. In the last two days, I had to wait for Firefox to install updates before I could browse and now, I'm alerted that there'...

how to update database with dataset update
I tried to update my database after modifying data in dataset, but failed. in fact a worse thing is I do not understand why my codes didn't work. thanks for your help in advance ladybug ===================================== Dim DBConn As OleDbConnection Dim DBComm As OleDbDataAdapter Dim ds As New DataSet() DBConn = New OleDbConnection("...") DBComm = New OleDbDataAdapter("SELECT * FROM tblHit", DBConn) DBComm.Fill(ds, "tblHit") ds.AcceptChanges() 'dataset.tables("tblHit")'s recordes were changed ds.Tables("t...

Gridview Update not updating database
Hi , I have a gridview object that has the standard edit | Update/Cancel button in the row.  The row has a series of checkboxes that are associated with 'bit' fields in a SQL 2007 database  When I click the edit the row changed to the update view, I can make the changes, but when I click the update button all changes are discarded and it returns to the default view.  When I go to a seperate details page (non gridview) and make the same change with the same SQL code the update posts correctly, and I have used the same SQL code directly in the SQL database and the ...

Updates Updates Updates
I was a big fan of Firefox.. Now, I don't even like to use it. Seems like every time I start it up I have to update some addin OR the browser itself. And unfortunately the update nag messages are always presented when I start a browsing session. I find this annoying because, typically, when I open a web browser all I want to do is go to a web page. Not update the browser. I can agree that staying current with the latest version could give the best, fastest, safest web experience. But that point becomes mute when I start off every other browsing session with a new update. And mos...

ListBox not updated after database update
I have a problem.  I think it has to do with the way .NET processes a page and its events.  Here's the scenario: I have a ListBox control bound to a SqlDataSource.  When I select an item, I present a FormView control with the details of that item, bound to another SqlDataSource control.  My problem is that, after I edit the item, the ListBox does not reflect the changes, until the page is refreshed again or called again in some other way. What I've found is that the processing of the update happens after the SqlDataSource queries the database and the ListBox is populate...

Web resources about - Updates with datetime in Where clause - sybase.powerbuilder.database

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: &lt;span class="utctime"&gt;2010-01-01 11:30 PM&lt;/span&gt; I would like a jquery script or plug in to convert every utctime ...


Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ...

Limburg.be - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: limburg.be Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows http://simplesideias.com.br/usando ...

Resources last updated: 12/9/2015 12:46:07 AM