|
dboy221 wrote: How do I return records from a query where a specific field does not have a value.
SELECT * FROM MyTable WHERE SomeColumn IS NOT NULL
|
|
|
|
|
Colin Angus Mackay wrote: SELECT * FROM
I know this is an easy way to show the example but, being that it is an example, some poor schmo might actually use this in production. Might I suggest:
SELECT SomeColumn1, SomeColumn2 FROM MyTable WHERE SomeColumn IS NOT NULL
Never, never use select * in production.
topcoderjax - Remember, Google is your friend.
|
|
|
|
|
TopCoderJax wrote: I know this is an easy way to show the example but, being that it is an example, some poor schmo might actually use this in production.
Since no columns where given as an example, there was no list from me to select from.
TopCoderJax wrote: Might I suggest
You can suggest it. My my time is limited and I'm not going to spend time writing out a ficitious list only for the OP to come back and say it doesn't work because *I* supplied invalid column names - And that has happened on a number of occasions. Some people are increadibly thick or just don't want to understand what is going on. (NOTE: This is a general observation and may not apply to the current OP)
TopCoderJax wrote: Never, never use select * in production.
I completely agree.
TopCoderJax wrote: Remember, Google is your friend.
And one which most people shun for some bizzare reason. By the way, have you tried to set up a Custom Google Search[^]? It is great you can set filters on it so you get more relevant hits. I've set mine for code project, microsoft and my blog (because half the time I've done it before but I've forgotten how). I get most everything I want in the first couple of hits.
|
|
|
|
|
A well reasoned response, but I guess we'll have to remain in disagreement I've fixed too many programs/stored procedures where this caused problems; It is the equivilent of nails on a chalk board to me... it just makes my skin crawl.
As to this:
Colin Angus Mackay wrote: have you tried to set up a Custom Google Search[^]?
Really cool tip. I can't tell you how many times I wished they would add this feature. Truely appreciated. I'm going to try it out now.
Thanks.
topcoderjax - Remember, Google is your friend.
|
|
|
|
|
Hi,
I don't understand ur question. Give some examples.
If I am right , try this
When column is given NULL
Select isnull(<column>,0) from
When column is given NOT NULL
Select case <column> when "" then "0" else <column> end from
Senthil
|
|
|
|
|
any code sample : unicode into database by store procedure
|
|
|
|
|
Use NVARCHAR or NTEXT to pass the data.
|
|
|
|
|
I'm having difficulty setting up my databinding to allow for a new
record to be inserted. On one side of my form I have a grid with
just a ID and a name (lets say we're doing with People entities) and
on the other is a panel with say 10 diferent textboxes for various
fields (address, phone, etc..) Both the grid, and all detail
textboxes are bound to a bindingsource with a dataview as it's
datasource.
I want the user to be able to click a "New" button, add a new row to
the underlying datasource with it as the current selected row, in edit
mode. The user clicks new, and can begin entering data into the
various textboxes that are bound to the datasource.
My background datatable has various non-null, type, constraint
validations that fail for a new row. I've discovered I can suppress
this with DataTable.BeginLoadData(), but it just doesn't seem wise. I
need to add a "breaking" row to the table, but as soon as a call
Table.Rows.Add() (without the beginloaddata()) it fails immediately.
I can't seem to find an example that does not use a grid control for
in-line editing, or where you do not have the field values ahead of
time. Many examples and articles show this problem, but they require
non-bound controls the user populate and THEN click add new, then they
add the row with the appropriate values - this does not meet my
requirements.
|
|
|
|
|
Hi all,
Is there a datatype "column" in SQL SERver 2000?
If not can we generate user-defined datatype as "column"?
How to convert a variable to datatype column ?
Please reply asap..
Senthil
|
|
|
|
|
What exactly do you mean by datatype column. Is this an object? If you are talking about saving objects in sql I would suggest you serialize the object into xml and save the xml in a text column.
Ben
|
|
|
|
|
Hi,
Thanks for your reply.
I will give an example
Declare @i int
Set @i=1
while @i<=7
Begin
Update set <column> + @i = value
End
In the above example, column name will be dynamic , i.e., column1, column2.
So, I want to use dynamic query for updation. As dynamic query will hit my performance,I need to avoid dynamic query.
Is there any possible way for this?
There is a datatype called table . Similar to that, whether there is any datatype called column?
Senthil
|
|
|
|
|
Certainly if your table doesn't have very much data I would look at doing this in memory. If you are using .net there is a DataTable which you can dynamically create. There are DataColumns which you can dynamically add to the dataTable. Then you can take the devaultview of the datatable and use the rowfilter to query the table's data.
In sql 2000 and 2005 there are table variables, but they only stick around for the duration of the stored procedure so you probably want it to be around a little longer then that.
If there aren't too many rows and columns I would think seriously of keeping the data in an internal table in your program.
Ben
|
|
|
|
|
I am using such type of query in a stored procedure.
My stored procedure is compiled each time when it is executed.
I am in a position to remove those dynamic queries to get my stored procedure execute faster.
I hope u understood. If not I can explain some more.
Senthil
|
|
|
|
|
Perhaps it would be better to explain exactly what you are trying to do. Why do you need a dynamic query? Why do you need to create a table? If you dynamic query returns certain columns you can just return those columns in the result set and your program can read them. The only issue because how your program knows what to do with the columns.
Ben
|
|
|
|
|
Hi, I have a form that I am using (MS Access) and it needs to be able (when clicking a command button) to take a value from a text field, alter an already created table by adding a column with that text field name, but also populate that column with checkboxes. Right now I can add the column, with the Yes/No data type, however in the table, instead of checkboxes I have to give a value of 0 (false) or -1 (true). Is it possible for SQL to do this?
-
reegan41
|
|
|
|
|
I believe the "checkbox columns" you are referring to are a "feature" in MS Access where in the table view yes/no fields are displayed as checkboxes. I imagine Access allows this because it is a user/desktop database and editing table data directly is a desired feature. SQL Server is not a user/desktop database and SQL Server administrators often go to great lengths to prevent users from opening up a table and editing it. Microsoft also recommends not doing it as well. Hence, the checkboxes will not show when you open up a table to view the data. That said, you can write an application (which could be using MS Access as a front-end) that displays that data type as a checkbox.
|
|
|
|
|
If you are making the UI for the data entry yourself, you can use binary type and use values of 0 or 1 as Yes/No.
The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.
|
|
|
|
|
I have workgroup 2005 installed and when i right click on the replication folder a messagebox comes up:
TITLE: New Publication Wizard
------------------------------
Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Management+Studio&ProdVer=9.00.3054.00&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=21028&LinkId=20476
////////////////////////////////////
but when i run the setup again it says replication already installed.
any ideas will be much appreciated
|
|
|
|
|
Hello,
I am using a typed dataset and have created a query the same as the one below:
SELECT TaskID, Details<br />
FROM IncidentTask<br />
WHERE Type IN (@typeArray)
I have created the methods to return a data table and fill a data table.
My table adapter is called incidentTask.
I am using the code to get all the types that the user will want to query on, as below.
'Add the support type that have been checked<br />
If (Me.chkITPhoneSupport.Checked) Then<br />
typeList.Add("'IT Phone Support'")<br />
End If<br />
If (Me.chkITRemoteSupport.Checked) Then<br />
typeList.Add("'IT Remote Support'")<br />
End If<br />
If (Me.chkITEmailSupport.Checked) Then<br />
typeList.Add("'IT Email Support'")<br />
End If
I then put all these into an array called typeArray. And attempt to fill the data table. It works ok, if i only have one item, but if i have more than 1 it won't work, as the parameter in the query is only expecting a single value, not an array.
Me.TA_IncidentTask_DSJobSheet1.FillByCritera(Me.DsJobSheet1.IncidentTask, String.Join(",", typeArray))<br />
Is there any way I can send an array to a query,
Many thanks for your suggestions,
Steve
|
|
|
|
|
|
HI
plse help me How to bind data to dataset using Views in sqlserver2005...
I encountered this problem in crystal reports I want to bind to tables and create a report for
that two tables or is there any another way help me...
came out of hardwork
|
|
|
|
|
Hi,
please can anyone let me know the date formats in sql server for this query.
my table structure is"
PDate PMonth PYear
10 05 2007
03 05 2004
12 10 2007
o1 12 2005
now i have to retrieve the records based on some specified dates.
for example, i want the records which matches "12/10/2007"
i know how to write the code for these 3 columns to format (mm/dd/yyyy)using C# & vb.Net but i want to know how to write using Tsql
Thanks,
Suketh
|
|
|
|
|
Hi
Use the date functions like Month(), Day() and Year() to get month,date and year. Based on these functions you can filter records. For example
Month(DateColumn) will display month of the given datecolumn
Hope you got it...
Harini
|
|
|
|
|
use this query
select * from t1 where pdate=day('10/12/2007') and pmonth=month('10/12/2007') and pyear=year('10/12/2007')
Note= you should format the date as 'MM/dd/yyyy'
date function returns day('10/12/2007') as 12
shally
|
|
|
|
|
set dateformat dmy
Go
select * from Table
where
Pdate=select Datepart(Day,'12/10/2007')
and PMonth=Datepart(Month,'12/10/2007')
and PYear=Datepart(Year,'12/10/2007')
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
