Can't select a datetime field in a query without time portion

Hi,
I am doing some project with SQL Server 2000 and C#. I am using a
SELECT statement to get a column and to bind it to a dropdown list. The
column is a datetime format. I don't need time portion of it. Before I
tried any CONVERT functions my query looked like this:
SELECT DISTINCT DateEntered FROM TBL_PTM_Report
I can get the data this way but only with time portion in it. I read
about CONVERT but can't get it working. I am getting all kinds of error
messages from Visual Studio.
I tried the lowwoling:
SELECT COVVERT(CHAR(10), DateEntered, 101) FROM TBL_PTM_Report
It didn't work. I need data in the following format MM/DD/YYYY
Thank you,
Stan> SELECT COVVERT(CHAR(10), DateEntered, 101) FROM TBL_PTM_Report
CONVERT.
AMB
"suslishe@.mail.ru" wrote:

> Hi,
> I am doing some project with SQL Server 2000 and C#. I am using a
> SELECT statement to get a column and to bind it to a dropdown list. The
> column is a datetime format. I don't need time portion of it. Before I
> tried any CONVERT functions my query looked like this:
> SELECT DISTINCT DateEntered FROM TBL_PTM_Report
> I can get the data this way but only with time portion in it. I read
> about CONVERT but can't get it working. I am getting all kinds of error
> messages from Visual Studio.
> I tried the lowwoling:
>
> SELECT COVVERT(CHAR(10), DateEntered, 101) FROM TBL_PTM_Report
> It didn't work. I need data in the following format MM/DD/YYYY
> Thank you,
> Stan
>|||This was my typing mistake, sorry! The statement looks like this:
SELECT CONVERT(CHAR(10), DateEntered, 101) FROM TBL_PTM_Report|||I just ran this in query analyser and everything works! But Visual
Studio gives me an error.

Can't seem to fix the column width

I have a matrix report. I put a rectangle with two text boxes - inside
the column group. Works great!
However - now I can't seem to fix the width of the columns. It just
grows and ignores the property. I have tried to set it for each
individual cell in that matrix. And I have tried to specify the size..
Any idea?
ThxI am not sure when the column width grows. Does this happen when authoring
the report or when you render it? Would you please add some more detail
reproduction steps to your description.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407150710.6351cec1@.posting.google.com...
> I have a matrix report. I put a rectangle with two text boxes - inside
> the column group. Works great!
> However - now I can't seem to fix the width of the columns. It just
> grows and ignores the property. I have tried to set it for each
> individual cell in that matrix. And I have tried to specify the size..
> Any idea?
> Thx|||The reproduction step is:
Create a matrix.
Add a field to the column group textbox.
Fix column width - by selecting Cannot Grow.
Fix the Cell width - by selecting cannot Grow.
Works great!
Now add a rectangle inside the column group textbox...
The 'Cannot grow' fails...|||CanGrow only applies to vertical growth, not horizontal growth. However, the
only thing that grows horizontally is autosized images and matrices (per
column group instance).
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407181543.300fff7c@.posting.google.com...
> The reproduction step is:
> Create a matrix.
> Add a field to the column group textbox.
> Fix column width - by selecting Cannot Grow.
> Fix the Cell width - by selecting cannot Grow.
> Works great!
> Now add a rectangle inside the column group textbox...
> The 'Cannot grow' fails...

Can't refer to Column

Hello I have this query:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1') AND (TOTBalance > 0)
GROUP BY ClientCred.ClientID
In the where clause, I need to filter out all results that have a balance
greater than 0, so I refer to the column I created in the select clause. I
get 'Invalid Column Name TOTBalance'. Can someone please explain why this
does not work and what a solution would be? Performing the SUM in the where
clause gives an error about having an aggregate in the where.
Thanks in advance!Because server will evaluate the Where clause first.
Try this:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1') AND (SUM(ClientCred.Balance) > 0)
GROUP BY ClientCred.ClientID
Perayu
"Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
news:uZ1sA1JtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Hello I have this query:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1') AND (TOTBalance > 0)
> GROUP BY ClientCred.ClientID
> In the where clause, I need to filter out all results that have a balance
> greater than 0, so I refer to the column I created in the select clause.
> I get 'Invalid Column Name TOTBalance'. Can someone please explain why
> this does not work and what a solution would be? Performing the SUM in
> the where clause gives an error about having an aggregate in the where.
> Thanks in advance!
>
>|||WHERE clauses deal with individual rows underlying the aggragate query;
if you need to refer to the aggragate, use HAVING instead:
SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
FROM ClientCred INNER JOIN
Clients ON ClientCred.ClientID = Clients.ClientID
WHERE (Clients.Active = '-1')
GROUP BY ClientCred.ClientID
HAVING SUM(ClientCred.Balance) >0
HTH,
Stu|||Thanks Stu, this works!
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1126202094.969185.94760@.g44g2000cwa.googlegroups.com...
> WHERE clauses deal with individual rows underlying the aggragate query;
> if you need to refer to the aggragate, use HAVING instead:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1')
> GROUP BY ClientCred.ClientID
> HAVING SUM(ClientCred.Balance) >0
> HTH,
> Stu
>|||Still doesn't work Perayu, can't have aggregate in where clause.
Thanks for the reply Stu's works.
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:uc68I5JtFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Because server will evaluate the Where clause first.
> Try this:
> SELECT ClientCred.ClientID, SUM(ClientCred.Balance) AS TOTBalance
> FROM ClientCred INNER JOIN
> Clients ON ClientCred.ClientID = Clients.ClientID
> WHERE (Clients.Active = '-1') AND (SUM(ClientCred.Balance) > 0)
> GROUP BY ClientCred.ClientID
> Perayu
> "Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
> news:uZ1sA1JtFHA.3080@.TK2MSFTNGP15.phx.gbl...
>

Cant insert the value NULL into column

I'm trying to modify the data type in a field on a table. I rec'd this message:
Unable to modify tblship_sched
Can't insert the value NULL into column mfg_qty table qc.dbo.Tmp_tblship_sched; Column doesn't allow nulls.
Insert fails.

What is or where is the Tmp table?

Thank you.I am not sure what you mean when you ask where the table is.

SELECT * FROM SYSOBJECTS WHERE NAME = 'Tmp_tblship_sched'

If the table exists then I suggest you check the column constraint for the table in question. Looks like it has a NOT NULL constraint.

You can disable it for your insert.

Good Luck!

Cant insert Decimal from textbox

Hi guys.

Hopefully someone can help me, im pulling out my hair over this!

Im trying to insert a price into my MSSQL database.

The Column type is decimal.

If i use the following simple script

<%@. Page Language="VB" Debug="true" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Sub Submit_Click(Sender As Object, E As EventArgs)
Dim sqlCmd AS SQLCommand
Dim sqlConn as SQLConnection = New SqlConnection("************;")
Dim mySQL as String = "Insert Into Products (Delivery, Price) Values (" & Dvalue.Text & ", " & Pvalue.Text & ")"
sqlCmd = New SQLCommand(mySQL,sqlConn)
sqlConn.Open()
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
End Sub
</script>
<form action="" runat="server">
Delivery - <asp:TextBox ID="Dvalue" runat="server" /><br>
Price - <asp:TextBox ID="Pvalue" runat="server" /><br>
<Asp:Button id="Submit" runat="server" text="Insert" onclick="Submit_Click" />
</form
And add in the values as
Delivery - 12.9999
Price - 56.777

The resulting numbers on the database are
Delivery - 13
Price - 57

Im using 1and1 to host my sql database and using their own SQL admin tool to configure it.

The price column has the following options

Name - Price
Type - Decimal
size(if char) - 19 ( i cant seem to change this)
default value - <blank> (I cant seem to change this)

No other options are ticked (as in nullable, indexed, unique key, primary key, identity)

Is there some setting or something ive missed/ dont know about? (ive only really used access and mysql in the past)

I would really appreciate any helpFirst, I woulduse parameters.

Next, run SQL Profiler and look at what you are actually sending to the database.|||Thanks for the quick reply :)

I cant use SQL Profiler as its a shared account over at www.1and1.co.uk
(I cant seem to even run proper SQL statements that alter tables etc)

The script im trying to fix uses a dataset and the update command, but has the exact same problem as the little example of my woe script i posted here.

Im trying to figure out if its me whos to blame, or some setting on the SQL database i need to annoy them to fix.|||What is the scale set to on your table? Precision will likely be 18, but the default scale is 0, and so you will loose anything to the right of the decimal.|||Im going to call them now. Their administration thingy isnt exactly great.

Thanks for your help|||Suffice to say they dont know whats wrong and ive been put on the mighty waiting list.
They suspect it might to be with the server being German and using , instead of . for the decimal.

So i tried both using their control panel by entering in the SQL for it

Insert Into Products (Price, RRP, Delivery) values ('12.99', '12.99', '22.22')
Which rounded it up again.

There isnt any way i can change the precision from what i can see, so ive had to nag them.

Thanks again for your help.|||I would try to create a table like so (using SQL Script):

CREATE TABLE [dbo].[DecimalTestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[DecimalTest] [decimal](18, 5) NULL
) ON [PRIMARY]

Now, if you can insert properly into this table (up to 5 decimal places) then that is your issue.|||I just ran the SQL statement on it

Insert Into DecimalTestTable (DecimalTest) Values ('12.99999')

And Sucess!

12.99999

However, when i try it using their "add new row" it comes up as 129999!

No wonder im confused...

Suffice to say im going to throw their admin tool in the bin and recreate all my tables (nothing much important on them at this stage)

Thanks again, you saved my hairline!

Can't index functinon-based column

Hello, I am trying to add an index to a column based on a formula that
is a function. When I try to do so, I get the error:
'Line' table
- Unable to create index 'IX_Line_LineText'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
create index. Object 'fn_GenerateLineText' was created with the
following SET options off: 'ANSI_NULLS.'.
But when I created the function, the top part looks like this, so I am
by the message:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fn_GenerateLineText
(
@.LaborLineId int
)
***
I have to index this column. How can I do it?
-KJYou'll need to ensure that
SET ANSI_NULLS ON
when you create the underlying table
Also I believe you need "with schemabinding" option for your function

cant increase column size

hey

i've a db running sql server express sp2. the db size now is 1.1 gb
i've a table with a varchar column of size 20 . when i try to increase the column size to 50 i get a
timeout exception, and the the cloumn size is unchanged. this table has 2.5 million records

i use sql server management studio express to do the changes

is there a way to increase this timeout or whtever i can do to update this column size?

thx in advance

I am guessing you are using ssms and the GUI to edit this column. There are occasions when you use the GUI that sql server doesn't choose the most efficient way of executing a task. In this instance, i would hazard a guess that it will be creating a temp table with the new schema and then dumping the records in to it before dropping your table and renaming the new temp one to the original one. (stay with me!!)

If you just try,

ALTER TABLE tbl
ALTER COLUMN col1 VARCHAR(50)

Hopefully, that will be quicker.

|||well strangely thats actually works
weird how the gui can be soo stupid in such a simple task

thx man
|||

There is subtle difference doing this task from GUI and from T-SQL.

From T-SQL:

It alters the existing table to modify that column alone.

From GUI:

1) Creates a new tmp table with the new structure and inserts the data from the table you are modifying with insert select clause.

2) Drops the table you are modifying.

3) Renames the new tmp table to the old table's name.

You could see this for yourself if you turn on SQL Profiler and try to modify the table with T-SQL and from GUI.