Hi,

Here is my sql statement, which includes two insert and one select.

string sql = "Insert into TestMultipleTran..TradeValueCountryPeriod
values ('H1',1994,'100','101',14521.20);" +
" Select * from TestMultipleTran..TradeValueCountryPeriod;" +
" Insert into TestMultipleTran..TradeValueCountryPeriod
values ('H1',1994,'100','101',14521.20);";

I could able to do ExecuteNonQuery and Fill with sqldataadapter with
above sql statement.

When I do ExecuteNonQuery I get output as 2, thats good

When I do sqldataadapter fill I get my dataset for select statement;

Here is the question, How do I get all three result with one exeuction.
??

Thanks.

hi,
[quoted text, click to view]

if you mean something like
Private Sub ExecCMD()
Dim con As New SqlClient.SqlConnection
With con
.ConnectionString =
"Server=(Local);Database=tempdb;Trusted_Connection=True;"
.Open()
End With

Dim cmd As New SqlClient.SqlCommand
With cmd
.CommandText = "CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY
KEY, data varchar(10) NOT NULL);"
.CommandType = CommandType.Text
.CommandTimeout = 5
.Connection = con
End With
cmd.ExecuteNonQuery()

Dim dynCmd As String = "INSERT INTO dbo.TestTB VALUES ( 1, 'Andrea' );"
& ControlChars.NewLine & "SELECT * FROM dbo.TestTB;" & ControlChars.NewLine
& "INSERT INTO dbo.TestTB VALUES ( 2, 'Andrea2' );"
cmd = New SqlClient.SqlCommand
With cmd
.CommandType = CommandType.Text
.CommandTimeout = 5
.Connection = con
.CommandText = dynCmd
End With

Dim ds As New System.Data.DataSet
Dim da As New SqlClient.SqlDataAdapter
With da
.SelectCommand = cmd
.Fill(ds)
End With

cmd = New SqlClient.SqlCommand
With cmd
.CommandText = "DROP TABLE dbo.TestTB;"
.CommandType = CommandType.Text
.CommandTimeout = 5
.Connection = con
End With
cmd.ExecuteNonQuery()

cmd.Dispose()
cmd = Nothing
con.Dispose()
con = Nothing

For Each dt As System.Data.DataTable In ds.Tables
Debug.WriteLine(dt.TableName)
For Each dc As System.Data.DataColumn In dt.Columns
Debug.Write(dc.ColumnName)
Next
Debug.WriteLine("")
For Each dr As System.Data.DataRow In dt.Rows
For i As Int32 = 0 To dt.Columns.Count - 1
Debug.Write(dr.Item(i))
Next
Next
Debug.WriteLine("")
Next

ds.Dispose()
ds = Nothing
End Sub

to "output" in the defined datasets 3 datatable, 1 for the first insert
statement "return" value, the second for the actual "select" statement, and
the thrid, again, for the second insert statement "return" value, then this
can not be done... the Fill method actually (obviosly) returns the real
"select" statement only...

even a cycle over readers like
Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader
Do
While rdr.Read
For i As Int32 = 0 To rdr.FieldCount - 1
Debug.Write(rdr.Item(i))
Next
Debug.WriteLine("")
End While
If rdr.NextResult = False Then Exit Do
Loop

does not work as you require..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
--------- remove DMO to reply