|
devboycpp wrote: dataSet.Tables["Table1"];??? will this work ??
You can use -
dataSet.Tables[0] for first table.
dataSet.Tables[1] for the second and so on....
devboycpp wrote: How can I merge the three tables in one table ,assuming they have columns of the same number and datatype but with different names ????
You can use a union clause in the stored procedure instead of merging them in the dataset. I think it would be a better choice !
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
coolestCoder wrote: "A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
I have seen that one before and it is a good one. Nice to see it again 
|
|
|
|
|
PaulC1972 wrote: have seen that one before and it is a good one. Nice to see it again
Thanks !
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
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.
Cool. I learned something new today Always thought only one table could be in a data set. Makes sense 
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
PaulC1972 wrote: Always thought only one table could be in a data set
Why did you think that a DataSet contained a collection of DataTables?
|
|
|
|
|
I forgot that we can have multiple tables in a data set 
|
|
|
|
|
i think u can have multiple datatables in one dataset and at the
time of calling dataadapter's fill method u can specify the table name ur filling in dataset.
dataAdapter.Fill(dataset,"table name");
and can access particular table later on from dataset by
dataset.Tables["table name"];
rahul
|
|
|
|
|
Hi Rahul,
Yes, I agree that you can name the data tables while filing the data set. But did you read the question properly ? The requirement was to fill the data tables in only 1 round trip to the data base. And hence only a stored procedure can be used in this scenario ( atleast, i think so ).
And now if you are filling multiple tables using the stored procedure how can you name them ? No hard feelings, but if you know how to name tables generated from SP output, please feel free to reply, so that I can add it to my knowledge.
Thanks !
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
coolestCoder wrote: but if you know how to name tables generated from SP output, please feel free to reply
For this, you have to have your own TableMapping and then supply that mapping object to data adapter.
Something like this:
<br />
DataTableMappingCollection mappings = new DataTableMappingCollection();<br />
mappings.Add("Table", "DescriptiveName");<br />
mappings.Add("Table1", "DescriptiveNameFor2ndTable");<br />
mappings.Add("Table2", "DescriptiveNameFor3rdTable");<br />
<br />
Farhan Noor Qureshi
|
|
|
|
|
how create an ODBC connection? how trancefering data using a network?
madu
|
|
|
|
|
This Google[^] search may help you out. Been a long time since I've dealt with ODBC :->
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
I need to transfer some data from multiple labels on a VB.NET 2005 form to an Access database. I am trying to figure out how to transfer that data from those labels into a row of data and then update the databas and refresh the datagrid to show the new row???
|
|
|
|
|
There are lots of ways to bind data to controls.
On a recent project I used OleDbDataAdapter when working with an Access database. The applicaiton I developed was in C++ .Net but there are lots of samples that use VB, like this one that shows adding records: VB Sample [^]
Your refresh function would have code something like this:
(change the "->" to "." if you are not using C++)
oleDbDataAdapter1->SelectCommand->CommandText =
queryText;
dataSet1->Clear();
oleDbDataAdapter1->Fill( dataSet1, S"track" );
dataGrid1->SetDataBinding( dataSet1, S"track" );
QueryText->Text = queryText;
|
|
|
|
|
i am using sQL 2000 whtat I want to do is that I want ot create a trigger sot hat every time I import a text file and on every insert it checks for
a. if the imported data has hyphen '-' sign in it
b. If the imported data has correct date
c. if the imported data has values which exists in another table
I have tried it with using Instead of triggers , but I am stuck with the logic that how can I check for all the abovementioed cases before inserting the data
please help me
imran
|
|
|
|
|
a and b can be done using triggers, but I try to stay away from having triggers doing any kind of 'select' which is what I read c to be. Unless you are talking about referential integrity in which case you don't have to do anything about it in a trigger.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
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
You didn't really mention what you want to do if any of the a,b,c conditions are met...so I just put print statements in the example.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
thanks mate it worked
atleast upto the printing atleast ...
now what if i want to roll back the transaction if it finds any of the errors ..
and i want to completely fail the transaction and roll back to the original state in which the table was ... and if the checks pass than commit transaction
and i actually want to check if the DATE Is in valid format... like isdate() ,, if it passes then commit transaction , if it doesnt then fail the transaction
please bear with me i am just a newbie
sorry to bother u again
or replace them with the appropriate value
thanks again .. u solved my problem
cheers
imran
|
|
|
|
|
thanks mate it worked
atleast upto the printing atleast ...
now what if i want to roll back the transaction if it finds any of the errors ..
and i want to completely fail the transaction and roll back to the original state in which the table was ... and if the checks pass than commit transaction
and i actually want to check if the DATE Is in valid format... like isdate() ,, if it passes then commit transaction , if it doesnt then fail the transaction
please bear with me i am just a newbie
sorry to bother u again
or replace them with the appropriate value
thanks again .. u solved my problem
cheers
imran
|
|
|
|
|
Imran
If you extend this to work with the INSTEAD OF trigger that I mentioned in an earlier post, then you have the solution.
So, insert into the view.
Do your checks in the trigger, and if the data doesn't fail your tests, insert it into the table.
If the data does fail the tests, do nothing (or write to a log - whatever you want to do with the failed data).
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hey everyone! I have been scouring the internet for help on an SQL query i need to run and have been trying for a day or two now to no avail! Can any SQL heroes please help me???
My problem seems simple but may not be so! In this example i have two tables, with their respective columns beside in brackets!
1. SERVERS (ID, NAME, LOCATION, OS)
2. SERVERS_VM (PHYSICAL_ID, VIRTUAL_ID)
The columns ID, PHYSICAL_ID and VIRTUAL_ID are all primary keys!
Now consider that the SERVERS table contains lots of servers both physical and virtual each with their own unique primary key labelled ID. While the SERVERS_VM table contains a list of all physical servers that have virtual servers installed on them! So each PHYSICAL_ID in SERVERS_VM might have one or more VIRTUAL_ID's associated with it.
Below i will try to give an example;
SERVERS
-------------------------------------------
ID Name Location o/s
-------------------------------------------
1 Bob12 BobOffice Win Server
23 Ash14 AshOffice Virtual
7 Hank89 Hankoffice Win Server
14 Tom98 Tomoffice Virtual
8 Mark45 Markoffice Win Server
93 Bar123 Baroffice Virtual
SERVERS_VM
---------------------------
PHYSICAL_ID VIRTUAL_ID
---------------------------
7 23
8 14
7 93
Ok so, now not trying to confuse people, but if we see above, Ash14 tom98 and bar123 are virtual servers (os=virtual) located on physical servers (shown in servers_vm)
From SERVERS_VM we can see that server number 7 houses two virtual servers given by their VIRTUAL_ID which is the same as the ID i the SERVERS table! (23 & 93)
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??
The results should be something like;
---------------------------------------------
ID NAME VIRTUAL_ID
---------------------------------------------
7 Hank89 23
7 Hank89 93
8 Mark45 14
the problem is the fact that there are two primary keys in the SERVERS_VM table, well even though the PHYSICAL_ID repeats in the SERVERS_VM table
this is what i have so far;
SELECT PHYSICAL_ID, VIRTUAL_ID FROM (SELECT ID, NAME FROM SERVERS WHERE OS like '%Virtual%') WHERE SERVERS.ID=SERVERS_VM.VIRTUAL_ID
i have gone to much more depth but dont want to post it up because it is really messy.
Can anyone please Help??????
|
|
|
|
|
I can give you the answer but it sounds like a homework kind of question. So, in my attempt to help you but not give you the answer, here is the magic hint, use Group By, Having and count(*).
Farhan Noor Qureshi
|
|
|
|
|
Thanks Sir,
Actually im new in a Job and am learnin SQL on my own, so my knowledge of these different commands is limited. Thanks though, anymore hints would be great!
|
|
|
|
|
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)
But, the sql to answer your question is different from the results you wanted. To get this:
---------------------------------------------<br />
ID NAME VIRTUAL_ID<br />
---------------------------------------------<br />
7 Hank89 23<br />
7 Hank89 93<br />
8 Mark45 14
You would use something like the following sql:
select s.ID,s.name,p.virtual_id from servers s inner join servers_vm p on s.id = p.physical_id
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I want to use a Gridview for a standard CRUD app.
So far I have my delete event wired up but I have a problem with my stored proc. I receive this error everytime i run my debugger and click the delete event. I have a catch event to catch this error.
The SqlParameterCollection only accepts non-null SqlParameter type objects, not Parameter objects.
Here is the code for my deleterow event. Any help is appreciated.
protected void DeleteRow(object sender, GridViewDeleteEventArgs e)
{
try
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = ConfigurationManager.ConnectionStrings["PokerTraxConnectionString"].ToString();
connection.Open();
SqlCommand delCommand = new SqlCommand();
delCommand.Connection = connection;
delCommand.CommandText = "usp_Delete_Loan";
delCommand.CommandType = CommandType.StoredProcedure;
delCommand.Parameters.Add(new Parameter("LoanId"));
delCommand.ExecuteNonQuery();
SqlDataAdapter adapter = new SqlDataAdapter(delCommand);
DataSet ds = new DataSet();
GridView1.DataSource = ds;
adapter.Fill(ds);
Cache["Loan"] = ds;
BindPlayerNames();
connection.Close();
connection.Dispose();
delCommand.Dispose();
}
catch (Exception ex)
{
errorMessage.Text = ex.Message;
}
}
|
|
|
|
|
This could be that the parameter was not created completey. In code that I have used I set other values for parameter object.
Generic stored proc calling code:
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")
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
