GridView Troubles

Hi, I’m having trouble with my grid view. When the user is signed into my site and clicks button1, the users name is turned into a string and used in a query to display all the information for that user, but unfortunately it doesn’t work. It should output all the CD’s the user has input into the database. Here’s the code I have…


Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myString As String
myString = Page.User.Identity.Name.ToString()

Dim DBConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\aspnetdb.mdf"";Integrated Security=True;User Instance=True")

Dim DBCmd As New SqlCommand
Dim DBAdap As New SqlDataAdapter
Dim DS As New DataSet
Dim dt As New DataTable
DBConn.Open()

DBAdap = New SqlDataAdapter("SELECT * FROM Table1 WHERE UserName = " & myString, DBConn)

DBAdap.Fill(dt)

GridView1.DataSource = dt
GridView1.DataBind()

DBCmd.Dispose()
DBAdap.Dispose()
DBConn.Close()
DBConn = Nothing
End Sub


When I run it this error comes up “SQLExeption was unhandled by usercode” and it also says “Invalid column name 'Rachel'.”. Rachel is actually the user I’m signed into the site and is the value in myString which is fine, but I don’t seem to see what the problem is. Could it be that UserName is set to NVARCHAR in the sql database? Can someone help me out, I’d really like to get this working. Any advice or help would be great.

Thanks,
CaJack

A few things

1) In ADO.net the DataAdapter object handles the opening and closing of the connection object. You simply have to pass the instantiated instance of the connection, you don't have to call .Open and .Close() the Adapter handles this in the .Fill() method. HOWEVER, if you are doing multiple fills or multiple queries some will say that opening the connection will provide some performance gain. But in this case, and most standard cases you don't need to monitor the open and close events of the connection with the DataAdapter

2) Put []'s around UserName, so no system or reserved words are assumed [UserName]

3) If user name is a string field you need to concat the variable with single quotes so it reads into the query declaration as the string value for the WHERE clause

4) Create a stored procedure and simply pass the value as a parameter so you aren't messing with dynamic string formatting and potential sql injection. Just call the stored procedure from your ASP.net application.