SQL Newie needs help
-
I know and understand basic SQL but the only experience I have had with it is using it with web pages with a Access database.
I still want to use sql with web pages but I now want to use sql server as my database. I have created a Data source on my computer but from here I am lost. Can someone please point me in the direction of where to find the answers to these questions?
- How do i create a new database in server so i can add tables and fields etc.
- How do I connect to this database. I saw some code and it said to save it as a .cpp file. Would this be used in my web pages?
Any help will be appreciated.
Thanks.
-
quote:
- How do i create a new database in server so i can add tables and fields etc.
Do you have access to Enterprise Manager? If so, it's simple to create a new db. If not, I guess you use T-SQL. Check out CREATE DATABASE in BOL
quote:
- How do I connect to this database.
Via using ADO (OLEDB). There are plenty of examples out on the web. Look for ADODB.Connection.
quote:
I saw some code and it said to save it as a .cpp file. Would this be used in my web pages?
This indicates a C++ source file. Although you get use C++ for web development (ISAPI or cgi-bin), I guess ASP-pages are more common, and unless you are familiar with C++, VB (= ASP) programming has a steeper learning curve.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks frank. I do have Enterprise Manager so I will look for tutorial with that. I will also look for tutorials for OLEDB.
-
Ok, I created a new database called TestDatabase. In this I have a Table called Table1 and a field called test.
I used the sample code:
<%
set cnn = server.createobject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=databasename "
%>
to connect to my database. I haven't received any errors yet but I havent tried to manipulate the database yet either. This is just an update of my progress. It would be great if people could tell me if im doing something wrong.
Thanks.
-
I am now using:
<%
' Dim the Recordset Object
Dim rsADO
' Define the Connection String From Above
ConnectionString = "Provider=SQLOLEDB; Password=;User ID=sa;Initial Catalog=TestDatabase;Data Source=mydatasource;"
' Create the Object
Set rsADO = Server.CreateObject("ADODB.Recordset")
Source = "SELECT * FROM Table1"
' Open the Recordset
rsADO.Open Source, ConnectionString
if not rsADO.EOF then
Response.Write rsADO("test")
end if
' DeAllocate the Object to Free Server Memory
set rsADO = nothing
%>
Once again the page loads but nothing happens. I have give test the default value 'test' so I dont see why this isnt loading. Any ideas?
-
Sorry, me again. I just did a quick error test by adding.
if not rsADO.EOF then
Response.Write rsADO("test")
' Response.Write rsADO("FieldName2")
else
Response.Write("EOF")
and it now displays EOF to the page. I know this means that its the end of file but can someone please help me as to why it wouldnt display the default value 'test' of test? Sorry if thats confusing 🙂
-
A further question. Can u copy a table from one database to another using Enterprise Manager?
-
Hi Jigman,
quote:
Sorry, me again. I just did a quick error test by adding.if not rsADO.EOF then
Response.Write rsADO("test")
' Response.Write rsADO("FieldName2")
else
Response.Write("EOF")
and it now displays EOF to the page. I know this means that its the end of file but can someone please help me as to why it wouldnt display the default value 'test' of test? Sorry if thats confusing 🙂
by giving a field a default value does NOT mean, inserting data. It does only mean, that in cases you haven't entered anything else into that field, SQL Server automatically inserts the default value. So, as you get the response 'EOF' I assume, you haven't inserted any data. Try this before going on.
As for your other qusetion:
Yes, you can using EM!
Right-click on the table in question, select 'All Tasks', 'Export data'. This pops up the DTS Import/Export wizard. You then only need to follows the wizard.
HTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for your replies Frank. I was able to move the tables from one database to another.
I added 2 rows to my initial table1, test and test2. I was not sure how to add data to them though. I changed my SELECT statement into an INSERT statement instead and hardcoded values to be entered into the table. After I did that I use 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.
-
Heelo Jigman,
quote:
I added 2 rows to my initial table1, test and test2. I was not sure how to add data to them though. I changed my SELECT statement into an INSERT statement instead and hardcoded values to be entered into the table. After I did that I use 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'.
sometimes EM behaves VERY strange. I usually ignore this message.
It might be even better getting familiar with T-SQL and Query Analyzer.
First make sure you're pointing to the right DB, by choosing it from QA's combobox on top (can save a lot of headache [:-)]
Next you enter something like
DELETE FROM tablename
and hit F5 to delete all records in that tables.
quote:
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.
The following is something quick'n' dirty, you need to customize to you needs.
Set NOCOUNT ON
DECLARE @I INT
SET @i=1
While @i <= 625
BEGIN
INSERT into test3 (id, field1) VALUES (@i,'THIS is A Test')
Set @i=@i+1
CONTINUE
END
SET NOCOUNT OFF
This should create 625 records in your table.
To update existing data you use the SQL UPDATE statement like
UPDATE test3 SET field1 = 'End of TestTING'
Maybe I add one hint.
A very good starting point for QA and T-SQL is SQL Server's Books Online.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank, I think I need to have a good read of the online books before I go any further. One question though, the example you just showed me, where do I enter that code? After that you said "To update existing data you use the SQL UPDATE statement like
UPDATE test3 SET field1 = 'End of TestTING'
"
where do I enter these statements? In Query Analyzer?
I need somewhere that has a basic tutorial on starting with SQL Server. I think the online books are a little too in-depth for a beginnner.
-
Hi Jigman,
quote:
where do I enter these statements? In Query Analyzer?
Yes, in Query Analyzer (QA)!
quote:
I need somewhere that has a basic tutorial on starting with SQL Server. I think the online books are a little too in-depth for a beginnner.
is your mail account configured to receive a ~1.2 MB zipped pdf-file?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Hi Jigman,quote:
where do I enter these statements? In Query Analyzer?
Yes, in Query Analyzer (QA)!
quote:
I need somewhere that has a basic tutorial on starting with SQL Server. I think the online books are a little too in-depth for a beginnner.
is your mail account configured to receive a ~1.2 MB zipped pdf-file?
Cheers,
Frank
forget the mail account.
Search the web for 'Teach yourself SQL in 21 days'. Might be a good starting point, although nothing comes close to BOL
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
thanks for all the help. I think my mail is configured but I will search instead.
BOL = Books Online?
I will post any good URLs I find in here.
-
I feel this is a really good beginners guide: http://www.functionx.com/sqlserver
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?
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply