I'm just starting to learn asp.net. What I have is a registration form
for a message board system. Basically, the user enters in their info
and it's validated. So I have a custom validator check to make sure the
username is unique, I have another custom validator that makes sure the
email address is unique and then I save the info to the database. That
means I open the connection to the sqlserver 3 times.
Seems inefficient to me. Is there a better way?
Sub Button1_Click(sender As Object, e As EventArgs)
if me.isvalid then
Dim connectionString As String = "server='netsdk';
trusted_connection=true; database='db'"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(connectionStri ng)
Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.Connection = dbConnection
Dim uname as string
Dim password as string
Dim em as string
Dim ip as string
Dim rightnow as date
ip = Request.UserHostAddress()
rightnow = now
uname = username.text
password = password1.text
em = email.text
dbCommand.CommandType = System.Data.CommandType.StoredProcedure
dbCommand.CommandText = "sp_register"
Dim dbParam_username As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_username.ParameterName = "@username"
dbParam_username.Value = uname
dbParam_username.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_username)
Dim dbParam_password As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_password.ParameterName = "@password"
dbParam_password.Value = password
dbParam_password.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_password)
Dim dbParam_email As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@email"
dbParam_email.Value = em
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
Dim dbParam_ip As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_ip.ParameterName = "@ip"
dbParam_ip.Value = ip
dbParam_ip.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_ip)
Dim dbParam_last_ip As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_last_ip.ParameterName = "@last_ip"
dbParam_last_ip.Value = ip
dbParam_last_ip.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_last_ip)
Dim dbParam_created As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_created.ParameterName = "@created"
dbParam_created.Value = rightnow
dbParam_created.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_created)
Dim dbParam_last As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_last.ParameterName = "@last"
dbParam_last.Value = rightnow
dbParam_last.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_last)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
'Server.Transfer("index.aspx")
end if
End Sub
Sub CustomValidator_Username( s As Object, e As ServerValidateEventArgs
)
Dim connectionString As String = "server='netsdk';
trusted_connection=true; database='db'"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(connectionStri ng)
Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.Connection = dbConnection
dbCommand.CommandType = System.Data.CommandType.StoredProcedure
dbCommand.CommandText = "sp_username"
Dim dbParam_username As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_username.ParameterName = "@username"
dbParam_username.Value = trim(e.value)
dbParam_username.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_username)
dbConnection.Open
Dim cnt as integer
cnt = dbCommand.ExecuteScalar
If cnt = 0 Then
e.IsValid = True
Else
e.IsValid = False
End If
End Sub
Sub CustomValidator_Email( s As Object, e As ServerValidateEventArgs )
Dim connectionString As String = "server='netsdk';
trusted_connection=true; database='db'"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(connectionStri ng)
Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.Connection = dbConnection
dbCommand.CommandType = System.Data.CommandType.StoredProcedure
dbCommand.CommandText = "sp_email"
Dim dbParam_email As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@email"
dbParam_email.Value = trim(email.text)
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
dbConnection.Open
Dim cnt as integer
cnt = dbCommand.ExecuteScalar
If cnt = 0 Then
e.IsValid = True
Else
e.IsValid = False
End If
End Sub
--
..
http://sf-f.org, weblog and search engine for fans and writers of
speculative fiction.
..
