|
Hi,
I have 2 problems:
a) cannot connect to my sql table eventhough I used the same userid and pwd in Query Analyzer . I keep getting 'Not associated with a trusted SQL Server connection'
b) which date format I have to use to lookup for a row (from a search textbox)
Here is my code:
<%@ Import Namespace="System.Web.UI.WebControls"%>
<%@ Import Namespace="System.Data.SQLclient"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb" %>
Sub LookupDate (ByVal s As Object, ByVal e As EventArgs)
Dim myconn As SqlConnection
Dim strSelect As String
Dim cmdSelect As SqlCommand
myconn = New SqlConnection("server=localhost;UID=myUSERID;PWD=myPASSWRD;Database=myDBNAME")
strSelect = "SELECT [col1], [col2], [myTime] FROM [myTableName] WHERE myTime = @myTime ORDER BY [myTime] desc "
cmdSelect = New SqlCommand(strSelect, myconn)
cmdSelect.Parameters.AddWithValue("@myTime", textMyTime.Text)
myconn.Open()
lblVal.Text = cmdSelect.ExecuteScalar()
'lblVal.DataBind()
myconn.Close()
End Sub
<title>title
Date Lookup
<asp:textbox id="textMyTime" runat="server">
<asp:button text="search" id="search" onclick="LookupDate" runat="server">
<asp:label id="lblVal" enableviewstate="False" runat="server">
|
|
|
|
|
For some reason i got cutoff so here is my question again:
<%@ Import Namespace="System.Web.UI.WebControls"%>
<%@ Import Namespace="System.Data.SQLclient"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb" %>
Sub LookupDate (ByVal s As Object, ByVal e As EventArgs)
Dim myconn As SqlConnection
Dim strSelect As String
Dim cmdSelect As SqlCommand
conValid = New SqlConnection("server=localhost;UID=myUSERID; PWD=myPASSWRD;Database=myDBNAME")
strSelect = "SELECT [col1], [col2], [myTime] FROM [myTableName] WHERE myTime = @myTime ORDER BY [myTime] desc "
cmdSelect = New SqlCommand(strSelect, myconn)
cmdSelect.Parameters.AddWithValue("@myTime", textMyTime.Text)
myconn.Open()
lblVal.Text = cmdSelect.ExecuteScalar()
'lblVal.DataBind()
myconn.Close()
End Sub
<title>title
Date Lookup
<asp:textbox id="textMyTime" runat="server">
<asp:button text="search" id="search" onclick="LookupDate" runat="server">
<asp:label id="lblVal" enableviewstate="False" runat="server">
|
|
|
|
|
When you start Query Analyzer, tick the "Windows Authentication" option. You should need to input a user ID and Password for a trusted connection.
I would question the data-type of your parameter. If you use a datetime type and cast your text to a datetime variable then you shouldn't have to worry about the format.
Also, you probably should be using ExecuteScalar because your select statement is returning several columns, and looks like you want to return several rows too.
|
|
|
|
|
in SQL Analyzer, I was able to connect and login under SQL auth. I also able to login using Window Auth. but my query comes back empty. In another code, I used
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dbReMUSConnectionString %>"
="" selectcommand="SELECT .................... ">
on the same db and I had no problem.
I also using vs 2005 and the db is a sql 2000 db
Thanks
|
|
|
|
|
where the rest of my asp tag goes? Do I need to put something around them?
|
|
|
|
|
hi
how should i retrieve View's SQL statement in SQL Server.
i am using OleDb and i want it to be compatible with all the data Systems.
for example i can retrieve the Query statements from an Access database (with GetOleDbSchemaGuid() ) but i am not able to do it with SQL Server (it dowsnt support retrieving views that way).
is there another way or a specific SQL statement to do so??
thanks in advance
-- modified at 6:20 Monday 26th February, 2007
|
|
|
|
|
I used profiler to create a trace of all the queries from SSMS to the database and came up with this:
<br />
SELECT ISNULL(smv.definition, ssmv.definition) AS [Definition]<br />
FROM sys.all_views AS v<br />
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id<br />
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id<br />
WHERE (v.type = 'V')and(v.name=N'{my_view_name}' and SCHEMA_NAME(v.schema_id)=N'dbo')<br />
Just replace {my_view_name} with the name of the view you're interested in. Also, if the view is using a schema other than 'dbo' make sure to change that as well.
|
|
|
|
|
Hello everybody,
I have a Attendance table, which has columns Date. ShiftInTime, ShiftOutTime, EventTime, Login_Type.
Now for every EventCode= "I" or "O" there are two records. That means for a day say 07/10/2006 as Workdate there are two records like,
EmpID Date EventTime EventCode
1 07/10/2006 9:00 I
1 07/10/2006 17:15 O
NOW.. I want a report in Table or Crystal report which will have a single record for a single Workdate..as folllows
EmpID WorkDate ShiftInTime ShiftOutTime InTime OutTime
1 07/10/2006 09:00 17:00 9:00 17:15
Can you please help me out..I'm usng a table in ASp
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
select * from table t1 inner join table t2
on t1.date = t2.date
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com
|
|
|
|
|
Sir,
U'r suggestion is not enough...because i am using onlu only table ,,,my quear gave me theattendance report like i shown above...but Can i simply make the inner join with the same table?
i need the out put as follows
----------------------------------
ID |Name |InTime |OutTime|
1 |xxxxxxxxxxx| 09:05 | 06:01
2 |sdsssdddddd| 06:15 | 06:15
-----------------------------------
Like that..But at present they are displaying as i explainned in my first question.
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
Try this:
SELECT main.EmpId, main.Date,
(SELECT i.EventTime FROM Attendance i
WHERE i.EmpID = main.EmpId AND
i.Date = main.Date AND i.Login_Type = 'I') AS ShiftInTime,
(SELECT o.EventTime FROM Attendance o
WHERE o.EmpID = main.EmpID AND
o.Date = main.Date AND o.Login_Type = 'O') AS ShiftOutTime
FROM Attendance main
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thanks for u'r advide,
But I am using only one table namedv History with following tables
EmpId,EmpName,ShiftIn,ShiftOut,EventTime,LoginType From Event Time I am extracting the values based on the LoginType,,Sir u got me...That is only one table is there....(History)....One doubt regarding that u specified the main,i,o as defrent tables or not?
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
i, main and o are not different tables. They are all table aliases. If you look at the syntax carefully, you will see that this is a way to use items from the same table in sub-items. Change the references from Attendance to History, but leave the table aliases alone.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,
I tried but i am getting the error message as "At most one record can be returned by this sub query" ?
This is the code thai i tried
SELECT main.UserName, <br />
(SELECT i.EventTime FROM History i <br />
WHERE i.FuncCode="0") AS InTime, <br />
(SELECT o.EventTime FROM History o <br />
WHERE o.FuncCode="10") AS OutTime <br />
FROM History main
Any chance of mistake in this code?
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
That'll teach me to do it away from SQL Server. Here you go:
SELECT
h.EmpID,
h.[Date],
h.EventTime AS ShiftInTime,
h2.EventTime AS ShiftOutTime
FROM
History h
LEFT OUTER JOIN
History h2
ON
h.EmpID = h2.EmpID AND
h.[Date] = h2.[Date] AND
h2.EventCode = 'O'
WHERE
h.EventCode = 'I'
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,
Some changes i made from u'r query ..any way u'r concept worked here,,,Thanks for u'r assistance sir
This is the code i used.....
SELECT h.UserName, h.EventDate, h.EventTime AS InTime, h2.EventTime AS OutTime, h.Dept<br />
FROM History AS h LEFT JOIN History AS h2 ON (h.UserName = h2.UserName) AND (h.EventDate = h2.EventDate)<br />
WHERE (((h.EventDate)=#2/24/2006#) AND ([h.FuncCode]="0") AND ([h2.FuncCode]="10") AND ((h.Dept)="Engg"));
Thanks and Regards
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
i have 3 columns in my table the room,timeStart,timeEnd.
For example i have inserted this data
room: room1
timeStart: 2007-03-02 8:00 AM
timeEnd: 2007-03-02 3:00 PM
Here's the data that will not be accepted if try to insert
room: room1
timeStart: 2007-03-02 9:00 AM
timeEnd: 2007-03-02 4:00 PM
because the timestart is between the timeStart and the timeEnd. Room1 will only be occupied after 3:00 PM.
Can you help me with this?.
Thank you very Much
Bernie
|
|
|
|
|
Hmm. You and this guy Clickety[^] seem to be trying to solve the same problem. Coincidence? Homework?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
please help me getting a portable data access layer code which independent of language
thanks in advance 
Tony
|
|
|
|
|
tonyalex wrote: please help me getting a portable data access layer code which independent of language
Have you looked at NHibernate? It will help you build a language independent DAL.
|
|
|
|
|
Tony
DevExpress Persistent Objects is pretty good. I've used it quite a bit now and I have to say that I like it.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sorry,
What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........
|
|
|
|
|
tonyalex wrote: What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........
What DAL source code? Microsoft does not supply DAL source code.
|
|
|
|
|
yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER
|
|
|
|
|
tonyalex wrote: yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER
That is part of the Enterprise Library. It is not a DAL, it is designed to help you create a DAL more easily.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
