Parameter to DataEnvironment

[mehtarohit]

Hi,

I have designed a report using DataEnvironment as the Datasource.

My Report have two parameters, one is of Integer type and the other is of String (Char) type.

My Query works fine for Integer datatype, but when I add the string datatype then it gives me an error.

In the parameter tab in dataenvironment what datatype to be selected when it is a string parameter. I have tried with all adstr,adlongstr,adchar etc but of no help.

Any one please...

Regards,

[dee-u]

What is the error generated? What database are you using? And how are you setting the parameter?

[wes4dbt]

Post your SQL statement or attach the data environment. Not enough info to help you.

[mehtarohit]

Hello,

I am using Access 2000 as my Database.
The error generated is Error No 13, Type Mismatch.

I am passing the parametes from VB. The sql query in dataenvironment is as below :

select OrderEntry.Code,CustOrderNo,OrderDate,OrderSrNo,ItemName,Measurement,OrderQty from OrderEntry,OrderEntryDetails,ItemMaster where OrderEntry.Code=OrderEntryDetails.OrderCode and OrderEntryDetails.ItemCode=ItemMaster.Code and CustomerCode=? and OrderDate between ? and ? and Status=?

Where parameter 1 is Integer, Parameters 2 & 3 are Date/Time and Parameter 4 is String (Char)

My call from VB is as below

Dim new_cn As New DataEnvironment1
Dim from_date As String
Dim to_date As String
Dim status As String
           
    from_date = Format(DTPicker1.value, "dd/MMM/yyyy")
    to_date = Format(DTPicker2.value, "dd/MMM/yyyy")
    
    If cboStatus.ListIndex = 0 Then         'For Pending Orders
        status = "A"
    ElseIf cboStatus.ListIndex = 1 Then     'For Completed Orders
        status = "C"
    End If
 
    new_cn.OrderEntryReport_Grouping CInt(cboName.ItemData(cboName.ListIndex)), CDate(from_date), CDate(to_date), status

This works fine if I remove the last parameter.
I don't know where it is going wrong.

Regards

[wes4dbt]

Have you checked the data type of the status parameter and field?

[dee-u]

It would be best if you can attach a simple project that demonstrate your problem so we may have a look at it.

[mehtarohit]

Hello,

Pls check the uploaded file.

Regards,
Rohit

[dee-u]

Try using the following sql statement since yours has a typo (OrderEntryDetaisl).

select CustomerName,CustOrderNo,OrderDate,OrderQty,Measurement from OrderEntry,OrderEntryDetails,CustomerMaster where OrderEntry.CustomerCode=CustomerMaster.Code and OrderEntry.Code=OrderEntryDetails.OrderCode and ItemCode=? and OrderDate between ? and ? AND Status = ?

And also don't forget to select the correct Host Data Type, if your Data Type is adDate then your Host Data Type should be date, etc.

For the third parameter the following setting worked for me.

[mehtarohit]

Hi dee-u,

I tried using your suggestion of setting the 4th parameter's data type and Host Data Type , but I am getting the error. Pls check the error image attached.

I have not changed the query as I want ItemName on the report instead CustomerName.

Regards,

[dee-u]

Your typo was on this "OrderEntryDetaisl", did you change it already?

[dee-u]

Wait, I tested on the ItemWiseReport, will look at the other one.

[mehtarohit]

I didn't get you.
What do u mean by "typo"

Also my database table is "OrderEntryDetails"

So whats this "OrderEntryDetaisl". I am not getting.

[dee-u]

Not sure if it is an ODBC issue since it works when I tried it with OledDb using the following connectionstring: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Stuffs\DataEnvi-Problem\DataEnvi-Problem\Database\CurrYear\data.mdb;Persist Security Info=False".

[mehtarohit]

Ya got it. My mistake in writing the query (ItemDetails). Rectified it.
Thanks.

But the earlier one still gives an error.

[mehtarohit]

Will check it out writing the connection string instead of ODBC.

[dee-u]

Have a look at the attachment if it works for you.

[mehtarohit]

In Data Environment, if I change the Provider from ODBC to Jet 4.0, then it worked fine for me. There is no error and getting the desired output.

But this is Ok on my machine. The data path on my machine and client's machine is not the same. Is there any way to set the provider for Data Environment at run time.

Regards,

[dee-u]

You may follow how I did it in the attachment on my previous post.

[mehtarohit]

Ok. Thanks a lot.
One last question.... Can I use password along with connection string for a password protected data.

[dee-u]

Yes. Have a look at www.connectionstrings.com for the different types of connectionstrings.

[dee-u]

And to mark your thread as Resolved use the Thread Tools menu.

[mehtarohit]

Thanks dee-u

All problems resolved.
www.connectionstrings.com is a great one.

Regards,