0

I need to gather information from an SQL query. I am using a SQL Data Source control because I will be using it in a grid view after I get the data.

My query looks like this: [wrapped for readability]

SqlDataSource1.SelectCommand = "SELECT [index] AS idex, store_number AS snum, 
    store_name AS sname, store_username AS suser, store_password AS spass, 
    store_count AS scount 
    FROM Stores 
    WHERE store_name = '" & Session("storename") & "'"

Very sloppy, but hopefully will work for what I need. The little I understand about variables should mean that the field of index should be stored to a variable named idex? is this correct? How do I use it later?

How do I take a variable from the column and put it in something like a text box,

Improve this question
5
  • This will almost certainly be vulnerable to sql injection attacks. Commented Nov 6, 2013 at 2:31
  • Also, which is it: mysql, or sql server? Those are two very different database flavors. Commented Nov 6, 2013 at 2:32
  • Finally: "should mean that the field of index should be stored to a variable named idex? is this correct?" That is absolutely not correct. To help you more, it would nice to see more of your code. Commented Nov 6, 2013 at 2:33
  • Mis-clicked on MySQL. injection I am not so worried about at the moment. I have two pages, 1st it grabs the name of the store from user input (listbox). It will then redirect you to this page. Change the SQLDataSource1.Select command to select the information only relevant to the store selected from the previous page. lets take the field index for example in the database. I would like to put index into a variable that I can use later on. Example: index in the database is 10. I want to then put that 10 into an Integer variable, I don't know how to put index into a variable. Commented Nov 6, 2013 at 2:43
  • I generally used to use a SQLDataReader in a while loop (While SQLDataReader.Read). Recently I've started using CSLA.NET and found it very useful; populate your gridview with child objects in a parent list, and give each child object its own DataFetch method. Commented Nov 7, 2013 at 14:44

1 Answer 1

Reset to default
2

The basic structure of the code is below.

Open the connection. Best to do using Using structure. Create the command (again, Using structure). Execute the command and get the value.

Dim idx As Integer ' the variable to hold your index

Using conn As SqlConnection = New SqlConnection("your connection string") ' put your connection string here or get it from a config file
    conn.Open()
    Dim commandText As String = "SELECT [index] AS idex, store_number AS snum, store_name AS sname, store_username AS suser, store_password AS spass, store_count AS scount FROM Stores WHERE store_name = @storename"
    Using command As SqlCommand = New SqlCommand(commandText, conn)
        command.Parameters.Add(New SqlParameter("@storename", SqlDbType.VarChar, 50)).Value = "store name" ' replace the store name and the length of the field

        Using reader As SqlDataReader = command.ExecuteReader
            If reader.Read Then
                idx = reader.GetInt32(0) ' the first column
            End If
        End Using
    End Using
End Using

To get the connection string from your config file, do the following:

Add the reference to System.Configuration.dll

Add the connection string to your config file:

<connectionStrings>
    <add name="YourConnection" connectionString="Details"/>
</connectionStrings>

You can get your connection string from the code

 Dim connStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("YourConnection").ConnectionString
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you for the answer, I will try this. Is there a way I can do this with less code? I thought there was a way to grab them from the SQLDataConnection.
No, you can't use connection for that. That's not that much code anyway.
what would I use to pull my connection string from the config file instead of copy pasting it
Added more to my answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.