Re: VB6.0 / ADO / Parameterized Query / SQLServer 2000 -- help...

No, wait.
No apology was needed. I was just thinking you would get quicker help
there.

One thing you should be doing is using SQL Profiler to trace the
statements being executed by your VB app.


jamesfreddyc wrote:
Sorry -- I assumed that this forum included many prog environments....

I'll move the thread.

j

"Bob Barrows [MVP]" wrote:

If you get no help here, you should try one of the vb groups (not
one of the ones with dotnet or vbnet in their names)

jamesfreddyc wrote:
Ok --- did one more test after I posted this thread and found
something interesting:

If I add another string value to the listbox, it will include the
first 2 values in the list and assign these to the @spec_type
parameter!

The problem with that is that the last value in the list is exluded.

Suggestions on how to include that last value in the listbox?


Thanks,

j

"jamesfreddyc" wrote:

1. Original problem: Create and send a parameter (@spec_type) of
string values that come from a listbox loaded with user-defined
strings (species types). Include each of these into a larger
SProc's WHERE clause.

2. Aaron over on the sqlserver.programming forum was a HUGE help in
getting the SProc setup for me. Basically, a #temp table is built
and populated with the @spec_type parameter values (from the
listbox). The rest of the SProc performs a query with other
parameters (not a "list" of strings, but straight forward things,
like a date1=@date1 for ex.).

3. In QueryAnalyzer, I get the results perfectly! So I know the
SProc is working perfectly when the parameters are hardcoded into
the SProc and running an EXEC command on the SProc verifies that
all is working correctly.

4. Moving back to the VB environment, I get different results when
populating a DataGrid with the ADOrs. When run from the VBApp, the
DataGrid gets populated with records but they are incorrect ----
only the first value in the listbox is showing up.

Here are some test results I have done:

QueryAnalyzer: Results in correct rows
EXEC dbo.AO_RT_Analysis_TotCollByMonth NULL, 'Ma dyari,Cx
nigripalpus,';
GO

VBApp: Results in correct list ('Ma dyari,Cx nigripalpus,') which
is EXACTLY how it looks in the QueryAnalyzer EXEC statement above!

'This creates a list of species from the lstboxSpeciesListR
selection xCriteria_input = ""
For xCnt = 0 To frm_RT_Analysis.lstboxSpeciesListR.ListCount -
1 If Not frm_RT_Analysis.lstboxSpeciesListR.Selected(xCnt)
Then If Len(xCriteria_input) > 0 Then xCriteria_input =
RTrim(xCriteria_input) & ","
xCriteria_input = xCriteria_input &
frm_RT_Analysis.lstboxSpeciesListR.List(xCnt)
xCriteria = "'" & RTrim(xCriteria_input) & "," & "'"
End If
Next


Then this list of "species" is set to the xCriteria variable, which
the @spec_type parameter's value is set to this xCriteria list.

Any help is appreciated. Any comments/tips on setting up
parameters made up of listbox values would be a big help too....

Thanks!

Take Care,

james

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages
Loading