help with .net2005, sqlserver 2000, listbox textbox and SELECT...FROM

[SanMiguel12]

Hi, I have a huge problem which drives crazy for a month... I am
working on a database program and I am using .net2005 and Sql server
2003. In a form I have a listbox and two texboxes and there's also in
my database a table with three sells: "name", "e-mail" and "address".
The listbox shows the "name" and I want when I choose a name, the two
textboxes to show the other two sells ("name","address") that goes for
the name i chose in the table. And guess what? I don't know how!!! I
know I have something like "SELECT...FROM...WHERE...LIKE..." but the
best think I ever got is "false" in the two textboxes... Please tell
how to do that and also in which listbox's event I have to do this?
PLEASE HELP ME !!!! IT'S ARGENT !!! Thanx a lot =)

 

[Armin Zingler]

Hi, I have a huge problem which drives crazy for a month... I am
working on a database program and I am using .net2005 and Sql server
2003. In a form I have a listbox and two texboxes and there's also
in my database a table with three sells: "name", "e-mail" and
"address". The listbox shows the "name" and I want when I choose a
name, the two textboxes to show the other two sells
("name","address") that goes for the name i chose in the table. And
guess what? I don't know how!!! I know I have something like
"SELECT...FROM...WHERE...LIKE..." but the best think I ever got is
"false" in the two textboxes... Please tell how to do that and also
in which listbox's event I have to do this? PLEASE HELP ME !!!! IT'S
ARGENT !!! Thanx a lot =)

Assuming that the record exists and the name is unique:

'untested:
Private Sub ListBox1_SelectedIndexChanged( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles ListBox1.SelectedIndexChanged

Dim name As String
Dim cmd As SqlCommand
Dim dr As SqlDataReader

name = ListBox1.SelectedItem.ToString

cmd = New SqlCommand("select * from persons where name = @name")
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = name

dr = cmd.ExecuteReader
dr.Read()
txtAddress.Text = dr("address").ToString
txtEMail.Text = dr("[e-mail]").ToString
dr.Close()

End Sub


Though, is the name unique? If yes, why do you use LIKE, and if no, what if
there are several records with the same name?


Armin

 

[tommaso.gastaldi]

Hi, I have a huge problem which drives crazy for a month... I am
working on a database program and I am using .net2005 and Sql server
2003. In a form I have a listbox and two texboxes and there's also in
my database a table with three sells: "name", "e-mail" and "address".
The listbox shows the "name" and I want when I choose a name, the two
textboxes to show the other two sells ("name","address") that goes for
the name i chose in the table. And guess what? I don't know how!!! I
know I have something like "SELECT...FROM...WHERE...LIKE..." but the
best think I ever got is "false" in the two textboxes... Please tell
how to do that and also in which listbox's event I have to do this?
PLEASE HELP ME !!!! IT'S ARGENT !!! Thanx a lot =)

it may become Gold if you post some code and a better explanation

T

 

[SanMiguel12]

look I am not very good at .net and sql (and I am trying something
difficult? very clever of me =D) but I will do my best.
this is the best thing I've managed to do and it's not working.

Private Sub Customers_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

CustomersTableAdapter1.Fill(_Dol_Ammad_E_Store_DataSet11.Customers)
End Sub

Private Sub ListBox1_DisplayMemberChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles ListBox1.DisplayMemberChanged

Using connection As New SqlClient.SqlConnection("Data
Source=DRJAMES;Initial Catalog=Dol-Ammad(E-Store);Integrated
Security=True;")
connection.Open()
Using command As New SqlClient.SqlCommand("SELECT [e-mail]

From Customers WHERE Name = '" & ListBox1.SelectedItem & "'",

connection)
TextBox2.Text = command.ExecuteScalar.ToString
End Using
End Using
End Sub

Now, I don't know what else to sent you and to be honest I don't have
anything else done on this problem... If need something else tell me
what and I'll try to sent it. Thanx for your interest.

 

[tommaso.gastaldi]

look I am not very good at .net and sql (and I am trying something
difficult? very clever of me =D) but I will do my best.
this is the best thing I've managed to do and it's not working.

Private Sub Customers_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

CustomersTableAdapter1.Fill(_Dol_Ammad_E_Store_DataSet11.Customers)
End Sub

Private Sub ListBox1_DisplayMemberChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles ListBox1.DisplayMemberChanged

Using connection As New SqlClient.SqlConnection("Data
Source=DRJAMES;Initial Catalog=Dol-Ammad(E-Store);Integrated
Security=True;")
connection.Open()
Using command As New SqlClient.SqlCommand("SELECT [e-mail]>From Customers WHERE Name = '" & ListBox1.SelectedItem & "'",

connection)
TextBox2.Text = command.ExecuteScalar.ToString
End Using
End Using
End Sub

Now, I don't know what else to sent you and to be honest I don't have
anything else done on this problem... If need something else tell me
what and I'll try to sent it. Thanx for your interest.

try ExecuteReader and get the field values you need.

ExecuteScalar returns 1 value and there is no way you can fill 2
TexBoxes with 1 value.
Further, that value may not be what you want, as for instance is a
field of no interest to you.

T

 

[SanMiguel12]

Yes the name is unique and I was using LIKE in this way:

SqlDataAdapter1.SelectCommand.CommandText = "SELECT e-mail FROM
persons WHERE name LIKE '" & ListBox1.Text & "'"
TextBox2.Text = SqlDataAdapter1.ToString

I tried what you sent me but still nothing happend. =( there's no
problem with the code, the program runs without faults and when I
change the listbox item the two textboxes remain empty. WHY???????? I
know that the solution is like the one you sent but it doesn't work.

 

[Armin Zingler]

cmd = New SqlCommand("select * from persons where name = @name")

Sry, forgot the connection:

cmd = New SqlCommand("select * from persons where name = @name", con)


And too many brackets:

txtEMail.Text = dr("[e-mail]").ToString

txtEMail.Text = dr("e-mail").ToString


Armin

 

[Armin Zingler]

Yes the name is unique and I was using LIKE in this way:

SqlDataAdapter1.SelectCommand.CommandText = "SELECT e-mail FROM
persons WHERE name LIKE '" & ListBox1.Text & "'"
TextBox2.Text = SqlDataAdapter1.ToString

I'm a bit confused because, in the other posting

a) you don't use LIKE
b) the last line is

TextBox2.Text = command.ExecuteScalar.ToString

c) you use Listbox1.SelectedItem, not Listbox1.Text

The latter shouldn't work if you enable Option Strict.



Armin

 

[SanMiguel12]

Oh, sorry. My fault! Let's forget completely the LIKE thing and all
the stupid code I wrote... ( I told you that obviously I was writting
whatever I found because it's almost a month I am trying to fix this,
so everything is wrong!!!).
Now, the code I am using right now - and still not working... - is
this:

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs)
Dim name As String
Dim cmd As SqlClient.SqlCommand
Dim dr As SqlClient.SqlDataReader

name = ListBox1.SelectedItem.ToString

cmd = New SqlClient.SqlCommand("SELECT * FROM Customers WHERE
name = @name", SqlConnection1)
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value =
name

dr = cmd.ExecuteReader
dr.Read()
TextBox3.Text = dr("address").ToString
TextBox2.Text = dr("E-mail").ToString
dr.Close()

End Sub

Do you find anything wrong???

 

[Armin Zingler]

Oh, sorry. My fault! Let's forget completely the LIKE thing and all
the stupid code I wrote... ( I told you that obviously I was
writting whatever I found because it's almost a month I am trying to
fix this, so everything is wrong!!!).
Now, the code I am using right now - and still not working... - is
this:

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs)
Dim name As String
Dim cmd As SqlClient.SqlCommand
Dim dr As SqlClient.SqlDataReader

name = ListBox1.SelectedItem.ToString

cmd = New SqlClient.SqlCommand("SELECT * FROM Customers WHERE
name = @name", SqlConnection1)
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value =
name

dr = cmd.ExecuteReader
dr.Read()
TextBox3.Text = dr("address").ToString
TextBox2.Text = dr("E-mail").ToString
dr.Close()

End Sub

Do you find anything wrong???

No, nothing wrong.

Are you sure you connected to the right database? Right table? Really no
duplicate names?
I guess you have checked this alredy, but...


Is it possible that you can send me the project via e-mail?
Use the sender address of this post and insert a "_" between "no" and
"spam".


Armin

 

[zacks]

Oh, sorry. My fault! Let's forget completely the LIKE thing and all
the stupid code I wrote... ( I told you that obviously I was writting
whatever I found because it's almost a month I am trying to fix this,
so everything is wrong!!!).
Now, the code I am using right now - and still not working... - is
this:

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs)
Dim name As String
Dim cmd As SqlClient.SqlCommand
Dim dr As SqlClient.SqlDataReader

name = ListBox1.SelectedItem.ToString

cmd = New SqlClient.SqlCommand("SELECT * FROM Customers WHERE
name = @name", SqlConnection1)
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value =
name

dr = cmd.ExecuteReader
dr.Read()
TextBox3.Text = dr("address").ToString
TextBox2.Text = dr("E-mail").ToString
dr.Close()

End Sub

Do you find anything wrong???

Try running your code with the SQL Profiler running a trace. Verify
that the SQL statement being executed is what you expect it to be. You
are still using the .SelectedItem property as opposed to the.Text
property, that may be part of the problem. Perhaps single step through
the code and after the variable "name" is assigned a value, dump the
value in the command window and verify the variable was assigned the
correct value.