Good morning,
I am quite new to using ASP and SQL together, I have tried all the tutorials I can find and most of it makes enough sense that I am able to bumble my way through what I am trying to do, but I am not able to find any reliable information on how to do the following:
I want to call a SQL stored proc, by pressing a "submit" button. It is for a simple login... the user types in their username and password, then I get the data from the fields with Request.Form.Get("blah")s. The stored proc returns a true or false to indicate success or failure.
The SQL server that I am trying to use is called 'Trim2005' and the database is called 'Internet Database'
Since I'm learning, if anyone has a better way which is not too much harder than what I am trying to do (or easier, if I'm lucky![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_tyQcEnd4v2T1NEof7eJusxneCbczMh5BHpLGXUXGJrGlXDAMDxU8zim9mCm5NHcurBirxZcKN9HwStpJ2aeHlkJ-XiimXLqnIS8U2IQYjy8kKje_ENQQ=s0-d)
Set cmdupdate=Server.CreateObject("Adodb.Command")
cmdupdate.ActiveConnection=conn
cmdUpdate.CommandText="Trim2005"
cmdUpdate.CommandType=adCmdStoredProc
Then Execute the above
'Conn' is the connection String
Regards,
Gautham
|||Data Source=TRIM2005;Initial Catalog="Internet Database";Integrated Security=True
This is the connection string, right? I got it by creating a dropdown list and then copying and pasting the connection string during the 'select data source' wizard. If so, would I surround the whole thing in brackets or quotations, or neither? In addition, every time I try to type "set cmdupdate" it removes the word set and then complains that the variable has not been defined. Would I resolve this by adding 'Dim cmdupdate'?
How would I pass in the login and password? I am storing them as session vars Session("Username") and Session("Password")
Thanks,
|||Normally, you'd do it like this:
strConn="Data Source=TRIM2005;Initial Catalog=Internet Database;Integrated Security=True"
surrounding it all with double quotes.
(of course, the strconn part is not necessary - it just shows how to refer to the connection string in general)
|||Are you using ASP or ASP.NET? Sounds like ASP.NET. If that's the case, I wouldn't use ADO. Use the native SqlConnection and SqlCommand instead.|||I am using ASP.NET (blah.aspx) I believe.
So then how would I use the SqlConnection + SqlCommand instead of ADO?
Sorry to ask so many questions, but the tutorials I have been able to find are only slightly helpful as I do not actually know VBscripting.. sorta learning that as I go along. I know a smattering of JS, but all of my programming experience is in C/C++ and Java.
I realized after posting my questions that I should have chose a more relevant subject too, but too late for that :/
|||dim conn as new SqlConnection("{Your connect string here"})
conn.open
dim cmd as new SqlCommand("{Your stored procedure name here}",conn)
cmd.CommandType=StoredProcedure ' Sorry, this isn't 100% correct, the property and/or value may actually be different
cmd.parameters.add("@.{Your parameter name here}",SqlDbType.{Your data type here - probably VarChar or Int32}).Value={Your parameter value}
.. repeat for each parameter .. (Loginname,password?)
cmd.parameters.add("@.RETURN_VALUE",SqlDbType.Int32).Direction=returnvalue
cmd.executenonquery
dim result as integer=cmd.parameters("@.RETURN_VALUE").Value
conn.close
That assumes that when you said the stored procedure returns a true/false, you meant as a return value rather than an output parameter. An output parameter would be similiar, except you'd use these lines instead of the return_value stuff:
cmd.parameters.add("@.{output parameter name}",SqlDbType.boolean).Direction=output
dim result as integer=cmd.parameters("@.{output parameter name}").Value
|||My code is at the bottom, I just wanted to post it so that someone could make sure I'm not typing something wrong. I know that my sub doesn't do anything yet, I just wanted to test out whether or not it would work and reply back asap. I get the following errors:
It does not recognize SqlDbType or "as new SqlConnection" nor does it recognize "ReturnValue" where I add my return parameter. I suspect that the reason for these is that I need to import a header?
Sorry for how long it takes me to reply, a mod needs to clear my post before I can reply to anything. I would like to thank everyone for their help so far, it has all been a learning experiance and once I know how to do this the first time I'll not need to ask again. I've a long way to go, but I look forward to learning how to do everything.
ProtectedSub loginButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)
Dim connAsNew SqlConnection("Data Source=TRIM2005;Initial Catalog=Internet Database;Integrated Security=True")
conn.open()
Dim cmdAsNew SqlCommand("Login")
cmd.parameters.add("@.Username",SqlDbType.VarChar).Value = usernameTextbox.Text
cmd.parameters.add("@.Password",SqlDbType.VarChar).Value = passwordTextbox.Text
cmd.parameters.add("@.RETURN_VALUE",SqlDbType.VarChar).Direction =ReturnValue
cmd.executenonquery()
Dim resultAsInteger = cmd.parameters("@.RETURN_VALUE").Value
conn.close()
EndSub
|||imports system.data
imports system.data.sqlclient
at the top of the page.
The second parameter to your new SqlCommand, should be conn:
Dim cmd as New SqlCommand("Login",conn)
and you aren't setting the commandtype to storedprocedure yet, sorry I don't know the exact property off hand, and I suspect you are putting this off until you have the correct imports so that you can use intellisense to find the right one.
|||in order to import I just type those at the top of the page? I tried adding those two imports to the very top of the page as they are written in your post, and I have not gotten any intellisense on anything.
It recommends that I change the SqlCommand and SqlConnection to Data.SqlClient.SqlCommand and Data.SqlClient.SqlConnection. I figure that since those look pretty much identical to the imports you suggest, that might work.
I am, however, still having trouble with the datatype SqlDbType... is this supposed to be something user-defined, or is there a data type in one of these imports which seems to not be importing? I will google importing when I get a chance, but I'm swamped at the moment.
Thanks,
|||<%@.importnamespace="system.data"%>
<%@.importnamespace="system.data.sqlclient"%>
...............
ProtectedSub loginButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)
Dim connAsNew Data.SqlClient.SqlConnection("Data Source=TRIM2005;Initial Catalog=Internet Database;Integrated Security=True")
conn.Open()
Dim cmdAsNew Data.SqlClient.SqlCommand("Login", conn)
cmd.Parameters.Add("@.Username", SqlDbType.VarChar).Value = usernameTextbox.Text
cmd.Parameters.Add("@.Password", SqlDbType.VarChar).Value = passwordTextbox.Text
cmd.Parameters.Add("@.RETURN_VALUE", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
Dim resultAsString = cmd.Parameters("@.RETURN_VALUE").Value
conn.Close()
Response.Write(result)
EndSub
Okay :) I have all but one bug worked out (or so I think) and it was mentioned above I believe. I get an error during runtime about cmd.ExecuteNonQuery(). It doesn't really say what it is, it just dumps out the call stack. "Incorrect syntax near 'Login'."is the exception detail.
What I believe the problem might be is that it is not sending a query as it is saying to ExecuteNonQuery and then is expecting a return value.. but I don't really know so I would be quite happy if someone could explain what I am missing.
I have decided instead to return the username of the successful login(a failure results in an empty string which I will later account for as a failed attempt)
|||
Obsidian Shadow wrote:
I get an error during runtime about cmd.ExecuteNonQuery(). It doesn't really say what it is, it just dumps out the call stack. "Incorrect syntax near 'Login'."is the exception detail.
I am pretty sure the problem is that you have not set your SqlCommand'sCommandType, which will therefore default to CommandType.Text. Add a line to set the CommandType to CommandType.StoredProcedure:
Dim cmdAsNew Data.SqlClient.SqlCommand("Login", conn)
cmd.CommandType = CommandType.StoredProcedure
The incorrect syntax is caused because you failed to set the commandtype property.
However, you can't return a varchar as a return_value. Return_value must be an int. Your SP should look something like this:
CREATE PROCEDURE Login
@.Username varchar(40)
@.Password varchar(40)
AS
RETURN (SELECT COUNT(*) FROM LoginTable WHEREUsername=@.Username andPassword=@.Password)
GO
And yes, doing the import basically just allows you to shorten what you type, for example:
Dim conn as new System.data.sqlclient.sqlconnection("...")
becomes
Dim conn as new SqlConnection("...")
if you have system.data.sqlclient imported.
|||Thank you everyone, I am now using the stored procedure.
Syntax error converting the varchar value 'ekisner' to a column of data type int.
I have checked my stored procedure, and I have checked my sub and I cannot find where it would be trying to convert to an int, so I am left with posting both here to see what you guys know that I don't. When I execute the query outside of the webpage, I get the same error, which leads me to believe that it is my procedure that is not working correctly.
My sub:
ProtectedSub loginButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)
Dim connAsNew Data.SqlClient.SqlConnection("Data Source=TRIM2005;Initial Catalog=Internet Database;Integrated Security=True")
conn.Open()
Dim cmdAsNew Data.SqlClient.SqlCommand("Login", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.Username", SqlDbType.VarChar).Value = usernameTextbox.Text
cmd.Parameters.Add("@.Password", SqlDbType.VarChar).Value = passwordTextbox.Text
cmd.Parameters.Add("@.RETURN_VALUE", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
Dim result = cmd.Parameters("@.RETURN_VALUE").Value
conn.Close()
Response.Write(result)
EndSub
My SP:
CREATE PROCEDURE dbo.Login
@.username varchar(50),
@.password varchar(50)
AS
RETURN(
SELECT Username
FROM Users
WHEREUsername=@.username ANDPassword=@.password
)
GO
Read my last post.
