Database

Good ADO/ SQL Server 2005 Books

16-Feb-06 2:24

ma se wrote:
Is there any .NET 2.0 application that I can download that works well with this database?

Not that I know of. There are plenty of tutorials that are based on it: Adventure Works Tutorials[^]

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Brendan Vogt15-Feb-06 20:57

Brendan Vogt

15-Feb-06 20:57

Hi,

Can someone please advise on some good ADO.NET and SQL Server 2005/Express books? It's always better getting advice from someone that already has hands on experience with a particular book. What I am looking for is a good beginners book, and intermediate book, and an advanced level book.

ISBN numbers would be appreciated.

Regards,
Brendan Vogt

SQL Log viewer

devvvy15-Feb-06 20:52

devvvy

15-Feb-06 20:52

Hi, I need to review SQL server log and I need some filtering/ordering/search capability. Any recommendation? Any open source/free tools available for the purpose?

Thanks!

SQL 2005 MSDN connections

Tad McClellan15-Feb-06 17:51

Tad McClellan

15-Feb-06 17:51

Somehow I think I remember that SQL 2000 installs from an MSDN subscription are limited to 10 connections. Is that the same with 2005? Even if you pool connections?

Thanks in advance.

E=mc² -> BOOM

Re: SQL 2005 MSDN connections

Paul Conrad15-Feb-06 19:10

Paul Conrad

15-Feb-06 19:10

Albert,

For your information, here is a link with the limitations of SQL 2005 Express:

SQL 2005 Express limitations

I hope this is a start for you.

Paul

No SQL Server Management Tools

Brendan Vogt14-Feb-06 23:40

Brendan Vogt

14-Feb-06 23:40

Hi,

I downloaded and installed the trial version of SQL Server 2005, which lasts for 180 days. When you extract the file it creates a folder on your C drive with 2 subfolders, namely Servers and Tools. I installed everything under Servers, then tried installing the tools. Made my selection and I got a message saying "None of the selected can be installed or upgraded. Setup can not proceed since no effective change is being made to the machine..." Is this because these management tools have been installed already?

When I installed it there is a menu in Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools, then there is the following items:

* SQL Server Configuration Manager
* SQL Server Error and Usage Reporting
* SQL Server Surface Area Configuration
* Notification Services Command Prompt
* Reporting Services Configuration

Please let me know what is wrong, I need the SQL Server Management Studio.

And one last thing. Can I connect to SQL Server 2000 with this management tool?

Regards,
ma se

Re: No SQL Server Management Tools

Colin Angus Mackay15-Feb-06 0:38

Re: No SQL Server Management Tools

15-Feb-06 0:38

Although I cannot help on the set up problems (everything went flawlessly for me - sorry, that's not much consolation) I can answer this:

ma se wrote:
Can I connect to SQL Server 2000 with this management tool?

Yes. I've found it works very well. The only thing that didn't work was that it couldn't deal with any diagrams that were set up in the SQL Server 2000 database.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Brendan Vogt15-Feb-06 1:02

Brendan Vogt

15-Feb-06 1:02

Do you have SQL Server Express Edition installed as well? I have both, I wonder if it's not because of the Express edition "being in the way"?

I wonder if you can do queries from SQL Server 2005 through Visual Web Developer, SQL Server Express comes with VWD, and you can do queries through VWD.

What IDE do you code in?

Re: No SQL Server Management Tools

Colin Angus Mackay15-Feb-06 1:07

Urjent SQL Exception Problem

15-Feb-06 1:07

ma se wrote:
Do you have SQL Server Express Edition installed as well?

Yes, but I don't recall in which order they were installed.

ma se wrote:
I wonder if it's not because of the Express edition "being in the way"?

I wouldn't think so. The Express Edition is just the database engine and not much more.

ma se wrote:
What IDE do you code in?

Visual Studio 2005 Professional Edition. But I still use SQL Server 2000's Query Analyzer for scripting.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

cbhkenshin14-Feb-06 21:20

14-Feb-06 21:20

Hi:

SQL Exception Message in C#:
Message:
The name 'SqlCommand' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

CODE:
//opening Conn
sqlConnection1.Open();
//creating SQL Command
SqlCommand insert_item = new SqlCommand("INSERT INTO inventory_item(stock_no, name, type, net_location, tag, brand_name, model_no, serial_no, machine_type_p_wh, oper_sys, ram, display, sound_card, nic, monitor, acess_type, computer_full_name, warranty, status, comments, emp_no, cate_no, supp_no) VALUES ('"+te_item_stock_no.Text+"','"+te_item_name.Text+"','"+te_item_type.Text+"','"+te_item_location.Text+"','"+te_item_tag.Text+"','"+te_item_brand_name.Text+"','"+te_item_model_no.Text+"','"+te_item_serial_no.Text+"','"+te_item_pentium.Text+"','"+te_item_os.Text+"','"+te_item_ram.Text+"','"+te_item_display.Text+"','"+te_item_sound_card.Text+"','"+te_item_nic.Text+"','"+te_item_monitor.Text+"','"+te_item_access_type.Text+"','"+te_item_computer_name.Text+"','"+te_item_warraty.Text+"','"+te_item_status.Text+"','"+te_item_comments.Text+"',"+"NULL"+","+cate_no_FK+","+supp_no+");", sqlConnection1);
//executing
int rows_affected = insert_item.ExecuteNonQuery();
//closing
sqlConnection1.Close();

Message:
The name 'SqlCommand' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

cbhkenshin14-Feb-06 21:59

14-Feb-06 21:59

Solved

cuz
in VALUES('test',...)

you only put the new value with specifying the column.

i used the sql from SQL server but ut only works there and not in C#.

Arigato Menna

George L. Jackson15-Feb-06 13:34

George L. Jackson

15-Feb-06 13:34

Your real problem is SQL injection! Use parameters! You cannot put everything between quotes (for example, dates).

Colin Angus Mackay15-Feb-06 21:54

15-Feb-06 21:54

To follow up on what George L. Jackson said: You might find this article, SQL Injection Attacks and tips on how to prevent them[^], to be useful.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

cbhkenshin17-Feb-06 19:26

17-Feb-06 19:26

Thanks Colin and George for your kind concern.

and I'm already validataing input via TextBox_TextChanged.

Thanks;)

Colin Angus Mackay17-Feb-06 23:44

Join 3 Fields - Show all records From Table 1

17-Feb-06 23:44

cbhkenshin wrote:
I'm already validataing input via TextBox_TextChanged.

It doesn't matter, you should use parameters as a matter of course. Your validation may have an unexpected flaw it in that you are not aware of. If you have available a tool to improve the security of your application that isn't going to take any longer to implement that they way you were doing before (and using parameters instead of injecting values has other advantages as well) then it should be used.

I occasionally have to do security audits of code. If you inject a value into a SQL string where it was possible to use a parameter I'd fail the code. Its as simple as that. It wouldn't matter that I see you validating the input (which is a very commendable thing to do, and you should continue to do that). I don't know every possible attack vector, so I use parameters as an extra safe guard in case I've forgotten about something.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

RichardBerry14-Feb-06 21:10

RichardBerry

14-Feb-06 21:10

Hi
Table1 has Warehouse, Product, Line_No, ....

Table2 has Warehouse, Product, Line_No, Circuit_Ref.

Table1 has a record for all Products

Table2 has records for some products

How do I write an MS-SQL query that will join on all 3 fields(Warehouse, Product and Line_no), and show ALL rows from Table1 and show Circuit_Ref from Table2 if it exists.

Thanks

Richard

CWIZO14-Feb-06 21:24

CWIZO

14-Feb-06 21:24

Please post a more detailed description of you database structure and provide some sample data.

Q:What does the derived class in C# tell to it's parent?
A:All your base are belong to us!

RichardBerry14-Feb-06 23:32

RichardBerry

14-Feb-06 23:32

Hi
1) Table bmassdm is a full list of products
bmassdm fields: warehouse, product_code, circuit_number

2) Table bmasscm contains circuit references for some of the items in bmassdm
bmasscm fields: warehouse, product_code, circuit_number, circuit_reference

I need to select all the rows from bmassdm, and need to get the circuit_reference from bmasscm if there is one

The statement below gets ONLY the rows from bmassdm that ALSO have a circuit_reference

strSelect = "SELECT bmassdm.warehouse + " _
& "bmassdm.product_code, " _
& "bmasscm.circuit_reference " _
& "FROM (bmassdm INNER JOIN bmasscm " _
& "ON (bmassdm.product_code = bmasscm.product_code) " _
& "AND (bmassdm.assembly_warehouse = bmasscm.assembly_warehouse) " _
& "AND (bmassdm.circuit_number = bmasscm.circuit_number) " _

cbhkenshin14-Feb-06 21:42

14-Feb-06 21:42

RichardBerry wrote:
Table1 has Warehouse, Product, Line_No, ....

Table2 has Warehouse, Product, Line_No, Circuit_Ref.

Hi Richard:

First of all what i've understod from your message that you have table1 and table2 which both have their Warehouse, Product, Line_No in common and thats Wrong you should normlize your database.

I'll give you a query that will give you an answer to joining.

SELECT inventory_item.stock_no, inventory_item.type, accessories.name, hard_disks.model_no
FROM inventory_item INNER JOIN accessories ON inventory_item.accs_no = accessories.accs_no INNER JOIN hard_disks ON inventory_item.item_no = hard_disks.inv_no

Colin Angus Mackay14-Feb-06 22:34

14-Feb-06 22:34

cbhkenshin wrote:
you have table1 and table2 which both have their Warehouse, Product, Line_No in common and thats Wrong

I would suggest that there is insufficient information to determine whether the model is a good or bad one. Normalisation is not always the lofty goal that a lot of people think it should be. For example, in a reporting senario normalisation is a hinderance rather than a help. However, that said, most data models would benefit from better normalisation.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell