|
I'd recommend not using Access if you can help it. If you own SQL Server, you can distribute MSDE to your clients. Then you can use stored procedures, which IMO simplifies the data layer enormously. Also, you can install SQL Buddy or similar and be able to browse the data on a client machine if you ever need to.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
Why not Access?
I don't own SQL server (that I know of).
------- sig starts
"I've heard some drivers saying, 'We're going too fast here...'. If you're not here to race, go the hell home - don't come here and grumble about going too fast. Why don't you tie a kerosene rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Because Access is crap. I just bought the developer edition of SQL Server for $89, so it's bound to cost around $40-$50 in the states. That gives me the right to distribute MSDE (which is SQL Server, but cut down so it doesn't like to have more than 20 ( from memory ) users at once - perfect for a desktop app ). Access does not support stored procedures AFAIK. That alone is the end, as far as I am concerned.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
The DTL is a pretty nice way to talk to databases from C++. Makes recordsets look like STL collections and does a pretty good job with disconnected data.
Much nicer than the whole Recordset.Next() thing.
Uses ODBC, so access should be fine.
Ryan.
It's yet another shock to our system to discover that not only are we not the centre of the Universe and we're not made of anything, but we started out as some kind of slime and got to where we are via being a monkey. It just doesn't read well.
Douglas Adams
|
|
|
|
|
From my experience I think ADO is good choice. I have been using Carlos Antollini's ADO classes for a few years now, in combination with Access2000 data sources, without any problems. All those apps had limited users (max 10) and relative small databases.
I have been using the classes in VC++ 6.0, VS.NET 2002 and VS.NET 2003.
|
|
|
|
|
Hi All!
I need to put a column in a table and want that column to store the date at which that particular row was updated or some new row is added into the table.
I am doing this to check when the table is updated.
Kindly guide me for creating such a column in SQL.
With best regards,
Zee_Zee
|
|
|
|
|
If you want to store the time a row is inserted, create a default on it with a value of getdate()
e.g.
create table foo<br />
(<br />
bar datetime default getdate()<br />
)<br />
If you want to know the time a row is updated, try using a trigger and setting the time (via getdate) in the trigger.
store your internet favourites online - www.my-faves.co.uk
|
|
|
|
|
Hi all,
I have a problem. I have a database "Users", there are usernames, passwords, and other information, and I'm doing on the registration page - I want to be sure there will be no duplicity of usernames - I searched the old comments but it didn't help me, I have written this code:
Dim vilemConn As New SqlClient.SqlConnection
vilemConn.ConnectionString = "...."
'Here I set the Select command
Dim selectCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT * FROM Users", vilemConn)
Dim vilemDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
vilemDA.SelectCommand = selectCMD
Dim cb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(vilemDA)
vilemConn.Open()
Dim vilemDS As DataSet = New DataSet
vilemDA.Fill(vilemDS)
Dim vilemDR As DataRow = vilemDS.Tables(0).NewRow()
vilemDR("jmeno") = Server.HtmlEncode(txtJmeno.Text)
vilemDR("prijmeni") = Server.HtmlEncode(txtPrijmeni.Text)
vilemDR("trida") = Server.HtmlEncode(txtTrida.Text)
vilemDR("email") = Server.HtmlEncode(txtEmail.Text)
vilemDR("login") = Server.HtmlEncode(txtLogin.Text)
vilemDR("heslo") = Server.HtmlEncode(inputHeslo1.Value)
vilemDR("skin") = Server.HtmlEncode(dropdownSkin.SelectedValue)
vilemDR("image") = Server.HtmlEncode(txtImage.Text)
vilemDS.Tables(0).Rows.Add(vilemDR)
vilemDA.Update(vilemDS)
vilemDS.Reset()
vilemDA.Fill(vilemDS)
vilemConn.Close()
Although I don't know what SQL command I should use (and please send me all the syntax of it, with txtLogin.Text added, because I have problems with this - I am absolute beginner in SQL)
So I don't know what SQL command I should use, where should I place it and how to execute it, because I don't want to rewrite the Select command for later adding of the new record.
This is my experiment completed with msdn and this forums, either correct it or don't mind it, it doesn't work
'Nastavení příkazu SQL pro vyloučení duplicity
Dim NoDuplicityCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT COUNT(*) FROM Users WHERE login= " & txtLogin.Text, vilemConn)
'NoDuplicityCMD.CommandText = "SELECT COUNT(*) FROM Users WHERE login=@txtLogin.Text"
Dim a As Integer
a = NoDuplicityCMD.ExecuteNonQuery() 'is it good place to execute the query and should I write a= noduplicity... or only noduplicitycmd.executenonquery...
Response.Write(a)
Thanks very very much for your answers.
|
|
|
|
|
First and foremost - For SECURITY. NEVER pass a value directly from a user control in to a SQL Command. This is suseptable to injection attack. What would happen if, in the txtLogin box, I had written
''; shutdown with nowait;
The answer is that your CommandText would fully read:
SELECT COUNT(*) FROM Users WHERE login=''; shutdown with nowait; This would run your SELECT and then immediately terminate your SQL Server Process - any other queries would fail and no new connections would be permitted. And you (or your DBA) would have to manually restart the SQL Server.
Okay - now that you know more about security I'll get on with your question...
I would always interact with the database using stored procedures. You add a layer of security there if you only permit access to the stored procedures, then all you can ever do is what the stored procudures can do.
CREATE PROCEDURE RegisterUser(
@UserName varchar(64),
@Password carchar(64)
)
AS
IF EXISTS(SELECT * FROM Users WHERE login=@UserName)
BEGIN
RAISERROR('The user '+@UserName+' already exists', 16, 1);
RETURN;
END
INSERT INTO Users (login, password)
VALUES(@UserName, @Password)
GO
Then from your .NET application you can do something like this
SqlCommand cmd = new SqlCommand("RegisterUser", vilemConn);
cmd.Parameters.Add(new SqlParameter("@UserName", txtLogin.Text);
cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text);
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException sqlEx)
{
}
This SQL and .NET code will register a user if one does not already exist, if one does exist an error is raised which you can then handle as appropriate.
Sorry, but I can only read VB.NET code, I don't know enough VB.NET to create the VB code without referring to a lot of books. Hopefully you will see what I am trying to achieve with the C# code - remember it is the Framework and not the language that is important here.
You will also need to add as parameters and extend the stored procedure for the other items in your table.
Does this help?
"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
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
Hi,
thank you for your reply, it helped a lot. However, I wasn't able to run it. It says this error:
Line 1: Incorrect syntax near 'RegisterUser'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'RegisterUser'.
Source Error:
Line 82: cmd.Connection.Open()
Line 83: cmd.Parameters.Add("@Password", inputHeslo1.Value)
Line 84: cmd.ExecuteNonQuery()
Line 85: '
Line 86: 'Dim vilemDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter
Source File: c:\inetpub\wwwroot\VilemWeb\Register.aspx.vb Line: 84
Here's a code:
Dim vilemConn As New SqlClient.SqlConnection<br />
vilemConn.ConnectionString = "...."<br />
<br />
Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn)<br />
cmd.Connection.Open()<br />
cmd.Parameters.Add("@UserName", txtLogin.Text)<br />
cmd.Parameters.Add("@Password", inputHeslo1.Value)<br />
cmd.ExecuteNonQuery()<br />
cmd.Connection.Close
and the stored procedure is here:
ALTER PROCEDURE RegisterUser<br />
( <br />
@UserName varchar(64), <br />
@Password varchar(64)<br />
)<br />
AS<br />
IF EXISTS(SELECT * FROM Users WHERE login=@UserName)<br />
BEGIN<br />
RAISERROR('The user CurrentUser already exists', 16, 1); <br />
RETURN<br />
END<br />
INSERT INTO Users (login, heslo)<br />
VALUES(@UserName, @Password)<br />
RETURN
BTW: It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why?
|
|
|
|
|
For the first problem it looks like I missed the line:
cmd.CommandType = CommandType.StoredProcedure You need to place this between creating the cmd object and the cmd.ExecuteNonQuery.
chodicimrkev wrote:
It said an error when I had written in RAISEERROR '+@UserName+' instead of CurrentUser, don't you know why?
Ah! Just realised that RAISERROR doesn't like strings being concatenated inside it. Weird!?!
Anyway, try
DECLARE @error_message varchar(256);
SET @error_message='The user '+@UserName+' already exists.';
RAISERROR(@error_message, 16, 1);
Hopefully it should work out now.
"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
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
I take my hat off to you!!! Your better help than MSDN. Thanks a lot, it works definitely. I am going to change all in my app to stored procedures, it's more clear than I did before. Thanks!
|
|
|
|
|
chodicimrkev wrote:
I take my hat off to you!!! Your better help than MSDN.
I'm not sure about that, I have to admit I've had 8 years to get used to the style. But thanks for the compliment, it is very welcome.
Have a great weekend. 
"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
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Another programmer converted to using stored procedures... you're an evil genius! 
my blog
|
|
|
|
|
Steven Campbell wrote:
Another programmer converted to using stored procedures... you're an evil genius!
 Mwwaaa haaa haa haa haaaaaaa!!
"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
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Just wanted to say that I thought your reply here was really good. I agree - I would never build a database that didn't work through stored procedures. I was shocked to find out that MySQL does not support them, I immediately stopped considering it when I found out.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
Christian Graus wrote:
I thought your reply here was really good
Thanks.
Christian Graus wrote:
I was shocked to find out that MySQL does not support them
Really! I heard also that it doesn't support transactions either (although there is supposed to be some sort of plug in that gives this functionality). That is the biggest showstopper for me.
"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
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
I am porting an enterprise level 3-tier C++ application to C# with a tight timeline. I am confused with the database jargon in .net Can somebody advice me on which objects/wizards can satisfy following requirements:
1. Compatibility with multiple database vendor (Oracle and MS SQL to start with)
2. Transactional control
3. Ability to fetch XML from query results
4. Ability to operate on views and execute stored procedures
Can some expert guide me?
|
|
|
|
|
1. .NET provides classes optimises for MS SQL, but also has a generic set of classes that I believe can connect to anything that is generally connectable to
2. Why don't you control transactions in stored procedures ?
3. The SQLCommand class has an ExecuteXMLReader method that should do this
4. The SQLCommand class does this all, you just give it the stored procedure name, tell it that it's a stored procedure and add to the parameters collection. Make sure any parameters that are not just 'in' parameters get set to whatever they are, or they will never come back to you.
Overall, I recommend buying the ADO.NET book from Microsoft, it answers all these questions and is a good reference to have.
Christian
I have drunk the cool-aid and found it wan and bitter. - Chris Maunder
|
|
|
|
|
Hi,
I have a Main Form, with controls, which are dynamically being populated from the database on FormLoad Event. There is a button called "Run Query", when I click this button, I want the result of the query to be displayed in a "Child Form" in a DataGrid, with sorting and paging facility and also a column in the "data grid", which allows me to edit the selected records.
Any working examples or help is appreciated.
Thanks
abhi
|
|
|
|
|
Hi,
Im trying to connect to an sql server through a PDA. When I use the PDA over a LAN network, it works absolutely fine, but when I try to connect to the sql server through Wi-Lan using the same PDA, I get an error saying that "Could not find assembly resource". Can anyone tell me the reason?
One thing that while switching from the LAN network to the Wi-Lan network, my IP address changes, so do I need to restart the Sql Server? Cant figure out the actual cause!
|
|
|
|
|
Try to read the information from here[^]. 
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hello
I am working on very simple SQL query but the resultset is not what I am waiting for.
cmdSelectConfigurations.CommandText = "SELECT Config_Number, Config_Type, " +
"Model_Serie, Serial_Number, Install_Customer_Nb, Install_Customer_Name, " +
"Install_Postcode, Install_City, Star_Zone, Install_Date " +
"FROM Configurations WHERE Config_Type like @Config_Type";
cmdSelectConfigurations.Parameters.Add(new SqlParameter(
"@Config_Type", SqlDbType.Char, 10, "Config_Type"));
cmdSelectConfigurations.Parameters["@Config_Type"].Value = @"%" + Search.ConfigType + @"%";
the character '%' seems to not be understanding, it seems to be understanding like '_'.
It considerates only one charactere before and after the value.
The query is OK when I put "'%" + AValue + "%'" directly in the Command text.
but it is always wrong when I try to use a parameter.
Thank you for your help
|
|
|
|
|
Just a WAG (Wild Ass Guess )
Change to
cmdSelectConfigurations.Parameters["Config_Type"].Value = "%" + Search.ConfigType + "%";
Chris Meech
I am Canadian. [heard in a local bar]
I think people should be required to have an operator's permit to use the internet. John Simmons
I have a feeling that if the millions of man hours wasted every year by geeks trying to get various video and sound cards working under Linux were put into some useful endeavor we'd have solved world hunger, we'd have peace and aids would be no more. [JOS poster]
|
|
|
|
|
I have tried all possibilities to write the value of the parameter.
Finally,I have changed the size of the parameter from 10 (the size of the field in the table to 12), and now the query is OK!
I am not sure that is a good solution, so maybe I will do a stocked procedure...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
