Database Discussion Boards

KeithF13-Aug-13 22:37

13-Aug-13 22:37

Hi Folks,

I have a quick question in relation to how i select data from a table, as seen in the table below i want to get rows where Person1 & Person2 are both on the same row, so in the table below i would just want Rows 1 & 2.

Table:

ID | Person1 | Person2 | . . .
1 | ABC | DEF | . . .
2 | DEF | ABC | . . .
3 | ABC | GHI | . . .
.
.
.

I have tried the following:

Select * From MyTable Where Person1 = 'ABC' OR Person1 = 'DEF AND Person2 = 'ABC' OR Person2 = 'DEF'

This does not have the desired effect however.

Where am i going wrong here?

Re: Select Statement in SQL Server

KeithF13-Aug-13 22:40

KeithF

13-Aug-13 22:40

Nevermind figured it out, was missing brackets in the SQL statement

KeithF wrote:
Select * From MyTable Where (Person1 = 'ABC' OR Person1 = 'DEF) AND (Person2 = 'ABC' OR Person2 = 'DEF')

Re: Select Statement in SQL Server

GuyThiebaut13-Aug-13 23:00

GuyThiebaut

13-Aug-13 23:00

This is another way of getting the same result:

SQL

Select * From MyTable Where Person1 in('ABC','DEF') AND Person2 in('ABC','DEF')

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

Re: Select Statement in SQL Server

KeithF13-Aug-13 23:20

KeithF

13-Aug-13 23:20

Thanks GuyThiebaut, that is cleaner code.

Re: Select Statement in SQL Server

Richard Deeming14-Aug-13 1:54

Richard Deeming

14-Aug-13 1:54

KeithF wrote:
Where (Person1 = 'ABC' OR Person1 = 'DEF) AND (Person2 = 'ABC' OR Person2 = 'DEF')

Your query will also return rows where Person1 and Person2 are the same value. It's not obvious from your description whether that would be a problem, or even if it's possible in your database.

If you only want rows which have both people on the row, try:

SQL

WHERE 'ABC' IN (Person1, Person2) And 'DEF' In (Person1, Person2)

or the slightly longer version:

SQL

WHERE (Person1 = 'ABC' And Person2 = 'DEF') Or (Person1 = 'DEF' And Person2 = 'ABC')

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Select Statement in SQL Server

KeithF14-Aug-13 2:01

KeithF

14-Aug-13 2:01

Thanks Richard,

It would not be possible for ABC to exist in Person1 and Person2 in the same row, but its no harm using the syntax you describe to circumvent it in case it were to happen.

Connecting to a Database in SQL Server 2008R2 Express

Bram van Kampen13-Aug-13 14:41

Bram van Kampen

13-Aug-13 14:41

Hi,

I had long running queery last May, early June. I got all sorts of errors trying to connect.
Then the VS2012Express Compiler gave up!
Eddy Vluggen made great effords to help me, but, in the face of a non compiling compiler, I had to call it a day.

That Compiler has now been fixed with the traditional indecent haste on behalf of Microsoft, (the Last Auto Ugrade apparently fixed it,got no notice of the fix, I hope it will last) However, the DB Connection String Problem has NOT Gone away. I now do wonder, Is this another Microsoft Issue, or am I doing something wrong!

I have the following (More or Less)from Eddy Vluggen, and in line with the documentation.:-

public static String GetConnectionString()
{
    string Result="server=b-pc\\Softguard;"+
                               "database=SgTextiles; " +
                               "Name=sa;Password=12345;"+
                               "connection timeout=5;";
    return Result;



}

The Code that uses this is:

String ConnectionString = GetConnectionString();
SqlConnection myConnection = new SqlConnection(ConnectionString);

I get the Exception:"System.ArgumentException: Keyword not supported: 'name'."

Irrespective of DB Configuration issues, the parser of the Connect String seems to choke on the Keyword 'name' (incidentely confirming the Non Case Sensitivity of the particular keyword parser).

Kind Regards Smile | :)

Bram van Kampen

Re: Connecting to a Database in SQL Server 2008R2 Express

PIEBALDconsult13-Aug-13 16:24

PIEBALDconsult

13-Aug-13 16:24

Shouldn't Name be User ID?

Re: Connecting to a Database in SQL Server 2008R2 Express

Richard MacCutchan13-Aug-13 21:13

Richard MacCutchan

13-Aug-13 21:13

See http://www.connectionstrings.com/sql-server-2008[^].

Use the best guess

How to work with databases

Patrick Skelton13-Aug-13 6:26

Patrick Skelton

13-Aug-13 6:26

Hi,

Apologies for the length of this question.

Does anyone know where to find information how to develop on MSSQL? I'm not looking for information on database design (Normal Forms etc.), but on how you actually work with it. For example, it's easy to create a new database in Visual Studio, but how do I then get this onto my web hosting server? How do I make sure I can repeat this process if need be? Can I create a new database with the same schema as an existing one? Is there a recognised way to set up the tables and relationships used by the security classes built into .NET? The list goes on.

I find information on designing databases everywhere, but I can't find anything good on how you actually work with them day-to-day. What are the best practices? I am a one-man-band, starting a project that uses an MSSQL database, and while the database won't be doing anything 'heavy' initially, I don't want to get into deep water. I'd like to do things 'right'.

Any advice would be gratefully received.

Kind wishes - Patrick

Thank you to anyone taking the time to read my posts.

Re: How to work with databases

Richard Deeming13-Aug-13 8:11

Richard Deeming

13-Aug-13 8:11

Patrick Skelton wrote:
it's easy to create a new database in Visual Studio, but how do I then get this onto my web hosting server?

That depends on your hosting company. The obvious options would be:

Backup your local copy of the database, copy the files to the server, and restore;
Use the "Generate Scripts" option to create a SQL script which you can run on the server to create the database;
If the host supports it, use Web Deploy[^];

Patrick Skelton wrote:
How do I make sure I can repeat this process if need be?

If you're overwriting the remote copy of the database, it's simply a case of repeating the initial deployment. If you just want to deploy structural changes, you'll need to create the SQL scripts to make the changes on the server.

Patrick Skelton wrote:
Can I create a new database with the same schema as an existing one?

Schemas are local to the database. Two schemas in different databases with the same name are not connected in any way.

Patrick Skelton wrote:
s there a recognised way to set up the tables and relationships used by the security classes built into .NET?

If you're using the SQL providers, then the ASP.NET SQL Server Registration Tool[^] is the way to go. However, these providers are quite old; you might prefer to look at the newer "Universal" providers[^].

As for your broader question, I presume you've looked at the SQL documentation on Technet[^]?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: How to work with databases

Patrick Skelton13-Aug-13 22:19

Patrick Skelton

13-Aug-13 22:19

Thank you for taking the time to write that reply. I will dive in with your suggestions.

- Patrick

Thank you to anyone taking the time to read my posts.

Re: How to work with databases

jschell13-Aug-13 8:57

jschell

13-Aug-13 8:57

Patrick Skelton wrote:
Does anyone know where to find information how to develop on MSSQL?

I buy books.

Re: How to work with databases

Patrick Skelton13-Aug-13 22:16

Patrick Skelton

13-Aug-13 22:16

Okay - can you recommend any good books?

Thank you to anyone taking the time to read my posts.

Re: How to work with databases

Mycroft Holmes13-Aug-13 12:59

Mycroft Holmes

13-Aug-13 12:59

Sage advice from both Patrick and j, I presume you are having trouble finding reference on developing sql server[^]

Also sqlservercentral.com[^] is a good resource but they tend more towards the DBA skills. As a one man show you are going to have to get a good handle on the development cycle, building the app is only the core skill.

POH has some excellent articles [^]you may be interested in

Never underestimate the power of human stupidity
RAH

Stored Procedures

Mahdi_kishislan12-Aug-13 2:07

Mahdi_kishislan

12-Aug-13 2:07

Hi
i want to design database that have different object but with the same structure .
we have feeling table with this structure (code,name,id) also zone table with (code,name,id).
my question is ,i have to design separate procedure for insert & update & delete or i can use one procedure for both table ??
is it possible to send table name??
is it good way to send table name from DAL OR BLL layer?
please help me

Re: Stored Procedures

David Mujica12-Aug-13 2:38

David Mujica

12-Aug-13 2:38

Keep it simple.

Create 2 stored procedures.

Just my opinion. Java | [Coffee]

Re: Stored Procedures

Mahdi_kishislan12-Aug-13 2:51

Mahdi_kishislan

12-Aug-13 2:51

tnx david.but i have 8 table with this structure .we have to design (delete,update,insert,SearchByName,SearchCode,....)store procedure .now what is the best way??

Re: Stored Procedures

David Mujica12-Aug-13 7:19

David Mujica

12-Aug-13 7:19

Since the data structure of the table seems to be identical across these 8 tables, why don't you create a "super table" which contains the name of the table, followed by the other columns ?

Data would look like:

Feeling,code,name,id
Zone,Code,name,id
Table6,Code,name,id

This way you would only implement a single set of Select,Insert,Upadate,Delete, just passing the name of the table as part of the "Where" clause.

Kind of hard to describe, but hopefully you get the idea. Cool | :cool:

Good luck. Thumbs Up | :thumbsup:

Re: Stored Procedures

Mahdi_kishislan12-Aug-13 21:18

Mahdi_kishislan

12-Aug-13 21:18

tnx

Re: Stored Procedures

Mahdi_kishislan16-Aug-13 21:17

Mahdi_kishislan

16-Aug-13 21:17

tnx david

Re: Stored Procedures

Eddy Vluggen12-Aug-13 8:13

Eddy Vluggen

12-Aug-13 8:13

Mahdi_kishislan wrote:
now what is the best way??

The best way would be a design where each table has a specific structure optimized for the data it is holding. By using a dump-table where all has the same structure, you'll loose a lot of advantages that a usual database offers - simple things like references and type-safety.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Design

David Mujica12-Aug-13 9:27

David Mujica

12-Aug-13 9:27

I totally agree. It seems that his design is kind of lacking.

I just tried answering his question. Sounds more like a homework assignment than a real project.

Re: Stored Procedures

Corporal Agarn12-Aug-13 3:08

Corporal Agarn

12-Aug-13 3:08

You can use dynamic SQL but I would not suggest that. Create the stored procedures. It is easy to use cut and past for creating all you need, and if one table changes you only change on procedure.

Another way would be to pass the table name into the procedure and have an IF ELSE IF structure within the procedure to update the tables.

Re: Stored Procedures

Anudeep Jaiswal - MCA7-Nov-13 16:52

Anudeep Jaiswal - MCA

7-Nov-13 16:52

I Think You Should Add One More Column To Your Main Table As Type

Now Your Table Structure Is (Code,Name,ID,Type)

Now In Type Column You Can Insert Feeling Or Zone.

And Now One One Insert Update And Delete Procedure Can Do The Same, As You Required.

-------------------------------------------------------------------
Another Approach

You Should Pass Table Name To Your Procedure And Create Dynamic Query.
To Execute That Dynamic Query Use SP_ExecuteSQL

Hope This Will Help You.......

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.