|
Hi,
i would like to know how it's possible to work with a output parameter.
i already worked this way for input:
"insert into ... (test, test2) values(@test, @test2)"
and then i add those parameters to the parameterscollection of my odbccommand object.
now i want to do a simple select of one column:
"select test from testtable" but how can i retrieve that value , the result of my query i mean, without working with a dataset or a reader. is it possible to also work with parameters for this?
grtz & thx
|
|
|
|
|
|
thx that works 
and what if i don't have more than one value, let's say:
select test, test2 from testtable
how do i get the value of test and test2 ? (without reader and without filling a dataset)
|
|
|
|
|
Hi, I have a remote query which i invoked using sp_ExecuteSQL. Using the SQL Profiler, I found out that the query is being executed 6 times instead of only ones. Does anyone know how to prevent this from happening? I am using ADO in VB6. Thanks.
SDE
|
|
|
|
|
I created a full text index who contain organizations and there propreties. I did a stored proc that find all the organization in the table that correspond to a spécific name. The problem is when I have a hyphen in the name, the proc get case sensitive only for the first letter.
example :
search : arc-en-ciel, it returns : Arc-en-ciel CPE and arc-en-ciel
if I serch for : arC-eN-cIeL, it returns : Arc-en-ciel CPE but not arc-en-ciel
and if I search for : Arc-En-Ciel, it returns : Arc-en-ciel CPE and arc-en-ciel
my stored proc looks like this :
CREATE procedure SearchOrganization
(
@Name varchar(1000) = null
)as
declare @WhereName varchar(1000)
if (@Name is null)
set @WhereName = ''
else
begin
--Do poutine to extract and build WhereName clause
SET @WhereName = @Name
if(@KeyWordsOperator = 1) -- 'and'
SET @WhereName = REPLACE(@WhereName, N',', N'") and formsof(inflectional,"') + N'")'
else
if(@KeyWordsOperator = 0) -- 'or'
SET @WhereName = REPLACE(@WhereName, N',', N'") or formsof(inflectional, "' ) + N'")'
if(@KeyWordsOperator = 1 or @KeyWordsOperator = 0) -- 'and' et 'or'
Set @WhereName = ' contains(oo.u_Namestrip, ''formsof(inflectional, "' +@WhereName + ''')'
else --'EXACT'
Set @WhereName = 'oo.u_name = ''' +@WhereName + ''''
If (@IncludeAnd = 1)
set @WhereName = @And + @WhereName
set @IncludeAnd = 1
end
I'm a beginner at SQL and this the code was created by a other person. I tried to find something on the net to help me but I found nothing.
Sorry for my english,
Nik
|
|
|
|
|
Hi
where i can find a 100% free sql server 2000 documentor tool?
Tanks.
|
|
|
|
|
CodeSmith
After downloading CodeSmith there are templates included with the installation that should take care of it. If not it should get you close as you can customize the templates. Great Tool!
http://www.ericjsmith.net/codesmith/
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I'm writing my first .Net app (old ASP/VBScript developer). Using VS2003 I set up a SQLConnection & SQLDataAdapter. I use a SQLCommand to fill a Dataset. I'm displaying the Dataset in a DataGrid that has Edit enabled. I'm trying to figure out how to use the generated Update command in the SQLDataAdapter in my Update event handler. I have all the code to pick up the values from all the cells, but all the examples I've seen for the SQL Update code create a new connection, adapter and hard-code an Update command, set all the command parameters and execute the command. The SQLDataAdapter already has a parameterized Update command. How do I reference the parameters of that Update command to set them to the values of my cells and execute the command?
|
|
|
|
|
i make a query on my database.
I want the image data inside a row.
I select it and i start to read data.
The problem is that sometimes there is no data inside, so if i try to read i get exception.
Can i check if there is data inside that field before start ot read?
That field doesn't support NULL default value.
i use Microsoft Access database .mdb and ADO.NET
|
|
|
|
|
Or you could just wrap your code to read that field in a Try/Catch block. If it fails, there wasn't anything there to read.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
i've created an application that register and make login the users.
When the user register himself enter the following texts:
name: Marco
password: project
Then if the user try to login with this value it get login either write Marco and mArCO, project and PROJECt
i use the following code:
string query = "SELECT * FROM Table WHERE Name = '" + textBoxName.Text + "' AND Password = '" + textBoxPassword.Text + "'";
It is not case sensitive, why?
|
|
|
|
|
Sounds like the collation sequence for your database is set to case insensitive. You can change the collation sequence on a column-by-column basis if you like, but I find that has many problems when comparing data between columns with different collation.
Once set, you cannot change the collation sequence of the database as a whole as it has to be set at creation time.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i could create a new database...
how can set the property that handle case-sensitive in query statment?
|
|
|
|
|
I have a question - I am trying to design a database - contacts - and I have seen many around but do not serve my purposes - so I started with my requirements:
The logical breakdown where:
- the adress stored could belong to an individual or company.
- the relationship of person to company could be one person can own multiple companies but one company could be the employer of many persons.
- Thus - the address could be the address of the company but also of the person's work address or the address could be the persons personal address (and have nothing to do with the company - say an unemployed alcoholic who is a good friend).
I am gong in circles - any suggestions? I have not even started into the phone table yet!
|
|
|
|
|
Sounds like you need some many-to-many joins
Okay - Let's try and put this in atomic bullet points.
* Address can belong to an individual
* Address can belong to a company
* Person can own 0, 1 or more companies
* Company can employ 0,1 or more persons
* Person can have a work address
* Person can have a home address
* A person can be employed by 0, 1 or more companies.
So, you have a person table, a company table and and address table. You also have some many-to-many joins which require intermediate tables, so you have a person_owns_company table and a company_employs_person table. These intermediate tables take the primary key from each side of the join and combine them together to make a compound primary key of its own.
So, if you have a PersonID and and CompanyID your person_owns_company table will have a compound primary key of PersonID and CompanyID. They also remain foreign keys back to their originating tables.
A person can have a homeAddressID column which will be a foreign key to the address table
A company can have an addressID column which, again, will be a foreign key for the address table.
You may wish to extend this futher as companies can have more than one location and the addressID may be better placed on a company_location table.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
This helps. Interesting that you brought in the location...I thought about it after I posted the question and ended with a headache. How would you handle if you wanted to specify the location for the person's workplace. Say Jill works for company foo, which have 5 locations, and Jill is in location 3. In other words, how would you tie person with company location using the above scenario?
And Thanks!
|
|
|
|
|
Two ways I can think of depending on what sort of data you need.
1. The person has a join to company_location rather than company (the company can be infered from the location)
2. If you have the idea that many companies can occupy the same location, for example a number of small companies in a business centre. The you would join the person to the company and the location.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks Colin,
This helps a lot. The clear, logical and objective perspective is what i needed.
And I looked at your Blog - browsed quickly through Normalising the data model. Looks very informative, will go and read it again. Its cool.
|
|
|
|
|
|
Colin Angus Mackay wrote:
so you have a person_owns_company table and a company_employs_person table
Question: Would it be better to add the above two tables or one one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType" eg: IsEmployee or IsOwner. (kind of like a crossreference). Of course this would also be two tables if I introduce a separate table called "TypeTable" and use it to lookup the "RelationType". Any suggestions?
|
|
|
|
|
hp108 wrote:
Would it be better to add ... one table called "PersonCompany" with foreignkeys: PersonID and CompanyID and one field called "RelationType"
I wouldn't have that as one table with a RelationType column unless there were going to be many queries that didn't care what the relation was, just that they were related in some way. The reason for this is that these are two distinct logical relationships and the physical side should match unless there are performance problems. In otherwords, design the model as normalised as possible, then denormalise it afterwards if you find performance problems.
If you do decide to put it only one table, remember that the RelationType column is most likely to become part of the Primary Key if you can have a person that shares both types of relation with the company.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I believe you have a point because:
(quick type, excuse mistakes - just to make a point)
select p.*, c.companyname from person p inner join personcompany pc on p.personid = pc.personid
inner join company c on pc.companyid = c.companyid where
pc.relationtype="IsEmployee"
group by c.companyid
would give the same result as the following which does not use the where clause:
select p.*, c.companyname from person p inner join company-employs-person pc on p.personid = pc.personid
inner join company c on pc.companyid = c.companyid
group by c.companyid
I'll experiment with both and see which is faster.Thanks.
|
|
|
|
|
Colin Angus Mackay wrote:
A person can have a homeAddressID column which will be a foreign key to the address table
A company can have an addressID column which, again, will be a foreign key for the address table.
Sorry for the bother! Does this mean that "homeAddressID" in the address table will be null when the address belongs to the company ( and vice versa ) ?
|
|
|
|
|
No, the address table would have an AddressID column which would be its primary key. The Address table does not need to know that it is a home address or office address. It is just an address.
The person table may have a homeAddressId column (indicating that it is the home address of the person). A person may have a workAddressID as well (if that is your design - although it may be inferred by their links to a company or companyLocation)
The Address table would be part of a one-to-many join, for every one address there are many people or many companies or both. The Address table cannot track that, so the tracking goes to the person or company table. Something like this:
ParentTable (Address) ChildTable (Person)
--------------------- -------------------
ParentId <----\ ChildID
OtherColumns \---> ParentID
OtherColumns
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Design wise this makes sense, but in practice this would mean that I have to add the address record before I could refer to it from the person or company table, which seems backward as it would allow me to add addresses without requiring it be used. So I could end up with addresses which are orphaned (as no one refers to them). I am new at this so I do not know if this is normal pattern?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
