|
I don't think you can do this with a query. I think you'll have to code it in a function. If it were possible, the query would look something like:
DROP TABLE (SELECT Name FROM MSysObjects WHERE Type=1 AND Name NOT LIKE 'MSys%');
How to put this into a function should be pretty obvious now: you run the inner query to get all the tablenames (DO NOT delete the MSys___ tables; although I doubt you could anyway) into a record set, then loop thru the record set with:
"DROP TABLE " & recordset!Name & ";"
Note that Access doesn't like to drop tables that have relationships on them, so this may not work unless you first delete the relationships.
Hope this helps.
David
|
|
|
|
|
Hi Friends,
i have data like this.......
number
--------------
123
234
3435
5656
i want to display column wise data in one row like this...
number
---------------
123,234,3435,5656
kindly tell me the solution for this? urgent
|
|
|
|
|
allisha wrote: i want to display column wise data in one row like this...
number
---------------
123,234,3435,5656
Read the data back and format a string appropiately. e.g.
bool isFirst = true;
StringBuilder sb = new StringBuilder();
SqlDataReader reader = myCommand.ExecuteReader();
while(reader.Read())
{
if (isFirst)
isFirst = false;
else
sb.Append(",");
sb.Append(reader.GetInt(0).ToString());
}
string displayResult = sb.ToString();
|
|
|
|
|
hi Colin,
first of all thanq but i want to show it in sql.
if possible cau u tell me the sol in sql.
|
|
|
|
|
I think through CURSOR only we can solve this issue. Try the below query.. You can also create one function with this query and get the value back in a single column.
DECLARE @OUTPUT NVARCHAR(100), @NAME NVARCHAR(100)
DECLARE LAST_CURSOR CURSOR
LOCAL SCROLL STATIC
FOR
---Here give your select query...
SELECT NAME FROM DBT_EMPL_DTL
OPEN LAST_CURSOR
set @OUTPUT = ''
FETCH NEXT FROM LAST_CURSOR INTO @NAME
WHILE @@FETCH_STATUS = 0 BEGIN
BEGIN
set @OUTPUT = @OUTPUT + @NAME +','
END
FETCH NEXT FROM LAST_CURSOR INTO @NAME
END
SET @OUTPUT = LTRIM(@OUTPUT)
PRINT @OUTPUT
CLOSE LAST_CURSOR
DEALLOCATE LAST_CURSOR
Cheers,
Shetty
|
|
|
|
|
if you are using sql server 2005 you can use the pivot and unpivot commands in your sql code to go from rows to columns or vice versa. It's pretty easy to use.
|
|
|
|
|
while i am trying to connect to sql server 2005 my system throughs an exception..
System.Data.SqlClient.SqlException was unhandled by user code
Message="An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Due to this I cann't proceed further...
plz help me soon..
|
|
|
|
|
I have had this problem occur several times with several different machines, but most particularly when running XP and installing SQLExpress or SQL Server 2005. Even if you enable all the protocols and use the Surface Area Configuration Tool to enable remote access, I have found that the Windows Firewall settings are not necessarily updated. Simply edit the firewall rules to allow SQL Server TCP/IP usage (or turn off the firewall - NOT recommended!) and you will be able to connect.
Regards,
Satips.
|
|
|
|
|
Besides the other fine advice you've gotten so far you also might wish to be sure the server instance is running.
Learning is not a spectator sport. - D. Blocher
|
|
|
|
|
Hi,
In my application,I am using MS Access as the db.In the db table,named Alarms, which have 3 columns:-
"DateIn"(Format:ShortDate)
"TimeIn"(Format:LongTime)
"Description"(Format:Text)
But when i bind the data into datagridview, the "TimeIn" column will display data in format "MM/DD/YYYY HH:mm:ss" instead of HH:mm:ss.The way i am binding the values as below:
OleDbConnection conx = new OleDbConnection(strAccessConn);<br />
strAccessSelect = "select * from Alarms;<br />
<br />
conx.Open();<br />
OleDbCommand cmd = new OleDbCommand(strAccessSelect, conx);<br />
OleDbDataReader read = cmd.ExecuteReader();<br />
DataSet ds = new DataSet();<br />
DataTable dt = new DataTable("Alarms");<br />
ds.Tables.Add(dt);<br />
ds.Load(read, LoadOption.PreserveChanges, ds.Tables[0]);<br />
read.Close();<br />
conx.Close();<br />
dataGridView1.DataSource = ds.Tables[0];
What i wish is just display HH:mm:ss in the TimeIn column in datagridview, instead of "MM/DD/YYYY HH:mm:ss"
Can anyone guide me to solve it out.If i am post in the wrong discussion area,please guide me to the correct one.
Thanks in advance
cocoonwls
-- modified at 22:35 Thursday 19th April, 2007
|
|
|
|
|
how are you inserting the "TimeIn" data.....Are you picking it from any DatetimePicker control or Textbox.....?? Only insert the 'Time' Part of your date.....
Tirtha
Miles to go before I sleep
|
|
|
|
|
Hi,
Yes, i am getting the value from DateTimePicker, code as below:
strInsert = "INSERT INTO Alarms (DateIn, TimeIn, Description) VALUES (#" +dateTimePicker1.Value.ToShortDateString()+ "#,#" +dateTimePicker1.Value.ToLongTimeString()+ "#,'e')";
In my database i alredy set the the format to LongTime.Any advise?
thanks in advance
cocoonwls
|
|
|
|
|
Hi all:
what is "No overload for method 'runNonSelectQuery' takes '3' arguments"
This is the code that give me this error message above.
I was trying to run an INSERT INTO FILE(Description,Title,Host,Port)Values('name','','') base on the User's input from the TextBox.
this.m_SQLClient.update(this.TB_Insert.Text,this.TB_Values.Text,this.TB_B.Text );
any suggestion how to insert multiple argument for the code above?
Thanks in advance for your help.
eyungwa
|
|
|
|
|
The compiler's error message you give does not match the code you have given. The error message will be for a line of code that calls a method called runNonSelectQuery. The actual method exists, but you have supplied 3 parameters (arguments) to the method. The method requires a different number of arguments.
Check what the methods needs and supply the correct information to it.
If you still have problems please post the relevant code.
|
|
|
|
|
Hello,
pls,I need help in which data type can I store this value "frt-123456"
it contains 3 characters and 6 auto increment numbers Can I do this in sql server or I have to do this in my app an set the data type to nvarchar.
thanks.
Dad
|
|
|
|
|
It looks like you are storing two different things in the column. If so then you are breaking the first normal form and you should consider splitting the value into two separate columns. You can always create a compound primary key if the existing value is used as a primary key. You can also create a calculated column to join the separated values together again (if you read the column frequently).
If you were to split the value into two separate parts the numeric part could be automatically incremented by the database.
|
|
|
|
|
I am new to SQL, especially use SQL in VC++ 6.0 framework.
I am told that creating INDEX on field(s) could speed up a query.
if I create a INDEX like the following
<br />
CREATE nonclustered INDEX IX_XYZ on TableA.field1<br />
Should I use the INDEX name IX_XYZ in some way in the following SELECT statement. Or the following SELECT statement will be carried out automatically based on the INDEX IX_XYZ.
<br />
SELECT * FROM TableA WHERE field1 = xxx:((<br />
|
|
|
|
|
The query optimiser in SQL Server will work out if it can use an index to speed up the query execution. You don't need to specify anything.
|
|
|
|
|
Hello All,
I have a problem on how to retrieve records from a database using SQL query. It is a little bit complicated query. I do not know what SQL query experission is correct.
In the database table, there are four fields "ClassID", "Personal Nmae", "Age", "Score". The following is an example for illustration.
<br />
Class Name Age Score<br />
0 Bob 20 78 <====<br />
0 John 26 66 <****<br />
2 Wilson 28 88<br />
1 John 26 77 <****<br />
3 Alice 25 56<br />
1 Bob 20 89 <====<br />
<br />
In the above example table, I would like to know the scores of students who share the same name, at the same age, BUT in different class (ClassID is specified by the user). Actually, I need a SQL SELECT experission which will lead to a query result (a recordset) like the following
<br />
Bob 78 89<br />
John 66 77<br />
Actually, in my application there are only two scores for a given name. One more further question,
If we have another table 'ClassTotalScore' consisting of two fields "Class" , "TotalScore"
<br />
SELECT s1.Name,s2.Score<br />
FROM student_results s1<br />
JOIN student_results s2 <br />
ON s1.Name=s2.Name <br />
AND s1.Age=s2.Age <br />
AND s1.Class<>s2.Class<br />
<br />
Can I add some clause to the above suggested SQL experission to get the sum of total scores of Class=0 and Class=2. Or I have to use another SQL statement to get the sum of total scores of two classes.
Moreover, if either way is possible, I would like to know whether ONE SQL statement is necessarily faster than TWO SQL statements.
Actually, those data in the two tables originally are stored in varibles in my VC++ 6.0 program. However, as more and more data are produced, the PC memory is almost exhaused. Hence, i have to resort to the database technology. I notice that the running speed of the databased based program is 10 times slower than the original program. I wonder if this is normal.
|
|
|
|
|
Try this out.
This query will result like
Bob 78 89
John 66 77
Wilson 88 88
Alice 56 56
(i.e. It will display Wilson and Alice even though they have only one class.)
SELECT A.[Name],[Score1],[Score] "Score2" FROM tblName A,
(
SELECT B.[Name], [Score] "Score1", [NameAge],"Min","Max" FROM tblName B,
(
SELECT ([Name]+CAST([Age] AS varchar))"NameAge",MIN([Class]) "Min",MAX([Class]) "Max"
FROM tblName GROUP BY [Name]+CAST([Age] AS varchar)
) C
WHERE B.[Name]+CAST([Age] AS varchar)=NameAge AND [Class]=[Min]
) D
WHERE A.[Name]+CAST([Age] AS varchar)=NameAge AND [Class]=[Max]
Please let me know if U want a query that will eliminate
the records
Wilson 88 88
Alice 56 56
Regards,
Arun Kumar.A
|
|
|
|
|
Try this query and let me know the result...
SELECT NAME, MIN(AGE)AS SCORE1,MAX(AGE)AS SCORE2 FROM DBT_EMPL_DTL
WHERE NAME IN (SELECT NAME FROM DBT_EMPL_DTL GROUP BY NAME HAVING (COUNT(NAME)>1))
GROUP BY NAME
I have used AGE field, for that you use score field.
Cheers.
Shetty
|
|
|
|
|
Hello All,
i am using ADO in VC++ 6.0 to access a SQL server database table.
I wonder how to put/get BOOL type value into/from the table.
I tried to use _variant_t but I got -1 for '1' when retrieving the BOOL field.
|
|
|
|
|
|
Q> I have a datagrid which is being used to disppay the employee details from employees table but now there is a quiestion how to retrive more tahn one column of the table to a single column of the datagrid without changing the query.
|
|
|
|
|
Wrong thread...
Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
