Ambiguous match found.

Does anyone know what this error means?

Ambiguous match found.

i'm using sql server 2000

here is my code

Dim con As SqlConnection = New SqlConnection

con.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"

Dim cmd As SqlCommand = New SqlCommand

cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT salt,hash FROM users WHERE login_id = '" + user.Text + "'"

Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

con.Open()

Dim salt, hash As String

While rdr.Read()

If user.Text = rdr.Item("login_id").ToString() Then

salt = rdr.Item("salt").ToString()
hash = rdr.Item("hash").ToString()

End If

End While

con.Close()

Label1.Text = salt

End SubAre you sure that SQL statement is what is really getting called in your code ?

rdr.Item("login_id") wouldn't exist in the data reader as you have only selected "salt" and "hash".

<MindReading
My guess is, you haven't posted the SQL statement you are really using, you are using something with a JOIN in it and you are doing a SELECT * in there.
In your result set from the SQL statement, you have two columns called "login_id" so the dataReader doesn't know which one to reference.

If you change your SQL statement to only return the fields you want, you shouldn't have the problem

i.e. SELECT salt,hash, users.login_id FROM users INEER JOIN Blah etc etc

</MindReading

Am I misunderstanding the outer join?

As I understand it, a left outer join should include records from the left table which match the where clauses for that table whether or not there are records from the right table that match the where clauses for that table.

CREATE TABLE dbo.Test1 (
testnumber varchar (10) NOT NULL
) ON PRIMARY
GO

CREATE TABLE dbo.Testlink (
testnumber varchar (10) NOT NULL ,
testdate datetime NOT NULL
) ON PRIMARY
GO

INSERT into dbo.Test1 values ('0051419')
GO

INSERT into dbo.Testlink values ('0051419','2004-11-22')
GO
INSERT into dbo.Testlink values ('0051419','2004-11-29')
GO
INSERT into dbo.Testlink values ('0051419','2004-12-22')
GO

select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
where test1.testnumber='0051419'

returns, as expected,

testnumber testdate
---- ----------------
0051419 2004-11-22 00:00:00.000
0051419 2004-11-29 00:00:00.000
0051419 2004-12-22 00:00:00.000

(3 row(s) affected)

adding a where clause that results in no records matching in testlink however, results in no records being returned-but I expected the record from test1 being returned with a null value in testdate. Am I misunderstanding what should be happening? Or am I simply doing something wrong?

select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
where test1.testnumber='0051419'
and testlink.testdate > '10/16/2006'

testnumber testdate
---- ----------------

(0 row(s) affected)

In the report I'm creating I do need to report the test1 records that meet the selection criteria even when there are no records in testlink that meet the selection criteria for that table.

Thanks.Yes - you are missing something. Everything you say is correct until you get to the bit about ALWAYS returning all rows from the left table that meet the where clause. You can think of the where clause being applied AFTER the join. You effectively want to apply the filter on testlink BEFORE the join. You can use a Derived Table or you can rely on a handy bit of syntax:
select test1.testnumber, testlink.testdate
from dbo.test1 left outer join dbo.testlink on test1.testnumber=testlink.testnumber
and testlink.testdate > '10/16/2006'
where test1.testnumber='0051419'
Putting the expression in the join syntax applies the filter before the left join is applied and should result in your required output.

HTH|||Hmmm. Thanks-that answers my question. It doesn't quite solve my problem but that's because I didn't sufficiently outline the circumstances-and I'm not sure the remaining issue is right for this forum. The remaining problem is that I'm using Crystal Reports & don't see an option for comparing a field to a constant value in defining the link (join). I'll go out to the Crystal site & see if I can get an answer to that question there.

Thanks a lot for explaining what I was missing.