|
Hi,
I am trying to insert into a DateTime field by concatenating a string and a nVarChar field like so:
CONVERT(datetime, '1/1/' + tblMembershipRecords.[YearJoined], 103)
But I get a message stating;
'Conversion failed when converting datetime from character string.'
Any ideas what is going wrong?
|
|
|
|
|
Liqz wrote: Any ideas what is going wrong?
The computer tries to "interpret" your string as a date, and it doesn't understand it. It seems that it expects the date to be in a different format.
Try something like this;
SELECT DATEADD(y, tblMembershipRecords.[YearJoined], getdate()) That will add a single year to the DateTime, without converting it to a string.
I are Troll 
|
|
|
|
|
Got it, thanks for the tip, all working now
|
|
|
|
|
Good Day all
it is Probably a long day, i cant think Straight now.
i have a table that looks like this
Nodeid Parent Description Type Curr
==========================================================================================
89 NULL Compulsory 1 10
90 89 B1052 3 10
2820 89 One of 2 10
4113 89 B1061 3 10
2821 2820 B1054 3 10
2822 2820 B1055 3 10
Now the Bold Record needs to be on top of "One of" because the Parent is "Compulsary" with the "Parent" = 89. Now Even "One of " has the Same Parent as the Bold record but if its a "One of " and they have the same parent, then "One of " must always be below the record. What i mean is that if there is a record with a same parent but different Type , the one that has type 2 should go below the one that has type 3 in my query. here is my query
select distinct nP.id, nP.NodeID, nP.parent, nP.Description, nRef.ID refParent, np.type
from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by refParent,nP.id,np.type desc
Thank you
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Thanks now i have Changed my Code to look like this and its working perfect in SQl when i test it.
select distinct nP.id, nP.NodeID, nP.parent, nP.Description, nRef.ID refParent, np.type
from #Nodes nP
left outer join #Nodes nRef on nP.Parent = nRef.NodeID -- look up the reference id of the parent
order by np.Parent,np.Description,np.NodeID
And it returns
Nodeid Parent Description Type Curr
==========================================================================================
89 NULL Compulsory 1 10
90 89 B1052 3 10
4113 89 B1061 3 10
2820 89 One of 2 10
2821 2820 B1054 3 10
2822 2820 B1055 3 10
and its Good thanks.
am binding the values to a Tree Control like this
public void PopulateTreeFromCurr(int currID)
{
IEnumerable result = CurrStructDataSource.Select(DataSourceSelectArguments.Empty);
int Parent, Child;
CurriculumTreeView.Nodes.Clear();
ArrayList CurrNodes = new ArrayList();
if (result != null)
{
foreach (System.Data.DataRowView row in result)
{
TreeNode newnode = new TreeNode(row["Description"].ToString(), row["NodeID"].ToString());
CurrNodes.Add(newnode);
if (row["refParent"].ToString() == "")
{
}
else
{
Parent = Convert.ToInt32(row["Parent"]);
Child = Convert.ToInt32(row["ID"]);
TreeNode ParentNode = new TreeNode();
TreeNode ChildNode = new TreeNode();
[COLOR="DarkOrange"][B]ParentNode = (TreeNode)CurrNodes[Parent];[/B][/COLOR]
ChildNode = (TreeNode)CurrNodes[Child];
ParentNode.ChildNodes.Add(ChildNode);
CurrNodes[Parent] = ParentNode;
}
}
if (CurrNodes.Count > 0)
{
CurriculumTreeView.Nodes.Add((TreeNode)CurrNodes[0]);
CurriculumTreeView.ExpandAll();
}
}
}
in the code i get an Error in the bolded red part line
ParentNode = (TreeNode)CurrNodes[Parent];
and it says
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Thank you
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
select ROW_NUMBER() OVER (ORDER BY pid DESC) as RowNum, pid,grpname from
(select distinct p.ParticipantId as pid,p.pname + ' (' + p.username + ' )' as grpname from
TSParticipants as p inner join TSGroupParticipants as gr
on p.ParticipantId=gr.ParticipantId
where p.status=0) as grplist
in the above query i have to add the the below where condition
WHERE RowNum BETWEEN 0 AND 0 + 6 -1
where should i add this condition
if add it its showing error:
Msg 207, Level 16, State 1, Line 8
Invalid column name 'RowNum'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'RowNum'.
--------------------------------
select * from
( select *, row_number() over (unique_column_name order by asc) as rownum from emp ) as test where rownum between 0 and 0 + 6 -1
-----
this above query gives all the rows
i need only unique rows
|
|
|
|
|
I have created a sample example. Hope this may help you.
declare @tbl table(firstname varchar(50),lastname varchar(50))
insert into @tbl
select 'firstname' + CONVERT(varchar(50),column_id)
,'lastname' + CONVERT(varchar(50),column_id)
from sys.columns
select * from @tbl
select firstname,lastname from
(select ROW_NUMBER() over(order by firstname) as rn,firstname,lastname
from
(
select
distinct(firstname),lastname
from @tbl t
)X(firstname,lastname))Y(rn,firstname,lastname)
where rn between 0 and 0 + 6 -1
As per the understanding I made after going thru ur statement, I thought that u r looking for unique records. Henceforth, in the very beginning only I have used a distinct clause.
Note:- I tried to explain the concept. You need to customise and implement as per ur requirement.
Let me know in case of any concern.
I cannot make out why instead of using 5 directly u are using 0+6-1?
Niladri Biswas
|
|
|
|
|
Hi,
I'm working on replication. It's working fine for same network sql servers.
But when i'm using one remote server then at the time of creating subscription it's not able to connect the remote sql server.
Plz tell me how to fix this issue..
Thanx...
krishna veer singh
|
|
|
|
|
I am trying to OUTPUT a data from the SP but getting this error:
System.InvalidOperationException was unhandled
Message="String[5]: the Size property has an invalid size of 0."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at The_Internet_Cafe_System.frmMain.connect_to_host() in C:\Users\Jassim\Documents\Visual Studio 2008\Projects\Internet Cafe\Internet Cafe\main_form.cs:line 206
at The_Internet_Cafe_System.frmMain.timerHost_Tick(Object sender, EventArgs e) in C:\Users\Jassim\Documents\Visual Studio 2008\Projects\Internet Cafe\Internet Cafe\main_form.cs:line 496
at System.Windows.Forms.Timer.OnTick(EventArgs e)
at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at The_Internet_Cafe_System.Program.Main() in C:\Users\Jassim\Documents\Visual Studio 2008\Projects\Internet Cafe\Internet Cafe\Program.cs:line 35
InnerException:
this is my SP:
USE the_internet_cafe
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_update_account_on_host]
@system_guid uniqueidentifier,
@net_qty int,
@net_amount money,
@items_qty int,
@items_amount money,
@banner_file varchar(255) output
AS
BEGIN
SET NOCOUNT ON;
UPDATE internet_cafe SET net_qty = @net_qty, net_amount = @net_amount, items_qty = @items_qty, items_amount = @items_amount, last_connected = getdate() WHERE system_guid = @system_guid
SET @banner_file = (SELECT banner_file FROM banners WHERE banner_id = 1)
END
and this is my C# code:
private void connect_to_host()
{
sql_connection = new SqlConnection("Data Source=.\\TICSSQL;initial catalog=the_internet_cafe;integrated security=true;");
sql_connection.Open();
sql_command = new SqlCommand("sp_update_account_on_host", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("@system_guid", SqlDbType.VarChar).Value = Properties.Settings.Default.system_net_cafe_guid;
sql_command.Parameters.Add("@net_qty", SqlDbType.Int).Value = Properties.Settings.Default.system_net_qty;
sql_command.Parameters.Add("@net_amount", SqlDbType.Money).Value = Properties.Settings.Default.system_net_amount;
sql_command.Parameters.Add("@items_qty", SqlDbType.Int).Value = Properties.Settings.Default.system_item_qty;
sql_command.Parameters.Add("@items_amount", SqlDbType.Money).Value = Properties.Settings.Default.system_item_amount;
SqlParameter banner_file = sql_command.Parameters.Add("@banner_file", SqlDbType.VarChar);
banner_file.Direction = ParameterDirection.Output;
int result_rows = sql_command.ExecuteNonQuery();
MessageBox.Show(banner_file.Value.ToString());
sql_connection.Close();
|
|
|
|
|
jrahma wrote: sql_command.Parameters.Add("@banner_file", SqlDbType.VarChar);
It seems fairly obvious, add a size
sql_command.Parameters.Add("@banner_file", SqlDbType.VarChar, 255);
only two letters away from being an asset
|
|
|
|
|
Hi.
I just wanted to let you know that db4o has improved its memory manager making it more appealing to .NET & Java developers who work in memory constrained environments. In memory usage of db4o is now more closely integrated with the storage API which removes redundancies while bringing better support for administrative features such as defragment and backup. The new PagingMemoryStorage implementation significantly reduces memory footprint.
More info:
http://developer.db4o.com/blogs/product_news/archive/2009/08/13/memories-running-db4o-in-memory-revisited.aspx
Best!
|
|
|
|
|
I think you should pay to advertise like others have to...
|
|
|
|
|
Hi,
Which method is the best way to transfer realtime data from one sqlserver to another sqlserver.
Plz let me know..
Thanx...
krishna veer singh
|
|
|
|
|
|
If you are using 2005 or greater then you should look at Sql Server Service Broker as it was designed specifically for your requirement.
|
|
|
|
|
Do you mean transfer or replicate? For the former, assuming sql server 2005 or later, useService Broker, for the latter google replication.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I have an Access database which contains every date between 01/01/2009 and 21/12/2029. In a 2nd column is a char value indicating whether the date is a working day or not. I need to decide whether xmas & boxing day fall on a non-working day and if so adjust the working day column value. I have the following to calculate the adjusted dates:
SELECT switch((DATEPART("w",FullDate,2) In (6,7)),DATEADD("d",+2,FullDate),(DATEPART("w",FullDate,2) Not In (6,7)),FullDate)
FROM UKWorkingDays
WHERE DATEPART("m",FullDate,2)=12 And DATEPART("d",FullDate,2) In (25,26);
If I wrap this in an update statement the SQL appears to execute but does nothing at all
UPDATE UKWorkingDays SET WorkingDay = "B"
WHERE FullDate in
(
SELECT switch((DATEPART("w",FullDate,2) In (6,7)),DATEADD("d",+2,FullDate),(DATEPART("w",FullDate,2) Not In (6,7)),FullDate)
FROM UKWorkingDays
WHERE DATEPART("m",FullDate,2)=12 And DATEPART("d",FullDate,2) In (25,26)
);
Can anyone give me any assistance please ?
|
|
|
|
|
Ew Access again - and a newer version than I have worked with. Some thoughts.
Why do you have the switch (what is switch doing in SQL code) in the select portion, surely you want that in the where clause.
Select Fulldate
From UKWorkingDays
Where fulldate in (filter 25/6) and weekday in (filter weekdays)
I leave it to to grasshopper to fix the filter.
|
|
|
|
|
Thanks for the reply Mycroft but I'm not sure that it is of much help.
Take this year for example. 25/12 is a Friday, 26/12 is a Saturday so 28/12 is actually a holiday not a working day. My inner select statement returns 2 rows correctly containing Friday 25/12 and Monday 28/12. I then have to check every row in the table against these returned values and if equal mark the date as a non-working day (my outer update). The inner select works a treat so I thought an update statement where date in (inner select) would achieve what I wanted but for some reason it appears to do nothing, no rows returned updated and no exception thrown. Maybe its looping too many times and would eventually finish. I'll have to re-visit my code and see if there's a better way.
|
|
|
|
|
More on this..
I've just stripped out all but this years dates and run my query which has done exactly as I expected and updated 25 & 28/12. I guess its just baulking at the number of repetitions it has to perform on the full table so I'm on the right track but do need to improve the code.
|
|
|
|
|
Hi,
i want to export an sql express database... i have create the db in vs 9... if i user the "publish to provider" function, i have a error...
I want my old enterprise manager back... 
thx benny
|
|
|
|
|
|
thank you!!! i try it...
|
|
|
|
|
declare @a varchar(500)
set @a = '1,2,3,4'
how to generate output for all the combinations of input @a like 1,2,3,4 ; 1,2,4,3 ; 1,3,2,4 ; 1,3,4,2 ; 1,4,2,3 ; 1,4,3,2 ; 2,1,3,4; 2,1,4,3 ; 2,3,1,4...
All the numbers will be in series like for 10 digits.. 1,2,3,4,5,6,7,8,9,10.
i require all the combinations to be generated. the series i found like in the input if last (N) and last-1 (N-1) digits are interchanged, in series 1,2,3,4, second sequence wil be 1,2,4,3 ( interchange last two digits). next 1,3,2,4(N-3 digit is incremented and missing numbers 2,4 are placed).Next sequence wil be 1,3,4,2 ( interchange last two digits).. it could give all the combinations.
Could some one help me in providing solution for this. while i am using sql server 2005.
thanks in advance,
rpp
|
|
|
|
|
I do not have an answer in T-SQL or SQL but here[^] is a link to a C# recursive algorithm that you might find useful.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
