I have a data window which has as stored procedure as its data source. The parameter passed to the stored procedure is of type datetime. In the datawindow design mode and when previewing, the data is properly retrieved on entering the datetime parameter.
However when this datawindow control is embeded in a window and data is retrieved by passing a datetime parameter through script, the retrieve fails and the error mentioned as below is displayed
Select Error: SQLSTATE =22005
[Microsoft][ODBC SQL Server Driver] Invalid character value for cast specification
I am unable to resolve the problem. I am using PB10 eval version. Please help
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
the procedure uses a datetime variable to receive the argument and the datawindow retrieve function also passes a datetime variable.
Earlier I used PB 6.5 with MSS conntivity to SQL server but with PB 10.2 native connectivity is not available, I am using OLEDB to connect and this ODBC related problem has crept up.
I believe the following may solve your problem:
In development mode, on the "Connection" tab of the database profile, enter the following string for the "Driver-Specific Parameters"
DelimitIdentifier='No',Msg
I hope this helps. Let me know if this solves your problem
moderators please split the points to all persons who gave a response and close the topic.
I found out the reason for the problem but notthe solution.
The problem is specifically with ODBC and is not there with OLEDB or ADO.NET. Actuallythe template generated by PB102 uses ODBC by force througha NVO. which resulted in the error.
He problem got solved by using ADO.NET and connecting to the database usning powerscrip after disabling th NVO.
Kathotia,
I went back through some of my code. I have run into this before.
The problem is the DateTime column.
PowerBuilder uses its PBODB??.INI file to keep information about each of the database types
using ODBC. If the settings in the PBODB??.INI has the Datetime formatting set to a format
that is incompatible with the database format, you will run into this error.
If you use embedded SQL in a script, you can see how this works.
Example:
SELECT ??
INTO :var
FROM tbl
WHERE col = :dtData
Reguardless of the dtData formatting, when PowerBuilder passes it to the database, the
datetime formatting will be changed to match the formatting in the PBODB??.INI file.
Changing the dtData to a StrData will have no effect.
An automattic type conversion will still convert the data into a datetime.
You can test this by changing the formatting of the datetime data being passed.
Example:
SELECT ??
INTO :var
FROM tbl
WHERE col = :var2
var2 = String(dtData, 'mm/dd/yyyy hh:mm:ss')
OR
var2 = String(dtData, 'yyyy/mm/dd hh:mm:ss')
OR
var2 = String(dtData, 'yy/mm/dd hh:mm:ss')
etc...
I had alot of trouble with this in PB 7 but not any any other version.
Your simptoms appear to be axactly the same.
Also, check the 'Syntax' tab of your profile. You will see
entries for 'Date Format', 'DateTime Format', etc..
Typically they are empty. If you have something there, remove it.
If yours are empty, fill them in.
Ben
Business Accounts
Answer for Membership
by: gajender_99Posted on 2005-05-25 at 04:20:17ID: 14076153
i think the variable you are passing is of date type where the argument in the datawindow is datetime or vis versa check your setting
or post the script where you are retrieving the data for the data window
gajender