Database

coolestCoder wrote:

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

PaulC1972 wrote:

have seen that one before and it is a good one. Nice to see it again

coolestCoder wrote:

Use a stored procedure. Once you call the stored procedure, you will get result of all three SQL statements, irrespective of any relation between the tables. Use a data adapter to fill a data set. This will create a seperate table for each sql query fired through the stored procedure.
It is as simple as it sounds to be.

PaulC1972 wrote:

Always thought only one table could be in a data set

coolestCoder wrote:

but if you know how to name tables generated from SP output, please feel free to reply

	<br />
DataTableMappingCollection mappings = new DataTableMappingCollection();<br />
mappings.Add("Table", "DescriptiveName");<br />
mappings.Add("Table1", "DescriptiveNameFor2ndTable");<br />
mappings.Add("Table2", "DescriptiveNameFor3rdTable");<br />
<br />

// set the text of the SQL query to what the user typed in
oleDbDataAdapter1->SelectCommand->CommandText =
    queryText;

// clear the DataSet from the previous operation
dataSet1->Clear();

// fill the data set with the information that results
// from the SQL query
oleDbDataAdapter1->Fill( dataSet1, S"track" );

// bind the DataGrid to the contents of the DatSet
dataGrid1->SetDataBinding( dataSet1, S"track" );

QueryText->Text = queryText;

CREATE TRIGGER tgrImporting ON [dbo].[ImportedData] 
FOR INSERT
AS

if (exists(select id from inserted where charindex('-',inserted.importedcol)>0))
begin
	print 'found hyphen'
end

if (exists(select id from inserted where importeddate = getdate()))
begin
	print 'found date'
end

if (exists(select id from inserted where importedcol in(select checkfield from checktable)))
begin
	print 'found values in other table'
end

bazpaul wrote:

So heres is the big question? How can i run an SQL query to list all the physical servers that have virtual servers alone installed on them??

select * from servers where id in(select physical_id from servers_vm)

---------------------------------------------<br />
ID NAME VIRTUAL_ID<br />
---------------------------------------------<br />
7 Hank89 23<br />
7 Hank89 93<br />
8 Mark45 14

select s.ID,s.name,p.virtual_id from servers s inner join servers_vm p on s.id = p.physical_id

Dim command As New System.Data.SqlClient.SqlCommand
command.Connection = m_sqlConnection

command.CommandText = strStoredProc
command.CommandType = CommandType.StoredProcedure

' declare the parameter object
Dim param As System.Data.SqlClient.SqlParameter


Dim myEnumerator As System.Collections.IEnumerator = _
            parameterArray.GetEnumerator()
Dim pItem As ParameterItem
While myEnumerator.MoveNext()
    pItem = CType(myEnumerator.Current, ParameterItem)

    ' Add a new parameter, get back a reference to it
    param = command.Parameters.Add(pItem.Name, pItem.Type)

    ' set the parameter's direction and value
    param.Direction = pItem.Direction
    param.Value = pItem.Value
End While

Dim myDataAdapter As New SqlClient.SqlDataAdapter
command.CommandTimeout = iTimeOut
myDataAdapter.SelectCommand = command

myDataAdapter.Fill(ds, "ResultSet")