Inserting data into MSSQL using TADOQuery

I need some advice/best practice for implementing CRUD (specifically the Create) on a MSSQL database.  I typically open an existing record by dynamically generating the SQL statement for a TADOQuery and then use data-aware components on my form to allow the user to make the changes.  Saving is done by a Post and canceling by a Cancel method. 

The problem arises when I want to insert a new record.  If the table keys off an integer value such as employee_nbr and is an autoincrementing field using TADOQuery.Insert will not work because the query is not open.  If the key field is not autoincrementing and I determine the next employee-nbr to assign by looking it up in a table I am left with the issue of inserting a blank record with the new key.  If the user cancels the insert that record needs removed.  I also have concerns using this in a multi-u
ser environment. 

Should I use non-dara aware components and send all fields to an insert function?  Should I use TClientDataSets (I have never used them before)?  Any advise on implementing CRUD in a multi-user environment would be greatly appreciated!
0
Rick
1/7/2009 5:11:38 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

1 Replies
829 Views

Similar Articles

[PageSpeed] 59

> {quote:title=Rick Glunt wrote:}{quote}
> I need some advice/best practice for implementing CRUD (specifically the Create) on a MSSQL database.  I typically open an existing record by dynamically generating the SQL statement for a TADOQuery and then use data-aware components on my form to allow the user to make the changes.  Saving is done by a Post and canceling by a Cancel method. 
> 
> The problem arises when I want to insert a new record.  If the table keys off an integer value such as employee_nbr and is an autoincrementing field using TADOQuery.Insert will not work because the query is not open. 

If you have populate your TadoQuery using a Select statment and the locktype is ltOptomistic then if there is an autoincrementing field the value will be generated when the record is posted and the value will be generated back to your TadoQuery record.
That is all done for you in ADO





 If the key field is not autoincrementing and I determine the next employee-nbr to assign by looking it up in a table I am left with the issue of inserting a blank record with the new key.  If the user cancels the insert that record needs removed.  I also have concerns using this in a multi-user environment. 
> 
> Should I use non-dara aware components and send all fields to an insert function?  Should I use TClientDataSets (I have never used them before)?  Any advise on implementing CRUD in a multi-user environment would be greatly appreciated!
0
Brian
1/7/2009 8:39:03 PM
Reply:

Similar Artilces:

how to use business entities in business logic and data access layers when object data source is used?Here is my code can somebody pls help me in using business entities to select,insert ,update and d
Hi, I am new to dot net ,can someone please help me in how to use business entites in dataccesslayer and businesslogiclayer. I should not use dataset instead i should use business entites to get all the links,I am posting my code here,i am posting my business entites and dataaccess layer and business logic layer.Also I have a question regarding event handlers ,how to use that using grid view.Please can any reply as soon as possible.Here is the code:   Business Entities:  namespace UsefulLinkEntity { public class UsefulLinkEntity {public string strdp_Title...

Inserting data from text file into MSSQL via ADO
I am having a text file, with a header row, that is quote comma delimited that I want to put into a MSSQL table. From about.com I found this syntax: ADOCommand1.CommandText := 'INSERT INTO mytablename (myfield1, myfield2, myfield3)' + 'SELECT * FROM [asciidata.txt] ' + 'IN "v:\shareddata\transactions" "text;HDR=Yes;" ' When I execute the command, I get an error "syntax error near 'IN' ". I have added a stringgrid to the project and load the file, without error. Does anyone know what is wrong with my syntax...

Oracle NCLOB data getting truncated when using delphi ADO
I am using Delphi2007 and using ADO for data access. When trying to select nclob data from an Oracle database it generally works, but I have one query that is consitantly truncating the data at 12 characters. I have already been in touch with Oracle and they have confirmed that the OLEDB provider that is being used is correctly returning the data. Here is the query select ACT_KEY, COALESCE((SELECT LTX_TEXT FROM TEXT_TABLE WHERE LTX_TABLE='SOURCE_TABLE' AND LTX_TEXT_ID = 'ACT_DESC' AND LTX_TABLE_KEY = CAST(SOURCE_TABLE.ACT_KEY AS NVARCHAR2(10))),TO_NCLOB(ACT_DESC)) as A...

How to insert data into sql server in bulk using ADO.net
Hi!  I'm building a web application. I need to read data from a text or excel file and process the data and then store the result records into database. The record number is big. I can store the data record into database (SQL Server 2005) one at a time. I think it's slow. Is there any way to insert the data in bulk.   Thanks! ccy Hi, Try DataAdapter's Update() method. Thanks and best regards,Faraz Shah KhanMCP, MCAD.Net, MCSD.Net, MCTS-Win/Web, MCPD-WebBlog HI, If you want to just insert the data then you can use sqlbulkinsert class. If you want to update a...

Inserting Data Using VB/ado.net Code-Behind is failed
HelloI am trying to insert data Using VB Code-Behind.I want to Insert the data from textbox on the webform into table of database sql 2005, simple INSERT STATEMENT Using code and not special controls or wizard. TNX  Here's an example for you: Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString) Dim cmd As New SqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone); SELECT SCOPE_IDENTITY();", conn) cmd.Parameters.AddWithValue("CompanyName", txtC...

how to insert,edit data either using ADO.net or Sqldatasource in vwd2205?
Hi everyone..can anyone explain me with simple example onhow to select, insert data to sqldatabae & update data to sqldatabase either using ADO.net or Sqldatasource in vwd2205? plz explain with code (C#).thanks.jack.  Hope this link helps you http://www.asp.net/learn/data-access/?lang=cs Thanks -Mark post(s) as "Answer" that helped youMark post(s) as "Answer" that helped youElectronic ScrewWebsite||Blog||Dub@i.net Check thiswith sqlDataSourcehttp://www.codersource.net/asp_net_grid_view_whidbey.aspxwith ADO.nethttp://www.aspdotnetcodes.com/GridView_Inse...

Inserting data to the database using ADO.NET "Update" method...slow!
Hi, I am using Odbc ADO.NET code to insert data to a database. I add around 30,000 reocrds to a dataset, and then using a dataadapter if I call the "Update" method to save it takes ages - about 40 minutes!! Is this normal, surely it shouldn't take so long? Admittidly the oracle db server is slow, but not that slow ;) I'm pointing at an Oracle 9i database. The reason I use Odbc rather than the specific Oracle objects is that I also need to be able to point at other databases. I tried changing to Oracle specific objects but it made little difference to performance. My insertCommand looks li...

Problem in inserting data using insert command
Hi! I am trying to insert data into the databse using the following method. Protected Sub UserAddButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UserAddButton.Click Dim addUserDS As New SqlDataSource() addUserDS.ConnectionString = ConfigurationManager.ConnectionStrings("RSSConnectionString").ToString() addUserDS.InsertCommandType = SqlDataSourceCommandType.Text addUserDS.InsertCommand = "INSERT INTO user (UserID, BuildingID) VALUES (@UserID, @BuildingID)" addUserDS.InsertParameters.Add(&quo...

Error inserting data into text data type of MSSQL
Here is my code : Public kode As String   Public MyCon As New SqlConnection("server=127.0.0.1;uid=sa;pwd=r**secret**2;database=amipka")   Private Sub beforePageLoad()     If Session("login") = "" Then       Response.Redirect("login.aspx")     End If   End Sub   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load     'Put user code to initialize the page here     Call beforePageLoad()     Response.Cache.SetCa...

How do you Inserting an Image into MSSQL using a FormView insert template
I have looked high and low and cannot find an answer. I have a table set up with personal information and included an "Image" DataType field. I have used Stored Procedures for the DAL. I am now working on the "Insert New Personal" form and have dropped a FormView on the page and set up the ObjectDataSource. Set the default to insert template and have arranged and set up the InsertTemplate so it looks nice. I removed the text field generated by the FormView that was bound to the "Image" field in the table and have replaced it with a FileUpload control. Bound the FileUpload control to...

ASA7 ADO & Delphi ADO ?? Anyone is using it?
Any comments? Is it working? Any Problems? Thanks Hi, PR <noemail@no.com> schrieb in im Newsbeitrag: BoTThJaq$GA.89@forums.sybase.com... > Any comments? Is it working? Any Problems? I tried to connect to ASA7 via the new OLE DB - Provider and ADOExpress from Delphi 5.01. No chance. Only the error message 'Invalid Enum Value'. About the new provider I haven't heard anything good until now... Regards Frank Frank, I've experienced the same problem with the ASA 7 OLEDB Provider. I'm not sure if this is a bug in the AsaProvider or Delp...

Fill Grid when data is inserted into data base using with timer.
Hello friedends, I hve a problam, in our aplication 2 link r there , then click on Ist link show grid with in web part.   when data inseted into database timer is tick and fill data grid... now when we click on 2nd link same process is repeated,.. means, when data insert into table timer is call and grid refreshed and update . how can I do this ? help me? reply me ASAP   Hi, Based on my understanding, you want to update the GridView when the datasource is changed. As far as I know, there is a approach can achieve your objective. And there are sever...

Problem inserting data using DataAdapter without using command
Dear friends I am new with ADO.NET , I have a problem when I am trying to save the webform data to oracle database, I am using DataAdapter and Dataset but NOT COMMAND. .. The problem that I've found is tha I can't save the new record? And most of the examples are using Command but I don’t want to use command ... Any help or a link to a sample code will be appreciated The code as following in Button Click event Dim myconnection As OleDbConnection Dim myda As OleDbDataAdapter Dim ds As DataSet myconnection = New OleDbConnection(&q...

how to page load veiw data (dropdownlist) and inserted the data? using #
send me the dropdownlist item load and how to insert the data using c#   public void laodcenter()  {   string str;      str = "Select CenterCode,CenterName from CenterDetails where" +    " CountryCategory='INDIA' and StateCategory='TAMIL NADU'   and Status='Operational' and Agreement >= Convert(datetime,getDate(),103) order by CenterName" ;      SqlCommand sqlCmd = new SqlCommand(str,Dbcon);   sqlCmd.CommandTyp...

Web resources about - Inserting data into MSSQL using TADOQuery - embarcadero.delphi.ado

Inserting Kate
jurvetson posted a photo: the speaking GPS avatar that we came to love.. more prep photos below... You can hear her voice in the video compilation ...

Inserting the Galaxy Note 5’s S Pen backwards can permanently damage the device
One of the features Samsung highlighted when it unveiled the Galaxy Note 5 was the device's new S Pen slot design. The Note 5 features a spring-loaded ...


Inserting Slavery Into The Climate Debate
Chris Hayes compares the fight against fossil fuels to the abolitionist movement. He states plainly that “there is absolutely no conceivable ...

Samsung smart TVs inserting ads into third-party apps
... playback have pissed off a number of consumers. People with smart TVs from Samsung have been complaining that the electronics maker is inserting ...

Inserting images into Gmail could be so much better
When Google first launched the new compact compose window in Gmail, many of you might have noticed something annoying about inserting images. ...

Gawker tells Steven Crowder to "stop whining, take your licks, and accept that getting hit in the face ...
"... in the middle of an argument between billionaire-funded know-nothing ideologues and people whose livelihoods and stability are being threatened ...

Gmail's Shortcut for Inserting Hyperlinks
... but that doesn't mean Google shouldn't improve them. Many people complained that Gmail's rich text editor doesn't have a shortcut for inserting ...

Why Hollywood Is Inserting These Two Huge Chinese Actresses Into Its Summer Blockbusters
... It shows the enormous potential in China for distributors. We've previously discussed how Hollywood is starting to cater to China by inserting ...

Samsung SmartTVs are inserting ads into movies - Business Insider
Random Pepsi pop-up ads have been appearing when SmartTV users watch content through the device's third-party apps.

Resources last updated: 1/11/2016 5:57:07 PM