SQL query get data into textbox.text after clicking a button-CodePudding

so i have a textbox1.Text

and a button

how can I get the sql ID data to textbox1.text i am trying to get a string value from sql. in MSSQL query this query works so well

select thisID FROM thisTable where ThisName = 'Name'

but in vbNet single qoutes are considered as a comment

textbox1.text = "SELECT thisID from thisTable where ThisName= '" & 'Name' & "'"

i just want the itemID from sql to appear in the textbox. help. thank you.

CodePudding user response：

i just want the itemID from sql to appear in the textbox. help. thank you.

Right.. Well.. It's nothing like that! Would you expect your program to load a file into a textbox if you set the text in the box to "c:\temp\my.txt" ?

Or do you think the textbox would just show C:\temp\my.txt ?

You have to connect to the db, download the data and put the data into the textbox

Let's start off making your life a bit easier:

use the nuget package manager (google for how to open it in your version of VS) to install Dapper
put a code like this:

    Dim c as New SqlConnection("your db connection string here")
    textbox1.text = c.ExecuteScalar(Of String)( _
      "SELECT thisID from thisTable where ThisName=@pName", _
      New With { .pName = "some name here" } _
    )

_{I guessed SqlConnection, but if your db is not SQLServer, use the appropriate connection type}

CodePudding user response：

First, are you sure ThisName is a unique value? Does this field have a UNIQUE constraint? If not you may not get the expected results from this query.

It is a good idea to separate you user interface code from your database code.

I am using ADO.net and the MS Sql Server provider.

Imports System.Data.SqlClient

To accomplish want you want, you will need a connection to the database and a command to tell the database what to do. Pass your connection string to the constructor of the connection and pass the CommandText and Connecion properties to the constructor of the command.

Database objects, like connections and commands, need to be disposed. We have Using...End Using blocks to accomplish this.

Open the connection and retrieve the data with ExecuteScalar. ExecuteScalar returns an Object which is the first column of the first row of the result set.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    TextBox1.Text = GetIDByName(txtName.Text)
End Sub

Private Function GetIDByName(name As String) As String
    Dim ID As String = ""
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand("select thisID FROM thisTable where ThisName = @Name", cn)
        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = name
        cn.Open()
        ID = cmd.ExecuteScalar().ToString
    End Using
    Return ID
End Function

EDIT

Since we are now requesting more than one field, change the return type of the function to DataTable. A DataTable is an in memory representation of the rows and columns requested.

The CommandText passed to the constructor of the command now includes both fields.

I should have shown you how to used parameters in my first code sample. This is very important because it will help you avoid sql injection - a great danger to your database. Note: I have correct the first code snippet. @Name is the name of the parameter and it is added to the parameters collection. Sql Sever will substitute the @Name in the CommandText with the value of the parameter.

I am using a DataReader to retrieve the data and load it into a DataTable. SqlCommand.ExecuteReader returns a DatarReader.

In the button code I have reference the first row with index 0 and the first field we requested with index 0 to fill the first text box. The index of the field changes to 1 for the second text box because it is the second field.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt = GetDataByName(txtName.Text)
    TextBox1.Text = dt.Rows(0)(0).ToString
    TextBox2.Text = dt.Rows(0)(1).ToString
End Sub

Private Function GetDataByName(name As String) As DataTable
    Dim dt As New DataTable
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand("select thisID, ItemName FROM thisTable where ThisName = @Name", cn)
        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = name
        cn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function