| Author |
Topic |
|
JigMan
Starting Member
16 Posts |
 Posted - 2003-07-13 : 22:11:16
|
| I am new to SQL Server. I recently created a Database 'TestDatabase', in this i created a table 'Table1', and a field 'test' with the default value "test". I believe I am connecting to the database fine but when I try to access the data I get a display (that I coded) saying that it is at the end of the file before it displays anything.if not rsADO.EOF then Response.Write rsADO("test") Response.Write rsADO("test2")else Response.Write("EOF") end ifSo it displays EOF to the screen. Does anyone know why this isnt display my test field. Thank you. |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-07-13 : 22:49:26
|
| Jig,is there in your table only one row at least?Seems your table is just virginally empty...- Vit |
 |
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-13 : 23:05:00
|
| I added 2 rows, test and test2. I was not sure how to add data to them though. I change my SELECT statement into an INSERT statement instead and hardcoded values to entered into the table. After I did that I use the select again and it now shows the values. I checked the database by using enterprise manager and right clicking on the table and choosing open table, return all rows. It now shows me that I have a number of test and test2 fields filled with the same data. I try to delete these fields but it wont let me and says 'Key column information is insufficient or incorrect. Too many rows were affected by update'.I guess my question is how do you populate and update your database through SQL Server and not just through asp pages? I think as you pointed out my table didnt contain any data before I used the INSERT. The problem is I dont know how to add the data or how to delete it (without using ADD and DELETE hardcoded functions like I used for the INSERT). I hope this makes sense and you can help me with my problem. |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-14 : 01:18:00
|
| A further question. Can u copy a table from one database to another using Enterprise Manager? |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-14 : 02:25:18
|
| >>I guess my question is how do you populate and update your database through SQL Server and not just through asp pagesActually, it doesnt matter where you populate or update your database from as long as you are using ADO, which provides the middle-layer taking care of most of the things. But its much better programming practice to add/update/delete data from the database using stored procedures, which run on SQL Server rather than on the Web server. They afford a lot more security, speed and scalability. I suggest you invest in a good book on ASP programming with SQL Server, I suggest one of the O'Reilly or Wrox (now defunct, but useful nevertheless) books.>>I try to delete these fields but it wont let me and says 'Key column information is insufficient or incorrect. Too many rows were affected by update'. What this error is trying to tell you is that there is not primary key defined on this table that will let SQL Server identify each row uniquely. Since it cant make out row A from row B, it is unsure which one to delete, and you get this error! Read up on Primary keys and the concepts of Normalization to get a good idea about this. It is absolutely imperative that you start with a good knowledge of database design fundamentals if you are serious about it.>>A further question. Can u copy a table from one database to another using Enterprise Manager? Yes and no. You can actually copy a table, but you cant paste it!! Look at the Import/Export Utility (DTS), that will help you copy tables and data across databases. If you are using Enterprise Manager, right click on the database and select All Tasks->Export Data. Follow the wizard from there.Owais |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-15 : 02:39:45
|
| Thanks for the reply Owais.I know about primary keys, but seeing as I was just learning and messing around I didnt include any because I didn't know you couldnt delete fields unless you have a primary key. I guess that would make sense. Kinda. I have also researched normalisation.>>it doesnt matter where you populate or update your database from as long as you are using ADO, which provides the middle-layer taking care of most of the things. But its much better programming practice to add/update/delete data from the database using stored procedures, which run on SQL Server rather than on the Web server.When you say this do you mean using Query Analyzer to run the functions? I just don't understand where else you can get the actions to execute. I seen scripts as well and I don't know where you are meant to call them from. I'm not sure if my problem is clear but at the moment I dont have money to buy a book and I dont find the online books user friendly for a beginner. Are there any sites that give basic introductions to sql server?Thanks. |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-07-15 : 03:53:53
|
| Try this tutorial:[url]http://www.functionx.com/sqlserver/[/url] |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-15 : 21:33:12
|
| Thanks for the tutorial Andraax, I think its great.At one point in the guide it says "Another technique used to perform data entry consists of importing already existing data from another database or from any other recognizable data file. Microsoft SQL Server provides various techniques and means of importing data into Microsoft SQL Server."I saw that a Microsoft Excel document can be used. Does this document have to be in any particular format? Ideally I would love to be able to grab the data from my excel spreadsheet, insert it into my database, and then be able to show this data to users from the web page. Anyone have any experience in this? |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-16 : 01:47:52
|
| I read this great tutorial on templates at: http://www.databasejournal.com/features/mssql/article.php/10894_1560661_1but I have a few questions from it. I have searched on google with +scripts +sql +tutorial but can't find any answers to my simple but probably silly newbie questions.- I can now create templates that can then be saved as .sql scripts? Is this correct?- Once I have these scripts how can I run them? I know I could run them with QA, but how about with other methods like ASP, or C++? A lot of websites have ASP scripts for use but I couldn't find out how you actually use them. As in, how do I put my scripts that I made through QA into something like ASP, C++, etc.Thanks. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-16 : 03:11:12
|
quote:
I saw that a Microsoft Excel document can be used. Does this document have to be in any particular format?
Well... Excel format... It helps if you have nicely named columns and an entry in the same number of columns for all your rows, but it's not required. quote:
Ideally I would love to be able to grab the data from my excel spreadsheet, insert it into my database, and then be able to show this data to users from the web page. Anyone have any experience in this?
Yes you should be able to do that. Experiment using the DTS import wizard, with the file source as your excel file.-------Moo. :) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-16 : 03:15:09
|
| Hi jig:You can use Comma or Tab Delimited files for importing data into SQL Server using a utility called BCP that comes with SQL Server. Pretty nifty and fast, its command line though. BTW, in case you havent tried it, its easier to show the Excel sheet directly to the user through a web-browser than having to import into sql server and then retrieve it (as long as the user has Excel installed on the machine)If you want to run a bunch of SQL statements put them inside a Stored Procedure. then you can invoke the proc from QA, ASP, C++ whatever. Look up the documentation on the ADO Command and Recordset objects.Owais |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-16 : 21:49:10
|
| Thanks for the help guys but I think importing data from excel is going to be a no-go. I think the spreadsheets have too many different headings and what not, and when I try to import them I get alot of NULL values and the information I want is listed halfway down between all the NULLS. It actually works for a couple of rows but because of all of the different headings and descriptions it fills the data up with NULLS where it is expecting a price value. I think it would take the user longer to edit the spreadsheet to convert than using an application I could create to enter the data manually.I also searched Server Help for BCP but I couldnt find anything on this.>>in case you havent tried it, its easier to show the Excel sheet directly to the user through a web-browser than having to import into sql server and then retrieve itI actually want the user to be able to enter data and change the prices, this will then be saved to the database, of which the webpage will get and display the prices in a user friendly format. Confusing? So in the end I don't want to show the Excel sheet, just the data that is initially contained in it that I want put in the database, that will be displayed to the web page.I think I will create an application in C++ Builder that will allow the entering of the data, this will be saved into the database, when a client views the web page their desired information is grabbed from the database. Edited by - JigMan on 07/16/2003 21:52:41 |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-07-17 : 03:18:37
|
| If you are comfortable with C++, you should have no trouble learning ASP... ASP is by far the easiest way to build simple web apps, including user input and nice presentation. Go here: [url]http://www.aspin.com/home/tutorial[/url] for lots of info! |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-17 : 22:15:19
|
| Ok, back to SQL Server. Say I have a table populated with around 50 items. The primary key is their ID (eg 1-50). If I get a new item that I want to chuck in the database at say position 25, is it possible to do this without deleting position 25? For example can I insert this new item with ID 25, so the current item with ID 25 now becomes 26, the one after that goes from 26 to 27 and so forth, all the way to the end where the table will now hold 51 items instead of 50. Does this make sense? Is this possible? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-17 : 22:32:30
|
| You're misconstruing the value "25" as a position. There is no such thing as position in a relational database. Data is stored as values. The ID column is a value, not a position. Position is a meaningless concept, and you cannot and should not rely on it when querying relational databases. Think of musical chairs: the chairs stay the same, the players stay the same. Neither of them changes depending on which person sits in which chair. You can even rearrange the chairs and it won't affect anything about them like size, weight, color, etc.Anyway, you can't insert a specific value for the identity column unless you SET IDENTITY_INSERT ON for that table, and you can't have an existing row with that value if it's set as primary key or has a unique index. Regardless, it will not renumber the other existing values, which would be a mistake anyway if they are the primary key for that table. Changing their values changes their uniqueness and definition. It would be like playing musical chairs where each person changes their name, hair color, personality, whenever they sit in a different chair.You're not using Excel or Access anymore. You should take some more time to study up on relational database design before you get too deep into designing your database. |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-20 : 19:07:14
|
| This might not be the best place to post this because it is getting too asp instead of SQL but its worth a try :)When accessing data from a data base I try to test whether a value is less than another to move on to a further step. I have a value license, startEx and endEx.If I try:if license > startEx then....the code works fine, but when I try endEx it doesnt work. eg.if license < endEx. I have tried just chucking in an integer of the same value of what endEx should:if license < 6.... and that works. I have printed endEx to the screen and it comes up as a number. I have checked the database and it is stored as an Int. I have no idea whats wrong. Any ideas? |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-20 : 19:35:48
|
| Sorry all, please ignore. After hours I finally figured out that it was the license value that had the problem. I was passing it in a querystring and I dont know how but I must have been passing it as string because it was always greater than my startEx and endEx values. I will post another thread on my querystring problem. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-21 : 02:28:08
|
The Request.QueryString ("string", does that ring a bell  ?) is a collection of strings, so for any comparisions you'll need to cast it into the appropriate datatype. You need to be careful with this though, particularly if you are casting one of these values as int (CInt) or Date (CDate), you'll need to handle blanks and invalid data.Hence Dim LicenseLicense = Request.QueryString("LicenseNumber")If IsNumeric(License) Then License = CInt(License)End IfIf License < SomeNumericValue Then'''Blah blahEnd ifOwais |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-22 : 20:05:59
|
quote:
There is no such thing as position in a relational database. Data is stored as values. The ID column is a value, not a position. Position is a meaningless concept, and you cannot and should not rely on it when querying relational databases.
Thanks, I understand that there a no positions in my tables in my database, but when viewing the database in Enterprise manager (when I go 'Open Table, Return all rows'), it would be nice if it showed me my information ordered. For example at the moment it goes, 1,2,3,4,6,7..........20,5. Its not a must, but I would prefer it if it was shown in order with 5 after 4. Is this possible? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-22 : 20:20:28
|
| Well you can certainly do that in Query Analyzer by using the ORDER BY option. You can also do that in Enterprise Manager from the screen that you are on. Just click on SQL and then after where it says SELECT * FROM TableName, type ORDER BY ColumnName, where TableName is the name of the table that you are viewing and ColumnName is the column that you want to order the results by. Then click the exclamation point to run the command. I would suggest learning T-SQL if you are going to be working with SQL Server.TaraEdited by - tduggan on 07/22/2003 20:22:22 |
|
|
|
JigMan
Starting Member
16 Posts |
Posted - 2003-07-22 : 20:56:04
|
| Excellent. Thanks for the help. I just thought that Enterprise Manager would have done that automatically by itself. I guess there is no reason to. Thanks again. |
|
|
|
Next Page
|
EOF problem