Newbie question

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 )then I would be very interested to hear of it.

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.