Hi there,
Here we have got aasp.net application that was developed when database wassitting on SQL server 6.5. Now client has moved all of their databasesto SQL server 2000. When the database was on 6.5 the previousdevelopment team has used oledb connections all over. As the databaseshave been moved to SQL server 2000 now i am in process of changing thedatabase connection part. As part of the process i have a loginauthorization code.
PrivateFunction Authenticate(ByVal usernameAsString,ByVal passwordAsString,ByRef resultsAs NorisSetupLib.AuthorizationResult)AsBoolean
Dim connAs IDbConnection = GetConnection()
Try
Dim cmdAs IDbCommand = conn.CreateCommand()
Dim sqlAsString = "EDSConfirmUpdate"'"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@.logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@.password", password)
conn.Open()
'Get string for return values
Dim ReturnValueAsString = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values()AsString = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE"Then
ReturnTrue
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1Then
results.Message = Values(2)
EndIf
ReturnFalse
EndIf
Catch exAs Exception
Throw ex
Finally
If (Not connIsNothingAndAlso conn.State = ConnectionState.Open)Then
conn.Close()
EndIf
EndTry
EndFunction
''' ------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specificconnection string from the web.config file for the service, creates aconnection object and returns it as an IDbConnection.
''' </remarks>
''' ------------------------
PrivateFunction GetConnection()As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim connAs IDbConnection =New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
EndFunction
in the above GetConnection() method ihave commented out the .net dataprovider for oledb and changed it to.net dataprovider for SQLconnection. this function works fine. But inthe authenticate method above at the line
Dim ReturnValueAsString = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @.password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim connAs IDbConnection =New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<addkey="Common" value='User ID=**secret**;pwd=**secret**;DataSource="ESMALLDB2K";Initial Catalog=cj_common;AutoTranslate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'/>-->
<addkey="Common"value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;'/>
Please help. Thanks in advance.
what are the parameters your stored proc is defined with ?
NEVERgive out your userid/pwd in the connection string when pasting code over on a public forum.Mask your pwd.
|||
Thanks for the quick response Dinakar.
Here are the parameters the stored proc is expecting
*******************************
CREATE PROCEDURE EDSConfirmUpdate
@.logon char(10), @.curpswdclr varchar(255), @.newpswdclr varchar(255) = null
AS
***********************
But in the it is sending the parameter
NorisHelpers.DBHelpers.AddParam(cmd, "@.logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@.password", password)
obviously @.password is not a parameter in the sotred proc. But why it works when i use oledb... i am not sure about it.
Hope this will help you to understand further.
Thanks and i just pasted it like that. I won't do it again.
-D
|||well I dont know how it worked in OLEDB. But for SQL you need todeclare the same parameters that you used in the stored procedure.|||
yeah you are right. i couldn't figure out why it was working with oledb before with a wrong parameter. I have changed it to the right parameter now its working.
I have one more problem with the tree control that is defined. The client main concern is that they are not able to use their tree view control. before move it was rendering well but after the databases to 2000 its got messed up. I have changed the connections to SQL server and thought it would render automatically(off course there is not connection between rendering a treeveiw and the type of the provider thats being used ) but i thought some thing messed up with the move and the tree view was unable to find the data needed to build it. But no luck yet! i have posted it at
http://forums.asp.net/930668/ShowPost.aspx
Please take a look at it if you can think of any thing.
Thank you
-D
No comments:
Post a Comment