|
gharry wrote: IF(SELECT Horse_name, Horse_FEI From FEI WHERE Horse_Name LIKE inputName%)=True Then
IF(SELECT IDHorse From Boekelo_Horses WHERE Horse_ID = FEI.Horse_FEI)= True Then
Get all data and show edit screen
END IF
END IF
The structure you are looking for is something like this:
IF EXISTS(SELECT * FROM FEI
INNER JOIN Boekelo_Horses AS bh ON bh.Horse_ID = FEI.Horse_FEI
WHERE Horse_Name LIKE @inputName)
BEGIN
SELECT 1;
END
ELSE
BEGIN
SELECT 0;
END
You can then call this SqlCommand with ExecuteScalar() and it will return a 1 for true, and 0 for false. Your calling application can then decide to take appropriate action depending on the outcome.
I've also taken your two SQL statements and rolled them in to one as they were disjointed, the second IF statement seemed to need input from the first that it was never going to get.
In a IF EXISTS(SELECT ....) structure, the list of columns in SELECT is ignored so you don't need to specify them, and specifying * is about the only time it is not inefficient to do that as the Query Optimiser already knows it needs no fields, it is just looking for one row with any data before it will stop processing the statement.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Can someone help me? I can get a list of NT groups (xp_enumgroups) but how do I get a list of NT users not autorized yet for the database?
Bob Zagars
Senior Software Engineer
|
|
|
|
|
I have proc as below: It don't understand the datetime type that I input I don't know why please help me. Thanks.
ALTER PROCEDURE SP_PHIEUNHAP_Search
(
@FromNgayNhap datetime,
@ToNgayNhap datetime,
@CheckNgayNhap bit,
@MaNCC int,
@FromNgayLap datetime,
@ToNgayLap datetime,
@CheckNgayLap bit,
@NguoiLap int
)
AS
DECLARE @sql nvarchar(500)
DECLARE @flag bit
declare @tungay datetime
declare @denngay datetime
SELECT @sql = ' select MaPN, NgayNhap, TenNCC, NgayLap, HoTen, TongTien '
SELECT @sql = @sql + ' from PHIEUNHAP, NHACUNGCAP, NGUOIDUNG '
if (@CheckNgayNhap = 1 )
begin
SELECT @sql = @sql + ' where NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111) + ' and NgayNhap <= ' + CONVERT(NVARCHAR, @ToNgayNhap,111)
set @flag = 1
end
if(@CheckNgayLap = 1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111)
else
SELECT @sql = @sql + ' where NgayLap >= ' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111)
set @flag = 1
end
if(@NguoiLap != -1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap)
else
SELECT @sql = @sql + ' where NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap)
set @flag = 1
end
if(@MaNCC != -1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC)
else
SELECT @sql = @sql + ' where PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC)
set @flag = 1
end
SELECT @sql = @sql + ' and NguoiLap = MaND and PHIEUNHAP.MaNCC = NHACUNGCAP.MaNCC '
EXEC sp_executesql @sql
-- modified at 4:31 Saturday 14th January, 2006
|
|
|
|
|
Don't use: ... NgayNhap >= ' + CONVERT(NVARCHAR, @FromNgayNhap,111)
Just use: ... NgayNhap >= @FromNgayNhap
|
|
|
|
|
alter PROCEDURE SP_PHIEUNHAP_Search
(
@FromNgayNhap datetime,
@ToNgayNhap datetime,
@CheckNgayNhap bit,
@MaNCC int,
@FromNgayLap datetime,
@ToNgayLap datetime,
@CheckNgayLap bit,
@NguoiLap int
)
AS
DECLARE @sql nvarchar(500)
DECLARE @flag bit
declare @tungay datetime
declare @denngay datetime
SELECT @sql = ' select MaPN, NgayNhap, TenNCC, NgayLap, HoTen, TongTien '
SELECT @sql = @sql + ' from PHIEUNHAP, NHACUNGCAP, NGUOIDUNG '
if (@CheckNgayNhap = 1 )
begin
SELECT @sql = @sql + ' where NgayNhap >= ''' + CONVERT(NVARCHAR, @FromNgayNhap,111) + ''' and NgayNhap <= ''' + CONVERT(NVARCHAR, @ToNgayNhap,111) + ''''
set @flag = 1
end
if(@CheckNgayLap = 1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and NgayLap >= ''' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) + ''''
else
SELECT @sql = @sql + ' where NgayLap >= ''' + CONVERT(NVARCHAR,@FromNgayLap) + ' and NgayLap <= ' + CONVERT(NVARCHAR, @ToNgayLap,111) + ''''
set @flag = 1
end
if(@NguoiLap != -1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap)
else
SELECT @sql = @sql + ' where NguoiLap = ' + CONVERT(NVARCHAR, @NguoiLap)
set @flag = 1
end
if(@MaNCC != -1)
begin
if(@flag = 1)
SELECT @sql = @sql + ' and PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC)
else
SELECT @sql = @sql + ' where PHIEUNHAP.MaNCC = ' + CONVERT(NVARCHAR, @MaNCC)
set @flag = 1
end
SELECT @sql = @sql + ' and NguoiLap = MaND and PHIEUNHAP.MaNCC = NHACUNGCAP.MaNCC '
EXEC sp_executesql @sql
|
|
|
|
|
I’ve been working with test and deployment of a standard database based application. It really bugs me how some of the data loading tasks are performed right now.
Examples:
During unit testing, the database is reset to a known state by truncating/deleting all data (this is done by a script), insert some basic system data using SQL insert statements, and load remaining data from XML using Microsoft SQL XML Bulk-loading.
During first deployment, a new database is setup during installation, but without data. Afterwards a database backup is restored and data is transferred from backup database to new database. (Ugly I know!).
During following deployments (updates), SQL script inserts, updates and deletes data during installation.
There must be a better way to complete ALL above tasks! Do you have any ideas?
I’ve been thinking about defining an XML data format to describe the necessary steps to perform the load, and make a .NET component to process the data and update the database.
The format should be:
• Simple.
• Easy to read for humans (with XML knowledge).
• Able to insert,delete/truncate,update data.
• Readable using a forward only XML reader.
• Able to skip loading some sections (example: load data unless data is already present).
• Able to handle nested data where child records receives auto generated keys from parent (ID propagation I think it’s called).
This is a very early and simple draft of how a data file might look:
<?xml version="1.0" encoding="UTF-8"?>
<data>
<table name="Language">
<truncate/>
<append>
<row LanguageID="1" LanguageText="Danish"/>
<row LanguageID="2" LanguageText="English"/>
<row LanguageID="3" LanguageText="Ger"/>
<row LanguageID="4" LanguageText="Goof"/>
</append>
<update>
<row LanguageID="3" LanguageText="German"/>
</update>
<delete>
<row LanguageID="4"/>
</delete>
</table>
</data>
Has someone else already made something that can do this?
Any thoughts?
|
|
|
|
|
Hi
i have two tables lets say t_order and t_customer
the t_order(FK: CustomerID) is the child table and t_customer (PK: ID)is the parent table.
i have bindind the two tables to a datagrid using BindingSource so the customer Name appears instead of CustomerID,also i have managed to filter them using Parent(RelationShip) function ,but i does not work for Sorting.
so is there any way for sorting a table using a detail in other related table?
thanks
|
|
|
|
|
I am fetching records from a SQL database. I create an ADODB::_RecordsetPtr ptr. In my code this works:
newID = ptr->Fields->Item["ID"]->Value;
These don't:
newID = ptr->Fields->Item[0]->Value;
newID = ptr->Fields->Item[1]->Value;
newID = ptr->Fields->Item[0]->GetValue();
newID = ptr->Fields->GetItem(0)->Value;
newID = ptr->Fields->GetItem(0)->GetValue();
The SQL string creatint the recordset is "SELECT ID FROM COMPONENTS WHERE ACTIVE<>0"
Any idea why the ordinal approach doesn't work? I would like to save the overhead of comparing strings inside ADO as this may be called up to 2 million times in valuing the database.
Thanks,
Mark Jackson
|
|
|
|
|
Duh, the parameter to GetItem is a variant not an int, 0 = NULL that's why it was bombing. What I get for coding early in the morning....
Mark Jackson
|
|
|
|
|
Hello everyone,
I'm doing a query for user names and email address from a database. Unfortunately, due to circumstances out of my control, data is poorly/incorrectly formatted and sometimes leads to *mostly* duplicate entries for the same thing.
Example:
SELECT DISTINCT(email), firstname + ' ' + lastname <br />
FROM users <br />
WHERE compdate BETWEEN '1/1/2006' AND '1/31/2006' <br />
AND email IS NOT NULL <br />
AND email <> ' ' <br />
ORDER BY TLH_1_1240
will return the following:
1 test01@test01.com John Doe
2 test01@test01.com John T. Doe
As you can see, the email is the same and the name is similar enough, at least for my purposes.
The DISTINCT is obviously working in a way that I don't understand and carrying over to distinct names. I just want distinct email addresses and the name that comes up associated with them first. Your thoughts?
Thanks in advance for your help!
-------------------
abort, retry, fail?
-- modified at 11:57 Friday 13th January, 2006
|
|
|
|
|
Try something like:
SELECT
email,
MIN(firstname + ' ' lastname) AS fullname
FROM
users
WHERE
compdate BETWEEN '1/1/2006' AND '1/31/2006' AND
email IS NOT NULL AND
email <> ' '
GROUP BY
email
|
|
|
|
|
You know, I can't tell you how many times I did something just a *little* different than that without the results . All it took was just a that MIN. Thanks for your help!
-------------------
abort, retry, fail?
|
|
|
|
|
Hai
I am using SQL server 2005 BETA . After logging into SQL Server using WindowsAuthentication.
When I try to run comand GRANT CONNECT TO [MyUser] on query Analyser.
An error message is returned
Cannot find the user 'MyUser', because it does not exist or you do not have permission.
I had created a usernamed 'MyUser'. How can i solve this problem
Regards
|
|
|
|
|
hi, i'm trying to connect to a sql connection, in my computer, but it keeps sending me this error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)
i tried to use the : "server=(local)" in the connectionstring, but it doesn´t matter, what can i do?, is there a way that a i can modify the default settings?...where?
Thanks.
|
|
|
|
|
In VB i am using this string:
sqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=secret;database=EventMngt")
and works fine for both SQL2000 and 2005.
|
|
|
|
|
Is this sql server express or the full version. I would guess that the full version does allow remote connections by default ut i know that express does not. If it is express its an easy fix anyway.
Jonathan
|
|
|
|
|
This is because the default installation for SQL 2005 only lets you connect on the same computer (Shared memory protocol). To connect remotely with TCP, Named Pipes or whatever, you have to set up the protocols you want to use.
Go to Start -> Programs -> Microsoft SQL 2005 -> Configuration tools -> SQL Server Configuration Manager
Then open the treenode called SQL Server 2005 Network Configuration and click on Protocols for MSSQLSERVER.
Should be able to figure the rest out.
Regards,
Chad
|
|
|
|
|
Chad is correct. I had the same problem when trying to access SQL 2005 Express from a different computer. Did the steps he provided and all is well. Just my two cents worth 
Paul
|
|
|
|
|
Its the SQL 2005 Express edition, and the thing is that all that i'm trying to do is access my own pc, i mean (local), and it keeps sending me the same error even if i use: "server=localhost" or "server=(local)", i even tried the examples that other guys told me (i mean the ones in the forum), but it doesn't work... what i'm i doing wrong?
|
|
|
|
|
OK, the express edition is quite different to SQL server 2005. The comment above was quite correct but perhaps not explained completely.
The link below will give you full details on how to do this so there is no point me repeating any of it. Read it completely as its a good overview of how the express editions work.
Pay particular attention to the sections "Configure for network connectivity" and "Programmatically opening a database from a remote computer". These explain exactly how to do what you want to do.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvsdev05/html/vs05b1.asp[^]
Jonathan
|
|
|
|
|
Ok Here is thew error:
An OleDBParameter with ParameterName 'TableNumber' is not contained by this OleDbParameterCollection.
Here is the code:
private void UpdateTable(string strTable, decimal decSubtotal)
{
try
{
objUpdateSubtotal.Parameters["Subtotal"].Value = decSubtotal;
objUpdateSubtotal.Parameters["TableNumber"].Value = strTable;
oleDbConnection1.Open();
objUpdateSubtotal.ExecuteNonQuery();
oleDbConnection1.Close();
}
catch(System.Exception caught)
{
MessageBox.Show(caught.Message);
}
}
And here is the Sql statementr:
UPDATE tables
SET Subtotal = ?
WHERE (TableNumber = ?)
What am I missing?? (I built the statement in VS query builder in the same project and verified it)
Thanx in Advance !
Jude 
|
|
|
|
|
It means that your objUpdateSubtotal object doesn't have a parameter in its collection, called "TableNumber". We can't tell you exactly what's wrong, because you haven't supplied the code that builds your objUpdateSubtotal object.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Ok, here is the statement that VS created when I dragged and dropped an OleDbCommand on the form:
private System.Data.OleDb.OleDbCommand objUpdateSubtotal;
I then changed the CommandText field to :
UPDATE tables SET Subtotal = ? WHERE (TableNumber = ?)
through the query builder.
Thank you!
|
|
|
|
|
TheJudeDude wrote: when I dragged and dropped an OleDbCommand on the form:
Mistake number one. It's always much better to write your own DB code than to rely on the designer to do it for you. You have MUCH greater control, easier to debug, and you won't end up making mistake number 2.
Mistake number 2. Just changing the SQL statement will not add the Parameter objects to the Parameters collection. OleDb doesn't use named parameters so you have to make sure that the parameter objects you add are done in the correct order as your SQL statement expects them.
You're missing something like this:
OleDbCommand cmd = new OleDbCommand("UPDATE tables SET Subtotal = ? WHERE (TableNumber = ?)", connectionString);
cmd.Parameters.Add("@Subtotal", OleDbType.Single, 4).Value = decSubtotal;
cmd.Parameters.Add("@TableNumber", OleDbType.Char, 10).Value = strTable;
This code is, of course, not guaranteed to work because there is so much more we don't know about your code, database field types and widths, ...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Gotcha...just received the book "Mastering C# Database Programming" by Jason Price.
Well, what I did was look at the Data-->Parameters(Collection)...and the TableNumber had the word "Original_" in front of it...
What should be my next investment in futhering my knowledge in C# and .Net?
Thanx
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
