RE: Visual Basic SQl statement -- MIDRANGE-L

Walden,

Thanks for the help.
I got it to work, finally.
i took the cast to char, and the cstr.
the big problem, and still a problem i need to figure out how to handle is
that i didn't have any data in the as400 file with the current date.  i
changed 1 record to the current date and it worked great.
why it causes the whole ESRI application to crash is another story.

thanks

mike

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


Actually the error isn't all that cryptic. First, I'll bet that is
-2147220985 (note the negative.) Now put that into the handy windows
calculator and convert to hex and you get FFFF FFFF 8004 0207. Ignore the
first DWORD and you get an error of 8004 0207 which is a valid COM error.
Problem is it's an error in the user domain which means you need to know who
threw the error, was it ADO, was it IBM's driver, or was it the mapping
software (I'll bet on the mapping software). Can you find a job on the
AS/400 for this connection? If so check the joblog for errors. If not try
turning on ODBC tracing and look at the trace to see how far it gets before
it blows.

-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 14:37
To: 'midrange-l@midrange.com'
Subject: RE: Visual Basic SQl statement


thanks Walden.
Your right it is a pain to debug.  The error messages are cryptic.  I don't
know if they are messages specific to the software product or related to VB.


Your suggestion on the date worked great.  I had tried variations of this
from code snippets i had seen on the web, but they didn't work, so that is
why i was using the code- i couldn't get it right.

as for the error message about not being a field on the table.  well if i
did get it, i didn't know it.  LOL. i changed the SQL statement as you
suggested, but all i get it an error message like this.  -2147220985.  That
tells a whole lot huh?  <G>

last and not important, i just assumed i was using VBA(i didn't  know how to
tell the difference) because it was part of the ESRI package and as a result
would be VBA.

Thanks

Mike.


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


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.
_______________________________________________
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.