Retrieve the result of a SELECT (VB.net)

I thought you were developing in ASP.NET... that's why I talked to you about the URL... my mistake:

so you're in VB.NET: to put a variable in the SQL query attached to your datasource, you need to put an @ in front of the field name: select toto,titi,tata from machin where nom = @mavariable

automatically VS will recognize the variable and will ask you what it should be equal to:

you will tell it that it's a control, for example textbox1, and that the property you're interested in is ".text"

every time it refreshes, it will replace @mavariable with the content of textbox1.

that should solve your problem....

your schema problem is that you're creating a control from a certain query, and then you're changing it without modifying the control. with the trick of @mavariable you won't have that issue anymore because the schema of the query won't change.

LPG

You can place a GridView control on the same page and toggle its visibility when needed. This will create a very nice, dynamic, sortable table, etc. (to be configured in the graphical interface as usual).

But if you really want to navigate to another page, carry the parameter from the textbox with you:
the simplest way is to put it in the URL of the destination page, and you can retrieve it after opening the page:

- in your starting page:
page.redirect ("~/toto.aspx&?nom=" & textbox1.text)
(the "?" is used to indicate a parameter in a URL: it is followed by the variable name = value)

then upon arrival, in your toto.aspx page, you can retrieve this parameter:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
dim MyNom
MyNom = Request.QueryString("nom")
etc...

and then you make your request and populate a GridView or a DataGrid or any data control.
(it's still easier than building a table from scratch).

keep me posted...

LPG

Well, let's keep it super simple:

instead of dealing with datasets, data adapters, SQL sources, and other databindings in VS, we'll do everything manually:

just put a combo box and a data grid on your form, that's it!

And in the code, you put this: (you'll obviously change the queries, server names, database names, etc...)

In this example, I'm querying a table "magasins" based on the field "nom" in the "commandes" database on server VLPG01\SQLexpress.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Public i As Integer
Public sqlSelect As String
Public ConnSQL As New SqlConnection()
Public ReadSQL As SqlDataReader
Dim ev As System.EventArgs
Dim susername As String

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

'filling the combo box

ConnSQL.ConnectionString = "Data Source=vlpg01\sqlexpress;Initial Catalog=commandes;Integrated Security=SSPI"
ConnSQL.Open()

sqlSelect = "SELECT [mag_nom] FROM magasins"
Dim CommandSQL As New SqlCommand(sqlSelect, ConnSQL)
ReadSQL = CommandSQL.ExecuteReader()
Do While ReadSQL.Read()
ComboBox1.Items.Add(ReadSQL.GetValue(0))
Loop

ReadSQL.Close()
ConnSQL.Close()
End Sub

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

'selection in the combo

ConnSQL.ConnectionString = "Data Source=vlpg01\sqlexpress;Initial Catalog=commandes;Integrated Security=SSPI"
ConnSQL.Open()

sqlSelect = "SELECT * FROM magasins where mag_nom='" & ComboBox1.SelectedItem & "'"
Dim dt As New DataTable

Dim oSqlDataAdapter As New SqlDataAdapter(sqlSelect, ConnSQL)
oSqlDataAdapter.Fill(dt)

DataGridView1.AutoGenerateColumns = True
DataGridView1.DataSource = dt
DataGridView1.Refresh()
ReadSQL.Close()
ConnSQL.Close()

End Sub

End Class

you can adapt all this to your own controls.

have fun!

lpg

cnx.open() com=new sqlcommand("select...",cnx) TextBox1.Text = com.ExecuteScalar() cnx.close()

Thank you very much, LPG :)
But apart from that!
I still have problems modifying the base query in the datagrid view
I read that there are steps to follow in order to modify this query
I need to create a table adapter and then a dataset and a bunch of things I don't understand :s
I don't even know the steps!
And I found that in a course on ADO.NET from an older version of VB
Whereas I'm using Visual Studio 2005!
So what do you think about that!?

yes, it's true that all these data things are a bit messy:

I'm used to dotnet and it's much easier than in Windows Form

I'll try to do your thing and get back to you.

Hello
thank you very much LPG
you really helped me a lot !!
here's the code now it works well !

 Public Class Form1 Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click LoadData() End Sub Private Sub LoadData() Dim connectString As String = "Server=ALI\SQLEXPRESS;Database=TWO;Trusted_Connection=True;" Dim connection As New System.Data.SqlClient.SqlConnection(connectString) Dim command As New System.Data.SqlClient.SqlCommand("SELECT * FROM assia where testoo='" & TextBox1.Text & "'", connection) Dim dt As New DataTable Dim adpt As New Data.SqlClient.SqlDataAdapter(command) Try connection.Open() adpt.Fill(dt) DataGridView1.DataSource = dt Finally connection.Close() End Try End Sub End Class 

PS : thank you to you too tomlev :)

Thank you very much.

And if we want to put the results in a table !!
How to do it?!
For example, in a first form we enter the name and then click on the button to display all the existing data in a table "person" relating to the person whose name we just typed in!!
I just want the steps and what I need to use :)
Thank you very much!

Well, it's not complicated, my God!

On your form, put a text box "textbox1" and any button.
Add as many textboxes as you have fields in your table (textbox2, 3, etc...)

Double click on the button and you’ll enter the code. There, you put

(I'm assuming you've declared everything beforehand and included the necessary imports)

connsql.open()
sqlselect = select name, surname, age, height, color, gender, IQ from mytable where name='" & textbox1.text & '"
Dim CommandSQL As New SqlCommand(sqlSelect, ConnSQL)
ReadSQL = CommandSQL.ExecuteReader()
ReadSQL.Read()

textbox2.text = ReadSQL.GetValue(0)
textbox3.text= ReadSQL.GetValue(1)
textbox4.text = ReadSQL.GetValue(2)
... etc....

readsql.close
connsql.close
connsql.dispose

And that's it!

Note: you'll wrap all that in cotton candy to make it look nice (labels in front of the textboxes and all that jazz).

Good luck!

LPG

Thank you very much, LPG
but that’s not what I'm looking for!
Actually, I want the results to be displayed in a table!
Let me explain
first, you enter the name in a textbox and click the button
you go to another form where the results of the select concerning that name typed in form1 are displayed
you see!
And also, I want those results displayed in a table, not in a textbox!

So what I need to do is make the query in the code of the button of the first form
then keep it somewhere! :S "that’s where I get stuck"
then display it in a table! I'm struggling with that too :(

Hello
thank you very much LPG ^^'
so I'm telling you what I've done:
I managed to keep the value of the variable from form1 to display it again in form2 in a textbox!
you just need to do

Me.TextBox1.Text = Form1.TextBox1.Text

so now I have the variable that I will use in the select query of the datagrid view!
here comes the problem again!
when I create a datagrid view, I can't change the default query!
let me explain:
I click on the triangle of the datagrid view
then I choose the data source
I go through the steps until I have the table I want and then this table displays entirely
I click again to add a query
and I add it and execute it
but once I click ok to see the result in this dataview grid!
it gives me "the schema returned by the new query is different from that of the default query"
and there I get stuck again!!
and I always had this problem with managing datagrid views!
either I do the select of the entire table and it works
if I want to modify the query, I always go through views that I create in my sql server and retrieve them in the datagrid view
but now I can't use it because the variable needs to be entered in the form itself!

Hello!
Here I am again!!
I am really unhappy.. I spent 2 days and it doesn't work anymore :(

Here is my code:

Public Class Form1 Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connectString As String = "Server=ALI-582A65A96CB\SQLEXPRESS;Database=ONE;Trusted_Connection=True;" Dim connection As New System.Data.SqlClient.SqlConnection(connectString) connection.Open() Dim command As New System.Data.SqlClient.SqlCommand("SELECT * FROM assia ", connection) Dim mook As DataTable = New DataTable Dim hana As New Data.SqlClient.SqlDataAdapter(command) hana.Fill(mook) // here it blocks during debugging it tells me: the value cannot be null DataGridView1.ColumnHeadersVisible = True // here there are no autogeneratecolumns DataGridView1.DataSource = mook DataGridView1.Refresh() connection.Close() End Sub

this visual studio 2005 did not accept a lot of words from your code I don't know.. it's new maybe!!
well I did my best to be able to generate this manually!
but it doesn't work!
and also the query no longer accepts the condition with such syntax! :(

 Dim command As New System.Data.SqlClient.SqlCommand("SELECT * FROM assia where testoo='" & textBox1.SelectedItem & "' ", connection)

Hello Lorie, don't worry, we all have the same issues at the beginning.

I wrote this code in VS2008, that's why you see some differences.

When you fill the datatable from the sqladapter (hana.Fill(mook)), it finds fields in your table with the value NULL and doesn't know what to do with it. To work around this problem, instead of taking all the fields without any checks, we will use a more "intelligent" query:

Instead of "SELECT * FROM assia", you will write:

"SELECT isnull(field1, '0'), isnull(field2, '0'), isnull(field3, '0'), etc... FROM assia"

where field1, field2, etc... are the fields of your table (those you want to display in the datagrid).

The isnull(field1, '0') means: return the value of field1, and if it's null, put '0' in its place!

In general, we put '0' or 0 without quotes for numeric fields and 'None' or 'empty' for text fields.

Then your error on the
Dim command As New System.Data.SqlClient.SqlCommand("SELECT * FROM assia where testoo='" & textBox1.SelectedItem & "' ", connection)

comes from the selecteditem: you need to specify selecteditem.text...

Dim command As New System.Data.SqlClient.SqlCommand("SELECT * FROM assia where testoo='" & textBox1.SelectedItem.text & "' ", connection)

As for autogeneratecolumns, I assume it didn't exist in 2005 (asp2), but it does in VS2008 (asp3.5).
Let me know if that causes you any trouble (it shouldn’t be very useful anyway).

Have a great day!

The result cannot be negative, you can put all your information in an Excel file so that the devils can't bother you :D