Stored Proc Error: error converting character string to smalldatetime data type

I am trying to create a page that adds users to a MS SQL database.  In doing so, I have run into a couple errors that I can't seem to get past.  I am hoping that I could get some assistance with them.

Error from SQL Debug:
---
Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.
---
Error from page execution:
---
Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
Line 78:
Line 79: cmd.ExecuteNonQuery()
---

Below is what I currently have for my stored procedure and the pertinent code from the page itself.
Stored Procedure:
---
CREATE PROCEDURE dbo.AdminAddUser( @username varchar(100),
@password varchar(100),
@email varchar(100),
@acct_type varchar(100),
@realname varchar(100),
@billname varchar(100),
@addr1 varchar(100),
@addr2 varchar(100),
@city varchar(100),
@state varchar(100),
@country varchar(100),
@zip varchar(100),
@memo varchar(100) )
AS
BEGIN TRAN
--
-- Returns 1 if successful
-- 2 if username already exists
-- 0 if there was an error adding the user
--
SET NOCOUNT ON
DECLARE @error int, @rowcount int

--
-- Make sure that there isn't already a user with this username
--

SELECT userID
FROM users
WHERE username=@username
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount > 0 BEGIN
ROLLBACK TRAN
RETURN 2
END
--
-- Set expiration date
--
DECLARE @expDate AS smalldatetime

IF @acct_type = "new_1yr" BEGIN
SET @expDate = DATEADD( yyyy, 1, GETDATE() )
END

IF @acct_type = "new_life" BEGIN
SET @expDate = DATEADD( yyyy, 40, GETDATE() )
END

DECLARE @paidCopies AS decimal
SET @paidCopies = 5

--
-- Add this user to the database
--

IF @acct_type <> "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userPaidCopies, userExpirationDate, userLastPaidDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@paidCopies, @expDate, GETDATE(), @addr1, @addr2, @city, @state, @country, @zip, @memo )
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END

IF @acct_type = "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@addr1, @addr2, @city, @state, @country, @zip, @memo )
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END

COMMIT TRAN
RETURN 1
GO
---

Page Code:
---
Sub AddUser(Sender as Object, e as EventArgs)

Dim db_conn_str As String
Dim db_conn As OleDbConnection
Dim result As Int32
Dim cmd As OleDbCommand


db_conn_str = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=xxxxx; User ID=xxxxx; PWD=xxxxx;"
db_conn = New OleDbConnection( db_conn_str )
db_conn.Open()

cmd = new OleDbCommand( "AdminAddUser", db_conn )
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add( "result", OleDbType.Integer ).Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add( "@username", OleDbType.VarChar, 100 ).Value = Request.Form("userName")
cmd.Parameters.Add( "@password", OleDbType.VarChar, 100 ).Value = Request.Form("userPass")
cmd.Parameters.Add( "@email", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@acct_type", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@realname", OleDbType.VarChar, 100 ).Value = Request.Form("userRealName")
cmd.Parameters.Add( "@billname", OleDbType.VarChar, 100 ).Value = Request.Form("userBname")
cmd.Parameters.Add( "@addr1", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr1")
cmd.Parameters.Add( "@addr2", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr2")
cmd.Parameters.Add( "@city", OleDbType.VarChar, 100 ).Value = Request.Form("userCity")
cmd.Parameters.Add( "@state", OleDbType.VarChar, 100 ).Value = Request.Form("userState")
cmd.Parameters.Add( "@country", OleDbType.VarChar, 100 ).Value = Request.Form("userCountry")
cmd.Parameters.Add( "@memo", OleDbType.VarChar, 100 ).Value = Request.Form("userMemo")
cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")

cmd.ExecuteNonQuery()
(...)
---
0
jasondubya
2/13/2005 12:22:33 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

1 Replies
563 Views

Similar Articles

[PageSpeed] 26

what fields are not varchar(100)?
Kay Lee
MySpace.com - http://www.myspace.com/kragie
Infrastructure Group
MySpace.com

- Code to live, but Live to code.
0
KraGiE
2/13/2005 8:30:29 PM
Reply:

Similar Artilces:

Syntax error converting character string to smalldatetime data type
Here's my subroutine on a button click to do the insert using the SPROC sp_InsertDel. The problem is that I get an exception of:"Syntax error converting character string to smalldatetime data type." when I leave the date text box blank, or enter something like 06/29/2005.TrysCon1.Open()Dim sqldatenull As SqlDateTime = SqlDateTime.NullDim strsp As String = "sp_InsertDel"Dim myCommand As New SqlCommand(strsp, sCon1)myCommand.CommandType = CommandType.StoredProcedureDim strRQW As New SqlParameter("@strRQW", SqlDbType.NVarChar)strRQW.Value = "Test"myCommand.Parameters.Add(strRQW)Dim strP3Activit...

Syntax error converting character string to smalldatetime data type.
Hi guys and gals,I have the following code:CREATE PROCEDURE searchRecords(     @searchFor  NVarchar (25)  = NULL,     @fromDate  NVarchar (25)  = NULL,     @toDate  NVarchar (25)  = NULL) AS IF @fromDate IS NOT NULL     DECLARE @fromDateString VarChar(200)     SET @fromDateString = '  CONVERT(smalldatetime, ''' + @fromDate + ''') ' IF @toDate IS NOT NULL     DECLARE @toDateString VarChar(200)     SET @...

Syntax error converting character string to smalldatetime data type. #2
 Hello,I'm quite new to sql server 2000 so my (most likely simple) problems have become quite frustrating.  I'm trying to do a simple comparison of a smalldatetime between two dates however I keep getting an error.  @ShipDate gets set from my cursor; my code is as follows:SET @ShipYear = CAST(YEAR(@ShipDate) AS NVARCHAR(4))        SET @ShipYear2 = @ShipYear + 1 --YEAR(DATEADD(yyyy,1,@ShipYear))                SET @TempYear = CASE WHEN (@ShipDate) BETWEEN (SELECT CAST('3-01-&#...

error in SQL Store Proc ''Conversion failed when converting the varchar value to data type int'
Hi , I am having problem and error of 'Conversion failed when converting the varchar value to data type int ' CREATE PROC dbo.testingloop (DECLARE @testid varchar(max))ASBEGINSET NOCOUNT ONDECLARE @SQL varchar(600)SET @testvalue = CURSOR FORSELECT [test ID])  FROM Data42 where [test ID] = 36OPEN @testvalue      FETCH NEXT     FROM @testvalue INTO @testid      WHILE @@FETCH_STATUS = 0       BEGIN      ' My update statement here  ...

Error = Arithmetic overflow error converting expression to data type smalldatetim
  $exception {"Arithmetic overflow error converting expression to data type smalldatetime.\r\nThe statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException} occurs here is my code protected void EmailSubmitBtn_Click(object sender, EventArgs e) { SqlDataSource NewsletterSqlDataSource = new SqlDataSource(); NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();   //Text version NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text; NewsletterS...

SQL Server Error: "Arithmetic overflow error converting numeric to data type numeric"
I'm still quite new to SQL, and so I may be missing something quite obvious. Apologies in advance if that's the case. I've included my query at the bottom of the message as it is quite long; my problem is that I get "Arithmetic overflow error converting numeric to data type numeric" when the first parameter to my COALESCE calls is not NULL. That is, the COALESCEs and the query work as I expect when SUM(cust_vw_actual.Actual) is NULL (they return 0.00), but if it isn't I get the error. I am also getting "Warning: Null value eliminated from aggregate.&...

Problem building SQL text query with parameters / "Syntax error converting datetime from character string" error message
Hi folks, I keep looking at these SQL text query and I cannot figure out what I am doing wrong.If I include wrap the dates with quotes, I get the "Syntax error converting datetime from character string" error message, but I don't include them I don't get anything which is also wrong, because there is some data to be displayed. Bottom line, what do you think I am doing wrong when building the SQL query?1 DateTime dateFrom = txtDateFrom.SelectedDate;2 DateTime from = new DateTime(dateFrom.Year, dateFrom.Month, dateFrom.Day, 0, 0, 0);3 ...

Error converting data type... Cannot find the error
I cannot figure out what is causing this error... The stored procedure works fins in the SQL database, but when I try to call it through the datasource I get the error at the bottom of the page... Can anyone help me figure out what this problem is? <asp:SqlDataSource ID="SqlDataSource1" runat="server"ConnectionString="<%$ ConnectionStrings:SqlServer %>"ProviderName="<%$ ConnectionStrings:SqlServer.ProviderName %>"SelectCommandType="StoredProcedure"CancelSelectOnNullParameter="false"SelectCommand="pe_getAppraisals3" ConflictDetection="CompareAllValues" SortParameterName...

syntax error converting smalldatetime from character string
I have a datawindow on a store procedure on Microsoft Sql server 6.50. When I preview the datawindow correctly works, but when I run the application it give me this message: Select error: Syntax error converting Smalldatetime from character string. I don't have smalldatetime variable on my stored procedure and the program gives me the same error if I comment the body of the stored procedure. The variable I pass to the stored procedure are right and in the right order. Filippo Mazzaro ...

error error error
These are the 2 new errors i am getting now:   1).  Server Error in '/' Application. Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS.Source Error: Line 53: ...

Session.LCID error: Error converting data type nvarchar to datetime
Hi! I got a SQL Server db with datetimes in tables in format as culture sv-SE. (2009-02-12 12:12:12) I also got a web application where I want to set culture based on membership profile info, but when I set culture en-GB I get the format on the datetimes as 20/02/2009 09:39:04. When handling datetimes against database I got "error converting data type nvarchar to datetime". Is it a work around for this so I can work with datetimes in one application with several cultures?  Hi,AimitU can do that with convert function ie convert that into 103 format&...

errors errors errors..
Name: harun Email: harunbjk1903hrnatgmaildotcom Product: Firefox Summary: errors errors errors.. Comments: first of all when i launch firefox (3) i cant enter websites via writing.. for example when I type "google" and then press ctrl and enter, it crashes.. and i take lots of error reports. firefox 2 was better.. see you again , harun Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; tr; rv:1.9) Gecko/2008052906 Firefox/3.0 From URL: http://hendrix.mozilla.org/ ...

Error, Error and more errors
Okay, all I wanted to do was test this app out and it has been a nightmare. 1.) Didn't install the sql database. I had to manually install it. 2.) I get errors when trying to add a picture to an album. "Procedure or function ngUpdatePicture has too many arguments specified" Any ideas? I've gotten more errors than this, but there is no use in bitchin. Are you using the v1.6.1 installer? Also, did you select to install the SQL Server db and give it a valid admin login? What were some of the error messages? Did you have an existing DB from a previous install? ...

System.Net.Mail is giving error CS0029: Cannot implicitly convert type 'string' to 'System.Net.Mail.MailAddress'
I'm getting an error while trying to use the wizard to create a form and than pass it the vaibles to Mail. Any help would be much appreciated. SmtpClient smtpClient = new SmtpClient(); MailMessage message = new MailMessage(); try {   message.From = txtEmail.Text; message.To.Add("admin1@yoursite.com"); message.Subject = "Feedback"; message.CC.Add("admin1@yoursite.com"); message.CC.Add("admin2@yoursite.com"); message.IsBodyHtml = false; message.Body = txtMessage.Text; smtpClient.Send(message); lblStatus.Text = "Email successfully sent."; } catch (Exception ex) { lbl...

Web resources about - Stored Proc Error: error converting character string to smalldatetime data type - asp.net.sql-datasource

Data Types
... we handle synchronization of these by mapping them to INTEGER and handling the scaling.) date datetime datetime2 datetimeoffset smalldatetime ...

Just 5 Minutes - just 5 minutes to think how to paint the sky.. - Page 5
just 5 minutes to think how to paint the sky.. (by Duong Thanh)

Data Types TSQL Tutorial - Learn Transact SQL language with examples
Data Types TSQL Tutorial. The Transact SQL language allow you to use various data types like: Numerics, Character Strings, Unicode Character ...

One Second to Midnight - DateTimes in Sql Server 2005
Today I am enthused to write about the DateTime and SmallDateTime datatypes in SQL Server 2005 (and possibly this also applies to 2008, although ...

Retrieving data from compromised SQL server
Sometimes, I find myself in need of pulling data from a compromised SQL server. Usually, the database is too big to be downloaded without being ...

Resources last updated: 12/25/2015 9:45:42 PM