RE: Visual Basic SQl statement -- MIDRANGE-L

well, i'm making progress.
i took off the cast as char, and the CSTR  and now instead of my cryptic
message, i crash the whole application.

thats an improvement RIGHT???



-----Original Message-----
From: Walden H. Leverich [mailto:WaldenL@TechSoftInc.com]
Sent: Wednesday, December 26, 2001 3:03 PM
To: 'midrange-l@midrange.com'
Subject: RE: Visual Basic SQl statement


Yes, but it's not a date, it's an 8 position numeric field that the
programmers use as a date. Big difference.

-Walden


------------
Walden H Leverich III
President
Tech Software
(516)627-3800 x11
WaldenL@TechSoftInc.com
http://www.TechSoftInc.com



-----Original Message-----
From: Brad Jensen [mailto:brad@elstore.com]
Sent: Wednesday, December 26, 2001 14:27
To: midrange-l@midrange.com
Subject: Re: Visual Basic SQl statement


If that date was a PC SQL date, you would want #20021211# ,as best I
remember. Don't know what happens when you go to the AS/400 thru ODBC or OLE


The # at the start and end are field delimiters like quotes for text, that
say this is a date


"Walden H. Leverich" wrote:
>
> Gee, this will be a pain to debug, it's littered with the COM objects
> from the mapping program, what fun! <G>. First, for the record, you
> are using VB not VBA, VBA is a stripped down version of VB.
>
> As for debugging, I do see two problems. First: the way you are
> attempting to calculate the date won't work. Given your code:
>
> >CurrDate = CStr(MyYear) + CStr(MyMonth) + CStr(MyDay)
>
> IF MyMonth >=10 and MyDay >=10 all will be good. For example, a date
> of 12/26/2001 would end up as 20011231 which gives you your date.
> However, a date of 9/7/2001 would give you 200197 not 20010907, you
> are missing leading zeros.
>
> Why not replace everything from "Dim d" through "Currdate=" with one
> statement:
>
> Currdate = Format(date, "YYYYMMDD")
>
> Second, when you load SQLstr with the date (the commented out code)
> I'll bet you get an error about Currdate not being a field on the
> tables, no? Currdate is a field in the VB program, yes, but the SQL
> statement will run on the database server and it doesn't know about
> currdate. The easiest thing to do is change the SQLstr assignment as
> follows:
>
> SQLstr = "create view DISPATCH_V as select PZBSB, PZNAM, PZBDT cast as
> char, PZSCD, PZARA, P1RTE, P1STOP, P1SCD, PWLAT, PWLONG from UFDB,
> UFDBS, UCSRFD where PZBSB = P1BSB and P1MTR = PWMTR and PZBDT = " +
> cstr(Currdate)
>
> Note I've remove CurrDate from the string and instead I'm concating
> the value of currdate onto the end of the sql statement. This will
> result in a sql statement that says:
>
> ...and P1MTR = PWMTR and PZBDT = 20020101
>
> When the date is 1/1/2002.
>
> Hope this helps.
>
> -Walden
>
> ------------
> Walden H Leverich III
> President
> Tech Software
> (516)627-3800 x11
> WaldenL@TechSoftInc.com
> http://www.TechSoftInc.com
>
> -----Original Message-----
> From: Smith, Mike [mailto:Mike_Smith@RGCResources.com]
> Sent: Wednesday, December 26, 2001 12:29
> To: 'midrange-l@midrange.com'
> Subject: RE: Visual Basic SQl statement
>
> Walden,
> i'm on V4r5.  client access express.   win2k.
>
> as for the code, i didn't really write it(because i don't know enough
> about VB to try to write it).  i am using a package called ESRI.  It
> is a mapping software, and the code is a sample code(they provided)
> that allows me to plot points on to the map. here is the code. Thanks
> for any help you can provide.
>
> Private Sub UIButtonControl2_Click()
> '++ adds an OLE DB table as an XYEvent layer
> '++
>  On Error GoTo ErrorHandler:
>
>  Dim d
>  d = Format(Date, "Short Date")
>  MyDay = Day(d)
>  MyMonth = Month(d)
>  MyYear = Year(d)
>
>  Dim curdate As String
>  CurrDate = CStr(MyYear) + CStr(MyMonth) + CStr(MyDay)
>
>   '++ New property set for workspacefactory
>   Dim pPropset As IPropertySet
>   Set pPropset = New PropertySet
>
>   '++ Provider =
>   pPropset.SetProperty "CONNECTSTRING", "Provider=MSDASQL.1;Data
> source=RGOCCSTM;User ID=;Password="
>
>   Dim pWorkspaceFact As IWorkspaceFactory
>   Set pWorkspaceFact = New OLEDBWorkspaceFactory
>
>   '++ Create the new workspace/feature workspace objects
>   Dim pWorkspace As IWorkspace
>   Set pWorkspace = pWorkspaceFact.Open(pPropset, 0)
>
>   Dim pFeatWorkspace As IFeatureWorkspace
>   Set pFeatWorkspace = pWorkspace
>
>   '++ If a sub-set of the table is required and views are supported by
> the data source
>   '++ create a view of the table selection you wish to use.
>   Dim SQLstr As String
>   SQLstr = "create view DISPATCH_V as select * from UFDB,UFDBS,UCSRFD
> where PZBSB = P1BSB and P1MTR = PWMTR where PZBSB > 0"
>   'SQLstr = "create view DISPATCH_V as select PZBSB, PZNAM, PZBDT cast
> as char, PZSCD, PZARA,P1RTE,P1STOP,P1SCD,PWLAT, PWLONG from
> UFDB,UFDBS,UCSRFD where PZBSB = P1BSB and P1MTR = PWMTR and PZBDT =
> Currdate"
>
>   '++ Create the view and fetch the datasetnames from the workspace
>   pWorkspace.ExecuteSQL SQLstr
>
>   Dim pEnumDataset As IEnumDatasetName
>   Dim pDataset As IDatasetName
>
>   Set pEnumDataset = pWorkspace.DatasetNames(esriDTAny)
>   Set pDataset = pEnumDataset.Next
>
>   Do Until pDataset Is Nothing
>     If pDataset.Name = "RGOCCSTM.DISPATCH_V" Then
>       Exit Do
>     End If
>     Set pDataset = pEnumDataset.Next
>   Loop
>
>   '++ Create the new table object from the dataset name
>   Dim pTable As ITable
>   Set pTable = pFeatWorkspace.OpenTable(pDataset.Name)
>
>   Dim pDoc As IMxDocument
>   Set pDoc = ThisDocument
>   Dim pMap As IMap
>   Set pMap = pDoc.FocusMap
>
>   Dim pTableName As IName
>   Dim pDS As IDataset
>   Set pDS = pTable
>   Set pTableName = pDS.FullName
>
>   '++ Set the field properties for the event theme
>   Dim pXYEvent2FieldsProperties As IXYEvent2FieldsProperties
>   Set pXYEvent2FieldsProperties = New XYEvent2FieldsProperties
>   With pXYEvent2FieldsProperties
>     .XFieldName = "PWLONG"
>     .YFieldName = "PWLAT"
>     .ZFieldName = ""
>   End With
>
>   '++ Set the spatial reference for the event theme
>   Dim pSpatialReferenceFactory As ISpatialReferenceFactory
>   Dim pProjectedCoordinateSystem As IProjectedCoordinateSystem
>
>   Set pSpatialReferenceFactory = New SpatialReferenceEnvironment
>   Set pProjectedCoordinateSystem =
> pSpatialReferenceFactory.CreateProjectedCoordinateSystem(esriSRProjCS_
> NAD198
> 3UTM_11N)
>
>   Dim pXYEventSourceName As IXYEventSourceName
>   Set pXYEventSourceName = New XYEventSourceName
>
>   '++ Combine all the properties for the event theme
>   With pXYEventSourceName
>     Set .EventProperties = pXYEvent2FieldsProperties
>     Set .SpatialReference = pProjectedCoordinateSystem
>     Set .EventTableName = pTableName
>   End With
>
>   Dim pName As IName
>   Dim pXYEventSource As IXYEventSource
>   Set pName = pXYEventSourceName
>   Set pXYEventSource = pName.Open
>
>   '++ Create a new feature layer object for the event theme
>   Dim pflayer As IFeatureLayer
>   Set pflayer = New FeatureLayer
>   Set pflayer.FeatureClass = pXYEventSource
>   pflayer.Name = "Todays Dispatching"
>
>   '++ Add the layer extension (required to ensure when you edit
>   '++ the layer's Source properties and click the Set Data Source
>   '++ button, the correct "Add XY Events" Dialog appears)
>   Dim pLayerExt As ILayerExtensions
>   Dim pRESPageExt As New XYDataSourcePageExtension
>   Set pLayerExt = pflayer
>   pLayerExt.AddExtension pRESPageExt
>
>   '++ add the new layer to the display
>   pMap.AddLayer pflayer
>
>   Exit Sub
> ErrorHandler:
>   MsgBox Err.Number & Err.Description
> End Sub
>
> -----Original Message-----
> From: Walden H. Leverich [mailto:WaldenL@TechSoftInc.com]
> Sent: Wednesday, December 26, 2001 11:52 AM
> To: 'midrange-l@midrange.com'
> Subject: RE: Visual Basic SQl statement
>
> You really don't want to do that, you'd force the AS/400 to run the
> digits command for each row before comparing it. If there is an index
> that it could use you just threw it out the window.
>
> Mike, the statement you showed won't allow you to select records, it
> creates a view. To select records you don't want the 'create view as'
> part. Can you post the surrounding VBA code? I haven't hit any
> problems with numeric fields in a long time. What versions are
> involved? (AS/400, Client Access, ADO (you are using ADO, no?) and PC
> Operating system?)
>
> -Walden
>
> ------------
> Walden H Leverich III
> President
> Tech Software
> (516)627-3800 x11
> WaldenL@TechSoftInc.com
> http://www.TechSoftInc.com
>
> -----Original Message-----
> From: Bill [mailto:brobins3d@yahoo.com]
> Sent: Wednesday, December 26, 2001 10:57
> To: midrange-l@midrange.com
> Subject: Re: Visual Basic SQl statement
>
> Mike,
>
> Does it work correctly in MS Query, but throws an error when doing the
> download to your application?  If so, this seems to be an error in
> MS's products; they seem to cast the parameter's data type
> incorrectly.  Usually when I've had problems it's because I'm trying
> to use a parameterized query with a numeric field being one of the
> parameters.  This doesn't seem to be your case, but I'll share what I
> do anyways.  Use the Digits() function to turn your numeric field into
> an alpha/numeric field and then compare to a character value.
>
> In your case, the Where statement should be changed to:
> Digits(PZBSB) > '00000000'
>
> Bill
>
> > I'm trying to run a sql statement using ODBC through a VBA.  The
> > Database on the 400 has a Numeric field defined 8.0p(used as a date
> > field). i've created a SQL statement that lets me select records
> > from the file, however, when i try to condition the select based on
> > the date field i get cryptic errors.
> >
> > Here is my select statement.
> >   SQLstr = "create view DISPATCH_V as select * from
> > UFDB,UFDBS,UCSRFD where PZBSB = P1BSB and P1MTR = PWMTR and PZBSB >
> > 0"
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@midrange.com To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives at
> http://archive.midrange.com/midrange-l.
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@midrange.com To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives at
> http://archive.midrange.com/midrange-l.
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@midrange.com To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives at
> http://archive.midrange.com/midrange-l.
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@midrange.com To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives at
> http://archive.midrange.com/midrange-l.

--
Brad Jensen brad@elstore.com
President
Electronic Storage Corporation Tulsa OK USA
918-664-7276

LaserVault Report Retrieval & Data Mining
www.Laservault.com

www.eufrates.com - Add distance learning to
your site with easy course preparation
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.