shadstrife

hi all,
sorry if i'm posting in the wrong forums(but this seems to be the closest one to my qn)

ok...to makes things short...how do i create a table using sql commands in the backend code when say i click a button ..please help..

thanks


Re: Windows Forms Data Controls and Databinding need help with table creation

Derek Smyth

Hi,

Wrote this for a similar question in another post. Builds the table and fills it.

Code Snippet

Dim data As New DataTable
Dim conn As New SqlConnection("Data Source=Server;Initial Catalog=Northwind;Integrated Security=True")
Dim sqlSelectCmnd As New SqlCommand("SELECT * FROM Categories", conn)
Using conn
conn.Open()
Dim sqlReadr As SqlDataReader = sqlSelectCmnd.ExecuteReader(CommandBehavior.KeyInfo)
Dim schema As DataTable = sqlReadr.GetSchemaTable()

For Each row As DataRow In schema.Rows
Dim column As New DataColumn(row("BaseColumnName"), row("DataType"))
data.Columns.Add(column)
Next
data.Load(sqlReadr, LoadOption.OverwriteChanges)

sqlReadr.Close()
conn.Close()
End Using






Re: Windows Forms Data Controls and Databinding need help with table creation

shadstrife

wow thx for the fast reply! but err...sorry i forgot to mention this..but i'm coding in C#...would u mind if u can translate that into c#



Re: Windows Forms Data Controls and Databinding need help with table creation

Derek Smyth

Hi,

There is very little difference, I haven't ran the code below but it looks ok.

Code Snippet

DataTable data = New DataTable();
SqlConnection conn = New SqlConnection("");
SqlCommand sqlSelectCmnd = New SqlCommand("SELECT * FROM Categories", conn);
conn.Open();
SqlDataReader sqlReadr = sqlSelectCmnd.ExecuteReader(CommandBehavior.KeyInfo);
DataTable schema = sqlReadr.GetSchemaTable();

foreach (DataRow row in schema.Rows)
{
DataColumn column = new DataColumn(row("BaseColumnName"), row("DataType"));
data.Columns.Add(column);
}
data.Load(sqlReadr, LoadOption.OverwriteChanges);

sqlReadr.Close();
conn.Close();






Re: Windows Forms Data Controls and Databinding need help with table creation

BonnieB

This is a lot easier, use a DataSet and DataAdapter:

Code Snippet

SqlDataAdapter da = new SqlDataAdapter("select * from personnel", "MyConnectionString" );

DataSet ds = new DataSet();

da.Fill(ds, "MyTable");






Re: Windows Forms Data Controls and Databinding need help with table creation

shadstrife

err...sorry guys..i think i didn't make myself clear...the table i want to create is a database table...Sad

like...

MySqlCommand tempcreateCommand;

tempcreateCommand.CommandText = "create TEMPORARY TABLE userpw ( `Password` text)";

something like that...





Re: Windows Forms Data Controls and Databinding need help with table creation

BonnieB

Sorry about the misunderstanding. Sure you can create a database table:

Code Snippet

SqlCommand sc = new SqlCommand("create table xyz (MyField varchar(5))", new SqlConnection(("MyConnectionString" ));

sc.Connection.Open();

sc.ExecuteNonQuery();

sc.Connection.Close();

But, my question to you is: What exactly do you want to accomplish I notice you used TEMPORARY TABLE in your example. I don't think you'll be able to create a temporary table ... in SQL, that's accomplished by CREATE TABLE #xyz ... at least it didn't work when I tried it. Do you NEED a temporary table You could just create a normal table and then drop it when you're done with. But, again, I guess I'm just wondering what you're trying to do, because there's usually several ways to solve a problem.