How to conver 24 Hrs. time into 12 Hrs. time

hi
plz help me i have a varchar time like '18:30:15' and i want to convert it into '06:30:15 PM' format
plz help meLook convert statement with it's formats

How to control the loading of SqlDataSource?

I have an SqlDatSource that I have fully setup at design time, but I don't want it to open and load as soon as the page loads, instead I want to open it based on a condition.

How do you do that with a DataSource?

I know that I can simply remove the Select query, and then set it at run time, but I'm looking for something better, that allows me to have the Select query set at design time, in part because I have a lot of parameters.

Usually you have a control such as GridView or DetailsView that is connected to the SqlDataSource through its DataSourceId attribute. This will cause it to execute the query. If you leave the attribute empty the query will not be executed. From code you can set the DataSource and then call DataBind(). That will cause the SqlDataSource to execute:

GridView1.DataSource = SqlDataSource1;GridView1.DataBind();// Now the query is executed

|||

Within the SqlSataSource.Selecting event, you will have access to the Cancel property which you can set based on your conditions. If Cancel is set to True, then the Select event will not occur.

|||

while michielvoo's way is probably better, another way you can get the date to not display is to just set the GridView's Visible="false" initially, if you want the datasourceID property of the sqldatasource to be defined in the contol creation. Then, in your button_click event or whatever you do to get the data, you do:

VB:

GridView1.DataBind()
GridView1.Visible = True

C#:

GridView1.DataBind();
GridView1.Visible = True;

How to Control Print Button in SP2 ?

I want control print button for user to use print button one time per one
user account
How i do ?
Thank youHi,
You can control the print button with the report viewer style sheet
"htmlviewer.css".
Change the style
.ToolbarPrint
{
display: inline;
}
to
.ToolbarPrint
{
display: none;
}
Make sure u take a backup of the existing css and give a new name like
"noprint.css".
So have a logic in your system where you have a printer counter in the user
session, and if the counter is > 1, pass the following parameter to the
report URL
"rc:StyleSheet=noprint"
eg:
<report url >&rc:StyleSheet=noprint
This will hide the print button from the viewer.
Hope this will give a workaround to solve the problem.
rgds,
Kolitha
dkolitha@.gmail.com
www.dkolitha.com
"Zong" wrote:
> I want control print button for user to use print button one time per one
> user account
> How i do ?
>
> Thank you
>
>

How to connect to sql server on another machine through asp.net

Hi All,

I have been struggling with this problem for quite some time. Basically
i have an asp page (on the ISP server) that I want to connect to a sql
server on another machine (on my server) and I have been trying to
figure out how to do this. I tried to connect using an ip address in
the data source and it did not work. The asp.net code looks something
like this:

Conn.Open ("Provider=SQLOLEDB;Password=xxxx;Persist Security
Info=True;User ID=xxxx;Initial Catalog=mssqlxxx;Data
Source=xx.xxx.xxx.xxx,1433;Network Library=dbmssocn")

That did not work. Gave me an access denied or sql server does not
exist error. I tried playing around with or without the 1433 port,
still no luck.

Is there something I need to set up on sql server in order for me to
connect to this?? Can someone give me some suggestion how i can connect
to another computer?

My ISP offers sql server support and they give me an ip address to
connect directly to the sql server, code looks something like this:

conMyData = New SqlConnection( "Server=xxxxxxxxx.registeredsite.com;
UID=userxxxxxx; PWD=xxxxxx;Database=samplecom" )

now, my question is, how do I set up my sql server to have an ip
address so i can connect??

so i see 2 possible w ays to do this but i have no clue to make this
work. Any help is appreciated.This article might be useful:

http://www.aspfaq.com/show.asp?id=2126

Simon

how to connect to SQL server express 2005 database at the same time from both SSMSE and VB2005 a

Here is the situation:

I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.

I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String
Dim builder As New SqlConnectionStringBuilder
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim parameter As SqlParameter


builder("Data Source") = ".\SQLEXPRESS"
builder("Initial Catalog") = ""
builder("AttachDbFilename") = "C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf"
builder("Integrated Security") = True
builder("User Instance") = True
lconnectionString = builder.ConnectionString

Dim sqlConnection1 As New SqlConnection(lconnectionString)

cmd.CommandText = "SP_add_collection"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()
*******************************************************************************************************************

It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )

If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()

I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?

Thanks very much !

apple

I'll go out on a limb here and assume that you've attached the .mdf to SSMSE which is essentially just a pointer to the db. It is not technically a SQL database.

When you're connecting the .mdf through code, use the same connection as if you were connecting to MS Access.

If you have a stored procedure that uses the .mdf, use a sql connection.

Good luck,

Adamus

|||yes, i attached the .mdf file to the SSMSE. and i call the stored procedures in the VB application.

I want to see the result of data change of the VB application in the SSMSE right away, while the application updates the date in the table. is it possible ?|||

Yes it's possible but you have to connect directly to the .mdf not SSMS.

Adamus

sql

how to connect to SQL server express 2005 database at the same time from both SSMSE and VB20

Here is the situation:

I have SQL server express 2005 installed on my pc as instance SQLEXPRESS.

I have created a Visual Basic applicaion with the following as connection to the SQL server express 2005 running on the same PC:
****************************************************************************************************
Dim lconnectionString As String
Dim builder As New SqlConnectionStringBuilder
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim parameter As SqlParameter


builder("Data Source") = ".\SQLEXPRESS"
builder("Initial Catalog") = ""
builder("AttachDbFilename") = "C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf"
builder("Integrated Security") = True
builder("User Instance") = True
lconnectionString = builder.ConnectionString

Dim sqlConnection1 As New SqlConnection(lconnectionString)

cmd.CommandText = "SP_add_collection"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlConnection1

sqlConnection1.Open()
*******************************************************************************************************************

It seems that i can not connect to the abc.mdf in SSMSE while the VB program is running. (ERROR:
Database 'C:\My Documents\Visual Studio 2005\Projects\abc\abc\abc.mdf' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (.Net SqlClient Data Provider) )

If i connect to the abc.mdf first in SSMSE, then run the VB program afterwards, it gives me the error on this line -- sqlConnection1.Open()

I want to be able to access the abc.mdf database with both SSMSE and VB at the same time. Could anyone help me on this ?

Thanks very much !

apple

I'll go out on a limb here and assume that you've attached the .mdf to SSMSE which is essentially just a pointer to the db. It is not technically a SQL database.

When you're connecting the .mdf through code, use the same connection as if you were connecting to MS Access.

If you have a stored procedure that uses the .mdf, use a sql connection.

Good luck,

Adamus

|||yes, i attached the .mdf file to the SSMSE. and i call the stored procedures in the VB application.

I want to see the result of data change of the VB application in the SSMSE right away, while the application updates the date in the table. is it possible ?|||

Yes it's possible but you have to connect directly to the .mdf not SSMS.

Adamus

How To Connect To Sql Server

Hi

Im trying to develop an information system using CRYSTAL REPORT XI.

Im really having a hard time connecting to a SQLSERVER 2000 database server on my network...

I've tried manipulating the datasource but to no avail... If anyone can help me by showing some example please do...

i would really appreciate your help...

Thanks...!!If you just want to connect to MSSQL from CRXI and add database tables for your report, use OLEDB provider for SQL Server. The connection process is self-explanatory.

How to connect 2 table in one database.

Hi everyone.

Please Help Me.

Asp.net using C#.Net and Backend Sql server 2005.

1. How to conection 2 table at the same time.in c#.net.

2.How to Insert,update and delete for 1 or 2 table connections.

all the code give C#.Net in asp.net.

Thanks.

Hi, don't quite get your questions. What are you trying to do? What do you mean by connect 2 tables at the same time? Once you open a connection, you can access as many tables as you want through the connection; if the two tables are in two different database, you can open one connection for each database and then access yor tables.

Same confusion with the question 2. Don't know what you are asking.

Sorry

|||You should do some research on the SQL Join clause. Here's a starter for you: http://www.google.com/search?hl=en&q=sql+join&btnG=Google+Search&meta=|||

single database. 2 table at a time insert a values.

for example :

viswa is database.

student is a one table.

college fees is a one table.

student id is uni key

at a time 2 table insert a values.

please giveinsert, update and delete in c#.Net

|||

Those tables are not related. There is no reason that you should be performing operations on both tables at the same time. Nothing in college fees should relate to anything in student. If it does, then you need to look at your database design.

Also, C# does not offer any INSERT, UPDATE or DELETE procedures. You use the SQL language to perform operations against a database. You then use C# (or VB.Net, or J# etc) to get ADO.NET to present SQL commands to the database.

|||

I have Only one database and mutiple table.

database name = employee

table name = employee details

table name = employee salary

table name = department

morte...

I connect a database . how to select 2 tables.

my question isbatch process (multiple insert, update and delete ) in 2 tables

how to compare Time......... using DateTime field

hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do.....

select DATEPART(hour, loginTime) .....returns me the hour i can get the Hour part of the time but the prblem is how to identify it

whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level

any ideas????

i used following query..

my table is like Finddate

id bdate

1 2007-01-01 13:30:00.000
2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000
4 2007-01-01 23:30:00.000
5 2007-01-01 22:30:00.000

and i am selecting the records between 1 to 2 pm.

1 pm means 13 and 2 pm means 14, so i written query

select*from finddatewheredatepart(hour,bdate)>=13anddatepart(hour,bdate)<=14

this will gives me result as

id bdate

1 2007-01-01 13:30:00.000
2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000

hope this will help u..

|||

hi Mahadeomatre thnx for ur help i got the solution,ur query should b like this.

select*from finddatewheredatepart(hour,bdate) >=13anddatepart(hour,bdate)<14

then according to ur table it wil return only 1st row

1 2007-01-01 13:30:00.000

and "datepart(hour,bdate)<14" will omit the other two rows as per requirment.

2 2007-01-01 14:30:00.000
3 2007-01-01 14:20:00.000

How to compare just the Date portion of DateTime fields

What's the best way to compare just the Date portions of datetime
fields (ignore the time)
lateFlag = case
when TargetEndDate is null then 'N'
when TargetEndDate < getutcdate() then 'Y'
else 'N'
end
RonUse the CONVERT function to get the desired component of a date time.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"RonL" <sal_paradise_93@.yahoo.com> wrote in message
news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
> What's the best way to compare just the Date portions of datetime
> fields (ignore the time)
> lateFlag = case
> when TargetEndDate is null then 'N'
> when TargetEndDate < getutcdate() then 'Y'
> else 'N'
> end
>
> Ron
>|||If you want a condition that tells if TargetEndDate has a date
part before the date part of getutcdate(), you can do it while
still taking advantage of any useful index on TargetEndDate
by checking whether TargetEndDate with its time part is
before the date-only part of getutcdate() this way:
TargetEndDate < dateadd(day,datediff(day,0,getutcdate())
,0)
The time part of TargetEndDate can't change whether it
is before a date-only value or not.
Steve Kass
Drew University
RonL wrote:

>What's the best way to compare just the Date portions of datetime
>fields (ignore the time)
>lateFlag = case
> when TargetEndDate is null then 'N'
> when TargetEndDate < getutcdate() then 'Y'
> else 'N'
> end
>
>Ron
>
>|||Or better yet a couple datetime functions:
select dateadd(d, datediff(d, 0, current_timestamp), 0)
so the OP's snippet of code would be something like
lateFlag =
case
when TargetEndDate is null then 'N'
when dateadd(d,datediff(d,0,TargetEndDate),0)
<
dateadd(d,datediff(d,0,getutcdate()),0) then 'Y'
else 'N'
end
*mike hodgson*
http://sqlnerd.blogspot.com
SriSamp wrote:

>Use the CONVERT function to get the desired component of a date time.
>--
>HTH,
>SriSamp
>Email: srisamp@.gmail.com
>Blog: http://blogs.sqlxml.org/srinivassampath
>URL: http://www32.brinkster.com/srisamp
>"RonL" <sal_paradise_93@.yahoo.com> wrote in message
>news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
>
>
>|||Don't use CONVERT for this. Use the DateAdd and DateDiff version, (it's
already been posted twice, so I won't repeat)
The reason for this is that Convert writes the result to a memory page.
This means SLOW AND EXPENSIVE! Using the DateAdd and DateDiff functions
does not result in a page write, it only uses a small amount of CPU whilst
running the functions, and there is no conversion of datatypes taking place.
Regards
Colin Dawson
www.cjdawson.com
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:etjXwS1ZGHA.1192@.TK2MSFTNGP04.phx.gbl...
> Use the CONVERT function to get the desired component of a date time.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "RonL" <sal_paradise_93@.yahoo.com> wrote in message
> news:1145850793.160785.158820@.t31g2000cwb.googlegroups.com...
>|||Great. Thanks.
Ron

how to CHECK_POLICY = OFF

sorry for my bad english - i am working with some piece of code in SMO i do not understand well
(and rather had no time to understand it well) - this cpp-winapi-smo code was a part of instalator used to install
MSDE on client computer - It restore database from .backup file - and then configure dtabase, particulary
create few logins on restored database. -- I try to only change MSDE instalation on sqlex2k5 instalation
and encounter a problem mentioned in internet also - mainly - this code (i put here a fragmnt)

//create logins
Log(" Creating logins...");
debuglog(117,"try");
try
{
_LoginPtr pL;
if (SUCCEEDED(pL.CreateInstance("SQLDMO.Login")))
{
pL->Name = "flogin";
debuglog(118,"pL->Type = SQLDMOLogin_Standard;");
pL->Type = SQLDMOLogin_Standard;
pL->Database = m_sDatabase.AllocSysString();

try {
Log(" TTX ");

pSrv->GetLogins()->Add(pL); }

// <--

catch (_com_error err) {
Log("|| Exception during create flogin login");
Log("|| ErrorMessage: " + CString(err.ErrorMessage()));
debuglog(119,"CString sDesc = CW2A(err.Description());");
CString sDesc = CW2A(err.Description());
Log("|| ErrorDescription: " + sDesc);
(*dwExitCode) = err.WCode();
(*sErrorDescription) = sDesc;
}
pSrv->GetLogins()->Item("flogin")->SetPassword("", "jpnzpkzcnobwso");
pSrv->GetServerRoles()->Item("dbcreator")->AddMember("flogin");
Log(" Created 'flogin' login");
debuglog(120,"");
}
else
{
Log(" Creating 'flogin' login FAILED");
}

cannot create login becouse of CHECK_POLICY change in sql2k5 (as far as I know)
As far as I read I should execute from this kode sql line similiar to this "CREATE LOGIN bob WITH PASSWORD = 'password', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF" or do such thing in some SMO way
if possible but had no special idea how to do this one (sql) or the second (SMO)

MAybe someone could help me a bit with this.
Kenobi

There is a boolean property on the Login object called PasswordPolicyEnforced() which you'll want to set to false before creating the login.

That should solve your problem.

how to CHECK_POLICY = OFF

sorry for my bad english - i am working with some piece of code in SMO i do not understand well
(and rather had no time to understand it well) - this cpp-winapi-smo code was a part of instalator used to install
MSDE on client computer - It restore database from .backup file - and then configure dtabase, particulary
create few logins on restored database. -- I try to only change MSDE instalation on sqlex2k5 instalation
and encounter a problem mentioned in internet also - mainly - this code (i put here a fragmnt)

//create logins
Log(" Creating logins...");
debuglog(117,"try");
try
{
_LoginPtr pL;
if (SUCCEEDED(pL.CreateInstance("SQLDMO.Login")))
{
pL->Name = "flogin";
debuglog(118,"pL->Type = SQLDMOLogin_Standard;");
pL->Type = SQLDMOLogin_Standard;
pL->Database = m_sDatabase.AllocSysString();

try {
Log(" TTX ");

pSrv->GetLogins()->Add(pL); }

// <--

catch (_com_error err) {
Log("|| Exception during create flogin login");
Log("|| ErrorMessage: " + CString(err.ErrorMessage()));
debuglog(119,"CString sDesc = CW2A(err.Description());");
CString sDesc = CW2A(err.Description());
Log("|| ErrorDescription: " + sDesc);
(*dwExitCode) = err.WCode();
(*sErrorDescription) = sDesc;
}
pSrv->GetLogins()->Item("flogin")->SetPassword("", "jpnzpkzcnobwso");
pSrv->GetServerRoles()->Item("dbcreator")->AddMember("flogin");
Log(" Created 'flogin' login");
debuglog(120,"");
}
else
{
Log(" Creating 'flogin' login FAILED");
}

cannot create login becouse of CHECK_POLICY change in sql2k5 (as far as I know)
As far as I read I should execute from this kode sql line similiar to this "CREATE LOGIN bob WITH PASSWORD = 'password', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF" or do such thing in some SMO way
if possible but had no special idea how to do this one (sql) or the second (SMO)

MAybe someone could help me a bit with this.
Kenobi

There is a boolean property on the Login object called PasswordPolicyEnforced() which you'll want to set to false before creating the login.

That should solve your problem.