Database

www.Developerof.NET22-Aug-08 22:17

www.Developerof.NET

22-Aug-08 22:17

Sujay chakraborty wrote:
string selCmd = "SELECT eInfo.EmpID, eInfo.Name, lInfo.LeaveEnteredOn, lInfo.LeaveFrom, lInfo.LeaveTo, lInfo.LeaveType" +
"FROM lmsEmpInfo AS eInfo INNER JOIN lmsLeaveInfo AS lInfo ON lInfo.EmpID = eInfo.EmpID" +
"WHERE (lInfo.LeaveGranted = 0)";

First of all there should be some space in the string where u concat the 3 strings...

and for ur other query pls clear ur question a bit more...

When you fail to plan, you are planning to fail.

Sujay chakraborty23-Aug-08 21:19

Sujay chakraborty

23-Aug-08 21:19

well first of all thanx for helping me out with such silly mistakes. I really appreciate that.

well as far as my second doubt is concerned, what i m trying to do is to fetch records from the database to a DataGridView according to the query passed(i.e. a select command which will fill up the table in the dataset), what i m doing is m not externally attaching this DataGridView with the concerned table (from the properties of the datagridview), rather after filling up the dataset and the table m doing this in the SelectedIndexChanged event of the ComboBox:

DataGridView1.DataSource = null;
DataGridView1.DataMember = null;
DataGridView1.Refresh();
myAD.Fill(ds,"tbl1"); //myAD is DataAdapter and ds is the DataSet
DataGridView1.DataSource = ds;
DataGridView1.DataMember = "tbl1";
DataGridView1.Refresh();

well this code works fine for the first time and fetches me the exact result, but when i change the item in the combobox it still keeps the previous previous recods and adds the new ones below. I want the previous records to disappear.I tried the method DataGridView1.Rows.Clear() but its giving me error.

Please do explain me what is actually happening wrong because m new to this technology, your help is greatly awaited!!! Smile | :)

Regards
Sujay

Paul Conrad23-Aug-08 7:29

Paul Conrad

23-Aug-08 7:29

Your code is subject to sql injection attacks with the string concatenation. Look up Colin Mackay's article on this site about preventing such attacks.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham

Kanniah23-Aug-08 20:22

Kanniah

23-Aug-08 20:22

"SELECT eInfo.EmpID, eInfo.Name, lInfo.LeaveEnteredOn, lInfo.LeaveFrom, lInfo.LeaveTo, lInfo.LeaveType " +
" FROM lmsEmpInfo AS eInfo INNER JOIN lmsLeaveInfo AS lInfo ON lInfo.EmpID = eInfo.EmpID " +
" WHERE (lInfo.LeaveGranted = 0)"

When you try to form an Sql by Concating always insert one space before '"'

Hope this helps

Kan

SQL query to copy table from one database into another database

Pete O'Hanlon24-Aug-08 11:11

Pete O'Hanlon

24-Aug-08 11:11

Kanniah wrote:
When you try to form an Sql by Concating always insert one space before '"'

Are you really suggesting that this prevents a SQL Injection Attack? Please tell me that you are kidding, and that you know that this is absolute rubbish.

Deja View - the feeling that you've seen this post before.

My blog | My articles

ankswe22-Aug-08 19:24

ankswe

22-Aug-08 19:24

Hi,

I am a beginner in SQL server 2005. I have two databases. In the first database I have a table which I want to copy in the second database (with values and structure).

Please advise how can this be achieved through a sql query.

Thanks in advance,
Ankit Jain

Re: SQL query to copy table from one database into another database

Mycroft Holmes22-Aug-08 19:41

Re: SQL query to copy table from one database into another database

22-Aug-08 19:41

Presumaby they are in the same server!

Select *<br />
into Database2.dbo.Table1<br />
From Table1

You can also import the table from 1 database to another using management studio, SSIS package, DTS and of course a UI. You should do some reaserch or reading to get a handle on what you are asking.

Never underestimate the power of human stupidity
RAH

Jerry Hammond23-Aug-08 7:37

Jerry Hammond

23-Aug-08 7:37

Mycroft Holmes wrote:
You can also import the table from 1 database to another using management studio, SSIS package, DTS and of course a UI. You should do some reaserch or reading to get a handle on what you are asking.

I beleive that DTS is deprecated in SQL Server 2005. SSIS (SQL Server Intergration Services) is the replacement for DTS.

“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan

Re: SQL query to copy table from one database into another database

Mycroft Holmes23-Aug-08 13:48

Connecting to custom database [modified]

23-Aug-08 13:48

Jerry Hammond wrote:
I beleive that DTS is deprecated

True, I meant to type BCP.

Never underestimate the power of human stupidity
RAH

ASPnoob22-Aug-08 17:31

ASPnoob

22-Aug-08 17:31

Hello All,
I've been trying to connect to a custom database for weeks without success. I was planning to use the custom database to store users login info, role, etc. I ran aspnet_regsql.exe, tried to tweak the web.config file of my webpage, then set up users login etc using Website Administration Tool. Needless to say I was not able to connect to my database. I think that its probably the Configuration block in my Web.config that is causing the problem. The following is what the article that I was using as a guide, said I must do in order to connect to a custom database.

a. provision your database. you can do this, by running aspnet_regsql.exe at a .net command prompt. This will add the necessary tables to a SQL Server database of your choosing. The default is named aspnetdb but you can change that.

b. add the following to your web.config file:

<configuration>
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer"
connectionString="Data Source=localhost;
Initial Catalog=aspnetdb;
Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>

The part in the configuration section above that I don't quite understand is where LocalSQLServer is removed and then added back in, what is that about? Also where it says Data Source=localhost, what if the data source is not localhost, what do I use in its place? Is Integrated Security=True the result of running the aspnet_regsql.exe and choosing Integrated Security while in side of aspnet_regsql? I hope the answers to these questions will help solve my problems. Thank you in advance for your help.

modified on Saturday, August 23, 2008 1:25 AM

modified on Saturday, August 23, 2008 1:30 AM

Re: Connecting to custom database

Mycroft Holmes22-Aug-08 19:47

22-Aug-08 19:47

AAahhhh - you need to get an understaning of database servers, get hold of a beginner book on SQL Servers please.

I have no idea why it is removing the localserver and then adding it, possibly to insure the destruction of existing connections or registrations.

The rest of it is a connection string try this site[^] it may help you to understand the usage of a connection string. It basically dictates what you connect to and supplies the credentials.

Integrated security is active directories. Depends on how your server is configured, most servers are configured to accept both integrated and sql standard authentication.

Never underestimate the power of human stupidity
RAH

Expansion advice

David Kalkwarf22-Aug-08 16:16

David Kalkwarf

22-Aug-08 16:16

I need some advice on how to design for expansion.

I have a typical Employees table. It contains first name, last name, etc. I also need to track whether each employee can perform certain tasks (e.g. bricklayer, cook, brain surgeon). Note that these tasks are not mutually exclusive: they can perform all, none, or some. Also, note that some tasks require additional attributes. For instance, a cook may have a specialty (e.g. Italian, Chinese...).

In addition, I want to be able to add tasks later without upsetting the database (e.g. modify a table) too much. Ideally, this would mean no changes to an existing table.

I could break each task into a table of its own. But, some of these tables would only contain one field, the key from the Employees table. This doesn't seem right.

Another option is to include a field in the Employees table to track the tasks the employee can perform. Perhaps some bit-encoded valued with one bit for each task. But, this has the shortcoming of not allowing additional attributes, like a cook's specialty, without adding a new field (violates my wish of not wanting to change the table later).

Is a table with one field OK? Is it catastrophic to add a field to a table later? Is it bad to keep heaping fields into my Employees table?

Any advice is welcome.

FyreWyrm22-Aug-08 18:01

FyreWyrm

22-Aug-08 18:01

I'm not an expert, but I've seen systems similar to the one you're describing. For the sake of normalization I wouldn't put more fields into your Employee table. What you need is a mapping table. Since the tasks are not mutually exclusive you can create a table with a field for the employee's id and a field for the task id (assuming each task has it's own id in the tasks table). This way you can have multiple tasks per employee as each combination will just be an entry in this table. The only kicker is the attributes for the tasks. The only advice I can give on this one to have a generic "attributes" field in the Task table which contains the id of a special attribute from an Attributes table. This idea is still a little fuzzy though.

As for tables with one field, I believe those should be reserved for tables used as data sources for comboboxes. At the minimum, all tables should have two fields; id and item.

Does any of this make sense? It's late and I'm tired.

David Kalkwarf23-Aug-08 5:35

David Kalkwarf

23-Aug-08 5:35

Thanks for the reply. Your suggestions are similiar to the previous response and I have the same questions.

Actually, you anticipated the problem with the Attributes table, but it applies to the Tasks table as well. That problem is: What is the type of the field that identifies the Task or Attribute? What does your Tasks table look like? What is the field that identifies the task (TaskName?)? I suppose one could use a string, but then I have to know those strings when I do my SELECTs. This doesn't seem right.

This is how/why I arrived at tables for each task: the table itself implied that it only contained employees who perform the given task. This also eliminated the problem of attributes for a given task. But this design yields tables containing only one field and that didn't seem right either.

Thanks again for the reply and any more suggestions are welcome.

Mycroft Holmes22-Aug-08 19:58

22-Aug-08 19:58

Four tables
Employees
ID
Employee details....

Tasks
ID
Task details....

Task Attributes
ID
TaskID
Attribute details....

LinkEmpTask
ID
EmployeeID
TaskID

Link file is a many to many relationship table. A task can have many attibute, an employee can do many tasks and a task can be done by many employees. This is a basic normalisation excercise and examples can be in any textbook

Never underestimate the power of human stupidity
RAH

David Kalkwarf23-Aug-08 5:09

David Kalkwarf

23-Aug-08 5:09

OK, this has same possibilities, but...

What (type) is the Tasks.TaskDetails field? Put another way: What identifies the task as a bricklayer, cook, or brain surgeon? I suppose a string would be OK...

The same questions apply to the TaskAttributes.AttributeDetails field, though it is more complicated here. For instance, how do I represent a cook's specialty (Italian, Chinese) as well as a brain surgeon's preferred nurse? Also, how do I represent mutliple attributes for a single task? Again, I am wondering about the type of the field. A string can be used, but then you probably end up encoding information in the string (e.g. "Specialty: Italian").

Thanks for the feedback.

Mycroft Holmes23-Aug-08 13:46