|
Subject:
|
Function Question
|
|
Posted By:
|
stu9820
|
Post Date:
|
12/2/2003 4:35:07 PM
|
i have links that are returned and look like this:
test.aspx?loc=2&h=5
5 is the id
Is there any way to write a function that returns the value based on id and then use that value in another function?
|
|
Reply By:
|
planoie
|
Reply Date:
|
12/2/2003 4:41:48 PM
|
Public Function getHvalue() As Integer
Return CType(Request.QueryString("h"), Integer)
End Function
Might want to add some error handling to it but that's the basics.
|
|
Reply By:
|
stu9820
|
Reply Date:
|
12/2/2003 4:50:19 PM
|
Can that be then used to query the database based on id to return the value associated with that id?
|
|
Reply By:
|
cgoldrin
|
Reply Date:
|
12/2/2003 8:53:25 PM
|
You wouldn't even need the function if you just want to query a database based on the value of h in the querystring.
Dim conn As New SqlConnection (<connection string>)
Dim cmd As SqlCommand
Dim dtr As SqlDataReader
cmd = new SqlCommand ("SELECT * FROM TABLE WHERE ID = @ID", conn)
cmd.Parameters.Add ("@ID", Request.QueryString ("h"))
conn.Open ()
dtr = cmd.ExecuteReader ()
dtr.Read ()
dtr.Close ()
conn.Close ()
|
|
Reply By:
|
jacob
|
Reply Date:
|
12/3/2003 2:44:56 AM
|
Well, in general I think you have to bit careful with the use of query string variables directly in SQL queries, what if I did something like this...test.aspx?loc=2&h=;drop%20database; Or had other bad intensions! The point being... think it through, and format the input from the query string so that nothing can go wrong.
Jacob.
|
|
Reply By:
|
stu9820
|
Reply Date:
|
12/3/2003 8:59:58 AM
|
Are there any good books that deal with functions or web sites with tutorials?
|
|
Reply By:
|
stu9820
|
Reply Date:
|
12/4/2003 9:15:12 AM
|
I've written this:
Public Function getHvalue() As Integer
Return CType(Request.QueryString("h"), Integer)
End Function
Public Function getTdata()
Dim strConn As String = ConfigurationSettings.AppSettings("mytabledb")
Dim objConn As New OleDbConnection(strConn)
Dim strSQL As String = "SELECT * FROM Table3 WHERE (id=" & getHvalue() & ");"
End Function
I'm unsure how to return the value in the second Function. Can this work?
|
|
Reply By:
|
planoie
|
Reply Date:
|
12/4/2003 9:38:39 AM
|
Typically, when you want to return some kind of data structure from a function, you need to use a dataset and return it or a data table from it. If you try to use a datareader, it'll break, because the datareader is only filled with data as long as the connection is open. Seeing as you should close the connection before you exit the function, you'll loose the state of the reader. Here's what I'd do:
Public Function getTdata() As DataTable
Dim strConn As String = ConfigurationSettings.AppSettings("mytabledb")
Dim objConn As New OleDbConnection(strConn)
Dim strSQL As String = "SELECT * FROM Table3 WHERE (id=" & getHvalue() & ");"
Dim objCommand As New OleDbCommand(strSQL, objConn)
Dim objDataSet As New DataSet
objConn.Open
objCommand.Fill(objDataSet,"Table3")
objConn.Close
Return objDataSet.Tables(0)
End Function
I'm sure I'm missing something, but this is the general idea.
Peter
------------------------------------------------------
Work smarter, not harder.
|
|
Reply By:
|
stu9820
|
Reply Date:
|
12/4/2003 11:13:42 AM
|
Here is how I did it with classic asp 3.0 and I tried to use it with asp.net but I'm getting a weird response.
----------------------
Dim objConn
objConn = Server.CreateObject("ADODB.Connection")
objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/data/mytable.mdb")
Dim rS
Dim strSQL = "SELECT * FROM table3 WHERE id=" & Request.QueryString("h") & ""
rS = Server.CreateObject("ADODB.Recordset")
rS.Open(strSQL, objConn)
Response.Write(rS("prod_name"))
-------------------------------
Here is what's being returned: System.__ComObject
I have aspcompat="true" in the page directive. Can this be converted to asp.net?
|
|
Reply By:
|
planoie
|
Reply Date:
|
12/4/2003 11:29:36 AM
|
What's your goal here? Do you want return a single value from a query? If that's the case, use the "ExecuteScalar" method to return a single value. You'll also need to change the query to only select the one field you are interested in.
How about this?
Public Function getTdata() As String
Dim strConn As String = ConfigurationSettings.AppSettings("mytabledb")
Dim objConn As New OleDbConnection(strConn)
Dim strSQL As String = "SELECT prod_name FROM Table3 WHERE (id=" & getHvalue() & ");"
Dim objCommand As New OleDbCommand(strSQL, objConn)
Dim sResult As String
objConn.Open
sResult = CType(objCommand.ExecuteScalar(), String)
objConn.Close
Return sResult
End Function
Again, this suggestion lacks error handling and actual testing so you will need to tweak it.
Peter
------------------------------------------------------
Work smarter, not harder.
|
