About Stephen Jackson Expertise I can help with questions regarding VB.NET syntax and object references, with data interfaces and with the design and creation of robust, data aware object classes. I can also be very helpful with creating distributable applications and provide tricks and tips on .msi creation. I also have extensive experience in designing SQL Server Databases and interfacing them with VB.NET. I try to avoid web specific questions, as that is an area of expertise all its own.
Experience
Experience in the Area: I have been a programmer in Visual Basic since version 1.0 and have worked with VB.NET (which is infinitely more powerful than previous versions) since its initial release and SQL Server, both as a corporate IT professional and professional consultant. I first wrote Basic in 1976 on a TRS 80 and have worked in Visual Basic 1.0 and every subsequent release of Microsoft Visual Basic. I worked for over 7 years as a Senior Level Consultant in the area and currently hold a Project Manager position in IT.
Education and Credentials: MBA in Econometrics, 1983, University of Memphis.
BBA in Financial Management, 1982, Fogelman College of Business and Economics, University of Memphis. Microsoft Certified Professional
Areas of Special Expertise:
My specialty is the design of Object Oriented Solutions with robust, data aware object classes. I generally avoid the classic ‘Three Tier’ model as I find it redundant and cumbersome to maintain. I also specialize in the creation of ‘User Friendly’ User Interfaces which help lessen the need for user training and help prevent user error. I work best with Windows Forms based applications, and while I do work in C# as well, I prefer to limit my questions here to Windows Forms based applications created in Visual Basic.NET and SQL Server. I wil also address questions relating to the distribution and installation of Windows Forms based applications created in VB.NET.
Expert: Stephen Jackson Date: 4/24/2008 Subject: showing and deletion in vb.net
Question QUESTION: Hi,
I have two tables person and legnresp which are having personid as common field.Person table is parent and legnresp is child. i want to delete related record in legnresp so i made a form and do following coding in ComboBox1_SelectedIndexChanged as i want that while selecting combobox i get personid of both table but its showing error it is showing from person table but my question is how to show related record in textbox4.text from legnresp
Note:- without using cascade i want to do.
Dim sqlconn As New SqlConnection("data source=TBPC25\SQLEXPRESS;Initial Catalog=RealEstate1;Integrated Security=True")
sqlconn.Open()
cmd = New SqlCommand("select firstname from person where firstname='" & ComboBox1.Text & "'", sqlconn)
cmd2 = New SqlCommand("select personid from person where firstname='" & ComboBox1.Text & "'", sqlconn)
cmd3 = New SqlCommand("select personid from legnresp person where personid='" & TextBox3.Text & "'", sqlconn)
'cmd3 = New SqlCommand("select personid from person legnresp where personid.person = personid.legnresp", sqlconn)
'Dim ra As Integer
dr = cmd.ExecuteReader()
If dr.read Then
TextBox1.Text = Trim(dr(0))
End If
sqlconn.Close()
sqlconn.Open()
dr2 = cmd2.ExecuteReader
If dr2.Read Then
TextBox3.Text = Trim(dr2(0))
End If
sqlconn.Close()
sqlconn.Open()
dr3 = cmd3.ExecuteReader
' If dr3.Read Then
TextBox4.Text = TextBox3.Text
'End If
when i do textbox4.text=textbox3.text it always shows whatever present in textbox3 which i don't want how to show
("select personid from person legnresp where personid.person = personid.legnresp") in textbox4 while selecting personid from combobox it show related record of personid in tetbox4.
After showing it i want to delete particular record which i want.
Hope you understand what i want to explain and do the needful to help me in this.
Thanks
Sukhvinder
ANSWER: Sukhvinder,
If I understand correctly, you want to select a person in the combo box, display their first name in textbox1. Then you want to select the personid which relate to this person and place this value in textbox3, and then query the related records for this person from legnresp ,then be able to select a record from textbox3 and delete that record?.
Assuming I am right, I think you need to use a grid control to return the recordset from legnresp so you can select, but to get the related records to show should be simple enough ... just change
TextBox4.Text = TextBox3.Text
to
TextBox4.Text = Trim(dr3(0))
This way, you are actually showing the records you wanted to see. Now I have never tried to place a recordset in a text box .. it might work.
Stephen
[PS: this looks a lot like a homework question to me, which I usually do not answer, but I figured I would give this a try ;c)]
---------- FOLLOW-UP ----------
QUESTION: Thanks Stephen,
but how to show related data which is in person table like
person table has field(personid,firstname) and legnresp table which is child table has(legnid,personid).
i have made combo box and while selecting firstname related personid should come in textbox3 and textbox4, i am successful in showing textbox3 which related to parent table but how to show in same personid in textbox4.
cmd4 = New SqlCommand("select personid from legnresp where personid in(select personid from person where firstname='" & cbotext & "'", sqlconn)
dr3 = cmd4.ExecuteReader()
While dr3.Read()
TextBox4.Text = Trim(dr3("personid").ToString)
End While
it showing error at dr3 = cmd4.ExecuteReader()
Answer Sukhvinder,
Well it took me long enough to see this ;c)
cmd4 = New SqlCommand("select personid from legnresp where personid in(select personid from person where firstname='" & cbotext & "'", sqlconn)
In this, you are actually missing a closing parentheses.
It should read:
cmd4 = New SqlCommand("select personid from legnresp where personid in(select personid from person where firstname='" & ComboBox1.Text & "')", sqlconn)
Make sure the reference to the selection in the combo box is right, too. I changed that be consistent wit the others above. I think this will do the trick.
