How to use adParamOutput?

[Eliminator2009]

How to use adParamOutput to fetch records in MSHFlexgrid?

One more addition is i want to use adcmdtext not a adCmdStoredProc.

Thanks.

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

Your situation is not clear, and I don't know why you think adParamOutput would be of any use - that is used to get a value from a Stored Procedure, which you say you don't want.

If you give us far more details, we will be able to give you accurate advice.

[Eliminator2009]

Dear SI,

Kindly see this thread

My code is as follows

Dim StrSQL As String
Dim AdoCommand As ADODB.Command
Set AdoCommand = New ADODB.Command
StrSQL = "SELECT tblExp.TrDate, mstcenter.centername, tblExp.InternalTransport, tblExp.PartyTransport, tblExp.TotTransport, tblExp.Electricity, tblExp.DieselOil, tblExp.FirewoodBoiler, tblExp.Chemicals, tblExp.RepairMaint, tblExp.WaterExp, tblExp.TotDirectExpenses, tblExp.VehicleDieExp, tblExp.TravellingExp, tblExp.SalaryExp, tblExp.BankChargesExp, tblExp.OfficeExp, tblExp.PostagePhoneExp, tblExp.MisExp, tblExp.MarkeExp, tblExp.DonetionExp, tblExp.PrintingStaExp, tblExp.TotInDirectExp, tblExp.TotExp FROM MstCenter INNER JOIN tblExp ON MstCenter.centerid=tblExp.CenterId;Values (?,?)"

With AdoCommand
            .ActiveConnection = CN
            .CommandType = adCmdText
            .CommandText = StrSQL
            .Prepared = True
            .Parameters.Append .CreateParameter(, adDate, adParamOutput, , DTTransaction.Value)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , Text1.Text)
            .Execute , , adCmdText + adExecuteNoRecords
End With

I just need to Fetch record.

Thanks for Early reply.

[si_the_geek]

I am well aware of that FAQ thread.

First of all, your SQL statement is not valid... what is the ";Values (?,?)" supposed to be for? Using a Command object does not alter the way that SQL statements should be written, it only alters the values that you put into the SQL statements.

As for the parameters, you do not use Output parameters to read values (unless you are using an SP that sets them), you use a Recordset - as shown in the "Running it" section of FAQ article you linked to.

You also do not use Input parameters if your SQL statement doesn't contain values, which it currently doesn't. I presume you meant your parameters to be used as part of a Where clause, but I'm really not sure - because you haven't explained what you are trying to do.

[Eliminator2009]

Sorry for the late

I am going these records to display for editing.

how do i display this.

Thanks.

[si_the_geek]

I already knew that much, you have not clarified the situation at all.

To display it you first need to load the data into a recordset, and at the moment you are a long way from that - because you don't even have a valid SQL statement.

The SQL statement is valid up to the ; character (and will return all data in those two tables), but the rest of it does not make sense.

Based on your parameters, I presume that you want to use the values as part of a WHERE clause... but in order for that to work, you need to write a WHERE clause in the SQL statement.

As you haven't given any indication as to what you wanted, I can't help any more than that.

[Eliminator2009]

Here is the code. i receive the error Join expression not supported.

Private Sub CmdSearch_Click()
Dim StrSQL As String
Dim AdoCommand As ADODB.Command
Set AdoCommand = New ADODB.Command
StrSQL = "SELECT tblExp.TrDate, mstcenter.centername, tblExp.InternalTransport, tblExp.PartyTransport, tblExp.TotTransport, tblExp.Electricity, tblExp.DieselOil, tblExp.FirewoodBoiler, tblExp.Chemicals, tblExp.RepairMaint, tblExp.WaterExp, tblExp.TotDirectExpenses, tblExp.VehicleDieExp, tblExp.TravellingExp, tblExp.SalaryExp, tblExp.BankChargesExp, tblExp.OfficeExp, tblExp.PostagePhoneExp, tblExp.MisExp, tblExp.MarkeExp, tblExp.DonetionExp, tblExp.PrintingStaExp, tblExp.TotInDirectExp, tblExp.TotExp FROM MstCenter INNER JOIN tblExp ON MstCenter.centerid=tblExp.CenterId (?,?)" :(

With AdoCommand
            .ActiveConnection = CN
            .CommandType = adCmdText
            .CommandText = StrSQL
            .Prepared = True
            .Parameters.Append .CreateParameter(, adDate, adParamInput, , DTTransaction.Value)
            .Parameters.Append .CreateParameter(, adInteger, adParamInput, , Text1.Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(0).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(1).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(2).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(3).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(4).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(5).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(6).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(7).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(8).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(9).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(10).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(11).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(12).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(13).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(14).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(15).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(16).Text)
            .Parameters.Append .CreateParameter(, adInteger, adParamOutput, , txtAmount(17).Text)
            .Execute , , adCmdText + adExecuteNoRecords
End With


End Sub

The Dttransaction is dtpicker control for selecting date, the text1 is text box for getting field id from combo box.

The screenshot is below.

I am waiting for your reply.

[si_the_geek]

That still isn't anywhere near a valid SQL statement:

.... FROM MstCenter INNER JOIN tblExp ON MstCenter.centerid=tblExp.CenterId (?,?)"

Assuming that you want to limit the values somehow what you should have is a WHERE clause, something like:

.... FROM MstCenter INNER JOIN tblExp ON MstCenter.centerid=tblExp.CenterId WHERE field1 = value1 AND field2 = value2"

I strongly suggest looking at a basic SQL tutorial, such as the one at the top of the SQL section of our Database Development FAQs/Tutorials (at the top of this forum)


You should not even think about parameters until you have got a valid SQL statement. Once you have, the only thing about it that changes is just replacing the values (value1 and value2 in my example) with the placeholders.

[Eliminator2009]

Dear SI,

I mean to say that how do i create lookup table?

The database has two tables

1) MstCenter
#1. CenterId
#2. CenterName

2) tblexp
#1. dtdate
#2. CenterId
#3. internalTransport
etc.

can you help?

[si_the_geek]

Your join already copes with that.

I see no reason for the " (?,?)" in your query, or the parameters.

[Eliminator2009]

Thanks SI.

Can you explain it pls.

[si_the_geek]

Despite prompting you repeatedly, I still have absolutely no idea what you are trying to do, so what am I supposed to explain?

As far as I can tell you should remove the items I mentioned in my previous post.

If that doesn't do what you want, you will need to start explaining what is wrong with it, and what you do want - otherwise there is nothing we can do.