|
Hi M.
I tried it , But only found that you can replace Null value at the most
select IsNull(fldCol,1) from tblTest where fldcol2 ='abc'
hope someone comeup with the required stuff,May be Next version provide this facility.
Regards & Wishes
Navneet Hegde
Nashik(City Of Pilgrimage)
Develop2Program & Program2Develop
|
|
|
|
|
Hey all,
Are Stored Procedures always preferable to PQ's, when possible? Reason I ask is that i've used PQ's extensively during the last 3 years (or so) of development, and I'm wondering if I should "progress" (given that all my work has been, and probably will remain, with SQL Server, I'm not too worried about having to port anything to Oracle or whatever).
Some sites mention security concerns, but I had thought that PQ's were pretty good anyway, and certainly avoid SQL Injection style attacks. Are there other considerations?
Cheers,
Martin.
|
|
|
|
|
I use stored procedures only in all of my code. They are cached on the sql server so they will perform quicker. It also allows you to limit the rights of your user to only execute the stored procedures. They don't need any rights to tables at all. So that is more secure. I would suggest moving toward only using stored procedure.
Ben
|
|
|
|
|
The compiled query plan for a stored procedure has a slightly higher weighting than that for a parameterized ad-hoc query, so it will tend to stay around in memory for a little longer.
In security terms, you can GRANT access to EXEC a stored procedure without having to grant access to the tables that the stored procedure accesses. This allows you to control the entry points to your database. This is a defence-in-depth measure in case you happen to accidentally allow a SQL injection or disclose the credentials used to connect to the database, or they're compromised. Best practice is to only permit each user and/or application the rights they actually need to get their job done.
Don't go overboard, though. Some people are seduced by the idea of building a single stored procedure to handle querying a table or view with optional parameters (e.g. looking for books by title or by author). This often looks something like:
CREATE PROCEDURE SelectBooks
(
@title varchar(50),
@author varchar(50)
)
SELECT *
FROM books
WHERE
(title = @title OR @title IS NULL) AND
(author = @author OR @author IS NULL) They've found a syntactically valid solution, but it makes harder work for the optimizer. Further, the query plan gets compiled the first time the procedure is used, using the parameters supplied. If you specify @title the first time round, leaving @author set to NULL, the optimizer might choose to seek through an index on title. If you then call it with @author rather than @title, it may still try to use that index and fall back on a table scan (i.e. reading every row in the table), even if there was an index on title that it could have used. In this case, I think parameterized queries are better.
[EDIT:]
I've just tried an equivalent query on SQL Server 2000 SP4, with no primary key on the table but with a separate single-column index on each of the columns in the query. The table has 240,000 rows. SQL Server elected to do an Index Scan (that is, reading the index from start to finish) on the title column, whether you specified title or author. If instead you run the parameterized query specifying only the columns you actually want to filter on, it correctly picks an Index Seek to the value you're after. I'm not yet sure about SQL Server 2005.
In fact if you specify EXEC ... WITH RECOMPILE it's using the opposite index from the value you specify! Flushing the data from the cache using DBCC DROPCLEANBUFFERS causes the query using the above stored procedure to take 46 seconds! The database is 160MB but my laptop, a Core 2 Duo T7200 [2GHz], has 2GB of RAM so if you don't run from cold, it's sub-second. The index scan is so fast, even from cold, that the Client Statistics view in Query Analyzer shows a time of 0.
-- modified at 16:17 Thursday 28th June, 2007
|
|
|
|
|
Thanks for taking the time to investigate that Mike - I'll try a similar test in 2005 to measure the performance of SP's vs PQ's... I remember reading somewhere that 2005 can optomise and cache PQ's, so it'll be interesting to see.
|
|
|
|
|
This subject is hotly debated and is often the cause of religious wars! Well on the web anyway 
http://www.google.co.uk/search?hl=en&q=stored+procs+vs+dynamic+queries&meta=[^]
Personally I like Stored Procs, they appeal to my OO side with their encapsulation of data access sql code in the database. Another advantage is not having to update your application to change a query. But as others have said dynamic sql definatly has its place and shouldn't be completely dismissed.
|
|
|
|
|
originSH wrote: This subject is hotly debated and is often the cause of religious wars! Well on the web anyway
That's why I thought I'd ask you guys - everytime I searched Google the whole debate turned into a semi-religious debacle!
|
|
|
|
|
Hi ...
I use CrystalReport with vb.net 2005 ,
Can I view more than one page on CrystalreportViewer at the same time .. and also can I user Mouse scroll for navigatoin...
thanks ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
jooooo
|
|
|
|
|
Hi All,
Any can help me out by telling what is the alternative of " Convert(Varchar(10), Date, 101) As MY_DATE " this in MS Access. this function is actually used in query to get short date. How can i get this type of short date in MS ACCESS
|
|
|
|
|
If you open up a blank query and go to the functions under date/time there is a formatdatetime function. I believe 2 is the option for the short date.
______________________
stuff + cats = awesome
|
|
|
|
|
hello
In webform i have 4 date picker.
which are 1. Create date (that is todays date)
2. expirydate (i.e display automatically i write code todays date + 28 days)
3. warning date (i.e also automatically display. expiredate + 2 days)
4.updateDate(which we will select)
all code is correct
but when program run that time runtime error ocurrs.
i.e
The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value. The statement has been terminated.
In table i am taking datetime datatype.
pls give me a quick solution for this problem.
my code is ----
DateTime intWarningDate = DateTime.Now.Date.AddDays(28);
dtpWarningDate.SelectedDate = intWarningDate.Date;
DateTime intExpiryDate =dtpWarningDate.SelectedDate;
DateTime date1 = intExpiryDate.AddDays(2);
dtpExpireDate.SelectedDate = date1.Date;
Gayatri
Gayatri
|
|
|
|
|
Don't CrossPost in Multiple Forums also.
Why are you Creating the New Thread for the Same Problem.
Please Continue in the Same thread Itself.
Regards,
Satips. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
hello
In webform i have 4 date picker.
which are 1. Create date (that is todays date)
2. expirydate (i.e display automatically i write code todays date + 28 days)
3. warning date (i.e also automatically display. expiredate + 2 days)
4.updateDate(which we will select)
all code is correct
but when program run that time runtime error ocurrs.
i.e
The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value. The statement has been terminated.
In table i am taking datetime datatype.
pls give me a quick solution for this problem.
Gayatri
Gayatri
|
|
|
|
|
date format of date picket depends on regional settings of control panel.
use convert() function while inserting into table.
Regards
KP
|
|
|
|
|
convert cannot be used with MS Access.
|
|
|
|
|
Hi,
can u help me the datatypes of the dates in the table. Probably, I think the datatype is datetime (dd/mm/yyyy or so on) whereas the date value being passed could be either dd/mmm/yyyy or any character is included in that date
Thanking you in Advance
Kind Regards
Pratik Shah
|
|
|
|
|
hi all,
i am retrieving values from 5 tables, for that i've written joins and tried to retrieve,
but here my problem is Even if one value is null the query is not working and returning 0 rows.
so i want to approch in such a way that if anywhere if any value is null, the query should return a null value
and ultimatly query should work with the fields that have values
Please help me
thnks in advance
|
|
|
|
|
Can u please show your query?
The name is Sandeep
|
|
|
|
|
here's my query
select t.date_value, start_time,end_time,upper(r.last_name)+', '+r.first_name teacher_name,
datediff(hh,t.start_time,t.end_time)*t.rate total_rate,t.remarks
FROM dbo.ttcpCustomizedSchedule t left outer join dbo.ResourcePersons r
on r.teacher_id=t.teacher_id where t.tload_id = 5724 order by date_value
i want to make the start_time and endtime using a string format of:
"hh:mm tt"
please help me...
i am using mssql server 2000
|
|
|
|
|
Try: Substring(convert(varchar, StartTime, 100), 13, 8)
|
|
|
|
|
|
Hi,
I would like to know how i can perform update or delete by using Update method of sql data adapter. I have done the insert operation using update method. The code follows...
<br />
public Boolean SetStorageArea(int UID, DataTable dtStorageArea)<br />
{<br />
string strSql;<br />
strSql = "SELECT [Active],[WarehouseID],[StorageAreaID],[StorageAreaShortName],[StorageAreaName], " +<br />
" [Alias],[Notes],[CreatedBy],[CreatedOn],[LastModifiedBy],[LastModifiedOn] FROM [StorageArea]";<br />
try<br />
{<br />
DataSet dsStorageArea = new DataSet();<br />
dsStorageArea.Tables.Add(dtStorageArea);<br />
SqlDataAdapter sda = new SqlDataAdapter(strSql, sqlcon);<br />
SqlCommandBuilder sqb = new SqlCommandBuilder(sda);<br />
sda.Update(dsStorageArea, "StorageArea");<br />
return true;<br />
<br />
}<br />
catch (Exception Ex)<br />
{<br />
<br />
throw new Exception("SetStorageArea: Could not get the records of all Storage Areas.\nOriginal message: " + Ex.Message);<br />
}<br />
}<br />
.
awaiting for your valuable suggestions..
Thanks in advance
Sebastian
|
|
|
|
|
See this , i did this in vb.net
'save the datasource Changes
If mblnIsDirty Then
If MessageBox.Show("Do you want to Save the Changes?", "Property", _
MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
' Checking for Error in the dataset
Try
SqlDataAdapter1.Update(DataSet11, "Property")
Catch
MessageBox.Show("Error saving Data in the DataBase", "Property")
checkerrors()
End Try
End If
End If
<br />
<br />
The Check Errors will tell you where did you go wrong<br />
here is code for checkErrors<br />
<code><br />
Private Sub checkerrors()<br />
<br />
Dim table As Data.DataTable<br />
Dim row As Data.DataRow<br />
SqlDataAdapter1.Update(DataSet11, "Property")<br />
If DataSet11.HasErrors Then<br />
<br />
For Each table In DataSet11.Tables<br />
If table.HasErrors Then<br />
<br />
For Each row In table.Rows<br />
If row.HasErrors Then<br />
MessageBox.Show("Dataset has Errors")<br />
' Process error here.<br />
<br />
End If<br />
Next<br />
ElseIf table.HasErrors = False Then<br />
MessageBox.Show("Dataset has no Errors")<br />
End If<br />
Next<br />
End If<br />
End Sub<br />
<br />
And this is the insert
<br />
'save the New Record for an Add or Edit<br />
If mblnAdding Then<br />
Try<br />
Dim newRow As DataRow = DataSet11._PROPERTY.NewRow<br />
newRow("Num_key") = CStr(txtnumkey.Text)<br />
newRow("Extension") = CInt(txtextension.Text)<br />
newRow("Cell_ID") = CInt(txtcellid.Text)<br />
newRow("ACTUAL_EXTENT") = CInt(txtactualextent1.Text)<br />
newRow("Lis_key") = CStr(txtliskey.Text)<br />
newRow("Func_key") = CStr(txtfunckey.Text)<br />
newRow("PROP_CATEGORY_ID") = CInt(Prop_Category_ID)<br />
newRow("Geocode") = CStr(txtgeocode.Text)<br />
newRow("Rateable") = Rateable<br />
newRow("Non_Discreet_Valid") = Non_Discreet_Valid<br />
DataSet11._PROPERTY.Rows.Add(newRow)<br />
Catch exc As Exception<br />
MessageBox.Show("Unable to add the Record." & _<br />
ControlChars.NewLine & exc.Message, "Property")<br />
<br />
End Try<br />
mblnAdding = False<br />
lblRecordNumber.Text = "Record Added at the end of the Table"<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Hope this Helps
Vuyiswa
|
|
|
|
|
Hi,
have table configured like this:
CREATE TABLE `tblDane` (
`ID_DANE` bigint(20) NOT NULL,
`BAZA` varchar(50) default NULL,
`ID_ORG` varchar(50) default NULL,
`NAZWISKO` varchar(100) default NULL,
`TELEFON` varchar(50) default NULL
PRIMARY KEY (`ID_DANE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
my connections string looks like: "Server=192.168.1.18;Database=testtable;Uid=test;Pwd=test;charset=latin2;"
When retrive some data from table everything is ok - mean got polish chars like śćńł, but after update got only '??????' not a polish one
Any idea what can be wrong?
Greetings
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
