|
I am using the Query Builder in Visual Studio to fill my Dataset but one of the field/column names is unknown until runtime; therefore I wish to specify this on the fly.
My current query is as follows:
SELECT Cards.Id, Cards.Card, Cards.FirstName, Cards.LastName, Cards.AccessLevel, Cards.StartDate, Cards.EndDate, Cards.fTitle, ?,<br />
Photo.Id AS Expr1, Photo.Photo,<br />
AccLevel.AccessLevel AS Expr2, AccLevel.Name AS AccessName<br />
<br />
FROM (Cards<br />
LEFT JOIN Photo ON<br />
Cards.Card = Photo.Id)<br />
LEFT JOIN AccLevel ON<br />
Cards.AccessLevel = AccLevel.AccessLevel
The question mark represents a variable field name. Below is the code which fills the dataset by calling the query above - although for now, I have passed it a fixed string which represents one of the fields in the ‘Cards’ table and should replace the question mark in the query:
cards_Photo_AccessLevelTableAdapter.Fill(MyDataSet.Cards_Photo_AccessLevel, "Cards.fDepartment");
Although my code builds ok, an exception is caused when the Dataset is filled as above.
Any help would be much appreciated!!
Regards,
Christian
|
|
|
|
|
hi
i want my sql server database save the data like
12:12:12 PM..Database must contain only time value nt default date value.
currently it saved data like 1/1/1900 12:12:12 PM and i want it save as 12:12:12 PM
plz give me solution to do this.
|
|
|
|
|
A datetime field stores a whole date and time. You can't store just a time.
There are functions in T-SQL for extracting time-specific information from a datetime value.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hey,
Unfortunately you cannot save the dateTime column without date. However you can extract them using the convert function in SQl Server.
e.g:
select convert(varchar,DateColumn,108) from MyDateTest99
The following table gives you the details of which digit sequence to use:
0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM
Hope that this helps...
Regards,
Ali
|
|
|
|
|
Hello
I wish to bound 3 columns to a combobox in a win application. Column names are A, B and C. I can bind column's items but i cannot bind the columns themselves. When i was click the combobox, choose one of the columns(A, B, C) and click to button, datagridview must show me the selected column's items. What's this code? Thanks..
|
|
|
|
|
hi there,
i m trying to retrieve data from one table with 2 condition in 2 queries, here is an example
select id, count(Number) from myTable where Number < 10 group by id order by id
select id, count(Number) from myTable where Number >= 77 and Number <=100 group by id order by id
the result is coming what expected but i want to run only single query for single result
for example
the result is coming :
QUERY 1
ID----(Column Name)
10----66
14----3
19----44
30----22
QUERY 2
ID----(Column Name)
30----45
40----74
55----43
60----44
what i want :
ID----(Column Name)----(Column Name)
10------66--------------0
14------3---------------0
19------44--------------0
30------22--------------45
40------0---------------74
55------0---------------43
60------0---------------44
i used join but didnt got it work
any idea?
thanks
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
Are you using Sql Server 2005?
Regards,
Rob Philpott.
|
|
|
|
|
yupp!!!
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
Cool.
There may be a better way to do this, but here's what I'd do. In the old world you could create a view to represent each query and then join them together, but you can use Common Table Expressions in 2005 (http://msdn2.microsoft.com/en-us/library/ms190766.aspx[^]) which are similar.
So, create the two queries as you have them with the with keyword. Then to combine the two results set you want a full outer join with an isnull to check for null ids resulting from the join and to set nulls to zeros on the count.
Does that make sense?
Regards,
Rob Philpott.
|
|
|
|
|
yeah, i think i have to pick my old books, i will try and thanks 
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
SELECT Dat
FROM dbo.tblkmreading
WHERE DAT = GETDATE()
i have records pertaining to current date , the above query is not fetching the records.Please rectify it
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
Thie GETDATE() Function returns the time now ie: 3/30/2008 12.35.05...
You will need to parse the GETDATE() result to return just MM/DD/YYYY and use his in your WHERE clause.
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
select dat from tblkmreading where dat = convert(varchar(10) , getdate() , 103)
still getting the following error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
dat is a datetime type
this is the another way i did , but no record is comming
select dat from tblkmreading where dat = convert(datetime , getdate() , 103)
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
Is the data stored as DateTime dataType in the database? You cant expect that your query will run at the exact time that a record is written.
In this case you may have to change your Where clause to BETWEEN and look from 12:00:00am to 11:59:00pm. Or Between 3/31/2002 12:00:00 And 4/1/2008 12:00:00
Sorry, I dont have a query editor handy to check this out 
DECLARE @Start DateTime
SET @Start = '3/31/2008'
DECLARE @End DateTime
SET @End = '4/1/2008'
SELECT [Date] FROM TestData
Where [Date] Between @Start and @End
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Try this
declare @Now datetime
select @Now = convert(varchar,getdate(),103)
SELECT Dat
FROM dbo.tblkmreading
WHERE DAT >= @Now
I suspect your Dat column contains a time as well as a date. The above code trims off the time from getdate.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
dat is of datetime type
still occuring the error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
What version of SQL Server are you using? My code works fine with 2000 and 2005.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
If Dat is date time format in your table then use following query
SELECT Dat
FROM dbo.tblkmreading
WHERE convert(varchar,Dat,103) = convert(varchar,GETDATE(),103)
If Dat is varchar in your table then use following query
SELECT Dat
FROM dbo.tblkmreading
WHERE convert(varchar,convert(datetime,Dat),103) = convert(varchar,GETDATE(),103)
smile
|
|
|
|
|
iam using oracle 10g and iam getting this problem.plz tell me steps that how could i solve this problem
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
|
|
|
|
|
Hai,
I am hari,
I have a .MDF file, i want to restore this file into sqlserver 2005. How to restore this file give me clear process.....urgent please
thanks
pinna
|
|
|
|
|
Right click on "Databases" and select "Attach". Follow instructions from there on.
|
|
|
|
|
Hai,
Thanks for your reply its working
Thanks
pinna
|
|
|
|
|
please i need help 
i have teble withe the flowing colums (slotid,sectionid)
the primery key is bothe i have the sectionid only
and i want to update the slotid withe diffrent values for the same section
i use vb.net and i do not now how i spend 2 week to fand slotion please helpe if you can i use diffrent ways 
|
|
|
|
|
Hi,
I have a datatable with 3 expression column bind to datagridview with a bindingsource but the column are not displayed in the datagridview.
when i debug, it was throwing a StronglyTypedException and the innerexception is InvalidCastException "can not convert dbnull to decimal".
Please help.
Thanks in Advance
Lookman
|
|
|
|
|
this question isnt about SQL, however if you are adding rows through SQLDataReader then you will need to check the column weather its null or not, if not then add
use this for checking null value :
DR.IsDBNull( <Column index> )
hope it will help
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
