By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,715 Members | 1,773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,715 IT Pros & Developers. It's quick & easy.

DataTable before DataAdapter possible?

P: n/a
Hello all,

I have a DataTable which I am building column by column and adding rows
after each new column. The DataTable columns match the columns in my
database table. I'm building the DataTable first and I then want to roll
through the DataTable while in memory checking for errors and then commit
the rows to my database table (btw this is in ASP.NET). Is it possible to
have data in a datable before attaching at DataAdapter? I'm a little new to
this and know I need a SqlCommandBuilder, so some code example would be very
helpful. Below is what I know about using the DataAdapter/CommandBuilder,
but I don't need to SELECT anything because the DataTable already had new
rows (that why I put Id = 0 to get no data).

dim dt as New DataTable
dim da as DataAdapter
dim sqlCmdBuild as SqlCommandBuilder

da = New SqlDataAdapter("SELECT * FROM Table WHERE Id = 0", OpenDbConn)
da..Fill(dt)

sqlCmdBuild = New SqlCommandBuilder(da)

TIA,

-Randy
Jul 21 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"randy" <ra***@someplace.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...
Hello all,

I have a DataTable which I am building column by column and adding rows
after each new column. The DataTable columns match the columns in my
database table. I'm building the DataTable first and I then want to roll
through the DataTable while in memory checking for errors and then commit
the rows to my database table (btw this is in ASP.NET). Is it possible to
have data in a datable before attaching at DataAdapter?
Yes, and you never technically attach a datatable to an adapter. An adapter
could care less what you send to it, it only cares in that it matches the
commands that you try to have it send the db.
I'm a little new to this and know I need a SqlCommandBuilder,
Not so, and in all likelihood, avoid the command builder. Check out Bill
Vaughn's article Weaning Developer's from the commandBUilder at
www.betav.com ->Articles ->MSDN
so some code example would be very helpful. Below is what I know about using the DataAdapter/CommandBuilder,
but I don't need to SELECT anything because the DataTable already had new
rows (that why I put Id = 0 to get no data).
If you use the command builder, you should be ok, but that query will
probably cause drama. The commanduilder infers update/insert/delete logic
based on athe Select Command so that's all it needs. You don't need to
write over your exisitn gdata. You also probably want to use Parameterized
queries... Where ID = @SomeValue"

then do mySelectCommand.Parameters.Add("@SomeValue", SqlDbType.Whatever,
someLength).Value = 0

Run through the dataadapter configuration wizard at least once and check out
the code it generates for you. It will have parameters and column mappings
as well. While I don't recommend using it too much b/c it becomes a crutch,
it's a superb learning too.

Also, you don't have to call Select First. You don't even have to call
update on a datatable that you called fill on, or even one whose data came
from the db. The adapter doesn't care. I know this may seem like a lot so
you may want to check out Bill's article, play with the wizard, and just get
a valid Update command. Then do what you are now, and just call update on
your datatable. Let me know if you have any problems, I'll do my best to
get you through them.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

dim dt as New DataTable
dim da as DataAdapter
dim sqlCmdBuild as SqlCommandBuilder

da = New SqlDataAdapter("SELECT * FROM Table WHERE Id = 0", OpenDbConn)
da..Fill(dt)

sqlCmdBuild = New SqlCommandBuilder(da)

TIA,

-Randy

Jul 21 '05 #2

P: n/a
Thanks for you quick response. I went though the configuration wizdard and
looked over the example from betav.com. I mostly understand the code, but
I'm not making a connection between what I want/need to do and the example.

Simple example of what I'm doing. First I build my datatable and then add a
row to be inserted...

Dim dt as new datatable
Dim datCol as DataColumn
Dim DatRow as DataRow

datCol = new DataColumn("FName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datCol = new DataColumn("LName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datRow = dt.NewRow
datRow.Item("FName") = "Joe"
datRow.Item("LName") = "Smith"
dt.Rows.Add(datRow)

This is only one row, but I could have 500+ new rows. The "FName" and
"LName" are the same in my database table. Are you saying that I need to
loop though the rows of the datatable and assign them Paramerters? Could
please add some code below so I can better understand what I need next?


"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

"randy" <ra***@someplace.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...
Hello all,

I have a DataTable which I am building column by column and adding rows
after each new column. The DataTable columns match the columns in my
database table. I'm building the DataTable first and I then want to roll through the DataTable while in memory checking for errors and then commit the rows to my database table (btw this is in ASP.NET). Is it possible to have data in a datable before attaching at DataAdapter?
Yes, and you never technically attach a datatable to an adapter. An

adapter could care less what you send to it, it only cares in that it matches the
commands that you try to have it send the db.
I'm a little new to
this and know I need a SqlCommandBuilder,
Not so, and in all likelihood, avoid the command builder. Check out Bill
Vaughn's article Weaning Developer's from the commandBUilder at
www.betav.com ->Articles ->MSDN
so some code example would be very
helpful. Below is what I know about using the DataAdapter/CommandBuilder, but I don't need to SELECT anything because the DataTable already had new rows (that why I put Id = 0 to get no data).


If you use the command builder, you should be ok, but that query will
probably cause drama. The commanduilder infers update/insert/delete logic
based on athe Select Command so that's all it needs. You don't need to
write over your exisitn gdata. You also probably want to use

Parameterized queries... Where ID = @SomeValue"

then do mySelectCommand.Parameters.Add("@SomeValue", SqlDbType.Whatever,
someLength).Value = 0

Run through the dataadapter configuration wizard at least once and check out the code it generates for you. It will have parameters and column mappings as well. While I don't recommend using it too much b/c it becomes a crutch, it's a superb learning too.

Also, you don't have to call Select First. You don't even have to call
update on a datatable that you called fill on, or even one whose data came
from the db. The adapter doesn't care. I know this may seem like a lot so you may want to check out Bill's article, play with the wizard, and just get a valid Update command. Then do what you are now, and just call update on
your datatable. Let me know if you have any problems, I'll do my best to
get you through them.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

dim dt as New DataTable
dim da as DataAdapter
dim sqlCmdBuild as SqlCommandBuilder

da = New SqlDataAdapter("SELECT * FROM Table WHERE Id = 0", OpenDbConn)
da..Fill(dt)

sqlCmdBuild = New SqlCommandBuilder(da)

TIA,

-Randy


Jul 21 '05 #3

P: n/a
If the rowstate of the rows isn't added/deleted etc,the call to your update
statement is never made. If set up correctly, the adapter will walk through
the table for you and handle all of this, automatically assigning the
parameters in your quereis to the values in the datatable.

If you are building it manually like below, you just need a valid Insert
command but that command must map to the datatable in order for you to call
..Update and have it do anything.
"randy" <rs****@portfoliorecovery.nospam.com> wrote in message
news:ef**************@TK2MSFTNGP12.phx.gbl...
Thanks for you quick response. I went though the configuration wizdard and looked over the example from betav.com. I mostly understand the code, but
I'm not making a connection between what I want/need to do and the example.
Simple example of what I'm doing. First I build my datatable and then add a row to be inserted...

Dim dt as new datatable
Dim datCol as DataColumn
Dim DatRow as DataRow

datCol = new DataColumn("FName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datCol = new DataColumn("LName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datRow = dt.NewRow
datRow.Item("FName") = "Joe"
datRow.Item("LName") = "Smith"
dt.Rows.Add(datRow)

This is only one row, but I could have 500+ new rows. The "FName" and
"LName" are the same in my database table. Are you saying that I need to
loop though the rows of the datatable and assign them Paramerters? Could
please add some code below so I can better understand what I need next?


"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

"randy" <ra***@someplace.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...
Hello all,

I have a DataTable which I am building column by column and adding rows after each new column. The DataTable columns match the columns in my
database table. I'm building the DataTable first and I then want to roll through the DataTable while in memory checking for errors and then commit the rows to my database table (btw this is in ASP.NET). Is it possible
to
have data in a datable before attaching at DataAdapter?


Yes, and you never technically attach a datatable to an adapter. An

adapter
could care less what you send to it, it only cares in that it matches the
commands that you try to have it send the db.
I'm a little new to
this and know I need a SqlCommandBuilder,


Not so, and in all likelihood, avoid the command builder. Check out Bill Vaughn's article Weaning Developer's from the commandBUilder at
www.betav.com ->Articles ->MSDN
so some code example would be very
helpful. Below is what I know about using the DataAdapter/CommandBuilder, but I don't need to SELECT anything because the DataTable already had new rows (that why I put Id = 0 to get no data).


If you use the command builder, you should be ok, but that query will
probably cause drama. The commanduilder infers update/insert/delete

logic based on athe Select Command so that's all it needs. You don't need to
write over your exisitn gdata. You also probably want to use

Parameterized
queries... Where ID = @SomeValue"

then do mySelectCommand.Parameters.Add("@SomeValue", SqlDbType.Whatever,
someLength).Value = 0

Run through the dataadapter configuration wizard at least once and check

out
the code it generates for you. It will have parameters and column

mappings
as well. While I don't recommend using it too much b/c it becomes a

crutch,
it's a superb learning too.

Also, you don't have to call Select First. You don't even have to call
update on a datatable that you called fill on, or even one whose data came from the db. The adapter doesn't care. I know this may seem like a lot

so
you may want to check out Bill's article, play with the wizard, and just

get
a valid Update command. Then do what you are now, and just call update on your datatable. Let me know if you have any problems, I'll do my best to get you through them.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

dim dt as New DataTable
dim da as DataAdapter
dim sqlCmdBuild as SqlCommandBuilder

da = New SqlDataAdapter("SELECT * FROM Table WHERE Id = 0",

OpenDbConn) da..Fill(dt)

sqlCmdBuild = New SqlCommandBuilder(da)

TIA,

-Randy



Jul 21 '05 #4

P: n/a
Could you PLEASE supply a little code for my example???

Thanks,

-Randy

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:ei*************@TK2MSFTNGP09.phx.gbl...
If the rowstate of the rows isn't added/deleted etc,the call to your update statement is never made. If set up correctly, the adapter will walk through the table for you and handle all of this, automatically assigning the
parameters in your quereis to the values in the datatable.

If you are building it manually like below, you just need a valid Insert
command but that command must map to the datatable in order for you to call .Update and have it do anything.
"randy" <rs****@portfoliorecovery.nospam.com> wrote in message
news:ef**************@TK2MSFTNGP12.phx.gbl...
Thanks for you quick response. I went though the configuration wizdard and
looked over the example from betav.com. I mostly understand the code, but
I'm not making a connection between what I want/need to do and the

example.

Simple example of what I'm doing. First I build my datatable and then add a
row to be inserted...

Dim dt as new datatable
Dim datCol as DataColumn
Dim DatRow as DataRow

datCol = new DataColumn("FName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datCol = new DataColumn("LName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datRow = dt.NewRow
datRow.Item("FName") = "Joe"
datRow.Item("LName") = "Smith"
dt.Rows.Add(datRow)

This is only one row, but I could have 500+ new rows. The "FName" and
"LName" are the same in my database table. Are you saying that I need
to loop though the rows of the datatable and assign them Paramerters? Could please add some code below so I can better understand what I need next?


"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

"randy" <ra***@someplace.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...
> Hello all,
>
> I have a DataTable which I am building column by column and adding rows > after each new column. The DataTable columns match the columns in my > database table. I'm building the DataTable first and I then want to

roll
> through the DataTable while in memory checking for errors and then

commit
> the rows to my database table (btw this is in ASP.NET). Is it possible
to
> have data in a datable before attaching at DataAdapter?

Yes, and you never technically attach a datatable to an adapter. An

adapter
could care less what you send to it, it only cares in that it matches

the commands that you try to have it send the db.
I'm a little new to
> this and know I need a SqlCommandBuilder,

Not so, and in all likelihood, avoid the command builder. Check out Bill Vaughn's article Weaning Developer's from the commandBUilder at
www.betav.com ->Articles ->MSDN
so some code example would be very
> helpful. Below is what I know about using the

DataAdapter/CommandBuilder,
> but I don't need to SELECT anything because the DataTable already had new
> rows (that why I put Id = 0 to get no data).

If you use the command builder, you should be ok, but that query will
probably cause drama. The commanduilder infers update/insert/delete logic based on athe Select Command so that's all it needs. You don't need
to write over your exisitn gdata. You also probably want to use

Parameterized
queries... Where ID = @SomeValue"

then do mySelectCommand.Parameters.Add("@SomeValue", SqlDbType.Whatever, someLength).Value = 0

Run through the dataadapter configuration wizard at least once and check
out
the code it generates for you. It will have parameters and column

mappings
as well. While I don't recommend using it too much b/c it becomes a

crutch,
it's a superb learning too.

Also, you don't have to call Select First. You don't even have to
call update on a datatable that you called fill on, or even one whose data

came from the db. The adapter doesn't care. I know this may seem like a
lot so
you may want to check out Bill's article, play with the wizard, and
just get
a valid Update command. Then do what you are now, and just call

update on your datatable. Let me know if you have any problems, I'll do my best to get you through them.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

>
> dim dt as New DataTable
> dim da as DataAdapter
> dim sqlCmdBuild as SqlCommandBuilder
>
> da = New SqlDataAdapter("SELECT * FROM Table WHERE Id = 0", OpenDbConn) > da..Fill(dt)
>
> sqlCmdBuild = New SqlCommandBuilder(da)
>
> TIA,
>
> -Randy
>
>



Jul 21 '05 #5

P: n/a
INSERT INTO Employees
( LName, FName)
VALUES (@LName, @FName)
This will be the CommandText for the insert command. Add an update command
to dataAdapter1 for instance and use this text.

At the end of your code where you are adding your row (dt.Rows.Add(datRow);

call dataAdapter1.Update(dt)

I've just did this making the mods however I configured my dataadapter using
the wizard. Nonetlesss this is pretty much it (I used emplnum as a key but
you can just remove it)
Me.SqlInsertCommand2.CommandText = "INSERT INTO Employees(EmplNum, LastName,
FirstName) VALUES (@EmplNum, @LastName, " & _

"@FirstName); SELECT EmplNum, LastName, FirstName FROM Employees WHERE
(EmplNum =" & _

" @EmplNum)" THE SELECT STATEMENT CAN BE REMOVED TOO, IT'S ONLY USED TO
REFRESH THE DATASET

Me.SqlInsertCommand2.Connection = Me.cn

Me.SqlInsertCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@EmplNum", System.Data.SqlDbType.Int, 4,
"EmplNum")) 'REMOVE THIS LINE

Me.SqlInsertCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastName",
System.Data.SqlDbType.NVarChar, 20, "LastName"))

Me.SqlInsertCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FirstName",
System.Data.SqlDbType.NVarChar, 20, "FirstName"))

'
"randy" <rs****@portfoliorecovery.nospam.com> wrote
in message news:#v**************@tk2msftngp13.phx.gbl...
Could you PLEASE supply a little code for my example???

Thanks,

-Randy

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:ei*************@TK2MSFTNGP09.phx.gbl...
If the rowstate of the rows isn't added/deleted etc,the call to your

update
statement is never made. If set up correctly, the adapter will walk

through
the table for you and handle all of this, automatically assigning the
parameters in your quereis to the values in the datatable.

If you are building it manually like below, you just need a valid Insert
command but that command must map to the datatable in order for you to

call
.Update and have it do anything.
"randy" <rs****@portfoliorecovery.nospam.com> wrote in message
news:ef**************@TK2MSFTNGP12.phx.gbl...
Thanks for you quick response. I went though the configuration wizdard
and
looked over the example from betav.com. I mostly understand the code, but I'm not making a connection between what I want/need to do and the

example.

Simple example of what I'm doing. First I build my datatable and then add
a
row to be inserted...

Dim dt as new datatable
Dim datCol as DataColumn
Dim DatRow as DataRow

datCol = new DataColumn("FName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datCol = new DataColumn("LName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datRow = dt.NewRow
datRow.Item("FName") = "Joe"
datRow.Item("LName") = "Smith"
dt.Rows.Add(datRow)

This is only one row, but I could have 500+ new rows. The "FName" and
"LName" are the same in my database table. Are you saying that I need to loop though the rows of the datatable and assign them Paramerters? Could please add some code below so I can better understand what I need
next?



"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
>
> "randy" <ra***@someplace.com> wrote in message
> news:uJ**************@TK2MSFTNGP09.phx.gbl...
> > Hello all,
> >
> > I have a DataTable which I am building column by column and adding

rows
> > after each new column. The DataTable columns match the columns in

my > > database table. I'm building the DataTable first and I then want to roll
> > through the DataTable while in memory checking for errors and then
commit
> > the rows to my database table (btw this is in ASP.NET). Is it

possible
to
> > have data in a datable before attaching at DataAdapter?
>
> Yes, and you never technically attach a datatable to an adapter. An
adapter
> could care less what you send to it, it only cares in that it matches the
> commands that you try to have it send the db.
> I'm a little new to
> > this and know I need a SqlCommandBuilder,
>
> Not so, and in all likelihood, avoid the command builder. Check out

Bill
> Vaughn's article Weaning Developer's from the commandBUilder at
> www.betav.com ->Articles ->MSDN
> so some code example would be very
> > helpful. Below is what I know about using the
DataAdapter/CommandBuilder,
> > but I don't need to SELECT anything because the DataTable already had new
> > rows (that why I put Id = 0 to get no data).
>
> If you use the command builder, you should be ok, but that query
will > probably cause drama. The commanduilder infers update/insert/delete

logic
> based on athe Select Command so that's all it needs. You don't need to > write over your exisitn gdata. You also probably want to use
Parameterized
> queries... Where ID = @SomeValue"
>
> then do mySelectCommand.Parameters.Add("@SomeValue", SqlDbType.Whatever, > someLength).Value = 0
>
> Run through the dataadapter configuration wizard at least once and check out
> the code it generates for you. It will have parameters and column
mappings
> as well. While I don't recommend using it too much b/c it becomes a
crutch,
> it's a superb learning too.
>
> Also, you don't have to call Select First. You don't even have to call > update on a datatable that you called fill on, or even one whose
data came
> from the db. The adapter doesn't care. I know this may seem like a lot so
> you may want to check out Bill's article, play with the wizard, and just get
> a valid Update command. Then do what you are now, and just call

update
on
> your datatable. Let me know if you have any problems, I'll do my

best to
> get you through them.
>
>
>
> HTH,
>
> Bill
>
> www.devbuzz.com
> www.knowdotnet.com
>
> >
> > dim dt as New DataTable
> > dim da as DataAdapter
> > dim sqlCmdBuild as SqlCommandBuilder
> >
> > da = New SqlDataAdapter("SELECT * FROM Table WHERE Id = 0",

OpenDbConn)
> > da..Fill(dt)
> >
> > sqlCmdBuild = New SqlCommandBuilder(da)
> >
> > TIA,
> >
> > -Randy
> >
> >
>
>



Jul 21 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.