The Conversion Of Char Data Type To Smalldatetime Data Type Resulted In An Out-of-range Smalldatetime Value

Mar 30, 2007

I am using Visual Studio 2005 and SQL Express 2005. The database was converted from MS Access 2003 to SQL Express by using the upsize wizard.



I would like to store the current date & time in a column in a table. This column is a smalldatetime column called 'lastlogin'.

The code I'm using is:



Dim sqlcommand As New SqlCommand _

("UPDATE tableXYZ SET Loggedin = 'True', LastLogin = GetDate() WHERE employeeID = '" & intEmployeeID.ToString & "'", conn)



Try

conn.Open()

sqlcommand.ExecuteNonQuery()

conn.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try



This code works fine on my local machine and local SQL server. However at the client side this code results in the error as mentioned in the subject of this thread. I first used 'datetime.now' instead of 'getdate()', but that caused the same error. Then I changed the code to 'getdate()', but the error still remains.



The server at the client is running Windows Server 2000 UK . My local machiine is running WIndows XP Dutch.

Maybe the conversion from Dutch to UK has something to do with it. But this should be solved by using the 'Getdate()' function..... ?













View 1 Replies


ADVERTISEMENT

System.Data.SqlClient.SqlException: The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Dec 14, 2005

After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.

System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.

Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.

At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.

anyone having this problem before?? hopefully you guys can help.

Thanks,

View 4 Replies View Related

Getting Error : : The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value

Jan 28, 2008

update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?

View 5 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Apr 19, 2008

Advance thanks ....... My table is  TimeSheet:-----------------------------------  CREATE TABLE [dbo].[TimeSheet](    [autoid] [int] IDENTITY(1,1) NOT NULL,    [UserId] [int] NOT NULL,    [starttime] [datetime] NOT NULL,    [endtime] [datetime] NOT NULL,    [summary] [nvarchar](50) NOT NULL,    [description] [nvarchar](50) NULL,    [dtOfEntry] [datetime] NOT NULL,    [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution  

View 5 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type Resulted In An Out-of-range Datetime Value.

Aug 3, 2005

Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards

View 6 Replies View Related

Index Creation Causes Error The Conversion Of A Char Data Type To A Datetime Data Type Resulted...

Jul 23, 2005

Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 12, 2006

I am newbie in asp and sql, and I am using VS & SQL expresswhen I try to submit I get following error"Conversion failed when converting character string to smalldatetime data type"Following is my insert statement<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@DropDownList1','@DropDownList2','@DropDownList3','@TextBox6','@DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3','@TextBox1','@TextBox2','@TextBox3','@TextBox4','@TextBox5','@TextBox7')"></asp:SqlDataSource>These are @DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3' defined as datetime in database.I would appriciate if somebody could help.

View 7 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type.

Mar 25, 2007

Hello, I have problem with this code.(This program presents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are  2  time categories in DropDownList - this day, this week.
 Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.
Conversion failed when converting character string to smalldatetime data type.
Here is code:
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
 
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
 
string datePatt = @"yyyymmdd";
// Get start and end of day
DateTime StartDate = DateTime.Today;
 
string @StartDate1 = StartDate.ToString(datePatt);
 
string @EndDate = StartDate.AddDays(1).ToString(datePatt);
// Get start and end of week
string @startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);
string @startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);
 
 
switch (DropDownList1.SelectedValue)
{
 
case "1":
// day
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@StartDate1'" + " AND [DATE] < " + "'@EndDate'";
break;
case "2":
//week
SqlDataSource1.SelectCommand = "SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" + "WHERE" + "[DATE] >=" + "'@startOfWeek'" + "AND [DATE] <" + "'@startOfNextWeek'";
break;
 
}
 
}
 
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
body {
font: 1em Verdana;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
&nbsp;&nbsp;
 
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"
Style="z-index: 100; left: 414px; position: absolute; top: 22px">
<asp:ListItem Selected="True" Value="1">jeden den</asp:ListItem>
<asp:ListItem Value="2">jeden tyden</asp:ListItem>
</asp:DropDownList>
 
<asp:GridView ID="GridView1" runat="server" Style="z-index: 102; left: 228px; position: absolute;
top: 107px" DataSourceID="SqlDataSource1" AutoGenerateColumns="True">
 
</asp:GridView>
&nbsp; &nbsp;<br/> <br/>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=CRSQLEXPRESS;
Initial Catalog=MyConn;Integrated Security=True"
ProviderName="System.Data.SqlClient"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
 

View 4 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 3, 2007

I am running the following query:
 select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where  cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and  cast(HQDIS as varchar(8)) >= (Select CONVERT(varchar(8), getdate(), 1))  and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead   order by hqvend,fprod
The bold sections are giving me the error message. The query works as written above, butis only evaluating on one date, contained in hqeff. However, when I try to modify the second date, HQDIS, by casting it the same way as the one before it:
 select distinct hqvend, hqvprd,fprod,idesc,ilead,sum(cast(fqty as int) )as fqty, (cast(hqvend as varchar(5))+'/'+ltrim(rtrim(fprod))+'/'+cast(ilead as varchar(3))) as Keydata,'L1/'+rtrim(fprod) as LocPartno from tblkfpl01 inner join hqtl01 on (fprod=hqprod) inner join iiml01 on (hqvend=ivend and hqprod=iprod) where  cast(cast(hqeff as varchar(8)) as smalldatetime) <= (Select CONVERT(varchar(8), getdate(), 1)) and  cast(cast(HQDIS as varchar(8)) as smalldatetime) >= (Select CONVERT(varchar(8), getdate(), 1))  and hqvend like '134%' group by hqvend,fprod,hqvprd,idesc,ilead   order by hqvend,fprod
I get the error message. I need to select based on both dates (hqeff AND HQDIS), but cannot seem to be able to it.... Both fields are of type Dedimal, with a length of 8, and dates stored in them look like: 20071003 (YYYYMMDD).
Any suggestions?

View 2 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 21, 2007

Hi,

I have SQL Server 2005 database table with the following data definition as follows:

ID int
LST_TIME varchar(5)
LST_DATE varchar(21)
LST_WEEK int
SUBJECT varchar(100)


Date format (Month,day YEAR) eg.- October, 21 2007
Time format, 00 - 23 hours, HH:MM eg. - 18:58

, and a VB code as below:

Dim Command As New SqlClient.SqlCommand("SELECT * FROM dbo.LISTS WHERE (LST_WK = DATEPART(wk, GETDATE())) AND (CONVERT(datetime, LST_DATE) = CONVERT(datetime, CONVERT(varchar, GETDATE(), 110)))
AND(CONVERT(smalldatetime, LST_TIME) = CONVERT(smalldatetime, CONVERT(varchar(5), GETDATE(), 108)))", conn)

Dim dataReader As SqlClient.SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
While dataReader.Read()

Dim NewAlert As New Alert(dataReader("SUBJECT").ToString(), DateTime.Parse(dataReader("LST_DATE").ToString(),
DateTime.Parse(dataReader("LST_TIME").ToString())))

:
:
:
I am encountering error: System.Data.SqlClient.SqlException: Conversion failed when converting character string to smalldatetime data type, which points to the codes in bold. Just wondering anyone out there has got a solution to my problem. Thank you.

View 5 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Apr 10, 2008

Recently i traspased a database from sql server 2000 in spanish to sql server 2005 in english. I change the language of the user sa to spanish and the database is in spanish. . The problem is our dateformat is dmy, i try to force with set dateformat dmy, but always i execute a stored procedure that have a date as input it fail. The error is:

Msg 295, Level 16, State 3, Procedure pa_CalendarioGestion, Line 40
Conversion failed when converting character string to smalldatetime data type.

The code of stored procedure in this line is:

set @diaActual = dateadd("d", @i, @fechaInicio)

This stored procedure in sql server 2000 in spanish not have any problems. For this reason i think the problem is in the configuration.

I hope someone can help me. Thanks.

View 10 Replies View Related

Conversion Failed When Converting Character String To Smalldatetime Data Type

Oct 10, 2006

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@DropDownList1','@DropDownList2','@DropDownList3','@TextBox6','@DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3','@TextBox1','@TextBox2','@TextBox3','@TextBox4','@TextBox5','@TextBox7')">

</asp:SqlDataSource>

These are @DropDownCalendar1','@DropDownCalendar2','@DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

Thanks

View 3 Replies View Related

SQL Server 2008 :: Conversion Of Varchar Data Type To Datetime Resulted In Out Of Range Value

May 14, 2015

I have problem to execute query with interval date.

If try this query I don't have problem and the output is correct:

SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121)
AND CONVERT (datetime, '09/01/2015', 121);Instead if try this I have error:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121)
AND CONVERT (datetime, '28/01/2015', 121);

[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

View 3 Replies View Related

Convert Text Data Type To Smalldatetime Data Type

Oct 9, 2007

I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible?
I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail.


SELECT rsbirthday CAST(rsbirthday AS smalldatetime)

FROM panelists


the error returned is "incorrect syntax near 'rsbirthday'.

I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing.

Thanks for the help!

View 10 Replies View Related

Working With Smalldatetime Data Type

Mar 11, 2004

Hello all,

if i save a date value within a smalldatetime field, then later want to edit the record and set the smalldatetime field to nothing, how can i do this. I've tried populating the field with Null, and anything else, without success. Any suggestions?

Sammy

View 2 Replies View Related

Resulted In An Out-of-range Smalldatetime Valu

Oct 30, 2007

i get this error when running this sp: i think i should use convert method but how?
Msg 296, Level 16, State 3, Procedure CmmSPNet_userAlbumPhotos, Line 7
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'CmmSPNet_userAlbumPhotos' AND type = 'P')DROP PROCEDURE CmmSPNet_userAlbumPhotos
GOCREATE PROCEDURE [CmmSPNet_userAlbumPhotos]
@albumID int,
@orderBy varchar(100),
@orderDirection varchar(4)AS
SELECT ID,UserID,Photo,ALbumID,Title,views,date_added from cmmNet_userAlbumPic where albumID=@albumID order by
case @orderBywhen 'date_added' then case @orderDirection
when 'desc' then '31-dec-10' - date_added
/* highest date value */else date_added
endwhen 'views' then case @orderDirection
when 'desc' then 21474 - [views] /* highest integer value */else [views]
end
else date_added
end
GO

View 1 Replies View Related

The Conversion Of A Char Data Type To A Datetime Data Type!!

May 13, 2008

hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
 
in my SQL database I have the date feild as datetime and the version as nvarchar(max)
this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient
Imports system.web.configuration

Partial Class Admin_emag_insert
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Record_DateTextBox.Text = DateTime.Now

End Sub

Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click
Me.VersionTextBox.Text = ""
End Sub

Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click
Dim objConnection As SqlConnection
Dim objDataCommand As SqlCommand
Dim ConnectionString As String
Dim record_date As Date
Dim version As String
Dim emagSQL As String

'save form values in variables
record_date = Record_DateTextBox.Text
version = VersionTextBox.Text

ConnectionString = WebConfigurationManager.ConnectionStrings("HRDBConnectionString").ConnectionString

'Create and open the connection
objConnection = New SqlConnection(ConnectionString)
objConnection.Open()
emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _
"values('" & record_date & "','" & version & "')"

'Create and execute the command
objDataCommand = New SqlCommand(emagSQL, objConnection)
objDataCommand.ExecuteNonQuery()
objConnection.Close()

AddMessage.Text = "A new emagazine was added successfully"

Me.VersionTextBox.Text = ""

End Sub
End Class
 

View 10 Replies View Related

Syntax Error Converting Character String To Smalldatetime Data Type.

Aug 8, 2005

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 @toDateString = '  CONVERT(smalldatetime, ''' + @toDate + ''') '
SELECT * FROM MIPR WHERE      ID = @searchFor      OR DESC_QTY = @searchFor      OR REQ_ACTIVITY = @searchFor      OR ACC_ACTIVITY = @searchFor      OR MANYEARS = @searchFor      OR TECH_POC = @searchFor      OR RESOURCE_POC = @searchFor      OR SI_DATE BETWEEN @fromDateString   AND  @toDateString;GOit creates an error that I can't seem to fix. the error I get is:Syntax error converting character string to smalldatetime data type.How can I fix this? Thanks in advance!

View 1 Replies View Related

Char Data Type Conversion

Sep 11, 2006

im trying to convert char (18) data type to decimal (18,6) but it wont let me do it. It gives an arithematic error. what would be other way to solve this problem. Can i do it with float if yes how so? or any other suggested way. Thanks

View 6 Replies View Related

Date Conversion From Char To Smalldatetime

Dec 3, 2000

Hi All,
I am trying to convert date from a character string field 12 to another database which has smalldatetime. Is there a way to do this. Any help would be greatly appriciated.

cheers
dave

View 4 Replies View Related

Data Type Conversion - Numeric To Char

Jul 12, 2000

I would like to know how to translate a numeric value = 3 to a text or char value = 03.

Any input would be appreciated.

Thanks,


Joel

View 1 Replies View Related

Stored Proc Error: Error Converting Character String To Smalldatetime Data Type

Feb 12, 2005

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 resultAs Int32
Dim cmdAs 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()

(...)

---

View 1 Replies View Related

Datetime Data Type Resulted In An Out-of-range Datetime Value. Please Help

May 13, 2006

Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
 '31/08/2006 23:28:59'
 I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest

View 2 Replies View Related

Smalldatetime Type Behavior

May 10, 2006

Hello,

i've noticed this smalldatetime type behavior:

Passed value seconds are not truncated, but rounded.

Could you be so kind and help me change this behavior to truncation, please?

Thank you.

View 5 Replies View Related

About The Type Field : Datetime And Smalldatetime

Feb 4, 2006

hi all, the field type :datetime and smalldatetime,  i still can't understand.everytime when i inserted the data to the db, i also get the error message "System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."i must change the field type to string, so that i can insert data my codetxt_datetime.text = '5/2/2006'insert into datetime (datetime) values ('"& txt_datetime.text & "')"can anybody tell me the reason???thank you!!

View 4 Replies View Related

Bulk Insert Task Failing On Data Type Conversion For A Destination Column Of Type Bit

Jul 6, 2006

I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.

I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.

I hope someone can help me work through this.

Thanks in advance,

SK



SSIS package "Package3.dtsx" starting.

Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".

Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".

Task failed: Bulk Insert Task 1

Task failed: Bulk Insert Task

Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package3.dtsx" finished: Failure.

View 5 Replies View Related

Trans Replication With Updating Subscriber On Sql2000 (single Quote In The Data As Char Data Type)

Nov 17, 2006

Hi,

I am trying to setup Trans Replication with updating subscriber on sql2000. One column on few tables got data with single quote (').

How do I handle in this case? Did any one come across such case?

Can I Change default QUOTED IDENTIFIER from ' (single quote) to something else (@@@) on SQL2000?

If yes, how to do?

Thanks
mka

View 1 Replies View Related

Char To Smalldatetime

Mar 1, 2004

SQL Server 2000

Used to know how to do this but am having no luck today. I have data coming in from a .txt file that gives me char strings for dates, e.g. 02242003 for Feb. 2, 2003.

Need to whomp this into small datetime with the whole convert/cast thing but I guess I've previously only gone the other way--smalldatetime to char.

Thanks!!!

View 4 Replies View Related

Convert Char To Smalldatetime Issue

Feb 15, 2008

Hi,

i want to perform the following conversion to yyyy-mm-dd hh:mm: ss SMALLDATETIME format :

print convert( smalldatetime,'2008-02-14 10:16:00', 120)

But i get the error and the wrong conversion format:

Feb 14 2008 10:16AM

Msg 296, Level 16, State 3, Procedure TSU_CalcTempoRespostaPercInterv, Line 43

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.



Any way to achieve this ?! i'm tearing my hear off with this issue #"#"#$"#"$#$"

View 9 Replies View Related

Changing Data Type From Char To Datetime

Mar 2, 2007

Thanks in adance

Platform: SQL 2000

A SQL table has a field named "pay-day" with Char(8) data type.

I tried to change its data type to datetime but only with an error message like this. I did right-click the table and tried to modify a data type.

- Unable to modify table.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Jay

View 4 Replies View Related

Best Data Type For A Range

Jun 28, 2007

I want to know what the best datatype is for a situation like this e.g.if I have a field "age" and the data has a range i.e. 22-30, 31-50 etc, what is the best data type to use for this scenario.
Similarly if I have a field that whereby you use a range for example 1-2 in one record but in another you get an integer value of 0 for instance, what again would be the best datatype.
Many thanks

View 5 Replies View Related

Data Type Range

Jul 3, 2007

Hi,



When I set a tinyint field as identity, it automatically sets the identity seed as 1. I wonder whether the increment is set to go around in loop. Say, I have the range of 256 numbers for a tinybit. So, an identifier should go from 1 to 255 and then to 0 and then back to 1. Is that right?

View 4 Replies View Related

How Can I Find Values Outside Of The Smalldatetime Range?

Mar 25, 2008

I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort.

I have tried to find the values which are out of the smalldatetime range, with the following query


SELECT *

FROM midmar

WHERE bday BETWEEN '01/01/1900' AND '06/05/2079'




Which doesn't quite work, and gives me values that are actually between those two values listed.

I have also tried having the WHERE clause read:

WHERE bday <'06/06/2079' AND

bday>'01/01/1900'


and that doesn't really work either.

What's going on? Is there likely another problem besides the structuring of my queries?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved