Newbie DataTable question

Stupid question for the day,

Just playing around with rewriting my website in .Net, I want to add a
record to my database table, in the old ADO, it was simple you could
create a connection and recordset and then rs.AddNew etc... in .Net it
seems they recommend that you populate a DataTable then use the
NewRow method

Isnt this bringing back a entire copy of the table (in this case 40,000
users), if so isn't this very inefficient? I know you can run a
insert via a ExecuteNonQuery which is the way I have done it in the
past (usually passing parameters to stored procs)

But I would have thought you should be able to add a row (Datarow) in
this case to a table without returning the entire table when you
connect to it.

Probably being very stupid

heres my code

try
{
// Initializations
string database = "MyDB.mdb";
string connectionString =
@dotnet.itags.org."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Web\Database\" +
database;

//connection
OleDbConnection cnn = new OleDbConnection();
cnn.ConnectionString = connectionString;
cnn.Open();

OleDbCommand com = cnn.CreateCommand();
com.CommandText = "Select * from tblUser";

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = com;

//DataSet ds = new DataSet();
//da.Fill(ds, "Users");

DataTable dt = new DataTable();
da.Fill(dt);

//DataRow dr = dt.Rows[0];
DataRow dr = dt.NewRow();

dr["FirstName"] = FirstName.ToString();
dr["Surname"] = Surname.ToString();
dr["Email"] = Email.ToString();

dt.Rows.Add(dr);
da.Update(dt);

//close up

cnn.Close();
}
catch (OleDbException ee)
{
Console.WriteLine(ee.ToString());
}the notion of in-memory datasets, is you only keep a copy of what you need.
the premise is that normally you only need a subset of the data. a dataset
keeps track of the status of a row (new, modified,deleted,unchanged). then
the adapter can look at the row status to determine whether to insert,
delete, update, or do nothing.

in your case you are loading the dataset solely to get the structure. you
could use the folowwing statement.

com.CommandText = "Select * from tblUser where 1 = 0";

which would only return column info and no data. a better approach is to use
typed datasets. here you use a wizard to build a dataset class that initials
the table and column info. this removes the requirement to do a dummy
database select. also you get a dot notion for accessing column data.

-- bruce (sqlwork.com)

"Ben" <BenScott@.aemail4u.com> wrote in message
news:1151423636.132121.107680@.75g2000cwc.googlegro ups.com...
> Stupid question for the day,
>
> Just playing around with rewriting my website in .Net, I want to add a
> record to my database table, in the old ADO, it was simple you could
> create a connection and recordset and then rs.AddNew etc... in .Net it
> seems they recommend that you populate a DataTable then use the
> NewRow method
>
> Isnt this bringing back a entire copy of the table (in this case 40,000
> users), if so isn't this very inefficient? I know you can run a
> insert via a ExecuteNonQuery which is the way I have done it in the
> past (usually passing parameters to stored procs)
>
> But I would have thought you should be able to add a row (Datarow) in
> this case to a table without returning the entire table when you
> connect to it.
>
> Probably being very stupid
> heres my code
> try
> {
> // Initializations
> string database = "MyDB.mdb";
> string connectionString =
> @."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Web\Database\" +
> database;
> //connection
> OleDbConnection cnn = new OleDbConnection();
> cnn.ConnectionString = connectionString;
> cnn.Open();
> OleDbCommand com = cnn.CreateCommand();
> com.CommandText = "Select * from tblUser";
> OleDbDataAdapter da = new OleDbDataAdapter();
> da.SelectCommand = com;
> //DataSet ds = new DataSet();
> //da.Fill(ds, "Users");
> DataTable dt = new DataTable();
> da.Fill(dt);
> //DataRow dr = dt.Rows[0];
> DataRow dr = dt.NewRow();
> dr["FirstName"] = FirstName.ToString();
> dr["Surname"] = Surname.ToString();
> dr["Email"] = Email.ToString();
> dt.Rows.Add(dr);
> da.Update(dt);
> //close up
> cnn.Close();
> }
> catch (OleDbException ee)
> {
> Console.WriteLine(ee.ToString());
> }
thanks Bruce,

yeah as you where writing i had tried that, bit quicker, but then it
wont allow me to insert data

"Update requires a valid InsertCommand when passed DataRow collection
with new rows."

so i guess i need to change

da.SelectCommand = com;

to

da.InsertCommand = com;

that doesnt work :(. any more words of wisdom? the main reason i am
doing this is to avoid having to handle what data is being put into
each field when the users submit the data. creating a Insert string
seem to me the wrong way to do this.

Newbie DataTable question

Stupid question for the day,
Just playing around with rewriting my website in .Net, I want to add a
record to my database table, in the old ADO, it was simple you could
create a connection and recordset and then rs.AddNew etc... in .Net it
seems they recommend that you populate a DataTable then use the
NewRow method
Isnt this bringing back a entire copy of the table (in this case 40,000
users), if so isn't this very inefficient? I know you can run a
insert via a ExecuteNonQuery which is the way I have done it in the
past (usually passing parameters to stored procs)
But I would have thought you should be able to add a row (Datarow) in
this case to a table without returning the entire table when you
connect to it.
Probably being very stupid
heres my code
try
{
// Initializations
string database = "MyDB.mdb";
string connectionString =
@dotnet.itags.org."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Web\Database\" +
database;
//connection
OleDbConnection cnn = new OleDbConnection();
cnn.ConnectionString = connectionString;
cnn.Open();
OleDbCommand com = cnn.CreateCommand();
com.CommandText = "Select * from tblUser";
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = com;
//DataSet ds = new DataSet();
//da.Fill(ds, "Users");
DataTable dt = new DataTable();
da.Fill(dt);
//DataRow dr = dt.Rows[0];
DataRow dr = dt.NewRow();
dr["FirstName"] = FirstName.ToString();
dr["Surname"] = Surname.ToString();
dr["Email"] = Email.ToString();
dt.Rows.Add(dr);
da.Update(dt);
//close up
cnn.Close();
}
catch (OleDbException ee)
{
Console.WriteLine(ee.ToString());
}the notion of in-memory datasets, is you only keep a copy of what you need.
the premise is that normally you only need a subset of the data. a dataset
keeps track of the status of a row (new, modified,deleted,unchanged). then
the adapter can look at the row status to determine whether to insert,
delete, update, or do nothing.
in your case you are loading the dataset solely to get the structure. you
could use the folowwing statement.
com.CommandText = "Select * from tblUser where 1 = 0";
which would only return column info and no data. a better approach is to use
typed datasets. here you use a wizard to build a dataset class that initials
the table and column info. this removes the requirement to do a dummy
database select. also you get a dot notion for accessing column data.
-- bruce (sqlwork.com)
"Ben" <BenScott@.aemail4u.com> wrote in message
news:1151423636.132121.107680@.75g2000cwc.googlegroups.com...
> Stupid question for the day,
>
> Just playing around with rewriting my website in .Net, I want to add a
> record to my database table, in the old ADO, it was simple you could
> create a connection and recordset and then rs.AddNew etc... in .Net it
> seems they recommend that you populate a DataTable then use the
> NewRow method
>
> Isnt this bringing back a entire copy of the table (in this case 40,000
> users), if so isn't this very inefficient? I know you can run a
> insert via a ExecuteNonQuery which is the way I have done it in the
> past (usually passing parameters to stored procs)
>
> But I would have thought you should be able to add a row (Datarow) in
> this case to a table without returning the entire table when you
> connect to it.
>
> Probably being very stupid
> heres my code
> try
> {
> // Initializations
> string database = "MyDB.mdb";
> string connectionString =
> @."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Web\Database\" +
> database;
> //connection
> OleDbConnection cnn = new OleDbConnection();
> cnn.ConnectionString = connectionString;
> cnn.Open();
> OleDbCommand com = cnn.CreateCommand();
> com.CommandText = "Select * from tblUser";
> OleDbDataAdapter da = new OleDbDataAdapter();
> da.SelectCommand = com;
> //DataSet ds = new DataSet();
> //da.Fill(ds, "Users");
> DataTable dt = new DataTable();
> da.Fill(dt);
> //DataRow dr = dt.Rows[0];
> DataRow dr = dt.NewRow();
> dr["FirstName"] = FirstName.ToString();
> dr["Surname"] = Surname.ToString();
> dr["Email"] = Email.ToString();
> dt.Rows.Add(dr);
> da.Update(dt);
> //close up
> cnn.Close();
> }
> catch (OleDbException ee)
> {
> Console.WriteLine(ee.ToString());
> }
>
thanks Bruce,
yeah as you where writing i had tried that, bit quicker, but then it
wont allow me to insert data
"Update requires a valid InsertCommand when passed DataRow collection
with new rows."
so i guess i need to change
da.SelectCommand = com;
to
da.InsertCommand = com;
that doesnt work :(. any more words of wisdom? the main reason i am
doing this is to avoid having to handle what data is being put into
each field when the users submit the data. creating a Insert string
seem to me the wrong way to do this.

NEWBIE HELP

Hi All

I have a web form that uses an object class to populate a standard table (not datagrid etc)

I have a button on this form that when clicked opens another form for editing. In this form all the values of the object are used to populate text boxes (in the load event of the form there is a call to a DBHelper class which uses a SQL Parameter Array to execute a strored procedure (SQL) and return the data) and text areas which are free to edit in this form.

Also on this page I have a button called save changes which when clicked I am attempting to create a new paramater array to execute an update procedure again using the DBHelper Class.

Unfortunately when I try to do this the values in the new SQLParamater Array are exactly the same as before and are not reflecting the changes that I make by typing new entries into the text boxes on the form. As a newbie to .net I am lost here and realise that this is probably a scope or state problem but I would like someone to point me in the right direction please. When I hard wire the new SQLParamater Array the update does work so that eliminates the chances of the DBHelper Class from any fault.

Please help as I am going bonkers here!!!!!

Phill

You probably have your "populate form" bit in your Page Load function - which will execute when the save button causes a post back.

Surround your "populate the textboxes" code with:

if (!IsPostBack)
{

...

}

This will mean it only runs when the page is first loaded.

Have you looked if your sql update statement is passing the values as @.new_value

I would recommend doing this on a datagrid, it would be easier to update cuz the datagrid already has an update command field!

I cant use a datagrid as the recordset only ever returns one row with 17 fields. Datagrids need multirows to work dont they?

Datagrids work with "a number" of rows - that can be one row.

welshpunk:

I cant use a datagrid as the recordset only ever returns one row with 17 fields. Datagrids need multirows to work dont they?

Hi welshpunk,

Try to follow those codes as follows:

Working with a Single Table
http://msdn2.microsoft.com/en-us/library/aa719548(VS.71).aspx

Thanks.

Yes but you cannot turn a datagrid on its side. I have 17 fields in the recordset(just one row) Using a datagrid in this fashion is not user friendly as some of the fields are up to 2000 chars long. Datagrids will display like this

Column Heading Column Heading Column Heading Column Heading Column Heading

Data Data Data Data Data

I want to display the data in a more user friendly way like this

Column Header Data

Column Header Data

Column Header Data

Column Header Data

Column Header Data

Column Header Data

Column Header Data

With the 17 column headers on the left and their respective data adjacent to them. This is why I had to create the form in this way.

Hi,

You can write each cell every time by reading each cell once.

Hope this helps.

I have already marked as answer

Don't worry - you'll soon see that comment about marking posts as the answer absolutely everywhere!

You could try using a detailsview. The data would be displayed the way you want.

Newbie Membership question

I have a database that I want to basically place on the web. I have user information inside a table within this database. The database is an SQL Server DB. My question is how to use the membership and role features when a db with user info is already created. Are there any good examples of this out there? Thanks.

First off: there is a whole "Security" forum dedicated to membership and role talk

Secondly: what you are going to be after is a "Customer Membership Provider"

I also have a list of good security resources on ASP.NET 2.0 here:http://weblogs.asp.net/scottgu/archive/2006/02/24/438953.aspx

It includes a nice video that walksthrough how to setup a login system.

Hope this helps,

Scott

NEWBIE NEEDS HELP - pull value from sql table

I have searched this forum and can't find the answer to my question. I have a login page that validates username/password. I am validating that the username is in the sql table, but I would also like to return the rest of the information for that user to pass in a querystring. For example, the user ID . I have setup my sql string to select username, password, Name, and email. How do I pull the id from this dataset?

userDataset = myUserVAlidation(enterUsername, enterPassword)
If userDataset.tables(0).rows.count = 1 then
 'found record in database table
ENd if
Function myUserValidation(ByVal username as string, byVal password as string)
Dim connectionString as ...
Dim sqlConn as system.data.sqlclient.sqlconnection = new system.data.sqlClient.sqlconnection(connectionSTring)
Dim sqlString as string = "Select [userTable][username], [userTable][password], [userTable][fullName], [userTable][id] where (([userTable].[username] = @dotnet.itags.org.username) and ([userTable].[password] = @dotnet.itags.org.password))"
Dim sqlCommand as system.data.sqlclient.sqlcommand = new system.data.sqlclient.sqlCommand(sqlString, sqlConn)
sqlCommand.parameters.add("@dotnet.itags.org.username", system.data.sqldbtype.varchar).value = username
sqlCommand.parameters.add("@dotnet.itags.org.password", system.data.sqldbtype.varchar).value = password
dim dataAdapter as system.data.sqlclient.sqldataadapter = new system.data.sqlclient.sqldataadapter(sqlCommand)
dim dataset as system.data.dataset = new system.data.dataset
dataadapter.fill(dataset)
return dataset
end function

To get the id you could use the following:

string id = userDataset.Tables[0].Rows[0].ItemArray.GetValue(3)

The index for GetValue = 3 because id is the 4th column returned from your sql statement.