Re: Visual Basic SQl statement -- MIDRANGE-L

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