Database

26-Jul-08 21:28

mahabir wrote:
If I pick out 1 data poing per month,

There is a trading graph the shows hi/low and volume for period per period (Day per year). That will solve your problem. There are at least 3 other methods of displaying the data without dumping our millions of rows!

Never underestimate the power of human stupidity
RAH

ADO Excel problem

followait25-Jul-08 16:36

followait

25-Jul-08 16:36

By using ADO to deal with excel, I can create the table in Excel successfully, but when I try to insert a new column, the SQL command failed.
ALTER TABLE tablename INSERT COLUMN col1 FLOAT
Why?

system

Re: ADO Excel problem

Mycroft Holmes25-Jul-08 23:05

25-Jul-08 23:05

Question comes instantly to mind - WHY THE FREAKIN HEEL ARE YOU USING EXCEL AS YOUR DATABASE. Unless there is an overriding business reason to do this you are setting yourself up for a disaster. Please use SQL Express if possible, Access if you really must use office but Excel NNnnnnnooooooo!

Sorry that I cannot help you with this I am not even sure it is possible. Quite possibly I would not even if it is possible!

Never underestimate the power of human stupidity
RAH

Re: ADO Excel problem

followait26-Jul-08 6:07

followait

26-Jul-08 6:07

Yes, the customer need a report in an Excel file.

system

Re: ADO Excel problem

Mycroft Holmes26-Jul-08 14:17

LINQ to SQL and TransactionScope == DTC

26-Jul-08 14:17

followait wrote:
customer need a report in an Excel file

Ok thats a different problem. Do not create a database for outputting to excel. If you do a search in the articles here you will find many examples for output to excel from both SQL and UI.

You have 2 choices, output a csv file and allow excel to do the conversion when it is opened. This is by far the simplest method and probably the widest used.

Alternatively you can manually create an excel sheet by manipulating the excel application object. This will give you formatting options but will take a lot more work and require the excel application to be installed on the client computer.

3rd option it to use a reporting engine to render an excel report. This give you the best format but will require you get a handle on a report engine.

Never underestimate the power of human stupidity
RAH

Mark J. Miller25-Jul-08 12:33

Mark J. Miller

25-Jul-08 12:33

The client I'm working for is using LINQ to SQL behind a BLL of sorts. So each CRUD operation gets its own context. Here's an example of the Save method:

<br />
        public static Step Save(Step step)<br />
        {<br />
			using ( KRATDataContext context = new KRATDataContext( Helpers.EnvironmentState.CurrentConnectionString ) )<br />
			{<br />
				Step oldStep = step.StepID > 0 ? context.Steps.Single( p => p.StepID == step.StepID ) : null;<br />
<br />
				if ( oldStep == null )<br />
				{<br />
					context.Steps.InsertOnSubmit( step );<br />
				}<br />
				else<br />
				{<br />
					oldStep.Description = step.Description;<br />
					oldStep.PotentialHazard = step.PotentialHazard;<br />
					oldStep.ActionsToReduceRisk = step.ActionsToReduceRisk;<br />
					oldStep.InitialRiskCellID = step.InitialRiskCellID;<br />
					oldStep.PostRiskCellID = step.PostRiskCellID;<br />
					oldStep.DateCreated = step.DateCreated;<br />
					oldStep.RiskAnalysisID = step.RiskAnalysisID;<br />
					oldStep.DisplayIndex = step.DisplayIndex;<br />
				}<br />
				context.SubmitChanges();<br />
<br />
				return step;<br />
			}<br />
        }<br />
<br />

I have implemented functionality which allows a user to change the display order of a "Step" record by clicking an up/down arrow which essentially swaps the value of DisplayIndex for two separate records. Like this:

<br />
protected void Swap(int stepId1, stepId2)<br />
{<br />
  // get the actual records<br />
  Step step1 = Step.GetOne(stepId1);<br />
  Step step2 = Step.GetOne(stepId2);<br />
<br />
  // do the real swap (left out logic on purpose to focus on issue)<br />
  Swap(step1, step2);<br />
<br />
  using( System.Transactions.TransactionScope txCtx = new System.Transactions.TransactionScope()){<br />
    Step.Save(step1);<br />
    Step.Save(step2);<br />
<br />
    txCtx.Complete();<br />
  }<br />
}<br />

The important fact being demonstrated here is that each save operation has its own DataContext. So when the 2nd call to Save() is made I get an exception because I have explicitly turned off DTC (ie. I don't allow remote DTC operations). But these two records are in the same table.

So here's what I'm looking for:

1) Can someone else confirm this for me, but duplicating the error I get.
2) Is there a way around this, other than using the same DataContext object?
3) I'll need to double check, but this doesn't happen to me when I use the EnterpriseLibrary connection objects with TransactionScope. So is this a bug? Or at least something that should be changed so it works the way EnterpriseLibrary does?

#2 is important because while fixing this issue for the specific case above (both objects are based on the same database table) I have other cases where I'll have to create specialized methods in my data access layer which allow me to use the same data context so I can wrap operations on different tables with the same DataContext.

As always, I appreciate the help.

[Edit]
Here's the exception detail:

System.Transactions.TransactionManagerCommunicationException was unhandled by user code
Message="Communication with the underlying transaction manager has failed."
Source="System.Transactions"

InnerException: System.Runtime.InteropServices.COMException
Message="Error HRESULT E_FAIL has been returned from a call to a COM component."
Source="System.Transactions"

[/Edit]

Code responsibly: OWASP.org
Mark's blog: developMENTALmadness.blogspot.com

Re: LINQ to SQL and TransactionScope == DTC

Mark J. Miller28-Jul-08 10:18

Mark J. Miller

28-Jul-08 10:18

Ok, well I came up with a solution for this (for anyone interested).

The DataContext is not aware of any other contexts (through thread local storage) in any way. So I basically decided to use thread local storage to store my DataContext for the current thread (HttpRequest).

Here's how it works. The DataContext classes generated for a dbml file is a partial class, so I added a class file to the same project which contains a static factory method, GetDataContext(). GetDataContext checks HttpContext.Current.Items to see if a DataContext object already exists. If not, it creates and new one, stores it in HttpContext.Current.Items and the returns the new instance. If the object already exists, it just returns the existing object to the caller.

<br />
public static DataContext GetDataContext()<br />
{<br />
  if(HttpContext.Current == null)<br />
    return new DataContext(connectionString);<br />
<br />
  if(HttpContext.Current.Items.Contains("DataContext"))<br />
  {<br />
    return (DataContext) HttpContext.Current.Items["DataContext"];<br />
  }<br />
<br />
  return new DataContext(connectionString);<br />
}<br />

Now, I am able to use TransactionScope to wrap my business logic without having to use DataContext directly. Plus there are other benefits, my Save method no longer requires the additional database call to retrieve the record and copy the changes before calling SubmitChanges(). So compared with my previous post, my Save method now looks like this:

<br />
        public static Step Save(Step step)<br />
        {<br />
		KRATDataContext context = KRATDataContext.GetDataContext();<br />
<br />
		if ( step.StepID <= 0 )<br />
		{<br />
			context.Steps.InsertOnSubmit( step );<br />
		}<br />
<br />
		context.SubmitChanges();<br />
<br />
		return step;<br />
	}<br />

Which requires one less database call, always a plus Cool | :cool:

. This all solves my Transaction problem because it uses the same connection object (because we're using the same DataContext which owns the Connection object) and so there is no longer any distributed transactions (DTC) required.

Code responsibly: OWASP.org
Mark's blog: developMENTALmadness.blogspot.com

Database column

Verghese25-Jul-08 9:10

Verghese

25-Jul-08 9:10

My database table has got 5 fields and through my C#.Net code I'm adding the 6th field (using dataset and .WriteXmlSchema() )...........but it works fine for the first time............aftr tht since the DataSet schema has already been updated with the 6th column, it throws an error "6th Field already exists in database table"............

so i just want to do something like this :

if (check whther Access Database Column is already present for the ABC Table)  // if YES
{
    do {nothing}
}
else
{
    do {add the 6th column -  for which i already have the code}
}

Re: Database column

Blue_Boy25-Jul-08 11:59

Blue_Boy

25-Jul-08 11:59

Post your real code to see where is problem.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Crystal Report Title @ Run Time

Verghese25-Jul-08 5:33

Verghese

25-Jul-08 5:33

Hello Friends,

Please let me know if there's an easy to change the Report Title of Crystal Report at Runtime Coz I could see that once you create the Crystal Report there's no option to change the title else I have to start from the scratch creating a new Crystal Report and at that point of time adding the title what I want.

Please share if you know something about this.

Thanking you in anticipation.

Re: Crystal Report Title @ Run Time

SomeGuyThatIsMe25-Jul-08 5:52

SomeGuyThatIsMe

25-Jul-08 5:52

yes you can change it...assuming you mean some text thats displayed as a title. there isnt much you can do with the version of CR(2008) at runtime, at least as far as dynamic charts go, but assuming you know what section your title is in, you can loop through that sections report objects, looking for text objects, i cant remmeber if there are title objects, and then you can change the text of the objects yo ufind...something like

int i = 0;
CrystalReport rpt = new CrystalReport(the class should be the name of your rpt file - the extension)

foreach (TextObject to in rpt.Section1.ReportObjects)
{
to.Text = i;
i++;
}

this way you can see what the index of the box you want changed is.. i recomend reading anything by Brian Bischof(sp?) or picking up one or more of his books if you'll have to deal with CR alot, even ones written in an older version helped me alot. i think i have Crystal Reporting for .NET.

I gave up on CR a while ago so there may be an easier way, this was just the first method that came to mind. hope it helps.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Re: Crystal Report Title

David Mujica25-Jul-08 6:32

David Mujica

25-Jul-08 6:32

If you don't need to do it at run time, then you can change the title of the report by right mouse clicking on the file name (myreport.rpt), go to properties, then the Summary tab and change the name of the report. I thought this was a bit strange, but it works for me. Crystal Reports 10.

Re: Crystal Report Title

Verghese25-Jul-08 9:17

Verghese

25-Jul-08 9:17

I didn't meant to change the name of the report. I'm talking about the last section in the Crystal Report creation wizard which asks you to enter a title for the report and that title gets displayed every time you execute the report ( e.g Sales Summary Report - 2008 ). The top most title on the report page.

To be more clear, I'm talking about the : PAGE HEADER (SECTION 2) of the Crystal Report. And I don't see any option to change the title name.

Please comment.

Re: Crystal Report Title @ Run Time

Wendelius25-Jul-08 10:17

Wendelius

25-Jul-08 10:17

Hi,

After the report is created by the wizard:
- Select from menu Report / Report parameters
- Add a string parameter
- Right click on the title and select Expression
- you can see the static text in the expression, replace it with the parameter
- just before launching the report, deliver the value for the parameter

Hope this helps,

Mika

Dynamic Database Creation ??

C. L. Phillip25-Jul-08 4:13

25-Jul-08 4:13

I’m creating a SQL script for an installation package that I’m working on. I want to parameterize the database creation process. Wants to be able to set the database name in a variable, and have the SQL script use that variable to create the database and the necessary tables.
So far … this part works to create the database

USE [master]

/****** Object:  Database [FCDB]    Script Date: 07/24/2008 23:21:04 ******/
DECLARE @DBName varchar(60)
DECLARE @DBDir  varchar(100)
DECLARE @SQL    varchar(MAX)

SET @DBName = N'Test'
SET @DBDir = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\'

SET @SQL = N'CREATE DATABASE ' + @DBName + ' ON  PRIMARY 
      ( NAME = "' + @DBName + '", 
      FILENAME = "'+@DBDir + @DBName + '.mdf" , 
      SIZE = 4048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
      LOG ON 
      ( NAME = "' + @DBName + '-01_log", 
      FILENAME = "'+@DBDir + @DBName + '_log.ldf" , 
      SIZE = 4096KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
EXEC (@SQL)

EXEC dbo.sp_dbcmptlevel @dbname=@DBName, @new_cmptlevel=90

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC (@DBName + '.[dbo].[sp_fulltext_database] @action = "enable"')
End

However, I can’t seem to switch to the database in script, and create the tables in it
I’ve tried -> EXEC ('USE ' + @DBName)
And also -> USE @DBName

Could you help me please ? Thank you for your time

Cecil L. Phillip

TheFM23425-Jul-08 4:42

TheFM234

25-Jul-08 4:42

When you execute Exec('User' + @DBName), it is actually working. Since the exec syntax opens a new connection, your not retaining the new database in the connection your script in on. Your going to have to use 3 point qualifying names, or make a @Sql like:

Set @sql = 'Use ' + @DBName
+ ' --Do Something'

Then you'll probably run into errors about objects that must be ran first in the batch (stored procedures, for example), which "GO" will not fix because that is not a real sql syntax.

C. L. Phillip25-Jul-08 4:49

25-Jul-08 4:49

how would i use 3 point qualifying names and the @DBName with a create table statement or an insert ? Could you give an example ?

TheFM23425-Jul-08 5:14

TheFM234

25-Jul-08 5:14

Set @Sql = 'Create Table ' + @DbName + '.dbo.TestTable '
+ '( col1 int )'

Exec (@sql)

"3 point qualifying names" is Database.Schema.Table.

C. L. Phillip25-Jul-08 5:23

25-Jul-08 5:23

gotcha ...

I already generated the scripts to create the tables, populate them, and create the other db objects and its a lil over 5000 lines of sql code.

I guess there is not way to maintain the current connection and change the current database huh ... hhmmm

Thank you tho.

BhadeliaImran26-Jul-08 1:53

BhadeliaImran

26-Jul-08 1:53

Its better to use sp_executesql.

http://knowledgebaseworld.blogspot.com/2008/06/spexecutesql.html[^] OR http://del.icio.us/bhadelia.imran/SQL[^]

Wendelius25-Jul-08 9:53

Wendelius

25-Jul-08 9:53

Another point of view in creating an installation that might be useful for you.

If you create an initial database (in your own server) including all the necessary tables, views, stored procs etc. you can then:
- detach the database
- copy the data files to another location
- attach the database

After this you have a copy of the initial situation. What you can do with this is, that you can copy the files to another server (where you are installing your database) and again attach it in that sql server. This way you actually don't have to actually create any database or table creation script, simply *put* the database where you want it to be and attach it.

What you must do is to take care of:
- login creation
- user creation
- backup arrangement
- other feature used that are not stored in your database

For further info, take a look at sp_detach_db and CREATE DATABASE ... FOR ATTACH

Mika

Mycroft Holmes25-Jul-08 22:39

25-Jul-08 22:39

What I find horrifying is that you allow the user to set the database name!

Never underestimate the power of human stupidity
RAH

C. L. Phillip27-Jul-08 5:41

Sqlserver datatabase deployment along with setup file

27-Jul-08 5:41

i thought that was dumb too, but thats the process that the boss wants to follow. take in mind that we only develop internal applications. The users installing the app are actually the admins in the server room

vishnukamath25-Jul-08 1:45

vishnukamath

25-Jul-08 1:45

hi everybody

we developed windows application(c# + Linq),we have to create setup file for our application.How can i Deploy our application database along with our setup file.Please help me.

Thanks&Regards,
vishnu.

Re: Sqlserver datatabase deployment along with setup file

C. L. Phillip25-Jul-08 4:09