|
Hi Juan. I wish my Spanish were as good as your English.
My initial question is why are you manipulating rows in an in-memory dataset, rather than just INSERTing a row in the database in TableA, then INSERTing related rows in TableB? Upon inserting the row in Table A, you can retrieve the newly created AutoNumber, by issuing a SELECT @@Identity.
Here's a (very raw, but complete) example of what I mean (in C#):
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="C#" %>
<script runat="server">
void btnSubmit_Click(object o, EventArgs e)
{
const string kCONNECT_STRING =
"Provider=Microsoft.Jet.OLEDB.4.0; "
+ @"Data Source=c:\inetpub\wwwroot\tests\test.mdb";
const string kINSERT_STATEMENT =
"INSERT INTO [MyTable] ([E-Name], [E-Email]) "
+ " Values (@pName , @pEmail) ";
OleDbConnection con = null;
OleDbCommand cmd = null;
OleDbParameter pName = null;
OleDbParameter pEmail = null;
int insertedID = -1;
try
{
con = new OleDbConnection(kCONNECT_STRING);
con.Open();
cmd = new OleDbCommand(kINSERT_STATEMENT, con);
pName = new OleDbParameter("@pName", DbType.String);
pName.Value = tbName.Text;
cmd.Parameters.Add(pName);
pEmail = new OleDbParameter("@pEmail", DbType.String);
pEmail.Value = tbEmail.Text;
cmd.Parameters.Add(pEmail);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@Identity";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
insertedID = (int)cmd.ExecuteScalar();
Response.Write(
string.Format("Record has been inserted and assigned ID# {0}."
,insertedID) );
}
catch (Exception x)
{
Response.Write("There has been an error. ");
Response.Write(x.Message);
}
finally
{
if (cmd != null) cmd.Dispose();
if (con != null) con.Dispose();
}
}
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<h3>Insert into Access</h3>
<table>
<tr>
<td>Name</td>
<td><asp:TextBox id="tbName" runat="server" /></td>
</tr>
<tr>
<td>Email</td>
<td><asp:TextBox id="tbEmail" runat="server" /></td>
</tr>
</table>
<asp:Button id="btnSubmit" runat="server" text="Insert"
onclick="btnSubmit_Click" />
</form>
</body>
</html>
|
|
|
|
|
I see. I think I was trying to do things "too off-line". I read from the very start that behind ADO.NET lies a disconnected philosofy, but it is clear that some tasks are very difficult to accomplish that way. I was trying to do all the changes off-line, and then run a bunch of DataAdapter.Update() to commit all the changes. In this case, when tables relies on PK of other tables, then the INSERTs should be done at once to obtain a solid PK, isn't it?
Thanks for your help,
Juan Pedro Pérez
|
|
|
|
|
I would say that ADO.NET supports a disconnected DataSet - disconnected in that it is not specifically tied to any brand of relational database (and in fact can be created entirely in memory if desired).
But ADO.NET does support a consistent model of connecting to any brand of relational database (that has an ADO.NET provider) to either query for data, or modify data.
A common operation for example, is to use connection-specific objects to issue a SELECT statement to a specific database. For SQL Server, these objects would be SqlConnection, SqlCommand, and SqlDataAdapter. For an Access file, these objects would be OleDbConnection, OleDbCommand, and OleDbDataAdapter. The DataAdapter object is then used to .Fill a disconnected DataSet with the SELECT results. The disconnected DataSet may then be used throughout your application as necessary.
This is particularly useful when designing N-tier applications, where you want to separate database-specific code (into a Data tier) from your presentation code (.aspx pages). For example, your Data tier (think: custom class) could have a method that encapsulates the code necessary to connect to an Access database and query for a list of employees. This method could return this data as a DataSet object. The presentation code (your .aspx page) could then call this method and databind the resulting DataSet object to, for example, a GridView.
Now imagine that you want to replace your backend database, upgrading from Access to SQL Server. You would need to change the database-specific code in your Data tier, but you would not have to change your presentation layer at all. As long as your method for retrieving employees from SQL Server returns the disconnected DataSet, which is what your presentation layer expects, you don't need to make changes to the presentation code.
This is certainly an over-simplification of a discussion on N-tier architecture and Data tiers, but hopefully it gives you a sense of why the disconnected DataSet object in ADO.NET is useful (but also why you'll still use Database-specific code in ADO.NET too).
|
|
|
|
|
Got it. Thanks for your clear explanation. The deployment in N-Tier you described is what I was trying to implement, with little success  I find the learning curve of ADO.NET a lot stepped as conventional ADO, but I know it's worth the time. A lot of more hours of programming and everything will be OK.
Greetings,
Juan Pedro Pérez
|
|
|
|
|
I have get some records by OleDbDataReader like this,
OledbDataReader reader=cmd.ExecuteReader();
How to get the amount of records from it?
Thanks!
|
|
|
|
|
rushing wrote: How to get the amount of records from it?
Read all the records from it counting them as you go. There is no other way with a DataReader.
But, you do have the option of of getting the database to tell you in advance (assuming you need to know in advance) by sending off a query to find out how many rows it expects to return.
You can do this in the same command as your main data, or you can use a separate command.
If it is the former you can do something like this:
OleDbCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = "SELECT COUNT(*) FROM MyTable; SELECT * FROM MyTable";
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read();
int numRows = reader.GetInt();
reader.NextResult();
while(reader.Read())
{
}
Or you can use a separate command object requesting the count of the rows and use ExecuteScalar() on it.
Does this help?
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
|
|
|
|
|
Thanks.
However, this sql statement does not work in Oracle.
It is seem that we could not write two sql statement in one line.
|
|
|
|
|
Hi All!!
I am working through the documentation online of the Adventure Works database for SQL Server 2005.
Is there any .NET 2.0 application that I can download that works well with this database?
And secondly, is see they name there tables like Person.Address, Person.AddressType, Person.Contact... etc. What does this mean? What is this feature called so that I can do some more reading on this topic.
Regards,
Brendan
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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=mc2 -> BOOM
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Your real problem is SQL injection! Use parameters! You cannot put everything between quotes (for example, dates).
|
|
|
|
|
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
|
|
|
|
|
Thanks Colin and George for your kind concern.
and I'm already validataing input via TextBox_TextChanged.
Thanks;)
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
