|
I am writing a utility which will delete all existing records in a table and then add new records using ADO.NET (and C#). I define a Data Table with the same schema as the database table and then use the SqlDataAdaper Update method to add rows to the database table. This works fine if I first delete all the rows from the database table using the SQL Server 2000 Query Analyzer. I simply use the statement "DELETE FROM ". I wish to add code that will do this as a preliminary to repopulation. I have created an SqlDeleteCommand and set its Command Text to "DELETE FROM ". Then I set the DeleteCommand property of the SqlDataAdapter object to this object. I then call the adapter's Update method passing it a DataTable with the same schema as the database table but with 0 rows. It is my understanding that the Update method would attempt to reconcile the differences between DataTable and database table by calling invoking the SQL Delete statement. This does not happen. Can anyone help me clean the rows out of the database table programmatically?
Gary A Hyslop
|
|
|
|
|
Forget about using DeleteCommand property. Just use Delete() method of DataRow. And after that use Update() method f SqlDataAdapter. This cause first row delete from your DataTable and then they updated in database. See MSDN and search this site for sample codes.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
I'm not sure how you would execute this with ADO.NET, but there is a SQL DBA command 'truncate table tablename'. If you want all data contents removed prior to loading new data, this method will be much more performant. It may be possible to put this command in a stored procedure, but I can't recall for sure.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
Sure you can put it on a SP, but it won't work if the table has foreign keys.
Free your mind...
|
|
|
|
|
Also, Truncate Table will not cause the triggers to fire if they are present on the table.
It is the fastest way to remove all rows from a table.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi,
I'm new here & not sure if tis question could be posted here or not. I need help with tis problem. I'm using Ms Access for the db with the fID as autonumber. i'm shown an error "Operation must use an updateable query" when i click on e OK button. the vb.net code is
If (txtName.Text = " " OrElse txtEmail.Text = " " OrElse txtCountry.Text = " " OrElse txtSubject.Text = " " OrElse txtMsg.Text = "") Then
lblErrMsg.Text = "Please Fill In Every Field"
Else
sql = " insert into tbforum(Name, Email, Country, Subject, Message, MsgDateTime)values('" & Me.txtName.Text & "', '" & Me.txtEmail.Text & "' , '" & Me.txtCountry.Text & "' , '" & Me.txtSubject.Text & "', '" & Me.txtMsg.Text & "' , '" & Me.lblDate.Text & "' )"
conn = New OleDbConnection(connstr)
conn.Open()
dbcomm = New OleDbCommand(sql, conn)
End If
if i put the fID in the first field, it would still show me the same prblem
Can anyone pls help me with this problem? 
|
|
|
|
|
Check your connection string. Access DBs open as read-only, I believe, unless you specify an optional parameter.
Some people think of it as a six-pack; I consider it more of a support group.
|
|
|
|
|
I'm just getting into Inner Join methods. I've got a query that extracts a whole heap of data from my database using the following query:
"SELECT MainPosts.PostTitle, MainPosts.PostDate, MainPosts.PostTime " +
"FROM ((MainPages INNER JOIN MainPosts ON MainPages.PageID = MainPosts.PageID) " +
"INNER JOIN Members ON MainPosts.PosterID = Members.AccountID) " +
"WHERE (MainPages.PageName = 'Main') " +
"ORDER BY MainPosts.PostDate DESC, MainPosts.PostTime DESC"
Now, this works ok, i get a populated dataset. But i'm trying to get my head round a few things.
How is this query represented in a dataset?
It seems that i only have 1 table called table. Is this how it's supposed to work? I was expecting to get a table of the appropriate name, or a set of tables in the dataset of the appropriate names.
That's relatively trivial though.
My biggest dillema is deffining a delete call for it. If i delete an entry, or sign it off to delete, how does this behave? Can i delete something in this table, and have it removed only from the Posts table?
I'm a bit hazy.
Regards
Cata
|
|
|
|
|
It will be represented in the dataset as one table with the columns PostTitle, PostDate, and PostTime.
I usually use XSD diagrams to keep my tables separated and build the relationship between them.
You can delete specific tables depending on how you define the DeleteCommand.
From your SQL statement I'm assuming that you're deleting the records based on the PageID.
Here's an example of delete statement that will delete the record in MainPosts when you delete the record in the recordset:
DELETE FROM MainPosts
WHERE PageID = ?
You have to supply the PageID parameter into the DeleteCommand. I find that using Visual Studio .NET makes my job easier 
Edbert P.
Sydney, Australia.
|
|
|
|
|
D'oh! It's that simple?
hahahahaha!
I use VS.net to design my SQL statements and build my DLL components that i use in my page.
I'm always looking for anything new to learn, is it worth learning XSD / XML to construct, shape and manage datasets?
Cata
|
|
|
|
|
Oh yeah, it is worth learning XML and XSD. You'll find that it's much easier to use XSD for datasets.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi all,
I am having some problem converting SQL Server stored procedures to Oracle. The stored procedures with statement like
SELECT TOP 1 FROM SOME_TABLE does not export. It complains about 1. So, what would be the Oracle equivalant of Top 1 ??
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
You can use rownum in Oracle to do this.
SELECT * FROM Some_Table WHERE rownum < 2 If a specific sort or filter is necessary, you can perform that as a subquery, then select from that result using rownum. This selects the top 5:
SELECT * FROM
(SELECT *
FROM Some_Table
WHERE Some_Field = 'Value'
ORDER BY Some_Other_Field
)
WHERE rownum < 6;
|
|
|
|
|
This would not work in SQL Server neither.
It should be:
Select top 1 * From SOME_TABLE
Wout Louwers
|
|
|
|
|
I posted this over on the ASP.NET forums but didn''t hear much of anything. So I guess I'm cross posting....
I have an application that has stores sensitive data in an SQL server and I am currently handling this through my ASP.NET application using the encryption classes in C#.
One of the things we would eventually like to be able to do is use other programs (like Microsoft Access) to run advanced Querys on the tables and retrive the data. With the encyption being done in C#/ASP all that would be returned would be the encrypted data.
I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.
I don't know a lot about SQL right now (Mainly only MySQL so Stored Procs and all that stuff are really new to me) so I don't know if I would go about it this way or not? (Using a stored procedure)
Can anyone reccommened where I should start to learn about accomplishing this? Books are usually the best help but I'll take any kind of information that can be thrown my way
Thanks!
|
|
|
|
|
Kluch wrote:
I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.
I don't think any T-SQL statements can do that for you , why don't you write a web serice which do that encryption/encryption. It suit this situation.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
Mazdak wrote:
I don't think any T-SQL statements can do that for you , why don't you write a web serice which do that encryption/encryption. It suit this situation.
If it's a web service though would you still be able to tie into it with 3rd party programs (mentioned above) and still have it decrypt the data?
|
|
|
|
|
I don't know exacly what do you mean. Any platform can comunicate with web service. So you do can do your encryption and decryption in the web serivce. I mwan you send request to web service , then it send the request to your database and get encrypted data , you decrypt data in web service and send it back to client. So no problem. I don't know exactly how to communicate from Access to a web service , if you can write VB module there , then you can use Soap ToolKit and do that. Is that clear?
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
This seems like a good solution. As mentioed I would have to figure out
A) How to send a request to the web service (Hopefully this could even just be my select string with some logon info or something)
B) What type of Data would be needed to send back to the client (Don't know if it would be easy as just passing a data grid)
What would be nuce is my main ASP app could use the same method just to encrypt decrypt the data. It's something to look into. Thanks!
|
|
|
|
|
Oh , thats a long topic , you can use authentication too in web serive. Chris have introduction article about web service. Web Service is not very comlpicated topic , you send/recieve any datatype.
You can use it in the same way in your ASP.NET pages too. I really too lazy to describe all these topics , they are very simple and you can find lots of info about them in books and googling.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
You can create an extended procedure - basically a C function exported from a dll you write. You can then attach it to MSSQL and use it the same way you would use a stored procedure.
Look at :
http://www.codeproject.com/database/xp_md5.asp[^]
...cmk
Save the whales - collect the whole set
|
|
|
|
|
Hi,
I am not sure if I grasped your question completely. But here is my take:
I see two options:
- Have a windows or web service as a layer or interface between the outside program and your database. Since your database contains sensitive information, probably it is a good idea to allow connections only locally i.e. from your windows or web service.
- Second option would be to encrypt the data before you store it in the database. This way you do not have the hassle of encrypting an decrypting data everytime some program accesses it. You encrypt it only one time when you store it in the database and maybe decrypt it on the client side again.
Hope that helped.
Best,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Hi everyone,
I have very limited database experience. The little experience I have is on SQL server and now I have to port this database to Oracle.
I have the following questions about Oracle:
- What is the best tool to connect to an Oracle database? Something along the lines of SQL server Enterprise Manager? Right now I have the Oracle ODBC client installed and I use Access to connect to it. However, Access does not seem to be able to handle it well.
- Is it possible to change the password for an Oracle user over ODBC? What do I have to do to achieve that?
Thanks,
Sincerely,
Keith
|
|
|
|
|
- I use SQL Navigator[^], but it's a little bit expensive; they also have another solution, TOAD[^], very nice and useful.
- Yes you can. Use this SQL instruction:
ALTER USER USER_NAME IDENTIFIED BY NEW_PASSWORD
Hope this helps...
Free your mind...
|
|
|
|
|
Hi,
Thanks a lot. Have to wait till tomorrow now to try it out. Working hours are over here on my side of the world
Best always,
Keith
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
