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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SanMiguel12@gmail.com - 31 Jul 2007 12:14 GMT

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 =)

Reply to this Message

Armin Zingler - 31 Jul 2007 12:33 GMT

> 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
[quoted text clipped - 8 lines]
> 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

Reply to this Message

SanMiguel12@gmail.com - 31 Jul 2007 13:17 GMT

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.

Reply to this Message

Armin Zingler - 31 Jul 2007 13:35 GMT

> 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

Reply to this Message

SanMiguel12@gmail.com - 31 Jul 2007 13:50 GMT

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???

Reply to this Message

Armin Zingler - 31 Jul 2007 14:25 GMT

> 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
[quoted text clipped - 25 lines]
>
> 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

Reply to this Message

zacks@construction-imaging.com - 31 Jul 2007 16:39 GMT

On Jul 31, 8:50 am, SanMigue...@gmail.com wrote:

> 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
[quoted text clipped - 25 lines]
>
> 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.

Reply to this Message

Armin Zingler - 31 Jul 2007 13:25 GMT

> 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

Reply to this Message

tommaso.gastaldi@uniroma1.it - 31 Jul 2007 12:41 GMT

On 31 Lug, 13:14, SanMigue...@gmail.com wrote:

> 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
[quoted text clipped - 7 lines]
> 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

Reply to this Message

SanMiguel12@gmail.com - 31 Jul 2007 12:54 GMT

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.

Reply to this Message

tommaso.gastaldi@uniroma1.it - 31 Jul 2007 13:09 GMT

On 31 Lug, 13:54, SanMigue...@gmail.com wrote:

> 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.
[quoted text clipped - 24 lines]
> 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

Reply to this Message

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

Free Magazines