V
voidfill3d
I have a problem with ASP.NET and entering data into a MS SQL database.
I have the following
code and what happens is the data gets into the database, but with one
extra space at the end of
the entry. Is this preventable with something other than a trim in my
stored procedure? I know this is not necessarily in ASP.NET because I
returned the value with quotes around it and it shows up right, but
when I look in the database, it is wrong. I think it is with the SQL
Server.
***************************************
ASP.NET code
***************************************
<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Function gstrConn As String
gstrConn = "Provider = SQLOLEDB;" & _
"Initial Catalog = main;" & _
"Data Source = mssql.somesite.com;" & _
"User Id = someuser;" & _
"PASSWORD = password"
End Function
Function SignUpUser(strUserName As String, strPassword As String) As
Boolean
SignUpUser = False
Dim booResult As Boolean = False
Dim intUserId As Integer = 0
Dim objConn As New OleDbConnection(gstrConn)
Dim objCmd As OleDbCommand
Dim objParam As OleDbParameter
Dim objReader As OleDbDataReader
Dim ds As DataSet = New DataSet()
objCmd = New OleDbCommand("signup_user", objConn)
objCmd.CommandType = CommandType.StoredProcedure
objParam = objCmd.Parameters.Add("@user_name", OleDbType.Char)
objParam.Direction = ParameterDirection.Input
objParam.Value = strUserName
objParam = objCmd.Parameters.Add("@password", OleDbType.Char)
objParam.Direction = ParameterDirection.Input
objParam.Value = strPassword
Try
objCmd.Connection.Open()
objReader = objCmd.ExecuteReader
Catch Ex As OleDbException
Response.Write("x")
objCmd.Connection.Close()
SignUpUser = False
Exit Function
End Try
While objReader.Read()
intUserId = objReader.GetInt32(0)
End While
objReader.Close()
objCmd.Connection.Close()
If intUserId > 0 Then
booResult = True
Else
booResult = False
End If
SignUpUser = booResult
End Function
Sub Page_Load(Sender As Object, E As EventArgs)
End Sub
Sub btnSignUp_Click(Sender As Object, E As EventArgs)
lbltesttext.text = chr(34) & tbtesttext.text & chr(34)
SignUpUser(tbtesttext.text, tbtesttext.text)
End Sub
Sub btnReset_click(Sender As Object, E As EventArgs)
lbltesttext.text = ""
End Sub
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD><TITLE></TITLE>
<META http-equiv=Content-Type content="text/html; charset=utf-8">
</HEAD>
<BODY>
<FORM runat="server">
<asp:label id="lbltesttext" runat="server"/>
<asp:textbox id="tbtesttext" runat="server"/>
<asp:button id="btntesttext" text="Test" onclick="btnSignUp_Click"
runat="server"/>
<asp:button id="btnReset" text="Reset" onclick="btnReset_click"
runat="server"/>
</FORM>
</BODY>
</HTML>
***************************************
MS SQL Stored Procedure
***************************************
CREATE PROCEDURE signup_user
@user_name VARCHAR(32),
@password VARCHAR(32)
AS
INSERT INTO forum_users
(forum_user_name, forum_user_password)
VALUES(@user_name, @password)
I have the following
code and what happens is the data gets into the database, but with one
extra space at the end of
the entry. Is this preventable with something other than a trim in my
stored procedure? I know this is not necessarily in ASP.NET because I
returned the value with quotes around it and it shows up right, but
when I look in the database, it is wrong. I think it is with the SQL
Server.
***************************************
ASP.NET code
***************************************
<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Function gstrConn As String
gstrConn = "Provider = SQLOLEDB;" & _
"Initial Catalog = main;" & _
"Data Source = mssql.somesite.com;" & _
"User Id = someuser;" & _
"PASSWORD = password"
End Function
Function SignUpUser(strUserName As String, strPassword As String) As
Boolean
SignUpUser = False
Dim booResult As Boolean = False
Dim intUserId As Integer = 0
Dim objConn As New OleDbConnection(gstrConn)
Dim objCmd As OleDbCommand
Dim objParam As OleDbParameter
Dim objReader As OleDbDataReader
Dim ds As DataSet = New DataSet()
objCmd = New OleDbCommand("signup_user", objConn)
objCmd.CommandType = CommandType.StoredProcedure
objParam = objCmd.Parameters.Add("@user_name", OleDbType.Char)
objParam.Direction = ParameterDirection.Input
objParam.Value = strUserName
objParam = objCmd.Parameters.Add("@password", OleDbType.Char)
objParam.Direction = ParameterDirection.Input
objParam.Value = strPassword
Try
objCmd.Connection.Open()
objReader = objCmd.ExecuteReader
Catch Ex As OleDbException
Response.Write("x")
objCmd.Connection.Close()
SignUpUser = False
Exit Function
End Try
While objReader.Read()
intUserId = objReader.GetInt32(0)
End While
objReader.Close()
objCmd.Connection.Close()
If intUserId > 0 Then
booResult = True
Else
booResult = False
End If
SignUpUser = booResult
End Function
Sub Page_Load(Sender As Object, E As EventArgs)
End Sub
Sub btnSignUp_Click(Sender As Object, E As EventArgs)
lbltesttext.text = chr(34) & tbtesttext.text & chr(34)
SignUpUser(tbtesttext.text, tbtesttext.text)
End Sub
Sub btnReset_click(Sender As Object, E As EventArgs)
lbltesttext.text = ""
End Sub
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD><TITLE></TITLE>
<META http-equiv=Content-Type content="text/html; charset=utf-8">
</HEAD>
<BODY>
<FORM runat="server">
<asp:label id="lbltesttext" runat="server"/>
<asp:textbox id="tbtesttext" runat="server"/>
<asp:button id="btntesttext" text="Test" onclick="btnSignUp_Click"
runat="server"/>
<asp:button id="btnReset" text="Reset" onclick="btnReset_click"
runat="server"/>
</FORM>
</BODY>
</HTML>
***************************************
MS SQL Stored Procedure
***************************************
CREATE PROCEDURE signup_user
@user_name VARCHAR(32),
@password VARCHAR(32)
AS
INSERT INTO forum_users
(forum_user_name, forum_user_password)
VALUES(@user_name, @password)