ASP.NET and accessing data in an SQL database...
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. :cry:
Re: ASP.NET and accessing data in an SQL database...
strSQLSearchString = "SELECT Admin, DateOfRequest, ForeNameOfUser, SurNameOfUser FROM LOG_Requests WHERE (ForeNameOfUser = '" & Forename & "') AND (SurNameOfUser = '" & Surname & "')"
Re: ASP.NET and accessing data in an SQL database...
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.
Code:
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.
Re: ASP.NET and accessing data in an SQL database...
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.
Re: ASP.NET and accessing data in an SQL database...
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!
Re: ASP.NET and accessing data in an SQL database...
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?
Re: ASP.NET and accessing data in an SQL database...
so for example (SELECT..."&txbforename.text&")
ok thanks i'll try that.
SC
Re: ASP.NET and accessing data in an SQL database...
here is my updated code:
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
Re: ASP.NET and accessing data in an SQL database...
Re: ASP.NET and accessing data in an SQL database...
ah after i compile i the code changes to this:
Code:
Me.SqlSelectCommand1.CommandText = "SELECT ID, Admin, DateOfRequest, ForeNameOfUser, SurNameOfUser FROM LOG_Requests3" & _
" WHERE (ForeNameOfUser = '') AND (SurNameOfUser = '')"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Re: ASP.NET and accessing data in an SQL database...
You should consider either handcoding, or setting parameters in the CommandText to their values in your update command. Familiar?
Re: ASP.NET and accessing data in an SQL database...
ok i'll give it a go, thanks for your help :)
SC
Re: ASP.NET and accessing data in an SQL database...
Ok i've found the commandtext for the update command the enourmous query. It looks like this:
Code:
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?
Re: ASP.NET and accessing data in an SQL database...
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.