|
I'm new to SQL and am getting an exception on my webpage when I have an argument to my SQL DB that has spaces in it.
Basically, the page is being passed a name like "Name with spaces", which is being sent as templatename: (in VB)
ra = New SqlDataAdapter("exec sp_GetTemplateByName '" & templatename & "'", TestStatConn)
ra.Fill(dt)
The exception gets thrown at this line only for names that have spaces, not if a templatename is passed like "name_without_spaces". The exception is
System.IndexOutOfRangeException: There is no row at position 0.
ret &= "<td><span style=""color:#a9a9a9"">" & CheckForNullVal(dt.Rows(0).Item("lastchange")) & "</span></td>"
The sp_GetTemplateByName:
Select
..stuff..
from tbl_checklist_templates
where templatename=@TEMPLATENAME
order by stepnumber
Can anyone shed some light on what's going on here?
Edit:
If I remove the single quotes from this line
"exec sp_GetTemplateByName '" & templatename & "'" and make it
"exec sp_GetTemplateByName " & templatename & "" everything acts the same, except when I click on a template name with spaces, I get this exception:
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '%'.
From line 55:
ra = New SqlDataAdapter("exec sp_GetTemplateByName " & templatename & "", TestStatConn)
(THIS is lne 55) ra.Fill(dt)
modified on Friday, June 26, 2009 10:43 AM
|
|
|
|
|
I guess the error is coming while the control reaches this line
ret &= "<td><span style=""color:#a9a9a9"">" & CheckForNullVal(dt.Rows(0).Item("lastchange")) & "</span></td>"
Just check the count of your datatable, something like this(the code is in c#)
if(dt != null)
{
if(dt.Rows.Count > 0)
{
}
}
N.B.~ I don't know VB.net, henceforth presented the code in C#
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi
Is it possible to write stored procedures and views in ms access 2000
Thanks
Denny
|
|
|
|
|
Yes - sort of.
You can create views simply by using the query designer, or do it in VBA code.
You can use VBA to emulate stored procedures or use it to create querydefs that act like stored procedures.
If you want to invoke stored procedures on a linked database (e.g. on SQL Server) you can use pass-through queries.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
Hi ,
i have a doubt to choose a datatype in my database.I have a table with 3 fields :
1 - Code (nvarchar) PK;//in this field there is a code to refer a product.
2 - ProductName (nvarchar);
3 - here i have a doubt ,in this field i need to insert a number (percentage) but i don't know if the best choice is the d float(datatype), the purpose of this field is after the Total of some products we use this percentage to increase the Total for example :
if the Total is 25.000 USD i need calculate the Total + 25,7% so 25.000 +25,7%.
Which is the better datatype to use in this field to calculate the Total ,is it Float(datatype) or isn't?Can you advice me to apply this step in the right way?
Thanks so much for your attention.
|
|
|
|
|
Is your data language English, I note you use a comma as a thousand seperator.
If Yes then use VARCHAR() instead of nvarchar which supports unicode characters.
I use decimal rather than float, there is a slightly larger penalty when using float and IIRC you cannot use a float in an index/constraint.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: use VARCHAR() instead of nvarchar which supports unicode characters
Wrong way round, and whats it got to do with thousand separators?
|
|
|
|
|
nvarchar() supports unicode - I guess the sentence is ambigious.
Comma as the thousand seperator indicates non english format, reasonable assumption. All too many people accept the SQL Server default format with no knowledge why it may not be valid.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Money Data Type
Money is compatible for currency with dollar and cent format. The datatype is accurate to 10 thousand times of the monetary unit.
If the field is capable enough to store large aggregates rather than just the largest value, it is recommended to go with Money data type.
Also with money, you have the choice to use thousand of separators.
Float/Decimal Data Type
Float/Decimal is more flexible, not specific to currency. Suppose you have to support 10 numbers and 2 decimals. In this context decimal is a better choice. Also, decimal is more precise( since we can set precision) and scale to numbers.
From the above explanations, I think you should go with money data type.
Niladri Biswas
|
|
|
|
|
Hi all, I am a rocky with ACCESS Database
I have a basic question. Basically, I have an ACCESS 2003 database (mydatabase.mdb) in a network drive, I am planning to develop a C# windows application (not web base application) that will be installed (deployed) in many computers in the same network (intranet). These applications will be writing and reading continuously to this database. This has the risk of of accessing the database for writing and reading at the same time for more than one application.
My question is:
Would there be any problem if more than one application would be reading and writing to this database AT THE SAME TIME?
Should I setup an special configuration on the database for this kind of scenario?
Currently I am adding the db to the windows application using: From: Menu -> Data -> Add New Data Source ...
Thanks for your help.
|
|
|
|
|
kikeman wrote: My question is:
Would there be any problem if more than one application would be reading and writing to this database AT THE SAME TIME?
Yes. It's not designed with the intention of serving lots of users at the same time, and it doesn't perform well in that scenario. Microsoft Access is ideal for desktop-use, but rather a pain when you're trying to use it with some people (some being more than one).
kikeman wrote: Should I setup an special configuration on the database for this kind of scenario?
Depending on your particular Office-installation, you may have a Wizard to upsize your Microsoft Access database to a SQL Server database. SQL Express is a free download, and would perform better on that network drive.
The wizard doesn't convert forms or reports. You can, however, import a link (in the Microsoft Access database) to the tables in SQL Server/SQL Express and base your reports and/or forms on that. That way you can still benefit from the advantages of developing in Access
I are troll
|
|
|
|
|
I vote with Eddy, chuck Access, it is the wrong tool for the job. Use SQL Server, it is ideal for departmental processing.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply guys,
The problem would be now to "import" the ACCESS_2003 tables to the SQL Server Express Architecture.
Do you know if is there any tool for this convertion?
Does this tool comes with the SQL Server Express?
I think it would be better to move all database to SQL Server architecture, now that the database is not that bit at the moment. The forms and reports will be replaced with some Visual C# GUIs anyway.
|
|
|
|
|
Hi all,
I never really played with small applications.Now I want to write an application, really small.Its only going to be used by a homeuser.
Therefore its not really going to be that smart using SQL server.
But I need some suggestions.What will be the best technology?
I thought of XML, but Im not that clued up with it and it gives me the idea it will get tricky with big functionality.And then theres MS Access, but on the net it looks like its not really being used as much anymore.What will be the practical thing to use?
Thank you
|
|
|
|
|
My vote is for MS-Access. It isn't going away anytime soon. It will work fine if it is a single user application. Keep things simple. 
|
|
|
|
|
Either go with MS Access or MySql
Niladri Biswas
|
|
|
|
|
I would go with SQL Express a million times over MS-Access.
|
|
|
|
|
You don't say what technology you are using for the application as that may offer you different choices.
For example, if it's Java you could look at JavaDB, or HSQLDB (H2), or DB4O. Or, if you don't need a full-fledged database you could simply serialise your objects out to disk (or maybe use the built-in XMLEncoder class). I think DB4O might also have .NET support as well, I'm not sure.
For C or C++ you have SQLite among others.
Python also supports SQLite, and also has various options like pickle, shelve, gadfly.
SQL Server Express might actually a reasonable choice: it can be used embedded from a number of different languages and it is free (as far as I am aware if you are using it embedded in a single user application there are no licensing costs). FireBird is also an option for this sort of thing if you don't like SQL Server for whatever reason. I would say Access is not a bad choice for a stand-alone single user system, I would not agree that it is "not being used much anymore". I've seen several .NET applications that use it internally as a local data-store on a user's PC.
FoxPro is a complete all-in-one database and development environment (I haven't seen it in years myself but as far as I am aware it is still going strong with an army of loyal fans).
Or if all else fails you could simply write the data out to a text file on the disk in some sort of structured format. If your application is small and data volumes will be low, that might be the easiest option. If security is a concern you will need to work something out to encrypt the data.
I guess the short answer is, "It depends..."
|
|
|
|
|
I think it boils down to a choice between Access and SQL Server Express.
Off the top of my head here's some factors to consider.
Access pros: self contained, built in form designer, query wizards, reports designer etc. Can use VBA to enhance functionality.
Access cons: cost money (unless you already have Office Pro), can be slow over networks (even LANs). Not as extensible as SSE - but not an issue for home use.
SSE pros: 'proper' database. Easily extensible. Lots of help on internet/MSDN forums.
It's a free download. Good performance on networks.
SSE cons: need a front end (VB.NET Express or C#? - both free) to construct forms etc.
Needs a bit more management than Access.
There are also some free/open source databases you might consider (e.g. SQLLite) but I'd avoid MySQL because it's fairly heavy duty so probably over the top for what you need.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
The program "MSAccess" costs money, but you're not required to purchase the GUI
I are troll
|
|
|
|
|
SQL Server Express is free, just like MySQL. Both are database-servers, usefull if you want to work with large amounts of data. Oracle 10g Express would be the third option if you require a "real" database.
SQLite, SQLCE and MSAccess are good options for smaller databases. MSAccess has been around for some years, and the drivers are usually found on most systems. What's more; Access-databases can contain reports (fully integrated with Office, viewable in MSWord) and you can use the MSAccess-runtime to show them.
SQLCE is relative new, feels faster and more stable than MSAccess. It doesn't have embedded reports, and the drivers are probably not installed on the host-machine.
I haven't used SQLite intensively, so I haven't formed an opinion on that one yet. Seems to be a viable alternative for SQLCE though.
If you don't need an actual database, but still want to store "some" data, look at XML and LINQ-to-XML.
Hope this helps 
I are troll
|
|
|
|
|
I may be wrong, but I think that LINQ-to-XML only works with .NET 3.5. Not many home users will have that installed yet. It requires a 250Mb download just to install the .NET 3.5 framework, which might be a problem for some people especially since this is just a small home-user app. ("Our new application is just 100K, which you can download and install in seconds... after you spend an hour and a half trying to download and install all this other garbage that you don't really want.")
SQLite is fast and has a very small memory footprint. That's its main advantage.
SQLite datatypes are a bit weird because you don't tell it in advance what the datatype of a column will be. SQLite figures it out itself dynamically. This is mostly OK but every now and again it can surprise you (among other things, SQLite thinks it's OK to store string values in a column intended for integers without warning, it will happily convert from floating point numbers to integers according to its own internal logic, and so on). Also, there is no support for date/time values. If you want to store these, you have to figure out your own solution.
The biggest potential problem is that it is a single user database. Every time you update something, it locks the entire database (yes, the entire database not just the table being updated). May not be a problem for this particular application if it's a small home-user app but SQLite does not scale in terms of number of users.
Personally, I don't like SQLite. It's popular with C and C++ people and some Pythonistas but I just don't see the point of using it when there are better lightweight databases available these days.
|
|
|
|
|
You're right, that's a large download if you just want to save some application-data.
David Skelly wrote: SQLite datatypes are a bit weird because you don't tell it in advance what the datatype of a column will be.
Like I said, I haven't used it much just noticed that it converts all datetime values to string.
David Skelly wrote: The biggest potential problem is that it is a single user database.
That's also true for SQLCE. MSAccess has some more options, but isn't intended for this scenario either. They can however, be used as readonly-datastores by multiple users.
David Skelly wrote: Personally, I don't like SQLite
Me neither, but it is still an option that may be considered. I'd be using SQL Server Express
I are troll
|
|
|
|
|
You could alsways try www.sqlite.org[^]
I don't speak Idiot - please talk slowly and clearly
I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hi i want to split one large table into two. But here my problem is, i need to query from both the tables, which should give the data same as the parent table.
For example If the large table is like
COLA COLB
1 A
2 B
3 C
4 D
5 E
and if i split this into
FIRST table
COLA COLB
1 A
3 C
5 E
Second table as
COLA COLB
2 B
4 D
Now i want to query the two tables, so that i should give the value order by COLA. Result shold be like
COLA COLB
1 A
2 B
3 C
4 D
5 E
My small attempt...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
