|
Do you know what a UDF is and how they function?
Create a UDF that returns a rowset with dates and their state for defined period eg 200 days from 1/10/2009
In the UDF you create a table variable that you populate using the DataAdd function in a while loop
You then update the state based of DayOfWeek function
You then do a join to the holiday table to update the state based on dates entered by the user into the holiday table
(All of the above can be achieved in 1 step but try it in 3 steps to get a feel for the processes)
Return the rowset to the stored proc that called it where it can be used in a join or select statement.
|
|
|
|
|
i know UDF is user defined function. but i want to know is the column's type,
for example, monday and 2010.1.0 column's data type,is date? or enum? or others?
|
|
|
|
|
iceman8616 wrote: 2010.1.0 column's data type
This is not a date!
iceman8616 wrote: or enum
I do not know of an enum column data type in SQL Server
I would have a 2 column table Date datetime and State int or varchar
|
|
|
|
|
I am inserting two records from txtxbox name as txtRegno and txtName
when i run my prog i got error at con.open(); as object reference not set to an instsnce of an object
need help
here is the code
using System;<br />
using System.Collections.Generic;<br />
using System.ComponentModel;<br />
using System.Data;<br />
using System.Drawing;<br />
using System.Linq;<br />
using System.Text;<br />
using System.Windows.Forms;<br />
using System.Data.OleDb ;<br />
<br />
namespace studentManagementSystem1<br />
{<br />
public partial class StudentDetails : Form<br />
{<br />
public OleDbCommand cmd;<br />
public OleDbConnection con;<br />
<br />
string connectionString, query;<br />
public StudentDetails()<br />
{<br />
InitializeComponent();<br />
}<br />
<br />
<br />
<br />
private void StudentDetails_Load(object sender, EventArgs e)<br />
{<br />
<br />
this.masterTableAdapter.Fill(this.studmanageDataSet.master);<br />
<br />
connectionString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\studentManagementSystem1\studmanage.mdb";<br />
<br />
}<br />
<br />
private void btnAdd_Click(object sender, EventArgs e)<br />
{<br />
<br />
<br />
int rows;<br />
<br />
<br />
query = "INSERT INTO master(RegNo,Name)VALUES(@RegNo,@Name)";<br />
<br />
cmd = new OleDbCommand(query, con);<br />
<br />
cmd.Parameters.AddWithValue("@RegNo", txtRegNo.Text );<br />
cmd.Parameters.AddWithValue("@Name", txtName.Text );<br />
<br />
<br />
<br />
try<br />
{<br />
<big>con.Open(); </big> <br />
rows = cmd.ExecuteNonQuery();<br />
if (rows > 0)<br />
{<br />
MessageBox.Show("records inserted successfully");<br />
}<br />
}<br />
catch (Exception ex)<br />
{<br />
MessageBox.Show(ex.Message);<br />
}<br />
finally<br />
{<br />
con.Close();<br />
}<br />
}<br />
<br />
private void btnEdit_Click(object sender, EventArgs e)<br />
{<br />
<br />
}<br />
<br />
<br />
<br />
<br />
}<br />
}
|
|
|
|
|
I cant see anywhere con is initialized...
try
{
con = new OleDbConnection(this.connectionString);
con.Open();
rows = cmd.ExecuteNonQuery();
You need to initialize any object before you access any members .
|
|
|
|
|
i try this but noe it shows the following error
Execute Nonquery : Connection properly has not been initialized
what to do?
|
|
|
|
|
Mangesh Tomar wrote: what to do?
RTFM!!
only two letters away from being an asset
|
|
|
|
|
For your use in future posts, this is not really a Database Problem. Your problems are not caused by the database.
You should have posted it in the forum for the language you are using. C# I think in your case.
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.”
|
|
|
|
|
traffic cop at work again?
wouldn't it be easier to simply swap the two forum titles?
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
|
|
|
|
|
Inspect your connection object before you call con.open.
Put a breakpoint and debug your application.
|
|
|
|
|
Also put Initialization code above the creation of Command object
query = "INSERT INTO master(RegNo,Name)VALUES(@RegNo,@Name)";
con = new OleDbConnection(connectionstring);
cmd = new OleDbCommand(query, con);
cmd.Parameters.AddWithValue("@RegNo", txtRegNo.Text );
cmd.Parameters.AddWithValue("@Name", txtName.Text );
try
{
con.Open();
rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
MessageBox.Show("records inserted successfully");
}
}
I think it will be working now.
|
|
|
|
|
Hi all,
I have a table like :-
Start Time End Time
---------- ---------
12/12/2008 1:46:26 PM 12/12/2008 11:48:06 PM
how can i get the diffrence between these two dates?
(it should be in HH:MM:SS format.....)
Thanks in Advance...
|
|
|
|
|
Start by looking at the DATEDIFF function.
|
|
|
|
|
Here you go.. pretty darn simples.
declare @dt1 as datetime
set @dt1 = '2008-12-12 13:46:26'
declare @dt2 as datetime
set @dt2 = '2008-12-12 23:48:06'
declare @dt3 datetime
set @dt3 = @dt2-@dt1
select CONVERT(VARCHAR(10),@dt3,108)
108 is the format for HH:mi:ss
|
|
|
|
|
Mr. J4amieC solution is perfect which will work everywhere.
However, if you are using Sql Server 2008, you can use the new TIME datatype something like this
declare @tbl table(startdate datetime,enddate datetime)
insert into @tbl select '2008-12-12 13:46:26','2008-12-12 23:48:06'
select CAST((select enddate-startdate from @tbl) as time) TimeDiff
Niladri Biswas
modified on Wednesday, October 28, 2009 5:56 AM
|
|
|
|
|
Let me get this straight, you are casting a DATETIME to a DATETIME so you can cast it to TIME?
only two letters away from being an asset
|
|
|
|
|
Yae.. I didnot notice that I made that mistake.
Very sorry. But thanks.
Niladri Biswas
|
|
|
|
|
I don't know if this is the right form to ask this but I didn't know where else to post it.
I have windows server 2003 and in it I have SQL server 2000. My question is can I install SQL 2005 over my server and have both 2000&2005? Does this make any problem? Can I have both SQlServers over the same windows server?
Thanks in Advance.
|
|
|
|
|
You will not be installing 2005 OVER 2000, that is an upgrade, you should install 2005 using a named instance on the server. Then you use the named instance in the connection string in your apps.
|
|
|
|
|
So if i do this i can have both sql servers 2000%2005 on my machine?
|
|
|
|
|
You should be able to, have you Googled to see if you can?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yes, and 2008 as well if you want.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
crystal reports r new to me i want to make a query using Add Command and want to include a condition Between for a Date. How i can do that? when i done it i create parameters also usin CREATE PARAMETER but when i give value to Date1 and click ok it show error Permission violation, or other nonsecific error and give another error failed to open a rowset. here is my query plz chk and reply. thnx in advance
SELECT Sum(b.TotalSale*b.Price) AS total, a.Category , a.[Transaction]
FROM TransactionType AS a INNER JOIN CouponTransaction AS b ON a.TransactionTypeID = b.TransactionTypeID
where b.Transactiondate Between ({?Date1},{Date2})
GROUP BY a.Category,a.[Transaction];
|
|
|
|
|
I suggest you take your select processing away from CR and move it back to your database where it belongs. When I worked with CR , years ago, we would build a stored proc (in SQL Server) that returned the exact set of data to service the report. Testing the data set is dramatically quicker in SQL management studio. Then use a parameterised query to get the data from the proc.
|
|
|
|
|
thnx 4 the suggestions but i want to get Date parameters value at run time.what should i do?
|
|
|
|