|
The data is already in the db i've tried entering the fields that require just the time and not the date with:
1. only the time
2. the same date as the date only field + the time
the data all looks fine in the database itself
the problem is when the dataadapter fills my dataset (which is a auto generated typed dataset) the fields for the time only items still only show the date regardless if i entered it as 1 or 2 above
here's my stored procedure to look at too if that helps
ALTER PROCEDURE dbo.GetJobs
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS SELECT Job.JobID, Job.JobTitle, Job.JobTimeStart, Job.JobTimeStop
From Job
/* SET NOCOUNT ON */
RETURN
now running this stored procedure in vs returns only the time as they were entered into the db
now if i use the preview data option in vs for the data adapter suddenly that arbitrary date shows up but the time is gone.
also when i bind it to a datagrid control the same happens
when i tried binding one of the members that should return a time to a listbox it shows the same arbitray date plus the correct time.
so that tells me my stored procedure is working and even though it isn't showing everything the dataadapter is filling the dataset with the date and time for the db members correctly
so how do i change how the datagrid displays dates and times is it something i have to do at run time and if so what
Ryan
"Smile often!.... it distracts people while you load the gun."
|
|
|
|
|
Im not sure how much this would help, but have you tried the Format command when extracting the data?
Format(date, "hh:mm") as newdate {this is off the top of my head so its probally wrong, but should get you close}
Also does this help at all?
How to Set the Day/Month/Year Date Format in SQL Server[^]
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
|
|
|
|
|
Hey all,
I have DataRelation object between 2 tables & 2 DataGrids.
for some reasons, I delete the relation, activate an operation on the tables & then create the relation back. here is some code:
//before the operation starts:
Ds.Relations.Clear();
Ds.Tables["Cust2"].Constraints.Clear();
//after the operation
DataColumn dc1 = Ds.Tables["Cust1"].Columns["Id"];
DataColumn dc2 = Ds.Tables["Cust2"].Columns["Id"];
DataRelation Dr = New DataRelation("cust1to2", dc1, dc2);
Ds.Relations.Add(Dr);
everything is ok until now.
but when I tried to connect the tables to the datagrids:
DtGrd1.SetDataBinding(Ds, "Cust1");
DtGrd2.SetDataBinding(Ds, "Cust1.cust1to2");
I get the exception:
"Cannot have a relationship between tables in different DataSets"
does anyone know what is going on??
thanks,
RoyRose
|
|
|
|
|
HI:everyone
like this
declare @myid smallint
select @myid = 55
kill @myid -- it general some errors
How can i do it ?
POWER
|
|
|
|
|
Try:
declare @myid smallint <br />
select @myid = 53 <br />
<br />
declare @sql varchar(30)<br />
<br />
set @sql = 'kill ' + cast(@myid as varchar(20))<br />
<br />
exec (@sql)
Wout Louwers
|
|
|
|
|
Hi Guys..
I want to ask if there is a way to use *.sql files generated by MsSql Server (or written by someone )in the C#.
For e.g. i have a *.sql file and i want to create tables in my program using this script..
Is there any class in C# or u guys can give me a link ?
thx a lot..
good coding..!
|
|
|
|
|
|
hi all,
i have an procedure as follows:
ALTER PROCEDURE dbo.InsertNewSite
(
@param_url nchar(250),
@param_ip nchar(15),
@param_username uniqueidentifier,
@param_time datetime
)
AS
SET NOCOUNT OFF;
INSERT INTO sites
(url, ip_address, username, access_time)
VALUES (@param_url,@param_ip,@param_username,@param_time)
RETURN SELECT id FROM sites WHERE sites.url = @param_url AND sites.access_time = @param_time
and it is called from code like this:
string newSiteId = "";
lock (this.selectAllSitesNotBannedTableAdapter)
{
newSiteId = (string)this.selectAllSitesNotBannedTableAdapter.InsertNewSiteNotBanned(host, requestor, new Guid("bcadf8d7-c96d-4981-bf26-de9915ac6d49"), DateTime.Now);
}
but newSiteId always returns null?
now to explain the problem i am trying to solve with this code:
i get some data i want to store into the db, the db generates the GUID for the new row, and i then need that GUID in my application. so rather than reloading the entire datagrid, i just add the row to the grid that i just added to the db.
is it possible to get the GUID of the row just added, in one call to the db, or do i need to call it again with the select that is the return statement above ?
kind regards,
g00fy
|
|
|
|
|
Hi all !
in asp.net(using c#) i Plan this question ,and I think it related to this section , because i get no reply yet , Hope anyone help me !
consider this :
First Record is --> firstname :Joe , lastname :Meech
(in asp.net)i wrote this code for save and add :
For Add :
txtName.Text=String.Empty;<br />
txtFname.Text=String.Empty;
and For Save :
DataRow dr=dt.NewRow();<br />
dr["firstname"]=txtName.Text;<br />
dr["lastname"]=txtFname.Text;<br />
dt.Rows.Add(dr);<br />
da.Update(dt);<br />
dt.AcceptChanges();<br />
int RowTablePosition=0;<br />
RowTablePosition =dt.Rows.Count-1;
My difficult is :
if Page.DataBind() , and if want to add a new Record , and then Save this Record --> the First Record is added to the end of the table , No this New Record !!
But if Page or Text is not bind , this new Record is Saved !
i want to save , when "Page.DataBind();"
thanks if anyone help me !
mostafa hosseiny b.
|
|
|
|
|
hello to all ..
i am trying to install MS SQL SERVER 2000 on my pc ...
i have the Standard Edition
but when i run the setup i recive an error that say
that this program is not supported on my OS ..
i have Windows XP Pro , Server Pack 2 .
|
|
|
|
|
microuser_2000 wrote: when i run the setup i recive an error that say
that this program is not supported on my OS ..
i have Windows XP Pro , Server Pack 2 .
The error message is correct. SQL Server 2000 Standard Edition will NOT run on Windows XP.
See the SKU Matrix on the SQL Server 2000 Product Overview[^] page for more details.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hello again, everyone.
I'm querying a table of usernames, some of which have single quotes in them. I'm hesitant to make a stored procedure to perform a search and replace on the entire table, so I was trying to find a way to make the query tolerate the single quote using the REPLACE() method, but I haven't met with any success so far.
I've tried the following:
SELECT REPLACE(Name, ''', '%#39;') FROM Table
which it obviously doesn't like because of not having matching sets of single quotes. I've also tried replacing it with the ASCII equivalent to no avail, as well.
The particular error message I'm receiving in my ASP.Net page is:
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'BRIEN'. Unclosed quotation mark before the character string ''.
The record in question returns the string O'BRIEN.
It is worth noting that the operation is not aborted when this error happens, and it does in fact return the record in the proper format. It does, however, not return any records after that one, which exist.
Any ideas? Thanks in advance for your help.
-------------------
abort, retry, fail?
|
|
|
|
|
jszpila wrote: I'm querying a table of usernames, some of which have single quotes in them. I'm hesitant to make a stored procedure to perform a search and replace on the entire table, so I was trying to find a way to make the query tolerate the single quote using the REPLACE() method, but I haven't met with any success so far.
I'm not sure I understand the logic of your thinking. How would using REPLACE() tolerate single quotes?
It looks like the code is venturing into a high possibiliy of an Injection Attack. See SQL Injection Attacks and Tips on How to Prevent Them[^]
If you are issuing a SQL Statement from a .NET application like this:
SELECT * FROM MyTable WHERE Name = 'O'Brian' then you should use a paramter instead
SELECT * FROM MyTable WHERE Name = @Name
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hi
when i m working on reporting services' rdl file, following error is coming -
The table ‘Table1’ has rows that contain a different number of cells than the number of the columns in the table (including cells that span more than one column).
I think i m trying a bit too complex interface with rdl files, lot of merged cells are used, i may rpovide html prototype of the work i m doing but it'd b better for me if u can give me some hint by reading error only.
Thanks in advance
Utsav
|
|
|
|
|
I have a WebForm that will display paging data to customers.My purpose is
to let the customers can select any page number.The WebForm calls a stored
procedure in SQL2000:
ALTER PROCEDURE MyProc<br />
(@RecordPerPage int,<br />
@PageNumber int<br />
)<br />
AS<br />
select top @RecordPerPage *<br />
from MyTable<br />
where KeyField><br />
select Max(KeyField) from <br />
(select top @RecordPerPage*(@PageNumber-1) KeyField<br />
from MyTable<br />
order by KeyField) a<br />
order by KeyField
Unfortunately,this procedure has a syntax error.It seems that the 'TOP'
keyword can not be followed by an Expression.
How could I do?
I do not want to dynamicaly generate SQL string in client side(there are a dozen of reasons) and --
This is a large table,I will not use DataAdapter.Fill and DataGrid.AllowPaging.
Need your help!
Thanks a lot!
|
|
|
|
|
You should build your SQL statement as a nvarchar and then use sp_executesql:
<br />
DECLARE @query NVARCHAR(500)<br />
<br />
SET @query = N'select top ' + CAST(@RecordPerPage AS NVARCHAR(5)) + N'* from MyTable...<br />
<br />
sp_executesql @query <br />
look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
--> Nick
|
|
|
|
|
 hi
I am having problem in procedure i am getting error as 'Scope_Identity' is not a recognized function name.I read an article that @@IDENTITY should be used is it helpful.The procedure is for the inserting the details.
thanks
kal2na2
|
|
|
|
|
Sounds like you're using SQL Server v7 which does not support SCOPE_IDENTITY() - you must use @@identity however be careful, triggers will alter @@identity to reflect the result of the trigger and not your proc.
--> Nick
|
|
|
|
|
In queries I use the function, NVL quite a bit.
,nvl(some_column,'DefaultText')
Whenever some_column has a NULL value, the query always returns DefaultText for the column's value. Is there a smilar function for a varchar2 type column that would allow me to obtain DefaultText if some_column contained a string of all space characters of any length. I'm trying to develop a work around to a bug with a library that treats a column with all space characters as an empty string, regardless of how many space characters have been stored.
Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
We might end up with a large whack of code in stored procedures in our SQL2005 database and I was wondering if anyone knew of a code-coverage tool that could work against SPs? The SPs will be T-SQL and also use CLR.
I haven't looked into it that deeply but I am assuming the code-coverage tool in VS2005 can't do this.
regards,
Paul Watson
Ireland
Colib and ilikecameras.
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
|
|
|
|
|
Excuse my ignorance, Paul, but what is a code-coverage tool? Are you looking for version control, or a SQL editor, or some other animal?
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
OK let me see if I can explain this without looking it up again 
Code coverage presents metrics on how much of your code is covered by tests (e.g. unit tests.) The idea is that with a large code base you can't be sure the unit tests you have are testing all aspects of the code. So you run the code coverage tool against your code, including your tests, and it returns metrics on what is and what is not covered.
Within that you get all sorts of variations such as block coverage, conditional coverage, line coverage, method coverage etc.
I currently only know of code coverage tools that work against code in assemblies, not in SQL databases.
regards,
Paul Watson
Ireland
Colib and ilikecameras.
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
|
|
|
|
|
Hi all:
I'm pretty new at db programming, but over the past year have a come to a decent understanding of connections, data adapters, and datasets. Here's something though that I just can't think through.
Here's what I'd like to do.
1. My music collection and audio file information are in a MSDE database with several tables containing workid (PK), artist information, style, filename (location of the sound file), etc.
2. I created XML file to use as a playlist, something like
<playlist>
<workid>2</workid>
<workid>15</workid>
<workid>4</workid>
<workid>47</workid>
</playlist>
3. I saved the file something like saturdaynightmusic.xml
4. Using the Build Schema tool in VS Studio, I created a schema that joins the Music table with the XML playlist with workid as the key.
5. Now I have a .xsd file which contains the relationship, an .xml file to control the order of play, and a database table with fields pointing to the audio files, album and artist info.
However, I can't for the life of me, figure out how to make them work together.
Conceptually, I've got it - Read each element in the XML file, put them into a list (such as a list box), select first song in the list, it populates the fields with the artist info, title, then plays, the next song is selected, played .... until the end.
Here's how I think it could be done...
1. The XML playlist workid is stored as a variable.
2. The variable is added to a select command which is then run against a view or stored procedure
3. The database returns the information which is bound to textboxes, lists, and the filename variable to play the audio file.
4. Loop through the above steps until the end of the list
Am I on the right track???
Thanks for any help in clarifying this for me.
Norm
-- modified at 17:58 Tuesday 15th November, 2005
|
|
|
|
|
Hi,
I have two objects, both point to each other in OO.
e.g.
Class Father
{
private int id; //id of father
private Son son;
}
Class Son
{
private int id; //id of Son
private Father father;
}
so I need to have two tables in database as follow,
Table Father :
(id, SonId)
Table Son :
(id, FatherId)
The problem is, should I eliminate the one of the reference in one of the table, e.g.
Table Father :
(id)
Table Son :
(id, FatherId)
so I just have the fatherID in the son table.
But if I remove the sonID from the father table,
I need to join the two tables when I retrieve fathers
from the father table.
So what are the pros and cons of the two schema design ?
Thanks
|
|
|
|
|
Analyze a bit...
Without going into much trouble.. the relationship between father and son tables is as follows: A Father can have 1 or more sons, but a Son can only have a father at any one time. If you follow this, then only having idFather in the son table it's more than enough to make the searches you need.
Anything else you need to add?
daniero
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
