SQL Relational Database AddNew Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Relational Database AddNew Records

Author

Topic

cbn
Starting Member

11 Posts

Posted - 2002-10-11 : 22:39:37

From the topic, SQL svr, 2 tables, 1 relation, add new record.
Well, the addnew function has to add a new record set into one table then for the same id entered to add one record into the other table this way the second one has the id of first into the field 'lookup_id'.

I did this in Access and it works, i cannot accomplish it in SQL svr.

Is it a limitation or i am just not getting the syntax.

Regards,
Cris

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-11 : 23:09:47

Are you using VB?ADO?DAO?Recordsets???
Can you post your code please?

If your working with identity columns then
this maybe what your looking for?
http://www.sqlteam.com/item.asp?ItemID=319
http://www.sqlteam.com/item.asp?ItemID=422
http://vyaskn.tripod.com/retrieve_guid_value_like_identity.htm

If you want code for a stored procedure take a look at:
http://www.sqlteam.com/item.asp?ItemID=563
http://www.sqlteam.com/item.asp?ItemID=2755

As a rule of thumb if you provide a snipet of what your doing and the results you expect you will get a better answer.

cbn
Starting Member

11 Posts

Posted - 2002-10-11 : 23:37:10

Ok sorry:

Here we go:
I use ADO.
This is the original MS Access Snippet that i created:
I am trying to port the similar application onto SQL Svr
==================================================================
<% @ LANGUAGE="VBScript" %>
<%

''---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

''---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

dim SQL
dim Conn
dim RS
Dim StrDBPath
Dim Trim

strDBPath = Server.MapPath("../DB/*****************.mdb")
Set Conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
set RS = Server.CreateObject("ADODB.RecordSet")

RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic

SQL = "SELECT AppointmentEntry.Id, AppointmentDateEntry.LookUpEntry as LookUpEntry, AppointmentEntry.LastUpdated as LastUpdated, AppointmentEntry.TCName as TCName, AppointmentEntry.SFName as SFName, AppointmentEntry.SLName as SLName, AppointmentEntry.AppTime as AppTime, AppointmentEntry.Phone as Phone, AppointmentEntry.Subject as Subject, AppointmentEntry.Initials as Initials, AppointmentEntry.Notes as Notes, AppointmentEntry.Type as Type, AppointmentDateEntry.AppointmentDate as AppDate FROM AppointmentEntry INNER JOIN AppointmentDateEntry ON AppointmentEntry.Id = AppointmentDateEntry.LookUpEntry Where LookUpEntry = AppointmentEntry.Id"
RS.Open SQL, Conn
RS.AddNew
RS("TCName") = request.form("TCName")
RS("SFName") = request.form("SFName")
RS("SLName") = request.form("SLName")
RS("AppTime") = request.form("AppTime")
RS("LookUpEntry") = RS("ID")
RS("AppDate") = request.form("AppDate")
RS("Subject") = request.form("Subject")
RS("Phone") = request.form("Phone")
RS("Initials") = request.form("Initials")
RS("Type") = request.form("Type")
RS("Notes") = request.form("Notes")
RS("LastUpdated") = now

RS.Update

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
%>
==================================================================
There is an SQL referential integrity stabilized between the 2 tables addentry and adddataentry.

Dot ask why i dont collapse the 2 tbls, since there is a lot more than that.
I need help because i may not know all SQl svr syntaxes.

Thanks

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-12 : 01:57:22

Oh boy.

Here's the problem, in any relational model there are certain rules as to what data sets can be updated. Different products have different rules as to what can be updated. I'm guessing that since you have multiple tables joined together, the system can't figure out how to do inserts properly.

What you would want to do is create a stored procedure on the server to do the insert for you.

Inside the stored procedure, you would have to issue INSERT statements against the two tables. Or, you could issue the seperate INSERT statements from your ASP page, but I don't recomend it. (that's a personal preference. I really like to seperate things into different layers, and I think including SQL statements in ASP pages violates that.)

If you look on the main page of this site, you can find a lot of stuff about stored procedures and SQL syntax. It's pretty easy. (Well, the basics anyway. The set based mindset can be hard to come by for some.) If you don't have a copy, you can also find a link to the Books Online (the SQL Server documentation) which has sections devoted to syntax.

Of course, if you *really* don't want to learn the stuff, do two different queries. Build a recordset from each table with the fields you need. Then do a call to AddNew for each. Just make sure to do them in the right order. (Oh, and don't mix. Complete one, then do the other. Looks cleaner in code and easier to debug.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-12 : 11:45:29

Take a look at rob's answer.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20631

cbn
Starting Member

11 Posts

Posted - 2002-10-12 : 12:47:13

Thank you,
I got the idea of one at the time with max id from initial tbl and got the SQL svr app done. Thank you.

Anyway, i must admint i liked Access DB better.

The most i want to admit, that MySQL and CGI are the best from this point of view.

I know, i dont want to start a 'fight' but CGI and MySQL are better.

Cris
============================This is for all ADO users==============
for a = Session("eventStartDate") to Session("eventEndDate")
set RS = Conn.Execute("Insert into calendar (description,category,location,date) values ('" & title & "','" & category & "', '" & location & "', '" & a & "')")
set RS = Conn.Execute("Select MAX(ID) as caid from calendar")
caidimp = RS("caid")
set RS = Conn.Execute("Insert into event (cal_id, eventdate) values ('" & caidimp & "', '" & a & "')")
next
================================ADO End============================
===The initial connections and so on everyone should know==========

====Soon i will be porting this onto ADO.NET=======================

Edited by - cbn on 10/12/2002 13:25:45

Edited by - cbn on 10/12/2002 13:27:48

Lavos
Posting Yak Master

200 Posts

Posted - 2002-10-12 : 14:43:31

This isn't a flame or anything, but there is a complete world of difference between MySQL and Sql Server or Oracle. You can't compare the two. It doesn't make sense. It's like comparing McDonalds and an extremely expensive French restraunt. Yes, they both serve food, but they are just in different classes.

I mean, last I heard MySQL didn't even support something as important as transactions. Don't get me wrong, right tool for the right task (I use local Access databases to create prototypes rather than go through the work of setting up a SQL Server VM for instance) but in no way is MySQL "better" unless you really only want to base it on an extremely small set of features and "cost". One day, you might need the full power of a "real" DBMS (whether it be Oracle, DB2, or SQL Server) and you'll see why they aren't comparable to Access or MySQL. (That said, they are nice in their field, but their field is pretty small in comparison.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources