BEFORE Triggers: begginer

Showing posts with label begginer. Show all posts

Friday, February 10, 2012

Begginer needing help to resolve error.

Hi,
I hope one of you may be able to help me with a small probelm I have.
I get the following error:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression '[Date_Completed]
is NULL WHERE (([Customer] LIKE '%Davie%' OR [Address] LIKE '%Davie%'
OR [Post_Code] LIKE '%Davie%' OR [Telephone] LIKE '%Davie%' OR
[Mobile] LIKE '%Davie%' OR [Vehicle] LIKE '%Davie%' OR [Chassis_No]
LIKE '%Davie%' OR [Engine_No] LIKE ''.
/Jobsheet/Datalist.asp, line 227
The code that it highlights is this:
<%
' Set up Record Set
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open sSql, conn, 1, 2 --This line in particular --
nTotalRecs = rs.RecordCount
If nDisplayRecs <= 0 Then ' Display All Records
nDisplayRecs = nTotalRecs
End If
nStartRec = 1
SetUpStartRec() ' Set Up Start Record Position
%>
I would be much obliged if anyone can provide assistance.
Cheers,
PhillIs it Access query?
"FatboyDim" <webmaster@.pb-enterprises.co.uk> wrote in message
news:1183454575.272248.54230@.c77g2000hse.googlegroups.com...
> Hi,
> I hope one of you may be able to help me with a small probelm I have.
> I get the following error:
> Microsoft JET Database Engine error '80040e14'
> Syntax error (missing operator) in query expression '[Date_Completed]
> is NULL WHERE (([Customer] LIKE '%Davie%' OR [Address] LIKE '%Davie%'
> OR [Post_Code] LIKE '%Davie%' OR [Telephone] LIKE '%Davie%' OR
> [Mobile] LIKE '%Davie%' OR [Vehicle] LIKE '%Davie%' OR [Chassis_No]
> LIKE '%Davie%' OR [Engine_No] LIKE ''.
> /Jobsheet/Datalist.asp, line 227
>
> The code that it highlights is this:
> <%
> ' Set up Record Set
> Set rs = Server.CreateObject("ADODB.Recordset")
> rs.CursorLocation = 3
> rs.Open sSql, conn, 1, 2 --This line in particular --
> nTotalRecs = rs.RecordCount
> If nDisplayRecs <= 0 Then ' Display All Records
> nDisplayRecs = nTotalRecs
> End If
> nStartRec = 1
> SetUpStartRec() ' Set Up Start Record Position
> %>
> I would be much obliged if anyone can provide assistance.
> Cheers,
> Phill
>|||Yes,
It is an Access query.
On 3 Jul, 10:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Is it Access query?
> "FatboyDim" <webmas...@.pb-enterprises.co.uk> wrote in message
> news:1183454575.272248.54230@.c77g2000hse.googlegroups.com...
>
> > Hi,
> > I hope one of you may be able to help me with a small probelm I have.
> > I get the following error:
> > Microsoft JET Database Engine error '80040e14'
> > Syntax error (missing operator) in query expression '[Date_Completed]
> > is NULL WHERE (([Customer] LIKE '%Davie%' OR [Address] LIKE '%Davie%'
> > OR [Post_Code] LIKE '%Davie%' OR [Telephone] LIKE '%Davie%' OR
> > [Mobile] LIKE '%Davie%' OR [Vehicle] LIKE '%Davie%' OR [Chassis_No]
> > LIKE '%Davie%' OR [Engine_No] LIKE ''.
> > /Jobsheet/Datalist.asp, line 227
> > The code that it highlights is this:
> > <%
> > ' Set up Record Set
> > Set rs = Server.CreateObject("ADODB.Recordset")
> > rs.CursorLocation = 3
> > rs.Open sSql, conn, 1, 2 --This line in particular --
> > nTotalRecs = rs.RecordCount
> > If nDisplayRecs <= 0 Then ' Display All Records
> > nDisplayRecs = nTotalRecs
> > End If
> > nStartRec = 1
> > SetUpStartRec() ' Set Up Start Record Position
> > %>
> > I would be much obliged if anyone can provide assistance.
> > Cheers,
> > Phill- Hide quoted text -
> - Show quoted text -|||Hi
Its SQL Server news group. You will be better of ask the question in Access
group
"FatboyDim" <webmaster@.pb-enterprises.co.uk> wrote in message
news:1183455450.654505.196030@.n2g2000hse.googlegroups.com...
> Yes,
> It is an Access query.
> On 3 Jul, 10:27, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Is it Access query?
>> "FatboyDim" <webmas...@.pb-enterprises.co.uk> wrote in message
>> news:1183454575.272248.54230@.c77g2000hse.googlegroups.com...
>>
>> > Hi,
>> > I hope one of you may be able to help me with a small probelm I have.
>> > I get the following error:
>> > Microsoft JET Database Engine error '80040e14'
>> > Syntax error (missing operator) in query expression '[Date_Completed]
>> > is NULL WHERE (([Customer] LIKE '%Davie%' OR [Address] LIKE '%Davie%'
>> > OR [Post_Code] LIKE '%Davie%' OR [Telephone] LIKE '%Davie%' OR
>> > [Mobile] LIKE '%Davie%' OR [Vehicle] LIKE '%Davie%' OR [Chassis_No]
>> > LIKE '%Davie%' OR [Engine_No] LIKE ''.
>> > /Jobsheet/Datalist.asp, line 227
>> > The code that it highlights is this:
>> > <%
>> > ' Set up Record Set
>> > Set rs = Server.CreateObject("ADODB.Recordset")
>> > rs.CursorLocation = 3
>> > rs.Open sSql, conn, 1, 2 --This line in particular --
>> > nTotalRecs = rs.RecordCount
>> > If nDisplayRecs <= 0 Then ' Display All Records
>> > nDisplayRecs = nTotalRecs
>> > End If
>> > nStartRec = 1
>> > SetUpStartRec() ' Set Up Start Record Position
>> > %>
>> > I would be much obliged if anyone can provide assistance.
>> > Cheers,
>> > Phill- Hide quoted text -
>> - Show quoted text -
>

Begginer needing help to resolve error.

Begginer in SQL-Foreign KEy to Mulitple Tables

Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
Hello,

Let's say that we have the following tables:
property (property_id int primary key, property_type int, some general fields)
mountain_property (mproperty_id int primary key, property_id int references property(property_id), some specific fields)
beach_property (bproperty_id int primary key, property_id int references property(property_id), some specific fields)

property_type defines the mountain (1) or beach (2) property.

So when we need to select all records for mountain property we can execute the following SQL:

selecy * from property p inner joun mountain_property mp on p.property_id = mp.property_id where property_type=1.

So genenerally, property table knows nothing about mountain_property and beach_property and here is no relationships from property to any of these tables.

This example is similar to defining a class with collections and this type of relationships is called one-to-many (for each of pair property-mountain_property and property-beach_property).

The other possible (but different) solution is to use one-to-one relationship. In that case mountain_property's primary key is a foreign key to property table. E.g. the structure is:
property (property_id int primary key, property_type int, some general fields)
mountain_property (property_id int primary key references property(property_id), some specific fields)
and
selecy * from property p inner joun mountain_property mp on p.property_id = mp.property_id
will return a list of "mountain_property" objects with all fields.

Sincerely,
Alex|||

There are a couple of examples of what you're after in the Microsoft sample AdventureWorks database.

For example the Store and Individual tables both inherit from the Customer table. Store and Individual both have a primary key of CustomerId, which is a foreign key to the Customer table. Customer has a CustomerType column, which contains either 'S' or 'I' depending on whether the Customer row refers to a Store or an Individual.

This is also the way that my Foundation product implements inheritance; it looks for relationships like this and creates business / data access objects with the same inheritance structure. You can download either the free/trial version (free for up to 20 tables) or just the Knowledge Base from my site, the Knowledge base has a helpful page in it about exactly this stuff, it's called "Defining Inheritance Relationships".

Linq-to-Sql implements inheritance in a different way. You don't have a base table and derived tables. Everything goes into a single table, and any columns that would normally go into a derived table must be made nullable. So you end up with loads of nullable columns, only a subset of which can legitimately be populated for any particular row. Of course having to make the columns nullable means a lack of data-integrity, and you can't have unique indexes that use those columns, and no one-to-one relationships including those columns. Made me shudder when I read about it! Maybe I'm just too sensitive

Linq-to-Sql still uses the "CustomerType column" way of telling what derived type a row is.

Sean

|||Thank your for giving such a detailed example, it really helped. I guess I understand here that there wont be a direct link for the Property table to find one of its children, but that it can find them because it holds the property_type id so it will know what table to search for them in.
They question I have is that in the 1'st SQL statement that you gave it says "selecy * from property p inner joun mountain_property mp" which shows me that this statement already knows that it is seaching in the mountain_property table.
But what would you do if you didn't know which table to search in. As in, lets say you have your property instance (row) in the property table, and you now want to get more information about it(which is either stored in the mountain_property or beach_property table). I think I can see myself doing this in two statements by first getting the property_type and then processing that data in my application to choose what table to choose in and then executing another statement to get the data. Is this the way that you are supposed to do it. Eg below
So I guess if I was in the Property table and I had selected row which was of type mountain_property. To get the additional information of this property (held in the moutain_property table) I would first select the property_type id. I would then use an if/else statement in my application to find out wich table to search for. eg. If(type == 1) table = Mountain; else if(type == 2) table = Beach;. Then I would make another SQL statement that would then search for the data in the table name that I got from the if/else statement.
Is this right, or am I far off from course?

Begginer Confusion

Hey everyone,
I am brand new to SQL and I need some help getting started how to learn about relational databases and how to make the databases via Microsoft SQL(I have visual Studios PRO) and access and use them via C#.
I believe that the program that I am writing will be significantly better with a database. What I am doing is making a program that stores user input. But each user input that a person stores can have a reference to another user input, hence why I think that a relational database to store this information will be work out well. I also just want to learn more about databases because I think that it will be a good step in me learning more about programming.
What I need from you guys is a point into a direction for a book or a specific spot in a website that will introduce and teach me how to design these relational databases via Microsoft SQL. I have programmed alot with Java and recently switched over to C#, using the .net framework and found it to be great. I need to find out how to make these databases but also how to connect and access them through my application that will be coded in C#. I have "Programming in C#" by O'Reilly but it really doesn't go in that much about SQL databases.
The question that I really have come from the limited knowledge that I have gained looking online and talking to my brother (progamming whiz). A database is managed by a DBMS and clients can interact with the DBMS to get to the database. The question to me is how does one interact with a DBMS through a programming language like C#. I guess I am just really confused at the this link. If anyone could point me into the direction of a book to answer these question but help teach me to apply it to my own program, I would really appreciate it. Thank your for time.
P.S.
I have found some books online but I dont really know which one I really need. One that I found is called "Learning SQL on SQL Server 2005". If you have read, I would appreciate your opinion. Thanks

Here are two excellent resources for 'getting your feet wet'.

The ASP.NET site has quite a few videos about other topics -but if you scroll down, you will find some for incorporating databases in the project.

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

http://www.asp.net/learn/videos/default.aspx?tabid=63#sql

The Microsoft Press 'Step by Step' series are good for starting out.

|||Thank you for both of those sources. I will check them out right now. Also, one more question. If I use the SQL database for my application, will all the clients, and everyone that downloads my program have to have SQL installed on their computer so that it will work?
|||

If it is a web based application, the data server is usually located with the web server -all users only require a browser.

If it is a stand-alone Windows based application, you probably would distribute the database (and the server) with your application. (Most likely using the free SQL Server Express.)

Friday, February 10, 2012

Begginer needing help to resolve error.

Begginer needing help to resolve error.

Begginer needing help to resolve error.

Begginer in SQL-Foreign KEy to Mulitple Tables

Begginer Confusion

BEFORE Triggers

Blog Archive

About Me