|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need help with Stored Procedures being called from Client Code.I created the stored procedures for Select, Insert, Update and Delete I got the Select Stored procedure to select data from the database with no problem. By the way, I'm kind of new to stored procedures. So, I want to Insert, Update or Delete records from my Windows Application using the above created stored procedures. I was using Dynamic SQL before and decided to move to stored procedures for many reasons, one of them and very important for me is performance and security. Now as I was saying, when I call the Update command on the DataAdapter I got the message, depending of what I'm doing, that I need to supply a valid Update, Insert or Delete command to be performed. So I guess that it's not as simple to call the Update command to update the database, as I was doing it with Dynamics SQL statements???? How can I accomplish this in code? to be able to call just the update and have my database updated through the stored procedures. Thanks very much. Manny Manny,
Looks like you are using a SqlDataAdapter. Well, SqlDataAdapter has 4 properties - SelectCommand/UpdateCommand/InsertCommand/DeleteCommand. When you set the above, and call SqlDataAdapter.Update() it will call the above as per the rowstates of the dataset/datatable you have passed in. Now those commands might be DynamicSql or Stored proc - doesn't make a difference. Per the error you got, I think you didn't set the SqlDataAdapter.UpdateCommand property to a SqlCommand representing your update statement. - Sahil Malik http://dotnetjunkies.com/weblog/sahilmalik Show quote "Manny" <mcan***@hotmail.com> wrote in message news:ei4NET13EHA.1260@TK2MSFTNGP12.phx.gbl... > Hi there, > > I created the stored procedures for Select, Insert, Update and Delete > I got the Select Stored procedure to select data from the database with no > problem. By the way, I'm kind of new to stored procedures. > > So, I want to Insert, Update or Delete records from my Windows Application > using the above created stored procedures. I was using Dynamic SQL before > and decided to move to stored procedures for many reasons, one of them and > very important for me is performance and security. Now as I was saying, > when I call the Update command on the DataAdapter I got the message, > depending of what I'm doing, that I need to supply a valid Update, Insert > or Delete command to be performed. > > So I guess that it's not as simple to call the Update command to update > the database, as I was doing it with Dynamics SQL statements???? > How can I accomplish this in code? to be able to call just the update and > have my database updated through the stored procedures. > > Thanks very much. > > Manny > > Manny:
Although I totally advocate the use of Stored Procs for many reasons, Performance and Security aren't a slam dunk with procs - you can do the same with Paramaterized SQL --- but that's another story. Like Sahil said, you need to set a command for each operation that you are going to perform. In general, if you are planning to call Update, then you'll need all four commands because if you delete a row for instance, the rowstate will be marked as Deleted. When Update is called the adapter will look for a delete command and if it's not there, you'll have drama. I'd recommend using the DataAdapterConfiguration wizard the first time and look at the code it generates. Not only are you going to need to set an update command, you'll need to set up column mappings and the configuration wizard writes this code for you. After you see it once, it's pretty clear how it works - it definitely helped me out a lot. -- Show quoteW.G. Ryan MVP (Windows Embedded) TiBA Solutions www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com "Manny" <mcan***@hotmail.com> wrote in message news:ei4NET13EHA.1260@TK2MSFTNGP12.phx.gbl... > Hi there, > > I created the stored procedures for Select, Insert, Update and Delete > I got the Select Stored procedure to select data from the database with no > problem. By the way, I'm kind of new to stored procedures. > > So, I want to Insert, Update or Delete records from my Windows Application > using the above created stored procedures. I was using Dynamic SQL before > and decided to move to stored procedures for many reasons, one of them and > very important for me is performance and security. Now as I was saying, when > I call the Update command on the DataAdapter I got the message, depending of > what I'm doing, that I need to supply a valid Update, Insert or Delete > command to be performed. > > So I guess that it's not as simple to call the Update command to update the > database, as I was doing it with Dynamics SQL statements???? > How can I accomplish this in code? to be able to call just the update and > have my database updated through the stored procedures. > > Thanks very much. > > Manny > > Thanks for your inputs guys. The update command I believe it'll go on the
commandText property of the sqldataadapter or doesn't? because when I generated the sqldataadapter with store procs, the wizard places the name od the store proc on the commandText property of the command, whatever this maybe, update, insert, delete and select. Now I looked at the code the wizard generated, but I just can't figure out where should I place the let's say the update command for the command. I'm definitely confused now. Please some more help Manny Show quote "Manny" <mcan***@hotmail.com> wrote in message news:ei4NET13EHA.1260@TK2MSFTNGP12.phx.gbl... > Hi there, > > I created the stored procedures for Select, Insert, Update and Delete > I got the Select Stored procedure to select data from the database with no > problem. By the way, I'm kind of new to stored procedures. > > So, I want to Insert, Update or Delete records from my Windows Application > using the above created stored procedures. I was using Dynamic SQL before > and decided to move to stored procedures for many reasons, one of them and > very important for me is performance and security. Now as I was saying, > when I call the Update command on the DataAdapter I got the message, > depending of what I'm doing, that I need to supply a valid Update, Insert > or Delete command to be performed. > > So I guess that it's not as simple to call the Update command to update > the database, as I was doing it with Dynamics SQL statements???? > How can I accomplish this in code? to be able to call just the update and > have my database updated through the stored procedures. > > Thanks very much. > > Manny > > Manny,
The command goes in the commandtext property of the _updatecommand_ of SqlDataAdapter. Similarly there are 3 more properties - DeleteCommand,InsertCommand and SelectCommand (for fill). Show quote "Manny" <mcan***@hotmail.com> wrote in message news:eL82cK73EHA.4092@TK2MSFTNGP14.phx.gbl... > Thanks for your inputs guys. The update command I believe it'll go on the > commandText property of the sqldataadapter or doesn't? because when I > generated the sqldataadapter with store procs, the wizard places the name > od the store proc on the commandText property of the command, whatever > this maybe, update, insert, delete and select. > > Now I looked at the code the wizard generated, but I just can't figure out > where should I place the let's say the update command for the command. I'm > definitely confused now. > > Please some more help > > Manny > > > "Manny" <mcan***@hotmail.com> wrote in message > news:ei4NET13EHA.1260@TK2MSFTNGP12.phx.gbl... >> Hi there, >> >> I created the stored procedures for Select, Insert, Update and Delete >> I got the Select Stored procedure to select data from the database with >> no problem. By the way, I'm kind of new to stored procedures. >> >> So, I want to Insert, Update or Delete records from my Windows >> Application using the above created stored procedures. I was using >> Dynamic SQL before and decided to move to stored procedures for many >> reasons, one of them and very important for me is performance and >> security. Now as I was saying, when I call the Update command on the >> DataAdapter I got the message, depending of what I'm doing, that I need >> to supply a valid Update, Insert or Delete command to be performed. >> >> So I guess that it's not as simple to call the Update command to update >> the database, as I was doing it with Dynamics SQL statements???? >> How can I accomplish this in code? to be able to call just the update and >> have my database updated through the stored procedures. >> >> Thanks very much. >> >> Manny >> >> > > Thanks for your respose Sahil,
Now, I'm not sure wht it goes in the CommandText. Should I put the whole SQL statements there? for example the SQL statements that updates the database, but I have all those SQL statements in my stored proc!! Thanks, Manny Show quote "Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message news:exCCbt73EHA.3572@TK2MSFTNGP14.phx.gbl... > Manny, > > The command goes in the commandtext property of the _updatecommand_ of > SqlDataAdapter. > > Similarly there are 3 more properties - DeleteCommand,InsertCommand and > SelectCommand (for fill). > > > "Manny" <mcan***@hotmail.com> wrote in message > news:eL82cK73EHA.4092@TK2MSFTNGP14.phx.gbl... >> Thanks for your inputs guys. The update command I believe it'll go on the >> commandText property of the sqldataadapter or doesn't? because when I >> generated the sqldataadapter with store procs, the wizard places the name >> od the store proc on the commandText property of the command, whatever >> this maybe, update, insert, delete and select. >> >> Now I looked at the code the wizard generated, but I just can't figure >> out where should I place the let's say the update command for the >> command. I'm definitely confused now. >> >> Please some more help >> >> Manny >> >> >> "Manny" <mcan***@hotmail.com> wrote in message >> news:ei4NET13EHA.1260@TK2MSFTNGP12.phx.gbl... >>> Hi there, >>> >>> I created the stored procedures for Select, Insert, Update and Delete >>> I got the Select Stored procedure to select data from the database with >>> no problem. By the way, I'm kind of new to stored procedures. >>> >>> So, I want to Insert, Update or Delete records from my Windows >>> Application using the above created stored procedures. I was using >>> Dynamic SQL before and decided to move to stored procedures for many >>> reasons, one of them and very important for me is performance and >>> security. Now as I was saying, when I call the Update command on the >>> DataAdapter I got the message, depending of what I'm doing, that I need >>> to supply a valid Update, Insert or Delete command to be performed. >>> >>> So I guess that it's not as simple to call the Update command to update >>> the database, as I was doing it with Dynamics SQL statements???? >>> How can I accomplish this in code? to be able to call just the update >>> and have my database updated through the stored procedures. >>> >>> Thanks very much. >>> >>> Manny >>> >>> >> >> > > Each command property will get its own SQL Statement or procedure name.
However the Paramaters will be different for each of them that's where the columnmappings come in -- Show quoteW.G. Ryan MVP (Windows Embedded) TiBA Solutions www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com "Manny" <mcan***@hotmail.com> wrote in message news:eFi6#y73EHA.524@TK2MSFTNGP09.phx.gbl... > Thanks for your respose Sahil, > > Now, I'm not sure wht it goes in the CommandText. Should I put the whole SQL > statements there? for example the SQL statements that updates the database, > but I have all those SQL statements in my stored proc!! > > Thanks, > > Manny > > "Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message > news:exCCbt73EHA.3572@TK2MSFTNGP14.phx.gbl... > > Manny, > > > > The command goes in the commandtext property of the _updatecommand_ of > > SqlDataAdapter. > > > > Similarly there are 3 more properties - DeleteCommand,InsertCommand and > > SelectCommand (for fill). > > > > > > "Manny" <mcan***@hotmail.com> wrote in message > > news:eL82cK73EHA.4092@TK2MSFTNGP14.phx.gbl... > >> Thanks for your inputs guys. The update command I believe it'll go on the > >> commandText property of the sqldataadapter or doesn't? because when I > >> generated the sqldataadapter with store procs, the wizard places the name > >> od the store proc on the commandText property of the command, whatever > >> this maybe, update, insert, delete and select. > >> > >> Now I looked at the code the wizard generated, but I just can't figure > >> out where should I place the let's say the update command for the > >> command. I'm definitely confused now. > >> > >> Please some more help > >> > >> Manny > >> > >> > >> "Manny" <mcan***@hotmail.com> wrote in message > >> news:ei4NET13EHA.1260@TK2MSFTNGP12.phx.gbl... > >>> Hi there, > >>> > >>> I created the stored procedures for Select, Insert, Update and Delete > >>> I got the Select Stored procedure to select data from the database with > >>> no problem. By the way, I'm kind of new to stored procedures. > >>> > >>> So, I want to Insert, Update or Delete records from my Windows > >>> Application using the above created stored procedures. I was using > >>> Dynamic SQL before and decided to move to stored procedures for many > >>> reasons, one of them and very important for me is performance and > >>> security. Now as I was saying, when I call the Update command on the > >>> DataAdapter I got the message, depending of what I'm doing, that I need > >>> to supply a valid Update, Insert or Delete command to be performed. > >>> > >>> So I guess that it's not as simple to call the Update command to update > >>> the database, as I was doing it with Dynamics SQL statements???? > >>> How can I accomplish this in code? to be able to call just the update > >>> and have my database updated through the stored procedures. > >>> > >>> Thanks very much. > >>> > >>> Manny > >>> > >>> > >> > >> > > > > > > Thanks Sahil, I finally figured it out.
Thanks for you inputs guys. Manny Show quote "Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message news:exCCbt73EHA.3572@TK2MSFTNGP14.phx.gbl... > Manny, > > The command goes in the commandtext property of the _updatecommand_ of > SqlDataAdapter. > > Similarly there are 3 more properties - DeleteCommand,InsertCommand and > SelectCommand (for fill). > > > "Manny" <mcan***@hotmail.com> wrote in message > news:eL82cK73EHA.4092@TK2MSFTNGP14.phx.gbl... >> Thanks for your inputs guys. The update command I believe it'll go on the >> commandText property of the sqldataadapter or doesn't? because when I >> generated the sqldataadapter with store procs, the wizard places the name >> od the store proc on the commandText property of the command, whatever >> this maybe, update, insert, delete and select. >> >> Now I looked at the code the wizard generated, but I just can't figure >> out where should I place the let's say the update command for the >> command. I'm definitely confused now. >> >> Please some more help >> >> Manny >> >> >> "Manny" <mcan***@hotmail.com> wrote in message >> news:ei4NET13EHA.1260@TK2MSFTNGP12.phx.gbl... >>> Hi there, >>> >>> I created the stored procedures for Select, Insert, Update and Delete >>> I got the Select Stored procedure to select data from the database with >>> no problem. By the way, I'm kind of new to stored procedures. >>> >>> So, I want to Insert, Update or Delete records from my Windows >>> Application using the above created stored procedures. I was using >>> Dynamic SQL before and decided to move to stored procedures for many >>> reasons, one of them and very important for me is performance and >>> security. Now as I was saying, when I call the Update command on the >>> DataAdapter I got the message, depending of what I'm doing, that I need >>> to supply a valid Update, Insert or Delete command to be performed. >>> >>> So I guess that it's not as simple to call the Update command to update >>> the database, as I was doing it with Dynamics SQL statements???? >>> How can I accomplish this in code? to be able to call just the update >>> and have my database updated through the stored procedures. >>> >>> Thanks very much. >>> >>> Manny >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics