Advanced Transact_SQL

Hi everyone
I've a problem. I must convert a query dinamically composed in an ASP
script, in a stored procedure
The Visual Basic code is below.
Could you help me anyway?
SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
if(new_titolo <> "") then
mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
end if
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
if((da <> "" AND a <> "")) then
mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
end if
mySQL=trim(mySQL)
' tolgo eventuali AND finali
if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
AN") then
mySQL=mid(mySQL,1,len(mySQL)-3)
end if
mySQL=mySQL & " order by data DESC"
Thank you very much
Hi
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>
Check out http://www.sommarskog.se/dyn-search.html
John
|||in the following your string is not properly closed
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
this should be
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & "%' AND "
futher you are cheking whether the SQL string end with "AND" if true you're
removing it. but if all the three conditions fails "WHERE" clouse will throw
you an error. So try to remove the "WHERE" clouse in the "SELECT" statment is
no condition were met.
Otherwise you can include a dumy condition to avoid the error.
"SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE ID > 0 "
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>

Advanced Transact_SQL

Hi everyone
I've a problem. I must convert a query dinamically composed in an ASP
script, in a stored procedure
The Visual Basic code is below.
Could you help me anyway?
SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
if(new_titolo <> "") then
mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
end if
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
if((da <> "" AND a <> "")) then
mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
end if
mySQL=trim(mySQL)
' tolgo eventuali AND finali
if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
AN") then
mySQL=mid(mySQL,1,len(mySQL)-3)
end if
mySQL=mySQL & " order by data DESC"
Thank you very muchHi
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>
Check out http://www.sommarskog.se/dyn-search.html
John|||in the following your string is not properly closed
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
this should be
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & "%' AND "
futher you are cheking whether the SQL string end with "AND" if true you're
removing it. but if all the three conditions fails "WHERE" clouse will throw
you an error. So try to remove the "WHERE" clouse in the "SELECT" statment i
s
no condition were met.
Otherwise you can include a dumy condition to avoid the error.
"SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE ID > 0 "
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>

Advanced Transact_SQL

Hi everyone
I've a problem. I must convert a query dinamically composed in an ASP
script, in a stored procedure
The Visual Basic code is below.
Could you help me anyway?
SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
if(new_titolo <> "") then
mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
end if
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
if((da <> "" AND a <> "")) then
mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
end if
mySQL=trim(mySQL)
' tolgo eventuali AND finali
if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
AN") then
mySQL=mid(mySQL,1,len(mySQL)-3)
end if
mySQL=mySQL & " order by data DESC"
Thank you very muchHi
"elisacarli" wrote:
> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>
Check out http://www.sommarskog.se/dyn-search.html
John|||in the following your string is not properly closed
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
this should be
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & "%' AND "
futher you are cheking whether the SQL string end with "AND" if true you're
removing it. but if all the three conditions fails "WHERE" clouse will throw
you an error. So try to remove the "WHERE" clouse in the "SELECT" statment is
no condition were met.
Otherwise you can include a dumy condition to avoid the error.
"SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE ID > 0 "
"elisacarli" wrote:
> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>

Advanced Sql-Shape Query - Help

Hi,
This is my basic sql shape query:
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !
Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:

> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>
|||thanks, it was helpfull !

Advanced Sql-Shape Query - Help

Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !SHAPE is not a TSQL language element. If I'm not mistaken, it is implemented
in ADO. I have a
feeling that you will get better assistance if you post to a ADO group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<doar123@.gmail.com> wrote in message news:1137102448.006011.123840@.g43g2000cwa.googlegroups
.com...
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>

Advanced Sql-Shape Query - Help

Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:
> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>|||thanks, it was helpfull !

Advanced Sql-Shape Query - Help

Hi,
This is my basic sql shape query:
----
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
----
With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.
It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.
My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !
I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).
I hope that my question is clear enough. thanks !Hi
I have never used SHAPE/OLEDB, but in T-SQL you can do:
SELECT t1.* FROM tbl1 t1 WHERE EXISTS ( SELECT * FROM tbl2 t2 WHERE t1.field
= t2.field )
Therefore you may want to try this as your parent query.
John
"doar123@.gmail.com" wrote:

> Hi,
> This is my basic sql shape query:
> ----
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ----
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !
>|||thanks, it was helpfull !

Advanced Sql-Shape Query - Help

Hi,

This is my basic sql shape query:
------------------
SHAPE {select * from tbl1}
APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
field)
------------------

With this query i get a RecordSet (RS1), who handle all the records
from table tbl1, and a secondary RecordSet (RS2) who handle all the
records from table tbl2, who applies to the criteria that field1=1.

It is possible that RS2 will be empty (zero records) since there is no
record in tbl2 who applies to that criteria.

My wish is to design a query, that will collect only the records from
tbl1, that will have records from tbl2 who applies to the criteria -
that RS2 won't be empty !

I want to influence on the main part of the query (RS1), through the
criteria that is being used in the secondery query (RS2).

I hope that my question is clear enough. thanks !(doar123@.gmail.com) writes:
> This is my basic sql shape query:
> ------------------
> SHAPE {select * from tbl1}
> APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TO
> field)
> ------------------
> With this query i get a RecordSet (RS1), who handle all the records
> from table tbl1, and a secondary RecordSet (RS2) who handle all the
> records from table tbl2, who applies to the criteria that field1=1.
> It is possible that RS2 will be empty (zero records) since there is no
> record in tbl2 who applies to that criteria.
> My wish is to design a query, that will collect only the records from
> tbl1, that will have records from tbl2 who applies to the criteria -
> that RS2 won't be empty !
> I want to influence on the main part of the query (RS1), through the
> criteria that is being used in the secondery query (RS2).
> I hope that my question is clear enough. thanks !

If I get this right you want this in the SHAPE part:

SELECT * FROM tbl1
WHERE EXISTS (SELECT * FROM tbl2 WHERE tbl2.field = tbl1.fiedl)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I didn't know i can use the exists query - I will check it out and get
back to report.

thanks for your help !|||Well, It works like a charm !!!!

thank you !!!

adodb.connection > DBMS Name property equivalent in ADO.NET ...

Hi,

One of my team member was porting a Visual Basic function into VB.NET. We were struck up while retrieving the DBMS Name property from the connection object. Actually in VB, the ADODB.connection object's properties will have an item called DBMS Name which will hold the database name like "Oracle" , "Access" , "SQL Server" like that based on the database that I connect.

I wonder if there is any equivalent property in ADO.NET. The Server Version property of ODBCConnection object returns "8.0...." in case of SQL Server and returns "09.01.0000 Oracle9i Enterprise Edition Release 9.2.0.1.0" in case of oracle. This is not as precise as it's ADODB counterpart.

I'm hardly in need of a solution or workaround for this scenario. Can anybody help.

Thanks in advance.

Hi Prathap,

The .Net Framework Data Access and Storage forum is the best forum with which to seek your answer (I see that you've already posted this question there).

Il-Sung.

ADO Error Collection in VB With SQL Prints

In Visual Basic using ADO, I am trying to get print
statements from SQL into my VB project. How does query
analyzer get the print statements from the server? I am
able to display the first TWO print statements using the
[recordset].NextRecordset command, but I get an error if
I try to go past the second recordset. Please help if
you can.
[CODE]
Dim objerr As ADODB.Error
On Error GoTo err_check
Do
For Each objerr In gcn.Errors
GetSQLPrints = GetSQLPrints & vbCrLf &
objerr.Description
rs.NextRecordset
Next
Loop
[/CODE]
Only Prints two... I don't know why. Please help.It looks like you're executing a NextRecordset during your Errors collection
iteration. I suggest you complete the iteration before NextRecordset since
multiple messages can be returned along with a recordset.
Below is a VBScript example that shows one method to process multiple
recordsets with messages. In VB, another method is to handle the ADO
Connection InfoMessage event and process the messages in your event handler.
SqlScript = _
"PRINT 'test message 1'" & vbCrLf & _
"PRINT 'test message 2'" & vbCrLf & _
"SELECT 3" & vbCrLf & _
"PRINT 'test message 4'" & vbCrLf & _
"SELECT 5" & vbCrLf & _
"PRINT 'test message 6'" & vbCrLf
Set MyRecordset = MyConnection.Execute(SqlScript)
Message = ""
Do While Not MyRecordset Is Nothing
RecordsetNumber = RecordsetNumber + 1
Message = Message & "Recordset " & _
RecordsetNumber & ":" & VbCrLf
If MyConnection.Errors.Count > 0 Then
For Each SqlError In MyConnection.Errors
Message = Message & vbTab & "Message: " & _
SqlError.Description & vbCrLf
Next
Else
Message = Message & vbTab & _
"No messages." & vbCrLf
End If
If MyRecordset.State = adStateOpen Then
Message = Message & vbTab & _
"Rowset returned." & vbCrLf
Else
Message = Message & vbTab & _
"No rowset returned." & vbCrLf
End If
Message = Message & vbCrLf
Set MyRecordset = MyRecordset.NextRecordset
Loop
Message = Message & "Recordset is Nothing."
MyConnection.Close
MsgBox Message
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike B" <BGates@.Microsoft.com> wrote in message
news:034801c3c671$449addf0$a101280a@.phx.gbl...
> In Visual Basic using ADO, I am trying to get print
> statements from SQL into my VB project. How does query
> analyzer get the print statements from the server? I am
> able to display the first TWO print statements using the
> [recordset].NextRecordset command, but I get an error if
> I try to go past the second recordset. Please help if
> you can.
> [CODE]
> Dim objerr As ADODB.Error
> On Error GoTo err_check
> Do
> For Each objerr In gcn.Errors
> GetSQLPrints = GetSQLPrints & vbCrLf &
> objerr.Description
> rs.NextRecordset
> Next
> Loop
> [/CODE]
> Only Prints two... I don't know why. Please help.|||For our connection, we are using SQLOLEDB...
When we run you code example, getting an error saying:
"Current Provider does not support returning multiple
recordsets from a single execution"
If we take the do-while loop out, we get a message box
saying:
Recordset 1:
Message: text message 1
No recordset returned
Recordset is nothing
We did add the following Dim's:
Dim SqlScript as string
dim Myrecordset as new adodb.recordset
dim recordsetnumber as long
dim sqlerror as error
dim message as string
>--Original Message--
>It looks like you're executing a NextRecordset during
your Errors collection
>iteration. I suggest you complete the iteration before
NextRecordset since
>multiple messages can be returned along with a recordset.
>Below is a VBScript example that shows one method to
process multiple
>recordsets with messages. In VB, another method is to
handle the ADO
>Connection InfoMessage event and process the messages in
your event handler.
>SqlScript = _
> "PRINT 'test message 1'" & vbCrLf & _
> "PRINT 'test message 2'" & vbCrLf & _
> "SELECT 3" & vbCrLf & _
> "PRINT 'test message 4'" & vbCrLf & _
> "SELECT 5" & vbCrLf & _
> "PRINT 'test message 6'" & vbCrLf
>Set MyRecordset = MyConnection.Execute(SqlScript)
>Message = ""
>Do While Not MyRecordset Is Nothing
> RecordsetNumber = RecordsetNumber + 1
> Message = Message & "Recordset " & _
> RecordsetNumber & ":" & VbCrLf
> If MyConnection.Errors.Count > 0 Then
> For Each SqlError In MyConnection.Errors
> Message = Message & vbTab & "Message: " & _
> SqlError.Description & vbCrLf
> Next
> Else
> Message = Message & vbTab & _
> "No messages." & vbCrLf
> End If
> If MyRecordset.State = adStateOpen Then
> Message = Message & vbTab & _
> "Rowset returned." & vbCrLf
> Else
> Message = Message & vbTab & _
> "No rowset returned." & vbCrLf
> End If
> Message = Message & vbCrLf
> Set MyRecordset = MyRecordset.NextRecordset
>Loop
>Message = Message & "Recordset is Nothing."
>MyConnection.Close
>MsgBox Message
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>
>"Mike B" <BGates@.Microsoft.com> wrote in message
>news:034801c3c671$449addf0$a101280a@.phx.gbl...
>> In Visual Basic using ADO, I am trying to get print
>> statements from SQL into my VB project. How does query
>> analyzer get the print statements from the server? I am
>> able to display the first TWO print statements using
the
>> [recordset].NextRecordset command, but I get an error
if
>> I try to go past the second recordset. Please help if
>> you can.
>> [CODE]
>> Dim objerr As ADODB.Error
>> On Error GoTo err_check
>> Do
>> For Each objerr In gcn.Errors
>> GetSQLPrints = GetSQLPrints & vbCrLf &
objerr.Description
>> rs.NextRecordset
>> Next
>> Loop
>> [/CODE]
>> Only Prints two... I don't know why. Please help.
>
>.
>|||It looks like there are some differences in behavior between VB and
VBScript. On my system, the VB code below uses Recordset.Open instead of
Connection.Execute and returns the same result as the original VBScript I
posted.
Dim SqlScript As String
Dim MyConnection As New ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim recordsetnumber As Long
Dim sqlerror As Error
Dim message As String
Dim ConnectionString As String
Dim lastError As Integer
ConnectionString = _
"Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Integrated Security=SSPI"
MyConnection.Open ConnectionString
SqlScript = _
"SET NOCOUNT ON" & vbCrLf & _
"PRINT 'test message 1'" & vbCrLf & _
"PRINT 'test message 2'" & vbCrLf & _
"SELECT 3" & vbCrLf & _
"PRINT 'test message 4'" & vbCrLf & _
"SELECT 5" & vbCrLf & _
"PRINT 'test message 6'" & vbCrLf
MyRecordset.Open SqlScript, MyConnection
message = ""
Do While Not MyRecordset.ActiveCommand Is Nothing
recordsetnumber = recordsetnumber + 1
message = message & "Recordset " & _
recordsetnumber & ":" & vbCrLf
If MyConnection.Errors.Count > 0 Then
For Each sqlerror In MyConnection.Errors
message = message & vbTab & "Message: " & _
sqlerror.Description & vbCrLf
Next
Else
message = message & vbTab & _
"No messages." & vbCrLf
End If
If MyRecordset.State = adStateOpen Then
message = message & vbTab & _
"Rowset returned." & vbCrLf
Else
message = message & vbTab & _
"No rowset returned." & vbCrLf
End If
message = message & vbCrLf
On Error Resume Next
Set MyRecordset = MyRecordset.NextRecordset
Loop
message = message & "Recordset is Nothing."
MyConnection.Close
MsgBox message
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike B/Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:10e801c3c70e$22406160$a301280a@.phx.gbl...
> For our connection, we are using SQLOLEDB...
> When we run you code example, getting an error saying:
> "Current Provider does not support returning multiple
> recordsets from a single execution"
> If we take the do-while loop out, we get a message box
> saying:
> Recordset 1:
> Message: text message 1
> No recordset returned
> Recordset is nothing
> We did add the following Dim's:
> Dim SqlScript as string
> dim Myrecordset as new adodb.recordset
> dim recordsetnumber as long
> dim sqlerror as error
> dim message as string
>
> >--Original Message--
> >It looks like you're executing a NextRecordset during
> your Errors collection
> >iteration. I suggest you complete the iteration before
> NextRecordset since
> >multiple messages can be returned along with a recordset.
> >
> >Below is a VBScript example that shows one method to
> process multiple
> >recordsets with messages. In VB, another method is to
> handle the ADO
> >Connection InfoMessage event and process the messages in
> your event handler.
> >
> >SqlScript = _
> > "PRINT 'test message 1'" & vbCrLf & _
> > "PRINT 'test message 2'" & vbCrLf & _
> > "SELECT 3" & vbCrLf & _
> > "PRINT 'test message 4'" & vbCrLf & _
> > "SELECT 5" & vbCrLf & _
> > "PRINT 'test message 6'" & vbCrLf
> >
> >Set MyRecordset = MyConnection.Execute(SqlScript)
> >
> >Message = ""
> >Do While Not MyRecordset Is Nothing
> > RecordsetNumber = RecordsetNumber + 1
> > Message = Message & "Recordset " & _
> > RecordsetNumber & ":" & VbCrLf
> > If MyConnection.Errors.Count > 0 Then
> > For Each SqlError In MyConnection.Errors
> > Message = Message & vbTab & "Message: " & _
> > SqlError.Description & vbCrLf
> > Next
> > Else
> > Message = Message & vbTab & _
> > "No messages." & vbCrLf
> > End If
> > If MyRecordset.State = adStateOpen Then
> > Message = Message & vbTab & _
> > "Rowset returned." & vbCrLf
> > Else
> > Message = Message & vbTab & _
> > "No rowset returned." & vbCrLf
> > End If
> > Message = Message & vbCrLf
> > Set MyRecordset = MyRecordset.NextRecordset
> >Loop
> >Message = Message & "Recordset is Nothing."
> >MyConnection.Close
> >MsgBox Message
> >
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >
> >"Mike B" <BGates@.Microsoft.com> wrote in message
> >news:034801c3c671$449addf0$a101280a@.phx.gbl...
> >> In Visual Basic using ADO, I am trying to get print
> >> statements from SQL into my VB project. How does query
> >> analyzer get the print statements from the server? I am
> >> able to display the first TWO print statements using
> the
> >> [recordset].NextRecordset command, but I get an error
> if
> >> I try to go past the second recordset. Please help if
> >> you can.
> >>
> >> [CODE]
> >> Dim objerr As ADODB.Error
> >> On Error GoTo err_check
> >> Do
> >> For Each objerr In gcn.Errors
> >> GetSQLPrints = GetSQLPrints & vbCrLf &
> >>
> objerr.Description
> >> rs.NextRecordset
> >> Next
> >> Loop
> >> [/CODE]
> >> Only Prints two... I don't know why. Please help.
> >
> >
> >.
> >|||Thank you - your example worked great in VB as well.
Not sure where our problem was - went in circles for a
while...
>--Original Message--
>It looks like there are some differences in behavior
between VB and
>VBScript. On my system, the VB code below uses
Recordset.Open instead of
>Connection.Execute and returns the same result as the
original VBScript I
>posted.
>Dim SqlScript As String
>Dim MyConnection As New ADODB.Connection
>Dim MyRecordset As New ADODB.Recordset
>Dim recordsetnumber As Long
>Dim sqlerror As Error
>Dim message As String
>Dim ConnectionString As String
>Dim lastError As Integer
>ConnectionString = _
> "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Integrated Security=SSPI"
>MyConnection.Open ConnectionString
>SqlScript = _
> "SET NOCOUNT ON" & vbCrLf & _
> "PRINT 'test message 1'" & vbCrLf & _
> "PRINT 'test message 2'" & vbCrLf & _
> "SELECT 3" & vbCrLf & _
> "PRINT 'test message 4'" & vbCrLf & _
> "SELECT 5" & vbCrLf & _
> "PRINT 'test message 6'" & vbCrLf
>MyRecordset.Open SqlScript, MyConnection
>message = ""
>Do While Not MyRecordset.ActiveCommand Is Nothing
> recordsetnumber = recordsetnumber + 1
> message = message & "Recordset " & _
> recordsetnumber & ":" & vbCrLf
> If MyConnection.Errors.Count > 0 Then
> For Each sqlerror In MyConnection.Errors
> message = message & vbTab & "Message: " & _
> sqlerror.Description & vbCrLf
> Next
> Else
> message = message & vbTab & _
> "No messages." & vbCrLf
> End If
> If MyRecordset.State = adStateOpen Then
> message = message & vbTab & _
> "Rowset returned." & vbCrLf
> Else
> message = message & vbTab & _
> "No rowset returned." & vbCrLf
> End If
> message = message & vbCrLf
> On Error Resume Next
> Set MyRecordset = MyRecordset.NextRecordset
>Loop
>message = message & "Recordset is Nothing."
>MyConnection.Close
>MsgBox message
>
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>
>"Mike B/Steve Z" <szlamany@.antarescomputing.com> wrote
in message
>news:10e801c3c70e$22406160$a301280a@.phx.gbl...
>> For our connection, we are using SQLOLEDB...
>> When we run you code example, getting an error saying:
>> "Current Provider does not support returning multiple
>> recordsets from a single execution"
>> If we take the do-while loop out, we get a message box
>> saying:
>> Recordset 1:
>> Message: text message 1
>> No recordset returned
>> Recordset is nothing
>> We did add the following Dim's:
>> Dim SqlScript as string
>> dim Myrecordset as new adodb.recordset
>> dim recordsetnumber as long
>> dim sqlerror as error
>> dim message as string
>>
>> >--Original Message--
>> >It looks like you're executing a NextRecordset during
>> your Errors collection
>> >iteration. I suggest you complete the iteration
before
>> NextRecordset since
>> >multiple messages can be returned along with a
recordset.
>> >
>> >Below is a VBScript example that shows one method to
>> process multiple
>> >recordsets with messages. In VB, another method is to
>> handle the ADO
>> >Connection InfoMessage event and process the messages
in
>> your event handler.
>> >
>> >SqlScript = _
>> > "PRINT 'test message 1'" & vbCrLf & _
>> > "PRINT 'test message 2'" & vbCrLf & _
>> > "SELECT 3" & vbCrLf & _
>> > "PRINT 'test message 4'" & vbCrLf & _
>> > "SELECT 5" & vbCrLf & _
>> > "PRINT 'test message 6'" & vbCrLf
>> >
>> >Set MyRecordset = MyConnection.Execute(SqlScript)
>> >
>> >Message = ""
>> >Do While Not MyRecordset Is Nothing
>> > RecordsetNumber = RecordsetNumber + 1
>> > Message = Message & "Recordset " & _
>> > RecordsetNumber & ":" & VbCrLf
>> > If MyConnection.Errors.Count > 0 Then
>> > For Each SqlError In MyConnection.Errors
>> > Message = Message & vbTab & "Message: " &
_
>> > SqlError.Description & vbCrLf
>> > Next
>> > Else
>> > Message = Message & vbTab & _
>> > "No messages." & vbCrLf
>> > End If
>> > If MyRecordset.State = adStateOpen Then
>> > Message = Message & vbTab & _
>> > "Rowset returned." & vbCrLf
>> > Else
>> > Message = Message & vbTab & _
>> > "No rowset returned." & vbCrLf
>> > End If
>> > Message = Message & vbCrLf
>> > Set MyRecordset = MyRecordset.NextRecordset
>> >Loop
>> >Message = Message & "Recordset is Nothing."
>> >MyConnection.Close
>> >MsgBox Message
>> >
>> >
>> >--
>> >Hope this helps.
>> >
>> >Dan Guzman
>> >SQL Server MVP
>> >
>> >
>> >"Mike B" <BGates@.Microsoft.com> wrote in message
>> >news:034801c3c671$449addf0$a101280a@.phx.gbl...
>> >> In Visual Basic using ADO, I am trying to get print
>> >> statements from SQL into my VB project. How does
query
>> >> analyzer get the print statements from the server?
I am
>> >> able to display the first TWO print statements using
>> the
>> >> [recordset].NextRecordset command, but I get an
error
>> if
>> >> I try to go past the second recordset. Please help
if
>> >> you can.
>> >>
>> >> [CODE]
>> >> Dim objerr As ADODB.Error
>> >> On Error GoTo err_check
>> >> Do
>> >> For Each objerr In gcn.Errors
>> >> GetSQLPrints = GetSQLPrints & vbCrLf &
>> >>
>> objerr.Description
>> >> rs.NextRecordset
>> >> Next
>> >> Loop
>> >> [/CODE]
>> >> Only Prints two... I don't know why. Please help.
>> >
>> >
>> >.
>> >
>
>.
>