|
Hi All,
Currently in my stored procedure I need to build a table with all dates starting 1st Jan to the date passed (YTD). Cuurently the way I do is, I start from first date of year and loop till @Datepassed, ignoring Sat and Sun.
SELECT @tdate = @YEAR_start<br />
<br />
WHILE @tdate <= @Datepassed<br />
BEGIN<br />
if DATEPART (dw, @tdate) not in (1,7)<br />
INSERT INTO #DATES (tdate)<br />
VALUES (@tdate)<br />
<br />
SELECT @tdate = dateadd (dd,1,@tdate)<br />
END
Foreg:- if dec 12 2003 is passed, looping becomes inefficient.
Does any body know a better way.
Thanks
Ruchi
|
|
|
|
|
Why rebuild something that is static? If you don't specify many various years, build either a global temp table or add a new table with incremental dates, ignoring Saturday and Sunday. To get the dates, then, just use something like:
select Date into #DATES
from DateCache
where Date between @dateStart and @dateEnd Another way would be to use an extended stored proc. Programming something like this in C/C++ would be very efficient. What's questionable is how much more quickly you could build or fill a table.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
I have an application that has two forms, the main form displays info about my dataset in a datagrid (uses relationships), the second form I want to display the details of the selected data.
What do I need to insure that these two forms are sync'd. I've tried passing the DataViewManager over and setting the BindingContext of the second form to the first but this doesn't seem to work.
Any suggestions, comments, links are greatly appriciated.
Thanks!
-Dave W.
|
|
|
|
|
Ok so I figured it out  . I wasn't setting the bindingcontext in the correct place. Not quite positive WHY it works but not really concerned about that at this time.
-Dave W.
|
|
|
|
|
Hi!
I’m writing an application that manages a price list database in MS Access and does some calculations.
I have a global connection opened like this:
sConnectionString = "Provider=" + sProvider + ";"<br />
+ "Password=" + sPassword + ";"<br />
+ "User ID=" + sUserID + ";"<br />
+ "Data Source=" + sDataSource + ";"<br />
+ "Mode=" + sMode + ";";<br />
m_Connection = new System.Data.OleDb.OleDbConnection(sConnectionString);
Then I have some class that does some calculations to which I pass this connection
MyCalculator mc = new MyCalculator(m_Connection);
which has a method called DoCalcs(). This method instanciates some middle layer objects passing the same connection to all. Those objects get some data from the data layer (each one has it’s own) to be used in the calcs. In my data layer object I have a method called GetData similar to this:
public void GetData(out DataTable dtTarget)<br />
{<br />
OleDbCommand oleCmd = new OleDbCommand("select * from " + TableName + " " +, m_Connection);<br />
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(oleCmd);<br />
OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(m_DataAdapter);<br />
DataTable dt = new DataTable();<br />
myDataAdapter.Fill(dt);<br />
dtTarget = dt;<br />
}
Ok, this works fine, but i want show the user some progress bar. So, I have something like this on my Form:
private void btnDoCalculations_Click(object sender, System.EventArgs e)<br />
{<br />
System.Threading.ThreadStart threadStart = new System.Threading.ThreadStart(m_MyCalculator.DoCalcs);<br />
threadCalc = new System.Threading.Thread(threadStart);<br />
threadCalc.Priority = System.Threading.ThreadPriority.Normal;<br />
threadCalc.Start();<br />
}
(threadCalc is global)
I have a timer and from time to time I inspect some attributes from MyCalculator such as a percent. When percent reaches 100 I know the calculations are over and I call threadCalc.Abort() and show some window with the results.
This works fine too. The user can press the button for calcs again and they are recalculated, but when the user waits some time (something like 10 ou 15 mins) and press the button again I get an exception error “Specified cast is not allowed” when executing myDataAdapter.Fill(dt) from the GetData method. My call stack shows it happened inside the msdata.dll!IOpenRowset. If i call the DoCalcs method without using the thread it works fine every time. It seems like the connection gets corrupted when I use the thread. Does anyone have a hint on what’s happening? 
Sorry about the extension of the text.
Thanks in advance
jmocas
|
|
|
|
|
Hi,
I guess your connection is getting closed due to either timeout or some network blockade is breaking the connection with the database.
The best way would be to close the connection after extraction and make nw connection on the next click of calcs.
If the data in the database is static and no update is occuring from any other place then you may go for dataset which helps you work disconnected and such problems won't appear.
regards,
Aryadip.
Cheers !! and have a Funky day !!
|
|
|
|
|
Yeah, I know. My solution was to work disconnected as you said, but I’m curious why it only happened when I used the thread.
Cheers,
jm
|
|
|
|
|
Hi, 
I need to know how to insert all the records to a table which are fail to be inserted when executes a DTS package. The table is different with the destination or source table.
I realize there are many scenarios. One of them is using the text file - as the source in DTS package - that is generated when exception occured in execution process. But i think that this is a complicated and not the best way.
I also need to insert the current date to the table along with the failed records.
So, please help to solve this problem 
Thanks.
|
|
|
|
|
Could you try :
<br />
INSERT INTO table1 VALUES (SELECT * FROM table2)<br />
or something similar
Perhaps even
<br />
CREATE TABLE table2 AS (SELECT * FROM table1)<br />
Can't remember exact syntax though (as you might have guessed)
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
If anybody has ever had the need to provide progress while a lengthy stored procedure is executing, I've written up an article with code and a sample project, that illustrates how it can be done. Even though I used SQL Server and Visual Basic 6.0 in the example code, the mechanism can be used with any database engine and any development environment, including internet applications.
The full article can be found at http://punctualgraphics.com/Articles/A20030711/A20030711_p01.htm
-----------------
Hans De Schrijver
-----------------
|
|
|
|
|
|
Hi,
I'm in desperate need of someone that can help me import data from http://www.asp-shareware.org/pad/padlib.asp into my sql database from xoops.
Is there anyone who can help me setup a script of some kind to do this?
Any help/info is really appreciated
thanks
andy.grootaert@pandora.be
|
|
|
|
|
I am writing a utility which will delete all existing records in a table and then add new records using ADO.NET (and C#). I define a Data Table with the same schema as the database table and then use the SqlDataAdaper Update method to add rows to the database table. This works fine if I first delete all the rows from the database table using the SQL Server 2000 Query Analyzer. I simply use the statement "DELETE FROM ". I wish to add code that will do this as a preliminary to repopulation. I have created an SqlDeleteCommand and set its Command Text to "DELETE FROM ". Then I set the DeleteCommand property of the SqlDataAdapter object to this object. I then call the adapter's Update method passing it a DataTable with the same schema as the database table but with 0 rows. It is my understanding that the Update method would attempt to reconcile the differences between DataTable and database table by calling invoking the SQL Delete statement. This does not happen. Can anyone help me clean the rows out of the database table programmatically?
Gary A Hyslop
|
|
|
|
|
Forget about using DeleteCommand property. Just use Delete() method of DataRow. And after that use Update() method f SqlDataAdapter. This cause first row delete from your DataTable and then they updated in database. See MSDN and search this site for sample codes.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
I'm not sure how you would execute this with ADO.NET, but there is a SQL DBA command 'truncate table tablename'. If you want all data contents removed prior to loading new data, this method will be much more performant. It may be possible to put this command in a stored procedure, but I can't recall for sure.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
Sure you can put it on a SP, but it won't work if the table has foreign keys.
Free your mind...
|
|
|
|
|
Also, Truncate Table will not cause the triggers to fire if they are present on the table.
It is the fastest way to remove all rows from a table.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi,
I'm new here & not sure if tis question could be posted here or not. I need help with tis problem. I'm using Ms Access for the db with the fID as autonumber. i'm shown an error "Operation must use an updateable query" when i click on e OK button. the vb.net code is
If (txtName.Text = " " OrElse txtEmail.Text = " " OrElse txtCountry.Text = " " OrElse txtSubject.Text = " " OrElse txtMsg.Text = "") Then
lblErrMsg.Text = "Please Fill In Every Field"
Else
sql = " insert into tbforum(Name, Email, Country, Subject, Message, MsgDateTime)values('" & Me.txtName.Text & "', '" & Me.txtEmail.Text & "' , '" & Me.txtCountry.Text & "' , '" & Me.txtSubject.Text & "', '" & Me.txtMsg.Text & "' , '" & Me.lblDate.Text & "' )"
conn = New OleDbConnection(connstr)
conn.Open()
dbcomm = New OleDbCommand(sql, conn)
End If
if i put the fID in the first field, it would still show me the same prblem
Can anyone pls help me with this problem? 
|
|
|
|
|
Check your connection string. Access DBs open as read-only, I believe, unless you specify an optional parameter.
Some people think of it as a six-pack; I consider it more of a support group.
|
|
|
|
|
I'm just getting into Inner Join methods. I've got a query that extracts a whole heap of data from my database using the following query:
"SELECT MainPosts.PostTitle, MainPosts.PostDate, MainPosts.PostTime " +
"FROM ((MainPages INNER JOIN MainPosts ON MainPages.PageID = MainPosts.PageID) " +
"INNER JOIN Members ON MainPosts.PosterID = Members.AccountID) " +
"WHERE (MainPages.PageName = 'Main') " +
"ORDER BY MainPosts.PostDate DESC, MainPosts.PostTime DESC"
Now, this works ok, i get a populated dataset. But i'm trying to get my head round a few things.
How is this query represented in a dataset?
It seems that i only have 1 table called table. Is this how it's supposed to work? I was expecting to get a table of the appropriate name, or a set of tables in the dataset of the appropriate names.
That's relatively trivial though.
My biggest dillema is deffining a delete call for it. If i delete an entry, or sign it off to delete, how does this behave? Can i delete something in this table, and have it removed only from the Posts table?
I'm a bit hazy.
Regards
Cata
|
|
|
|
|
It will be represented in the dataset as one table with the columns PostTitle, PostDate, and PostTime.
I usually use XSD diagrams to keep my tables separated and build the relationship between them.
You can delete specific tables depending on how you define the DeleteCommand.
From your SQL statement I'm assuming that you're deleting the records based on the PageID.
Here's an example of delete statement that will delete the record in MainPosts when you delete the record in the recordset:
DELETE FROM MainPosts
WHERE PageID = ?
You have to supply the PageID parameter into the DeleteCommand. I find that using Visual Studio .NET makes my job easier
Edbert P.
Sydney, Australia.
|
|
|
|
|
D'oh! It's that simple?
hahahahaha!
I use VS.net to design my SQL statements and build my DLL components that i use in my page.
I'm always looking for anything new to learn, is it worth learning XSD / XML to construct, shape and manage datasets?
Cata
|
|
|
|
|
Oh yeah, it is worth learning XML and XSD. You'll find that it's much easier to use XSD for datasets.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi all,
I am having some problem converting SQL Server stored procedures to Oracle. The stored procedures with statement like
SELECT TOP 1 FROM SOME_TABLE does not export. It complains about 1. So, what would be the Oracle equivalant of Top 1 ??
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
You can use rownum in Oracle to do this.
SELECT * FROM Some_Table WHERE rownum < 2 If a specific sort or filter is necessary, you can perform that as a subquery, then select from that result using rownum. This selects the top 5:
SELECT * FROM
(SELECT *
FROM Some_Table
WHERE Some_Field = 'Value'
ORDER BY Some_Other_Field
)
WHERE rownum < 6;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
