ASP.NET and accessing data in an SQL database...

[StudentCoder]

Hey,

I'm trying to create a simple user search fuction on a website. For example i have on the form:

Forname: Jon
Surname: Doe

Button: Search

So, i',m using an query to search the database and the connection is all good however i can seem to get the query to recognise the values in the textboxes?

here is the query

SELECT Admin, DateOfRequest, ForeNameOfUser, SurNameOfUser FROM LOG_Requests WHERE (ForeNameOfUser = '&Forename') AND (SurNameOfUser = '&Surname')

i have stored the textbox values in strings Forename and Surname

i know its some thing to do with the syntax "&Forename" but i can't find the answer anywhere.

[mendhak]

strSQLSearchString = "SELECT Admin, DateOfRequest, ForeNameOfUser, SurNameOfUser FROM LOG_Requests WHERE (ForeNameOfUser = '" & Forename & "') AND (SurNameOfUser = '" & Surname & "')"

[StudentCoder]

Yeah it doesn't like that, its because the forename and surname are supplied by the user. So i'm a bit stuck because there will be no text in the strings i.e they aren't initialised. i'll try an explain what i'm doing. I'm using two text boxes:

Forename:
Surname:

Search Button

Once the user clicks the button a query is run and the results are bound to a datagrid.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        forename = TextBox1.Text
        surname = TextBox2.Text
        Dim resultString As String

        SqlConnection1.Open()
        SqlDataAdapter1.Fill(DataSet1)
        DataGrid1.DataBind()
        SqlConnection1.Close()
    End Sub

So like all the processing is done in the button but the query which is declared in the "Designer Generated" code doesn't recognise the values in the text box.

[mendhak]

Designer? This means that you've been using the data wizards. Tsk tsk... I oughta spank you with the rotting corpse of a halibut!

Nevertheless, if that's the way you want to go (instead of coding it all by hand), show us what you have in the generated code.

[StudentCoder]

Oops i did hard code it beforehand :P
Well i've found the problem, the text box values are not being referenced for some reason it works when i set the variables before runtime. But not at run time.
like when i declare forename as a global variable it works. hmmm not rotten fish for me please!

[mendhak]

Yes, most likely because the SQL Query you have is looking for ForeName, but its scope is local to the subroutine in which you had declared it earlier. Perhaps you should consider directly plugging in the values in the textbox into your sql statement?

[StudentCoder]

so for example (SELECT..."&txbforename.text&")
ok thanks i'll try that.
SC

[StudentCoder]

here is my updated code:

'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT ID, Admin, DateOfRequest, ForeNameOfUser, SurNameOfUser FROM LOG_Requests3" & _
        " WHERE (ForeNameOfUser = '" & TextBox1.Text & "') AND (SurNameOfUser = '" & TextBox2.Text & "')"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1

[mendhak]

And... it works, right?

[StudentCoder]

ah after i compile i the code changes to this:

Me.SqlSelectCommand1.CommandText = "SELECT ID, Admin, DateOfRequest, ForeNameOfUser, SurNameOfUser FROM LOG_Requests3" & _
        " WHERE (ForeNameOfUser = '') AND (SurNameOfUser = '')"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1

[mendhak]

You should consider either handcoding, or setting parameters in the CommandText to their values in your update command. Familiar?

[StudentCoder]

ok i'll give it a go, thanks for your help
SC

[StudentCoder]

Ok i've found the commandtext for the update command the enourmous query. It looks like this:

UPDATE    LOG_Requests3
SET              ID = @ID, Admin = @Admin, DateOfRequest = @DateOfRequest, ForeNameOfUser = @ForeNameOfUser, SurNameOfUser = @SurNameOfUser
WHERE     (ID = @Original_ID) AND (Admin = @Original_Admin OR
                      @Original_Admin IS NULL AND Admin IS NULL) AND (DateOfRequest = @Original_DateOfRequest OR
                      @Original_DateOfRequest IS NULL AND DateOfRequest IS NULL) AND (ForeNameOfUser = @Original_ForeNameOfUser OR
                      @Original_ForeNameOfUser IS NULL AND ForeNameOfUser IS NULL) AND (SurNameOfUser = @Original_SurNameOfUser OR
                      @Original_SurNameOfUser IS NULL AND SurNameOfUser IS NULL);
                          SELECT     ID, Admin, DateOfRequest, ForeNameOfUser, SurNameOfUser
                           FROM         LOG_Requests3
                           WHERE     (ID = @ID)

Which parts of this do i edit?

[mendhak]

I think you'll have to add parameters to this particular command. Each of the @Something is a parameter in your query, so you'd need all the values for all the @ fields there.