|
Thanks for the code snippet.
ToddHileHoffer wrote:
You can use this in a stored procedure, which is the only way you should update a database anyway.
Why?
BTW, in our application, the SQL is being generated dynamically from the schema information and the dirty fields. So SP's are pretty much out of the question.
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
There are three reasons only to use stored procs.
#1 Security. You can grant users the ability to update records without having permissions to the table. It prevents users from connecting to your database from say access to update the tables.
#2 Prevents SQL Injection attacks.
#3 Performance is improved because the queries' execution plans are cached on the server.
If you have to write out your sql commands because you are dynamically generating sql statements then so be it, but that sounds hairy to me.
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
ToddHileHoffer wrote:
#1 Security. You can grant users the ability to update records without having permissions to the table. It prevents users from connecting to your database from say access to update the tables.
In our system, the user doesn't have direct access to the database. All activity between the client and the server (not the DB server, but the application server) is secured, and only the application server on the remote machine has access to the DB.
ToddHileHoffer wrote:
#2 Prevents SQL Injection attacks.
My understanding is that SQL injection attacks can be prevented by using parameters.
ToddHileHoffer wrote:
#3 Performance is improved because the queries' execution plans are cached on the server.
My understanding is that SQL Server caches execution plans, regardless of whether they're from SP's or not.
ToddHileHoffer wrote:
If you have to write out your sql commands because you are dynamically generating sql statements then so be it, but that sounds hairy to me.
Not at all. It's quite robust. 
Marc
My website
Latest Articles:
Object Comparer
String Helpers
|
|
|
|
|
I am having a hard time using a correlated subquery. I have a table
id - int (identity)
ForwardDate - datetime
EffectiveDate - datetime
Price - float
I am trying to return the record for each ForwardDate where the EffectiveDate is equal to the max(EffectiveDate) for that particular ForwardDate. (There are many different ForwardDate's, each with a different max(EffectiveDate)) I tried many variations of this:
SELECT ForwardDate, EffectiveDate, Value
FROM dbo.ArchiveForward f1
WHERE (EffectiveDate = (SELECT MAX(f2.EffectiveDate)
FROM dbo.ArchiveForward f2
WHERE f1.ForwardDate = f2.ForwardDate))
ORDER BY ForwardDate
but all it does is return the values for those records where EffectiveDate is equal to the max(EffectiveDate) for the entire table.
What am I missing?
Thx
Mark
|
|
|
|
|
This should do the trick
SELECT f1.ForwardDate, f1.EffectiveDate, f1.Value
from dbo.ArchiveForward f1 join
(select ForwardDate, max(EffectiveDate) as EffectiveDate
from dbo.ArchiveForward group by ForwardDate) A
on (f1.ForwardDate = A.ForwardDate and f1.EffectiveDate = a.EffectiveDate)
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
Hi, I hope someone can come up with an explanation to this.
In order to populate my DB with ramdom records I execute this code:
(Parameters are generated using Randomize and a loop calls this function)
Private Function Insert_FakeTRans(ByRef TransNum As Long, ByRef Descrip As Byte, ByRef DDate As Date, ByRef Amount As Integer) As Boolean
Dim Cmd1 As New ADODB.Command() 'To Insert in Table1
Dim Cmd2 As New ADODB.Command()'To Insert in Table2
Try
Cmd1.Prepared = True
Cmd1.ActiveConnection = Con
Cmd1.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
Cmd1.Parameters.Append(Cmd1.CreateParameter("TransNum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , TransNum))
Cmd1.Parameters.Append(Cmd1.CreateParameter("Descri", ADODB.DataTypeEnum.adTinyInt, ADODB.ParameterDirectionEnum.adParamInput, , Descrip))
Cmd1.Parameters.Append(Cmd1.CreateParameter("DDate", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput, , DDate))
Cmd1.Parameters.Append(Cmd1.CreateParameter("Monto", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Amount))
Cmd1.CommandText = "[Insert FakeTrans]"
Cmd2.Prepared = True
Cmd2.ActiveConnection = Con
Cmd2.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
Cmd2.Parameters.Append(Cmd2.CreateParameter("TransNum", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , TransNum))
Cmd2.Parameters.Append(Cmd2.CreateParameter("CDate", ADODB.DataTypeEnum.adDate, ADODB.ParameterDirectionEnum.adParamInput, , DDate))
Cmd2.CommandText = "[Insert FakeTrans2]"
Cmd1.Execute(, , ADODB.ExecuteOptionEnum.adExecuteNoRecords) 'Err
Cmd2.Execute(, , ADODB.ExecuteOptionEnum.adExecuteNoRecords) 'Err
Return True
Catch
Return False
End Try
End Function
This code runs nicely 340 times, but at attempt 341 gives me a not so nice
"Object not set to an instance of an objet" error message.  (When it gets to the .Execute instruction)
I am VERY new to programming and honestly have no idea why I get this error. PLEASE, HELP, HELP. 
|
|
|
|
|
Hi
Suppose we have table T as :
ID | Rank
________|________
3 2
3 1
6 7
8 3
6 9
12 8
3 9
3 1
ID is the Id of an article. Each user that see the Article can give it a rank ; (1 =< rank <= 9)
For a given ID , I want all the ranks of it as a ordered table. For example :
For ID = 3 I want to have
Rank | Count
______|_________
1 2
2 1
3 0
4 0
5 0
6 0
7 0
8 0
9 1
I have wrote a stored procedure as:
SP_GetRank<br />
<br />
@ID int<br />
.<br />
.<br />
.<br />
<br />
select ID , Rank into #temp1 from T where ID = @ID<br />
select Rank , count(Rank) as [count] from #temp1 group by rank<br />
<br />
.<br />
.<br />
.
This procedure works peoperly when for each ID , each number of 1 to 9 at least one time presents in table T.
How i change my sp?
Thanks a lot!
|
|
|
|
|
First, create a Ranks table with the possible ranks (1 to 9), just one column called Rank.
Now try this query:
SELECT Ranks.Rank, CASE WHEN [count] IS NULL THEN 0 ELSE [count] END AS [count]
FROM Ranks
RIGHT OUTER JOIN (
SELECT Rank, COUNT(*) AS [count]
FROM T
WHERE ID = @ID
GROUP BY Rank) AS sub ON sub.Rank = Rank.Rank
* DISCLAIMER: I get my left and right outer joins mixed up. So, you may need to change the RIGHT OUTER JOIN to a LEFT OUTER JOIN.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Using the above Ranks table, couldn't you just do a left join?
SELECT Ranks.Rank, Count(T.Rank)
FROM Ranks
LEFT JOIN T
ON (Ranks.Rank = T.Rank AND T.ID = 3)
GROUP BY Ranks.Rank
The left join forces all of Ranks to be displayed and Count() can than properly process the null entries as Zero.
|
|
|
|
|
|
Hi,
I'm new to ADO.net,but have couple of years of experience in classic ADO !
Could you please explain me, what is the best method (way) to insert a record in a multiuser environement.
Stored Procedure option seems to be good, but since i'm using MySql,i can't.
Advance Thanks,
aB
|
|
|
|
|
If you're stuck with MySQL, you need to just pass SQL. If you don't have transactions, you're in trouble.
MySQL sucks.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hi Everyone,
1.I am working on DataGrid. Actually I have relationship between two table(Customers and orders)
displaying on DataGrid. I want to update a record of orders table when I click or select on datagrid,
all the records of order table goes to Textbox control then I or user can update the table easily.
2. How to store the primary key of order table. ( I am using ArrayList )
Thanks
|
|
|
|
|
i want to restore database using restore command but i do not now what parameter should use?i search the google and.... but did not get advantage?
|
|
|
|
|
Hi all,
I'm tryng to create trigger at run time.
I'm using Oracle database.
This is my code:
--------------------------------------------------------------------------------
Dim CmdTmp As OleDbCommand = New OleDbCommand
With CmdTmp
.Connection = oConn
'change_status Trigger
.CommandText = "CREATE TRIGGER change_status AFTER INSERT ON invoices " & _
"REFERENCING NEW AS newRow FOR EACH ROW " & _
"BEGIN update orders set orders.order_status='Close' where orders.orderID = :newRow.orderID; " & _
"END change_status;"
.CommandType = CommandType.Text
Try
.ExecuteNonQuery()
Catch ex As Exception
HasErr = True
Err += ex.Message + Chr(13)
End Try
...
--------------------------------------------------------------------------------
I dont get exceptions but the triggger wasn't created...
does anyone know what is wrong with my code..?
Thank you.
RoyRose
|
|
|
|
|
Hey anyone, everyone
We had our principle server crash this weekend (server2003) and the database backup file was inprogress of backup when it crashed, so the backup file shows "incomplete". I have the mdf and ldf files, but since they were active when it crashed they are not accessable right now.
Are there any tools available to recover this db from the active/ not detached .mdf file?
Currently when I try to reattach the files I get a Error 823, it seems my only option right now is to run dbcc checkdb, but since i've never done it and i'm also working on an entirely new build of sql server i'm not working against a db that i can query or even send a contorl/command to.
ANY help would be awesome!
thanks
|
|
|
|
|
Hi All
Got problems intrying to update foxpro 2.6 table when
setting Null value to a DateTime field by dataset.
There are two fields in tblTest:
ID int 4,
cDT Date/Time
part of my code is as follows:
DataRow row=new ds_tblTest.Tables[0].NewRow();
row[0]=2;
row[1]=DBNull.Value;
ds_tblTest.Tables[0].Rows.Add(row);
da_tblTest.Update(ds_tblTest);
ds_tblTest.AcceptChanges();
...
An error message will pop up when executing "da_tblTest.Update(ds_tblTest)" like this:
Field cDT does not accept null values..
I tried to update tblTest with OleDbCommand and succeed, but
have trouble when updating with dataSet...Very frustrated indeed.
Any help will be highly appreciated...
Many thanks!
Mei
|
|
|
|
|
Look at the UpdateCommand that is being (automatically if you wrapped an OledbCommandBuilder around the ds) built for the datatable. Modify it to look like the oledbCommand (text and parameters) that worked for you...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
:(Many thanks for your kind reply.
The oleDbInsertCommand1 looks like this:
this.oleDbInsertCommand1.CommandText = "INSERT INTO test(ID, cDT) VALUES (?,?)";
this.oleDbInsertCommand1.Connection = this.conn_Ole;
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("ID", System.Data.OleDb.OleDbType.Integer, 2, "ID"));
this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("cDT", System.Data.OleDb.OleDbType.DBDate, 0, "cDT"));
....
I think it should be ok..
The oleDbCommand I used to update foxpro table works fine:
string s_Ins="UPDATE tblTest SET cDT=CTOD('') WHERE ID=1";
OleDbCommand cmd2=new OleDbCommand(s_Ins,conn2);
int i2=cmd2.ExecuteNonQuery();
Very strange indeed. Is that possible to set Null to DataTime field in foxpro table?
Mei
|
|
|
|
|
Suppose you're making a site with a registration module, I'm doing this with DeKlarIT
step 1 - customer enters e-mail addy
step 2 - customer is e-mail verfied
step 3 - customer enters financial details
step 4 - customer's financial details are verified
step 5 - customer enters in advanced details
step 6 - adavanced details verified
...
you've got to keep track of what stage is the customer in, what do you do - create a status integer field which stores values 0-n or create lots of boolean fields.. this question applies to db and your classes
|
|
|
|
|
I don't see what this has to do with a database other than the data being entered is going in and out of a database. Surely the tracking infomation is a part of the web application. I suppose you could put it in the database if you want to give the user the option of returning to where they left off if this is a particularly long operation.
It depends on the functionality that you want which you should used. If you use an integer then it assumes that everything before it is completed and correct. If you use a set of booleans then you could give the the user the possibility of jumping around the pages in the order they choose, but the implementation will be more complex.
Also, this smells like a homework question. You're going to have to make your own decision on this one.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
it already occured to me that lots of booleans would mean the user could hop through the pages in any order but let me rephrase the question, although both methods are acceptable, which method would be more elegant to code
Take the first example -
you have a field in the db called status which is a integer and you have a enum in your code, take the other situations, after running a code generation tool on the db you have flags like User.Registration.IsEmailActivated, User.Registration.IsDataActivated, User.Registration.IsRegistered
|
|
|
|
|
|
Hi, can anyone bring more insight as to make db access thread-safe programmatically? i.e. if there are multithreaded programme or even several programmes trying to access a same db, what do you guys usually do to prevent them conflicting each other?
|
|
|
|
|
Let the database sort it out. So long as you follow the following rules you shouldn't run into many problems:
* Ensure that things are transacted properly.
* Access the tables in the same order for each function. e.g. If some function access table A, B, and D and another function access tables B, C and D ensure that they are accessed in the order A, B, C, and D because if the first function accesses tables in the order D, B and A and the second function access the tables in the order B, C and D then you could run into deadlock. The first function locks table D, meanwhile the second function locks table B, the first function then wants to access table B but can't so it waits for the lock to be released. The second function then gets table C and then requests table D but it cannot because it is locked. The second function then waits for the lock on table D to be released. Now both functions cannot complete until the other has - they are deadlocked. The database will detect this and rollback one of the queries (the deadlock victim). Your application can then attempt again to run function that was the deadlock victim.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
