11 Replies - 60236 Views - Last Post: 27 August 2007 - 05:05 PM Rate Topic: -----

#1 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Displaying SQL results in textbox

Post icon  Posted 27 August 2007 - 11:18 AM

I know that this is going to sound terribly simple, but I have search the internet all over and can't find the answer to this seemingly simple problem. I need to put the results of my sql query into my form text boxes... I can't seem to find the correct syntax. I thought that by adding the @ symbol in front of the variable, that would work, but nope.

Here's my code.

	Public Sub GetMyData(ByVal myConnString As String, ByVal searchValue As String)
		Dim mySelectQuery As String = "Select customer_id, customer_bus_name, customer_contact1_first_name, customer_contact1_last_name, customer_contact1_phone, customer_contact2_first_name, customer_contact2_last_name, customer_contact2_phone, customer_address, customer_city, customer_state, customer_zip, customer_fax, customer_taxid, customer_discount_rate From customers Where ( customer_bus_name LIKE searchValue );"
		Dim myConnection As New SqlConnection(myConnString)
		Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
		myConnection.Open()

		customerIdTextBox.Text = @customer_id
		businessNameTextBox.Text = @customer_bus_name
		contact1FirstNameTextBox.Text = ""
		contact1LastNameTextBox.Text = ""
		contact1PhoneTextBox.Text = ""
		contact2FirstNameTextBox.Text = ""
		contact2LastNameTextBox.Text = ""
		contact2PhoneTextBox.Text = ""
		addressTextBox.Text = ""
		cityTextBox.Text = ""
		stateTextBox.Text = ""
		zipCodeTextBox.Text = ""
		faxTextBox.Text = ""
		taxIdTextBox.Text = ""
		discountRateTextBox.Text = ""

		myConnection.Close()
	End Sub



Is This A Good Question/Topic? 1

Replies To: Displaying SQL results in textbox

#2 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 11:46 AM

For starters your query isn't ever going to return any results, unless someone business name is like searchValue as you're not passing the value of the searchValue variable in your query, you actually passing the word searchValue, so your query needs to be

Dim mySelectQuery As String = "Select customer_id, customer_bus_name, & _
customer_contact1_first_name, customer_contact1_last_name, & _
customer_contact1_phone, customer_contact2_first_name, & _
customer_contact2_last_name, customer_contact2_phone, & _
customer_address, customer_city, customer_state, customer_zip, & _
customer_fax, customer_taxid, customer_discount_rate & _
From customers Where ( customer_bus_name LIKE '" & searchValue & "' );"



Second, you're not executing your query, you're just opening the connection to the database, which again isnt ever going to give you any results.

Third, research the SqlDataAdapter and the DataTable, this will show you how to populate a DataTable with the SqlDataAdapter, then use the values in the DataTable to populate your textboxes.

Once you research these 2 items and try to make it work and have problems, post your code here with the problems/errors you're having and we would gladly help you :)
Was This Post Helpful? 1

#3 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 12:01 PM

I have made the application before using the data sources, data sets, table adapters and all of that, that comes with it, but I didn't like the functionality of it... So I scrapped it in favor of creating my own forms and queries. This is for my final project in VB .NET, so I'm really trying to have a nice, user friendly application. Unfortunately the book we used in class, "Microsoft Visual Basic 2005 Reloaded Second Edition" doesn't cover a whole lot in terms of database applications.

So I apologize for my obvious lack of basic vb .net & SQL knowledge, my experience is in PHP w/MySQL, so much different. I have however been spending the entirety of my days researching these questions prior to submitting them here for help, as I do believe in research.

Thank you though for your help.
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 12:09 PM

Im not talking about the SqlDataAdapter & DataTable controls that you drag & drop onto the form, Im talking

Dim daMyName As SqlDataAdapter
Dim dsMyName As DataSet

...'Create your connection, command objects
MyConnection.Open
daMyName.SelectCommand = MyCommand
daMyName.Fill(dsMyName)



Then you have access to all the values you returned in your query, like

TextBox1.Text = dsMyName.Tables(0).Rows(0).Item("YourColumnName").ToString
...and so on


Was This Post Helpful? 1

#5 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 01:04 PM

Ok, correct me if I'm wrong, but in order to display the queried output, I need to find out the row of the matching result and fill in the Tables(0) and Rows(0) , so it can then display the matching row? From what I'm finding, you're supposed to use a foreach loop?

	Public Sub GetMyData(ByVal myConnString As String, ByVal searchValue As String)
		Dim daMyName As New SqlDataAdapter
		Dim dsMyName As New DataSet
		Dim mySelectQuery As String = "Select customer_id, customer_bus_name, customer_contact1_first_name, customer_contact1_last_name, customer_contact1_phone, customer_contact2_first_name, customer_contact2_last_name, customer_contact2_phone, customer_address, customer_city, customer_state, customer_zip, customer_fax, customer_taxid, customer_discount_rate From customers Where ( customer_bus_name LIKE '" & searchValue & "' );"
		Dim myConnection As New SqlConnection(myConnString)
		Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
		myConnection.Open()
		daMyName.SelectCommand = myCommand
		daMyName.Fill(dsMyName)

		customerIdTextBox.Text = dsMyName.Tables(0).Rows(0).Item("customer_id").ToString
		businessNameTextBox.Text = dsMyName.Tables(0).Rows(0).Item("customer_bus_name").ToString
		contact1FirstNameTextBox.Text = ""
		contact1LastNameTextBox.Text = ""
		contact1PhoneTextBox.Text = ""
		contact2FirstNameTextBox.Text = ""
		contact2LastNameTextBox.Text = ""
		contact2PhoneTextBox.Text = ""
		addressTextBox.Text = ""
		cityTextBox.Text = ""
		stateTextBox.Text = ""
		zipCodeTextBox.Text = ""
		faxTextBox.Text = ""
		taxIdTextBox.Text = ""
		discountRateTextBox.Text = ""
		myConnection.Close()
	End Sub


Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 03:00 PM

Well thats right if you're returning more than a single row. The way you asked the question it sounded as if you're were returning a single row and wanting to put that information into textboxes. If you're just returning a single row theres no need for a loop.
Was This Post Helpful? 0
  • +
  • -

#7 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 03:04 PM

That's what I thought, but the code isn't working... I get this error...

There is no row at position 0.
Was This Post Helpful? 0
  • +
  • -

#8 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 03:15 PM

Then you have no returned rows, so you need to check for that before trying to populate your textboxes

If Not dsMyName.Rows.Count > 0 then
...let them know no data was returned
Else
....populate your textboxes
End If



That will do it, as long as your code is right. If you want [post it so I cal look through it
Was This Post Helpful? 0
  • +
  • -

#9 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 03:24 PM

I'm sorry, you're right, I was querying only the first part of the name that I knew was in the db... I need to change my query a bit ( customer_bus_name LIKE '" & searchValue & "' ) to include + '%'

It's all good now, but since there could be multiple selections from querying a name with LIKE, I'll have to create that forementioned loop and a type of thingy bobber to scroll through them...

oh, BTW, this code doesn't work, it says that Rows is not a part of the system.data.dataset

If Not dsMyName.Rows.Count > 0 then
...let them know no data was returned
Else
....populate your textboxes
End If



Thanks again PsychoCoder ! You Da Man.

This post has been edited by webwired: 27 August 2007 - 03:45 PM

Was This Post Helpful? 0
  • +
  • -

#10 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 04:26 PM

You're right (my bad) I forgot the Table part

dsMyName.Tables(0).Rows.Count


Was This Post Helpful? 0
  • +
  • -

#11 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 04:42 PM

View Postwebwired, on 27 Aug, 2007 - 03:24 PM, said:

It's all good now, but since there could be multiple selections from querying a name with LIKE, I'll have to create that forementioned loop and a type of thingy bobber to scroll through them...


For displaying multiple rows I suggest using a DataGridView in your Windows Application, then theres no need for a loop or anything.
Was This Post Helpful? 0
  • +
  • -

#12 webwired   User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Re: Displaying SQL results in textbox

Posted 27 August 2007 - 05:05 PM

I suppose you're right, although I've put so much time and effort into this to be table view, it would be better for multiple selections to be in a datagrid view instead... Oh well, what I've learned was worth it.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1