RE: Visual Basic SQl statement -- MIDRANGE-L

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.