sqlDataSource parameters question

[djc]

I am playing with the (new to me) sqlDataSource class. I used it to bind
data from a database table into a listbox with no code, worked great. Now I
need to do an INSERT for every item in a listbox. I used to just code the
ado stuff myself in .net 1.1. For example creating connection object,
creating sqlCommand object, parameters, etc... I would just create the ado
stuff, then loop through the listbox changing the sqlcommand parameter
values and execute the command (ExecuteNonQuery) each time through the loop.
Easy enough. Now I just went to try the same technique using the
sqlDataSource and it seems I can't.

For example I thought I could just do something like:

foreach (ListItem li in lstKeyPhrases.Items)
{
SqlDataSource1.InsertParameters["FK_MainID"] =
(int)Session["theAutoID"];
SqlDataSource1.InsertParameters["Keyphrase"] = li.Value;
SqlDataSource1.Insert();
}

I was thinking this would open connection do all the ado.net stuff and close
the connection just like the simple filling of a listbox scenario. This
won't compile. It seems you are not able to modify parameter values on the
fly? So are you not supposed to use an sqlDataSource for this scenario?
just do it the way I did before (net 1.x)?

[Andrey Korneyev]

You should assign insert command parameter in an OnInserting event handler,
like it's described in

http://msdn2.microsoft.com/en-us/lib...rtcommand.aspx
http://msdn2.microsoft.com/en-us/lib...inserting.aspx

HTH,
Andrey.

"djc" <no***@nowhere.comwrote in message
news:e$**************@TK2MSFTNGP03.phx.gbl...

>I am playing with the (new to me) sqlDataSource class. I used it to bind
data from a database table into a listbox with no code, worked great. Now I
need to do an INSERT for every item in a listbox. I used to just code the
ado stuff myself in .net 1.1. For example creating connection object,
creating sqlCommand object, parameters, etc... I would just create the ado
stuff, then loop through the listbox changing the sqlcommand parameter
values and execute the command (ExecuteNonQuery) each time through the
loop. Easy enough. Now I just went to try the same technique using the
sqlDataSource and it seems I can't.

For example I thought I could just do something like:

foreach (ListItem li in lstKeyPhrases.Items)
{
SqlDataSource1.InsertParameters["FK_MainID"] =
(int)Session["theAutoID"];
SqlDataSource1.InsertParameters["Keyphrase"] = li.Value;
SqlDataSource1.Insert();
}

I was thinking this would open connection do all the ado.net stuff and
close the connection just like the simple filling of a listbox scenario.
This won't compile. It seems you are not able to modify parameter values
on the fly? So are you not supposed to use an sqlDataSource for this
scenario? just do it the way I did before (net 1.x)?

[djc]

thanks Andrey. I read through it. I'm thinking I should just do it the way I
always have for this scenario... seems much more straight forward. I could
be misunderstanding here but it looks like using the OnInserting event
handler in the example from the article is used to add a new parameter where
I am looking to just modify values of existing parameters. I'm sure
something could be written to do what I need using the event but it seems
like overkill for such a simple task.

I found a different post that asks my question better than I did so I am
going to repost. I will also include the 'accepted answer' from this post
(from experts exchange) which is one way I was considering. checkout the new
post

Your input is appreciated. Thanks.

"Andrey Korneyev" <ak*******@nyc.rr.comwrote in message
news:xtx4h.6041$Tz.1346@trndny01...

You should assign insert command parameter in an OnInserting event
handler, like it's described in

http://msdn2.microsoft.com/en-us/lib...rtcommand.aspx
http://msdn2.microsoft.com/en-us/lib...inserting.aspx

HTH,
Andrey.

"djc" <no***@nowhere.comwrote in message
news:e$**************@TK2MSFTNGP03.phx.gbl...

>>I am playing with the (new to me) sqlDataSource class. I used it to bind
data from a database table into a listbox with no code, worked great. Now
I need to do an INSERT for every item in a listbox. I used to just code
the ado stuff myself in .net 1.1. For example creating connection object,
creating sqlCommand object, parameters, etc... I would just create the ado
stuff, then loop through the listbox changing the sqlcommand parameter
values and execute the command (ExecuteNonQuery) each time through the
loop. Easy enough. Now I just went to try the same technique using the
sqlDataSource and it seems I can't.

For example I thought I could just do something like:

foreach (ListItem li in lstKeyPhrases.Items)
{
SqlDataSource1.InsertParameters["FK_MainID"] =
(int)Session["theAutoID"];
SqlDataSource1.InsertParameters["Keyphrase"] = li.Value;
SqlDataSource1.Insert();
}

I was thinking this would open connection do all the ado.net stuff and
close the connection just like the simple filling of a listbox scenario.
This won't compile. It seems you are not able to modify parameter values
on the fly? So are you not supposed to use an sqlDataSource for this
scenario? just do it the way I did before (net 1.x)?

[djc]

whoops... Sorry Andrey, I just realized you provided 2 links. I had only
read the first one. The second link cleary states to use this event to
'modify parameter values.' Thank you.

I am still going to post a second thread that will be slightly different. I
want to get some input on an alternative I came across that doesn't really
seem right to me (but is really really simple) and I have a follow up
question specific to my scenario.

check out the new post. Your input is very much appreciated.
thanks.

"Andrey Korneyev" <ak*******@nyc.rr.comwrote in message
news:xtx4h.6041$Tz.1346@trndny01...

You should assign insert command parameter in an OnInserting event
handler, like it's described in

http://msdn2.microsoft.com/en-us/lib...rtcommand.aspx
http://msdn2.microsoft.com/en-us/lib...inserting.aspx

HTH,
Andrey.

"djc" <no***@nowhere.comwrote in message
news:e$**************@TK2MSFTNGP03.phx.gbl...

>>I am playing with the (new to me) sqlDataSource class. I used it to bind
data from a database table into a listbox with no code, worked great. Now
I need to do an INSERT for every item in a listbox. I used to just code
the ado stuff myself in .net 1.1. For example creating connection object,
creating sqlCommand object, parameters, etc... I would just create the ado
stuff, then loop through the listbox changing the sqlcommand parameter
values and execute the command (ExecuteNonQuery) each time through the
loop. Easy enough. Now I just went to try the same technique using the
sqlDataSource and it seems I can't.

For example I thought I could just do something like:

foreach (ListItem li in lstKeyPhrases.Items)
{
SqlDataSource1.InsertParameters["FK_MainID"] =
(int)Session["theAutoID"];
SqlDataSource1.InsertParameters["Keyphrase"] = li.Value;
SqlDataSource1.Insert();
}

I was thinking this would open connection do all the ado.net stuff and
close the connection just like the simple filling of a listbox scenario.
This won't compile. It seems you are not able to modify parameter values
on the fly? So are you not supposed to use an sqlDataSource for this
scenario? just do it the way I did before (net 1.x)?