ok i'm new to this whole .net scene so i have been battling trying to find examples on how to add a newly created table into the database (connection)?
Here's what i have so far!
//create table
DataTable table = new DataTable("MyTable");
table.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
table.Columns.Add(new DataColumn("Age", Type.GetType("System.Byte")));
//add a row of data
DataRow dataRow = table.NewRow();
dataRow["Name"] = "John";
dataRow["Age"] = 30;
table.Rows.Add(dataRow);
//?????????????????????????
.
.
(Here's where i'm stuck)
How do I add my table above to the db connection below?
.
.
//?????????????????????????
//create database connection
OleDbConnection dbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + DBFULLNAME);
------------------------------------------------------
sorry i know their must be an example somewhere but have been searching for hours with no luck at all!
thanks for any help.
13 6480
If you are asking how to get data into SQL Server that is held in a DataTable the answer would be associate an Adapter or Command to DataTable and Update.
If you are asking how to add a physical Table to a SQL Server Database you would use SMO\SQLDMO or straight TSQL, not ADO. You could also use an OleDb connection to define a new table but it would not have any data in it.
If you are asking how to get data into SQL Server that is held in a DataTable the answer would be associate an Adapter or Command to DataTable and Update.
If you are asking how to add a physical Table to a SQL Server Database you would use SMO\SQLDMO or straight TSQL, not ADO. You could also use an OleDb connection to define a new table but it would not have any data in it.
Well if my above example didn't show you where i'm stuck, then i'm at a loss?
All i'm after is the missing link between my DataSet and DBConnection! I've already created the database i just need to insert the damn table into it.
I looked into the DataAdapter which will allow me to supply a DataSet that the DataTable i'm using contains, but that still doesn't bring me any closer to inserting the table?
any help would be appreciated.
ok it appears no one understands my problem? i'll try to explain further.
1) i created a new empty database in my project folder, using SQLConfigDataSource (win32 api)
2) now i'd like to insert a new table using my example above in my first post, which uses "DataSet", "DataTable", "DataColumn" and "DataRow"
I can achieve that using the following code: (but i'd much rather learn how to use the above code)
---------------------
OleDbConnection dbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\mydb.mdb");
dbConn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = dbConn;
// create table
cmd.CommandText = "CREATE TABLE mytable (Name VARCHAR(25), Age INTEGER)";
cmd.ExecuteNonQuery();
//add a row of data
cmd.CommandText = "INSERT INTO mytable VALUES ('John', 30)";
cmd.ExecuteNonQuery();
dbConn.Close();
---------------------
but what's the use of having all those previous classes if i can't insert the final table into the database????
i'm sure there's a way and it's probably just a couple of lines of code but i just haven't found the right class to do it, and i'm sick of google!, if anyone could help i would really apreciate it.
You created the database. Great.
The question was, does the table exist in the database?
The use of those precious classes is immense, and once you get the hang of it, they're great.
You are creating a DataTable object and filling it with data.
As stated, if the table already exists in the database (will make it much easier) you can associate a simple SQL insert statement for it.
If the table does NOT exist in the database, and you want to create it at runtime, there is a lot of work to do.
You need to check to see if it's there. DROP (sql command) the table, CREATE TABLE (sql command) and THEN you can add data to it.
However, if you just want to create the table beforehand, go into your database software and create the table in there. Then you can use the simple INSERT (sql command) with your DataTable object as mentioned above.
You created the database. Great.
The question was, does the table exist in the database?
NO, i already stated it's a new EMPTY database.
The use of those precious classes is immense, and once you get the hang of it, they're great.
well that's what i'm trying to achieve here, and without much success so far.
You are creating a DataTable object and filling it with data.
As stated, if the table already exists in the database (will make it much easier) you can associate a simple SQL insert statement for it.
i already said i'm creating it at runtime using the following:
[DllImport("ODBCCP32.dll")]
private static extern bool SQLConfigDataSource(IntPtr hwndParent, ODBC fRequest, string lpszDriver, string lpszAttributes);
If the table does NOT exist in the database, and you want to create it at runtime, there is a lot of work to do.
You need to check to see if it's there. DROP (sql command) the table, CREATE TABLE (sql command) and THEN you can add data to it.
again, this is not what i'm asking - i can check that myself (thanks for your concern)
However, if you just want to create the table beforehand, go into your database software and create the table in there. Then you can use the simple INSERT (sql command) with your DataTable object as mentioned above.
everyone seems to keep dodging my question? i could understand if this was top-secret, but where talking about inserting a silly populated DataSet into a new empty (WITH NO TABLES) database!
it seems like there is no answer here (let alone an small example), anyway thanks again plater for your time but since i can't get a straight answer i think i'll just keep searching elsewhere (what a nightmare should of stuck with c++!!!).
I'm sure if you look on google long enough you will find snarky answers :-)
Snarky questions lead to snarky answers.
Gosh this thread got carried away after what I thought was an intial "cover either question" answer ! I had to look up "snarky", not a word I would associate with.
The simple answer I believe was provided at the outset but obviously not explained clearly. You cannot create a database table directly from an ADO DataTable or DataSet, that is not the purpose of these objects which are by definition for manipulating data not defining schema. If you wish to use managed code to create a database object, table or otherwise, without using any SQL statements in the code, the use either SQL Server Management Objects (SMO), Data Management Objects (DMO) or OleDb schema objects.
I should of course have given DMO its proper name SQLDMO, which stands for Distributed Management Objects or Database Management Objects. Anyway in .Net with SQL Server 2005 you have SMO so don't need SQLDMO.
In short the reason you can't find the answer you are looking for is the answer is "no", it makes sense to separate the classes that manipulate data from those that manipulate structure and that is what Microsoft have done.
(ignoring the post's above camel's)...
thank's camel for clearly explaining that i was on the wrong path even if i may of got a tad hot-headed on my last post, but that's the toll from searching long hours for a solution that never existed, btw i did finally figure that out towards the end but won't take any credit from you! thanks again and sorry for all the confusion, just wish someone would have told me sooner but no one's to blame except my inexperience.
ps: hey camel i also had to search that word, all i can say is urbandictionary(dot)com has a nice page layout :-)
ok i'm new to this whole .net scene so i have been battling trying to find examples on how to add a newly created table into the database (connection)?
Here's what i have so far!
//create table
DataTable table = new DataTable("MyTable");
table.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
table.Columns.Add(new DataColumn("Age", Type.GetType("System.Byte")));
//add a row of data
DataRow dataRow = table.NewRow();
dataRow["Name"] = "John";
dataRow["Age"] = 30;
table.Rows.Add(dataRow);
//?????????????????????????
.
.
(Here's where i'm stuck)
How do I add my table above to the db connection below?
.
.
//?????????????????????????
//create database connection
OleDbConnection dbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + DBFULLNAME);
------------------------------------------------------
sorry i know their must be an example somewhere but have been searching for hours with no luck at all!
thanks for any help.
u want that code in C# or vb
Well if my above example didn't show you where i'm stuck, then i'm at a loss?
All i'm after is the missing link between my DataSet and DBConnection! I've already created the database i just need to insert the damn table into it.
I looked into the DataAdapter which will allow me to supply a DataSet that the DataTable i'm using contains, but that still doesn't bring me any closer to inserting the table?
any help would be appreciated.
-
public partial class _Default : System.Web.UI.Page
-
{
-
string conn = "server=balaji;database=harish;uid=sa;pwd=sqlserver";
-
-
//this method for geting selectedindexvalues for dropdownlist through sqldatasource for
-
//displaying the gridview
-
protected void DDLnames_SelectedIndexChanged(object sender, EventArgs e)
-
{
-
SqlConnection scon = new SqlConnection(conn);
-
SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", scon);
-
DataSet ds = new DataSet();
-
sda.Fill(ds, "tbl_names");
-
GridView1.DataSource = ds;
-
GridView1.DataBind();
-
}
-
-
//normal function
-
public void FillCustomerInGrid()
-
{
-
SqlConnection sconn = new SqlConnection(conn);
-
// SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", sconn);
-
SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names", sconn);
-
DataSet ds = new DataSet();
-
sda.Fill(ds, "tbl_names");
-
GridView1.DataSource = ds.Tables["tbl_names"];
-
GridView1.DataBind();
-
}
-
//update function
-
public void updatCustomerInGrid()
-
{
-
SqlConnection sconn = new SqlConnection(conn);
-
// SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", sconn);
-
SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names", sconn);
-
DataSet ds = new DataSet();
-
sda.Fill(ds, "tbl_names");
-
GridView1.DataSource =ds.Tables["tbl_names"];
-
GridView1.DataBind();
-
Response.Write("<script>alert('updated')</script>");
-
-
-
}
-
//edit function
-
public void editCustomerInGrid()
-
{
-
SqlConnection sconn = new SqlConnection(conn);
-
SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", conn);
-
//SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names", sconn);
-
DataSet ds = new DataSet();
-
sda.Fill(ds, "tbl_names");
-
GridView1.DataSource = ds.Tables["tbl_names"];
-
GridView1.DataBind();
-
}
-
//cancle function
-
public void cancelCustomerInGrid()
-
{
-
//creation of conncetion object
-
SqlConnection sconn = new SqlConnection(conn);
-
SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names where stu_name='" + DDLnames.SelectedItem.Text.ToString() + "'", sconn);
-
DataSet ds = new DataSet();
-
sda.Fill(ds, "tbl_names");
-
GridView1.Rows[0].Cells.Clear();
-
//GridView1.DataSource = ds.Tables["tbl_names"];
-
GridView1.DataBind();
-
}
-
-
-
-
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
-
{
-
//to get editindex values
-
GridView1.EditIndex = e.NewEditIndex;
-
editCustomerInGrid();
-
}
-
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
-
{
-
//creation of textbox objects
-
TextBox txtnum = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtnum");
-
TextBox txtnames = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtnames");
-
TextBox txtbranch = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtbranch");
-
TextBox txtupdate = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtupdate");
-
if(txtnum=)
-
{
-
//creating upadting command
-
string upcommand = "UPDATE tbl_names SET stu_num=" + txtnum.Text + ",stu_name='" + txtnames.Text + "',stu_branch='" + txtbranch.Text + "'WHERE stu_num=" + txtnum.Text +"";
-
SqlConnection sconn = new SqlConnection(conn);
-
sconn.Open();
-
SqlCommand scmd = new SqlCommand(upcommand, sconn);
-
//give the which type of the command it is query or storedprocs
-
scmd.CommandType = CommandType.Text;
-
scmd.ExecuteNonQuery();
-
sconn.Close();
-
GridView1.EditIndex = -1;//index value
-
updatCustomerInGrid();
-
-
}
-
}
-
protected void Page_Load(object sender, EventArgs e)
-
{
-
//displaying the gridview
-
-
//SqlConnection sconn = new SqlConnection(conn);
-
//SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_names", sconn);
-
//DataSet ds = new DataSet();
-
//sda.Fill(ds, "tbl_names");
-
//GridView1.DataSource = ds.Tables["tbl_names"];
-
//GridView1.DataBind();
-
-
}
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Necqui Teja |
last post by:
Is there a quick and effcient way to insert records into SQL Server database
from a DataTable (DataTable dt;) when the DataTable and the SQL Server
database contain the same field names and...
|
by: Dazz |
last post by:
Hello
I have made a small app to query a database which works fine. I am now
adding a logging function which writes back to the database in a separate
table. When I update the OleDataAdapter,...
|
by: Ed |
last post by:
Hi,
I want to load data to a table in Sql Server from a
dataset table in my vb.net app using a dataAdapter. I
know how to do this as follows (my question is to see if I
can reduce the amount...
|
by: Scott Emick |
last post by:
I have the following datatables which are related:
Transaction
TransactionId-Sequence
Orders
OrderDetails
when I process a
daTransaction.update(dtTransaction)...
|
by: Marcel Hug |
last post by:
Hi all !
I have a table in my database, which has 3 attributes. IDFailureControl,
ControlDate and ControlVersion.
In the following function I test, if the date of today allready exists.
Then I...
|
by: Brad Baker |
last post by:
I'm trying to write a simple asp.net page which updates some data in a SQL
database. At the top of the page I have the following code:
<%@ Page Language="C#" Debug="true" %>
<%@ import...
|
by: John Cosmas |
last post by:
I have a DATATABLE which I have populated in my application, and I need it
written out to a particular table I specify in my ACCESS database. My code
works to the point of the MERGE and UPDATE,...
|
by: iKiLL |
last post by:
Hi all
I am having problems getting my SqlCeDataAdapter to Update the SQL Mobile
Data base.
i am using C# CF2.
I have tried this a number of different ways. Starting with the command
builder...
|
by: krajah |
last post by:
Need Help Here.......
I'm using visual C#:
How to insert the value that user have entered combobox and click button.The value will sent into database.This is in form1.
In form2,if click button...
|
by: Matt |
last post by:
So as you all know the great thing about ADO.NET is that I can take an
entire table from a database and dump it into an in memory datatable
using ADO.NET.
Well my question is that now that I...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |