|
hi all.
I have problem, In my program I export xls file in SQL, one column in this file is datatime and this time must write by user. Sow I have problem
user can use one of this time format:
1) DD.MM.YY
2) YY.MM.DD
3) DD/MM/YY
4) YY/MM/DD
5) DD.MM.YYYY
6) YYYY.MM.DD
......
......
and I must covert it to standard YYYY-MM-DD.
if you can tell me how I can convert
this strings to standard YYYY-MM-DD format.
|
|
|
|
|
convert(datetime,'02/03/2004',102)
convert(datetime,'02/03/2004',101)
Rami Abdalhalim
|
|
|
|
|
use validation controls
alternatively you can use 3 different comboboxs(for year,for month and for days)for getting user input date.
|
|
|
|
|
hi
there are no of ways to conver the datetime to in to standard format.
here are some of the formats hope this is useful to you
100---Jun 30 200
101---06/30/2006
102---2006.06.30
103---30/06/2006
104---30.06.2006
105---30-06-2006
106---30 Jun 200
107---Jun 30, 20
108---11:54:59
109---Jun 30 200
110---06-30-2006
111---2006/06/30
112---20060630
113---30 Jun 200
114---11:54:59
select convert(varchar(10),getdate(),101)
get back for any qureries.
|
|
|
|
|
This code below connected to a Access Database. I want to make it connect to a SQL Server 2005(As u can see, I have a server connection string)..Are there any changes that I need to make to the methods of the OleDb Class or any other class because its not working.
private void btnLogin_Click(object sender, System.EventArgs e) <br />
{ <br />
string ConString = "Data Source=porky;Initial Catalog=IS3_sysdev;Integrated Security=True"; <br />
OleDbConnection DBCon = new OleDbConnection(ConString); <br />
GlobalVariables.g_login = this.textBox1.Text; <br />
<br />
string strPassword = this.textBox2.Text;<br />
<br />
if (GlobalVariables.g_login == "" || strPassword == "")<br />
{ <br />
MessageBox.Show("You are missing information. Please make sure that both the username and password fields are filled out.", "Missing Info"); <br />
this.textBox1.Focus(); <br />
return; <br />
} <br />
string strsql = "SELECT [UserID], [Password] FROM Users WHERE [UserID]='" + GlobalVariables.g_login + "' "; <br />
<br />
OleDbCommand cm = new OleDbCommand(strsql, DBCon); <br />
OleDbDataReader dr; <br />
bool valid = false; <br />
bool HasRows = false; <br />
try { <br />
DBCon.Open();<br />
dr = cm.ExecuteReader(); <br />
if (dr.HasRows) { <br />
while (dr.Read()) { <br />
if (strPassword == dr.GetString(1)) { <br />
valid = true; <br />
} <br />
} <br />
HasRows = true; <br />
} <br />
dr.Close(); <br />
} <br />
catch (OleDbException exO) { <br />
MessageBox.Show(exO.Message); <br />
} <br />
catch (Exception ex) { <br />
MessageBox.Show(ex.Message); <br />
} <br />
<br />
iCount = iCount + 1; <br />
if (valid == true) {<br />
Form n = new Form4();<br />
n.Show();<br />
this.Hide();<br />
} <br />
else if (iCount == 3) { <br />
MessageBox.Show("Contact Developers!", "Invalid Info"); <br />
this.Close(); <br />
} <br />
else if (HasRows == false) { <br />
MessageBox.Show("Invalid user name, try again!", "Invalid Info"); <br />
this.textBox1.Focus(); <br />
this.textBox1.Text = ""; <br />
this.textBox2.Text = ""; <br />
} <br />
else { <br />
MessageBox.Show("Invalid password, try again!", "Invalid Info"); <br />
this.textBox2.Focus(); <br />
this.textBox2.Text = ""; <br />
} <br />
<br />
}
Thanks in Advance....
I bis th bit
|
|
|
|
|
What's the error message?
OleDb should allow you to connect to SQL Server also if you provide the proper connection string. However, in the area of SQL parameters Access and SQL Server use different notations.
|
|
|
|
|
Your connection string is wrong. It is missing a provider designation. The string you are using is correct for an SqlConnection (System.Data.SqlClient), but not for an OledbConnection. See a correct example here[^]
|
|
|
|
|
plz help me
shell
|
|
|
|
|
Any of: open it with SQL Server Management Studio (you will need to have installed the client tools), or pass it as an argument to sqlcmd (preferred) or osql .
If you have SQL Server Management Studio installed it should be the program associated with the .sql filename extension. You can open the file simply by double-clicking it in Explorer. It will then prompt for connection information.
|
|
|
|
|
Hi
Don't know what's wrong with my code(below), it runs but doesn't return any childrows( however exists -childrows.length=0- & identified by parent rows ID). Dataset was created by wizard, table & nested table in the dataset browser exist. I don't have any idea left... Thanks for your posts in advance, g
private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
{
if (Validate() && uzemanyagBindingSource != null)
{
bool deleteRow = true;
DataRowView rowView = uzemanyagBindingSource.Current as DataRowView;
if (rowView == null)
{ return; }
Debmut9DataSet.UzemanyagRow row = rowView.Row as Debmut9DataSet.UzemanyagRow;
Debmut9DataSet.GepRow[] ChildRows = row.GetGepRows();
if (ChildRows.Length > 0)
{
DialogResult userChoice = MessageBox.Show("All records in this & realted table will be deleted.Continue?", "Delete...", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (userChoice == DialogResult.Yes)
{
foreach (Debmut9DataSet.GepRow childuzemanyag in ChildRows)
{
childuzemanyag.Delete();
}
}
else
{ deleteRow = false; }
}
if (deleteRow)
uzemanyagBindingSource.RemoveCurrent();
uzemanyagBindingSource.EndEdit();
Array.Resize(ref uzemanyagregiar, uzemanyagBindingSource.List.Count);
}
|
|
|
|
|
Think I got it
I forgot to fill the child table, upon the load event of the form whose control is bound to parent table.
so the classic: childtableadapter.fill (dataset.childtable)
|
|
|
|
|
Hi Guys and Gals.
I have the following (sample) info in a table in SQL
1 a 10
1 b 20
1 c 30
1 d 40
1 e 50
1 f 60
2 a 15
2 b 25
2 c 35
2 d 45
2 e 55
2 f 65
3 a 60
3 b 50
3 c 40
3 d 30
3 e 20
3 f 10
I need it in the following format
a b c d e f
1 10 20 30 40 50 60
2 15 25 35 45 55 65
3 60 50 40 30 20 10
Does anyone know of a quick and easy way that's not too painfull? It will be greatly appreciated.
Regards,
Elizma
|
|
|
|
|
If you are using SQL Server 2005, you may want to take a look at the PIVOT command. Here's[^] an example.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanx. It worked beautifully. Now the problem is, this is hard coded, which means in reality I have 30 "fields" that must be hard coded. What happens when we add another "field" in our table then we must go edit the script again.
Is there no way that I can read the field values in a loop maybe? Just wondering. I know how to do this type of goodies in C#, but it's been a while since I last played intensly with SQL.
Advise will be greatly appreciated.
Regards,
Elizma
|
|
|
|
|
Create table tblMatrix(Column1 int,Column2 char,Column3 int)
Create Procedure sp_Transpose As
DECLARE @SQLQuery AS NVARCHAR(2000)
DECLARE @ColumnName Nvarchar(50)
DECLARE @AddedColumns Nvarchar(500)
DECLARE @AddedValues Nvarchar(500)
DECLARE @Col1Val Nvarchar(50)
DECLARE @Col2Val Nvarchar(50)
DECLARE @Val2Add Nvarchar(50)
declare NavigatorRecords cursor for
SELECT DISTINCT(COLUMN2) FROM TBLMATRIX
open NavigatorRecords
fetch next from NavigatorRecords into @ColumnName
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTemp]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table dbo.tblTemp
SET @SQLQuery = 'CREATE TABLE tblTemp(Col Int,'
SET @AddedColumns = 'Col,'
SET @AddedValues = ''
SET @Val2Add = ''
while(@@FETCH_STATUS = 0)
begin
SET @AddedColumns = @AddedColumns + @ColumnName
SET @SQLQuery = @SQLQuery + @ColumnName + ' ' + 'NVARCHAR(50)'
fetch next from NavigatorRecords into @ColumnName
IF (@@FETCH_STATUS = 0)
begin
SET @SQLQuery = @SQLQuery + ','
SET @AddedColumns = @AddedColumns + ','
end
end
SET @SQLQuery = @SQLQuery + ')'
EXEC sp_executesql @SQLQuery
close NavigatorRecords
deallocate NavigatorRecords
declare CursorI cursor for
SELECT DISTINCT(COLUMN1) FROM TBLMATRIX
open CursorI
fetch next from CursorI into @Col1Val
while(@@FETCH_STATUS = 0)
begin
SET @AddedValues = @Col1Val + ','
declare CursorJ cursor for
SELECT DISTINCT(COLUMN2) FROM TBLMATRIX
open CursorJ
fetch next from CursorJ into @Col2Val
while(@@FETCH_STATUS = 0)
begin
set @Val2Add = '0'
select @Val2Add = Isnull(column3,'0') from tblMatrix where column1 = @Col1Val and column2 = @Col2Val
SET @AddedValues = @AddedValues + @Val2Add
fetch next from CursorJ into @Col2Val
IF (@@FETCH_STATUS = 0)
SET @AddedValues = @AddedValues + ','
end
SET @SQLQuery = 'INSERT INTO tblTemp(' + @AddedColumns +') values(' + @AddedValues +')'
EXEC sp_executesql @SQLQuery
close CursorJ
deallocate CursorJ
fetch next from CursorI into @Col1Val
end
close CursorI
deallocate CursorI
Select * from tblTemp
Go
Exec sp_transpose
Regards J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
Plz room, is it posible to mount .mdf files ( sql 2005 ) in sql 2000 maybe personal edition. Thanks
phatkin
|
|
|
|
|
prubyholl wrote: is it posible to mount .mdf files ( sql 2005 ) in sql 2000 maybe personal edition
No, it isn't.
|
|
|
|
|
|
Hi
I have month and year values, I want to get last day of month. How it can be acheived in SQL server 2005?
Shahzad Aslam
Software Engineer
Softech Systems Limited
Cell: +92-321-4606036
Email: shehzadaslam@hotmail.com
|
|
|
|
|
Try one of them......
1.
SELECT Datepart(dd,DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, Getdate())+1, 0)))
2.
Select datepart(dd,dateadd(mm,1,Getdate() - day(Getdate())))
have a nice time........
|
|
|
|
|
Thanx!!!
Shahzad Aslam
Software Engineer
Softech Systems Limited
Cell: +92-321-4606036
Email: shehzadaslam@hotmail.com
|
|
|
|
|
I have created 2 radio buttons on my form but using databinding how i can hendle them?????
Base table name = "Employeer"
Column to be binded = "Gender"
HELP????
|
|
|
|
|
try to set it manually...
radiobutton1.databinding.add("Value", bindinsource("Fields")
|
|
|
|
|
i want to know
when we use sqlserver as backend....
while using reporting
i heard people use msoffice
why is it?
|
|
|
|
|
Hi, I wrote a function in C# to be used as a scalar function in SQL2005:
[Microsoft.SqlServer.Server.SqlFunction(IsPrecise = true, IsDeterministic = true, DataAccess = DataAccessKind.Read)]
public static SqlDecimal GetTaxAmountFromTax(SqlString tax, SqlDecimal amount)
{
SqlConnection conn = new SqlConnection("context connection=true");
SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM TransactionTaxes", conn);
DataTable taxTable = new DataTable();
adap.Fill(taxTable);
DataRow[] rows = taxTable.Select("[ID] = '" + tax.ToString() + "'");
if (rows.Length == 0) return SqlDecimal.Null;
if (rows[0]["CalculatedOnTax"] != DBNull.Value)
{
if ((bool)rows[0]["Cumulative"])
{
DataRow[] rows1 = taxTable.Select("[ID] = '" + rows[0]["CalculatedOnTax"].ToString() + "'");
if (rows1.Length == 0) return SqlDecimal.Null;
return ((amount + (amount * (decimal)rows1[0]["Rate"]) / new SqlDecimal(100))) * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
else return amount * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
else return amount * (decimal)rows[0]["Rate"] / new SqlDecimal(100);
}
I want to publish this function making sure the return type has a precision of 2 and a scale of 18. I found that I have to use the SqlFacetAttributes but I am unsure how to do it for a function. I found examples only for a Strored Procedure parameter.
Any help appreciated. Txs
|
|
|
|