|
Here you go  this will do it..
using System;
namespace Tester
{
///
/// Summary description for Class2.
///
public class Class1
{
public Class1()
{
}
public void GenerateColumns(System.Data.SqlClient.SqlConnection connection)
{
string selectstr = "SELECT name FROM sysobjects WHERE xtype ='U' AND name <> 'dtproperties' ORDER BY name";//select only user tables
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(selectstr, connection);
cmd.Connection = connection;
cmd.Connection.Open();
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
this.AddColumn(dr.GetString(0), cmd.Connection.ConnectionString);
}
cmd.Connection.Close();
}
private void AddColumn(string tableName, string connnectionStr)
{
//ALTER TABLE table ADD column VARCHAR(20) NULL
string alterTableStr = string.Format("ALTER TABLE {0} ADD {1} {2} {3}", tableName, "YourColumnName", "datetime Default getdate()", "NULL");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(alterTableStr, new System.Data.SqlClient.SqlConnection(connnectionStr));
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch(System.Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
finally
{
cmd.Connection.Close();
}
}
}
}
-- modified at 14:02 Monday 5th September, 2005
|
|
|
|
|
Thanx Guys!!
that code really helped me.
keep on helping the others.
cheerz......
|
|
|
|
|
Hello,
I am developing an application where I need to load custom data to Analysis Server (MS-OLAP) without going through any OLE DB Provider.
I am able to load data into dimensions/cubes using Microsoft OLE DB provider for ODBC Drivers. But my requirement is loading data at runtime from a buffer. Is there any way to do this? Are there any APIs which can help me to load this data directly?
Thanks & regards,
kuldeep kumar
|
|
|
|
|
Hi, Exmaine the following code... specially the first adapter.Fill statement.
DB.DataBase db = new DB.DataBase("NorthWind");
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("select * from employees",DB.Global.SqlConnection());
adapter.Fill(db.DataSet); //rows in the 'Employee'-table is empty
adapter.Fill(db.DataSet.Tables["Employees"]]); // this works as expected..
Is there somone that can explane what is the disfference between the 2 lines above.. the Dataset holds all the tables from the northwind Database.
I would as minimum expext, there were some rows in the 'employee'-table.. since I only make a select statement for the 'employee'-table.
|
|
|
|
|
Since a recordset can be obtained from different tables, a SELECT query does not return the name of a table, so the SqlDataAdapter has no way of knowing which table to put the data into. So, it automatically names tables Table, Table1, Table2 and so on.
After the first Fill call, the DataSet will have a table named Table containing the records retrieved. With the second call, you're telling it which DataTable to fill, so you get what you expect.
The SqlDataAdapter has a property, TableMappings, to solve this issue. Try this code before your Fill call:
adapter.TableMappings.Add("Table", "Employees");This will tell the data adapter to map the first table returned to the Employees table inside the DataSet.
I hope I've made things clearer!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Thanks a lot, YES now it is very clear
This works!!!
int i = 0;
foreach(DB.DbTable o in db.DataSet.Tables)
{
adapter.TableMappings.Add(string.Format("{0}{1}","Table",i == 0 ? "" : i.ToString()), o.TableName);
adapter.SelectCommand.CommandText += string.Format("SELECT * FROM [{0}]{1} ",o.TableName, i < db.DataSet.Tables.Count ? ";" : "");
i++;
}
adapter.Fill(db.DataSet);
|
|
|
|
|
I'm glad I could help!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Hi Every body
I have a usercontrol (Named DatePicker) with a property called 'Date'.This is the syntax of this property :
[Category("Date Info"),Bindable(true)]
public string Date
{
get
{return this.dateString;}
set
{this.dateString = value;}
}
This control also have some string properties (Day,Year and month) without '[Bindable(true)]'Attribute.I use this user control and bind the 'Date' property of this control to a string column of a DataView in my form :
this.datePicker1.DataBindings.Add("Date",this.dvDataSource,"Birthday");
When I delete all rows of dataview whith this code :
this.BindingContext[this.dvDataSource].RemoveAt(this.BindingContext[this.dvDataSource].Position);
and then try to add a new row using the following code,
this.BindingContext[this.dvDataSource].AddNew();
I receive the following Exception : 
"DataBinding could not find a row in the list that is suitable for all bindings"
Note :
When I comment the line of code that contains instruction for Binding this property, everything works ok.I bound some other controls (Like DataGrid and TextBox) to the other columns of DataView.They haven't any problem !!
Please help me if anyone knows the answer.
Best Regards.
[ _ Always there is another way _ ]
|
|
|
|
|
hi all
in my database application and in my form :
I use 2 table from SQL .In table1(mainTable) my fields are id_car,car,id_city .
and in Table2 my fields are id_city,city .
id_city in table1 has a relation to id_city in Table2 , but
I want to fill the Datagrid by table1 .
by the way I have a combobox for displaying the city by the the city field from table2 (from the relation exist
between two table )and ,I have a textbox for displaying the car and a datagrid contaning from city and car,in my form .
For displaying the records , I use a View in SQL .
because when I want to save and repeat one city in the some or more record , I select it by the id_city from the table2 .
Now I want to add,update,Delete my database application
in spite of that we know about the relation table1 and table2 .
ofcourse my main table in the application is the table1 .
thanks in advance!!!
mostafa hosseiny b.
mostafa hosseiny b.
|
|
|
|
|
Hi,
I have the following C++ code, using MS-studio.Net (MFC).
CDatabase aDatabase;
TCHAR buffer[400];
SQLRETURN rc = SQLGetInfo(aDatabase.m_hdbc, SQL_DATABASE_NAME,(SQLPOINTER buffer, sizeof(buffer), &size);
With this code at present, the value returned in buffer if "master" if the DSN is not pointing to any database. I think master is the default database.
I actually want to know if the DSN is pointing to a correct(existing) database or not. I wanted to display a message if the DSN is not pointing to any database or if its pointing to a database that no more exists.
How can I achieve this? Anybody any thoughts?
Thanks,
Joe
|
|
|
|
|
Ask for the schema:
SELECT TABLE_CATALOG
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_CATALOG
Look up INFORMATION_SCHEMA in BOL if you need to verify more than just the catalog (database) name.
|
|
|
|
|
Hey guys,
QUESTION 1: Is this correct?
I. Transaction Isolation Level Serializable = Pessimistic Locking
For example:
trx = conn.BeginTransaction(IsolationLevel.Serializable)
ie. When two threads try to access the same record..
1. Thread 1 begins tran and select [record 123] (I presume a X-lock *Exclusive lock* is acquired)<br />
2. Thread 2 begins tran and *attemp* to select [record 123] (but must wait before select comes thru because Transaction Isolation is set to Serializable -- ie. pessimistic -- I presume Thread 2 is also trying to acquire X-lock at this time but need to wait)<br />
3. Thread 1 commit updates on [record 123]<br />
4. Thread 2 select on [record 123] comes thru <br />
5. Thread 2 update<br />
6. Thread 2 commits update<br />
II. Transaction Isolation Level = ??? For optimistic locking?
<br />
1. Thread 1 begins tran and select [record 123] (I presume a S-lock *shared lock* is acquired)<br />
2. Thread 2 begins tran and select [record 123] and it comes thru (Thread 2 also acquire a S-lock at this point)<br />
3. Thread 1 commit updates on [record 123 \ file updated: firstname] (Thread 1 acquired a U-lock *update lock* at this point)<br />
4. Thread 2 commit updates on [record 123 \ file updated: lastname] (CAUTION: firstname commited by Thread 1 overwritten by Thread 2 at this time) (Thread 2 acquired a U-lock *update lock* at this point)<br />
Is this correct?
QUESTION 2:
With cursor you get to specified locking, for example:
<br />
Dim objRs As ADODB.Recordset<br />
Set objRs = New ADODB.Recordset<br />
<br />
Or Dim Objrs As New Adodb.Recordset<br />
<br />
objRs.Open "users", objConn, adOpenKeyset, adLockOptimistic, adCmdTable
But how with ... "System.Data.IDbConnection"?
Thanks in advance.
-- modified at 23:37 Thursday 1st September, 2005
|
|
|
|
|
What DBMS you have ?
Level of isolation may differ from theory to a specific DBMS or
from a DBMS to another DBMS !
---
object oriented
uml oriented
iconix oriented
sql oriented
truespace oriented
---
solitare oriented
|
|
|
|
|
say, Microsoft SQL Server?
|
|
|
|
|
1) ok
2) Try use SQL Server SET command like this: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE for example
---
object oriented
uml oriented
iconix oriented
sql oriented
truespace oriented
---
solitare oriented
|
|
|
|
|
No, there seems to be distinction between "Data Isolation (Serializable/Repeatable Read/Read Committed/Dirty)" and "Optimistic (or Pessimistic) Locking"...
In my post I was asking:
"Serializable" corresponds to "Pessimistic locking" --> Now, for "Optimistic locking", which level of data isolation does it correspond to? Repeatable Read?
Norman Fung
|
|
|
|
|
HOW I KNOW WHAT TABLES ARE IN A DATA BASE
PLEASE HELP ME.
I KNOW THE ARE A DATA BASE PRINCIPAL IN SQL SERVER WHIT CONTAINS ALL DATA BASE NAMES AND TABLES CONTAINS
|
|
|
|
|
|
You could look in the table sysobjects (or this may be different if you are using a different database type)
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
STOP YELLING !!!
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
What is the database that you are using.
Assuming its SQL Server
To get an *approximate* count for all tables, you can use the following:
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
|
|
|
|
|
SELECT
[TableName] = so.name,
FROM
sysobjects so
WHERE
so.xtype = 'U'
ORDER BY
so.name
|
|
|
|
|
got a problem:
table(with names A, B):
A B
----------
1 Hallo
1 wie geht es dir?
2 schlecht!
3 nein,
3 eigentlich gut!
I want the result:
A B Resultat C
-------------------------------------------
1----Hallo----------------
1----wie geht es dir?-----Hallo wie geht es dir?
2----schlecht!------------schlecht!
3----nein,----------------
3----eigentlich gut!------nein, eigentlich nicht
Thank`s for helping!!!
nico
|
|
|
|
|
Try this:
First) You must create a "user defined function" (T SQL function)
like this
CREATE FUNCTION SSum (@a INT) //SSum = string sum
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @ssum NVARCHAR(255)
DECLARE @b NVARCHAR(25)
SET @ssum = ''
DECLARE crs CURSOR FOR SELECT B FROM table WHERE A = @a
OPEN crs
FETCH NEXT FROM crs INTO @b
WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @ssum = @ssum + '-' + @b
FETCH NEXT FROM crs INTO @b
END
CLOSE crs
DEALLOCATE crs
IF( LEN(@ssum) != 0 )
SET @ssum = SUBSTRING(@ssum,2,255)
RETURN @ssum
END
Second) Build a SELECT query using SSum function like this:
SELECT DISTINCT A , dbo.SSum(A) AS Result
FROM table
---
object oriented
uml oriented
iconix oriented
sql oriented
truespace oriented
---
solitare oriented
|
|
|
|
|
Hi,
I have .MDF ,SQL server DB files.
How to import those data or how to open .MDF files to the sql server or any other DB .
Pls help
Ram
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
