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
ADVERTISEMENT
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
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
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>
<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>
<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
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
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
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
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
Aug 6, 2004
Here's the deal. I'm a newbie trying to update a date (@Effective) in sql server. I've been trying all sorts of things, but nothing seems to work. Here's my current code:
Sub btnUpdate_Click(sender As Object, e As EventArgs)
Dim sqlStmt As String
Dim conString As String
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim sqldatenull As SqlDateTime
Try
sqlStmt = "UPDATE [Submission] SET [Effective]=Convert(DateTime, '@Effective', 101), "& _
"[Status]=@Status WHERE ([Submission].[Submission]=@Submission)"
conString = "server='(local)'; user id='sa'; password='password'; database='database'"
cn = New SqlConnection(conString)
cmd = New SqlCommand(sqlStmt, cn)
cmd.Parameters.Add(New SqlParameter("@Submission", SqlDbType.Int, 4))
cmd.Parameters.Add(New SqlParameter("@Effective", SqlDbType.VarChar, 10))
cmd.Parameters.Add(New SqlParameter("@Status", SqlDbType.VarChar, 8))
sqldatenull = SqlDateTime.Null
If (txtEffective.Text = "") Then
cmd.Parameters("@Effective").Value = sqldatenull
Else
cmd.Parameters("@Effective").Value = txtEffective.Text
End If
cmd.Parameters("@Status").Value = dropStatus.SelectedItem.Text
cmd.Parameters("@Submission").Value = LabelSubmission.Text
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
Response.Redirect("main.aspx")
Catch ex As Exception
lblError.Text = ex.Message
Finally
cn.Close()
End Try
End Sub
My db is setup as follows:
Submissionint4
Insuredvarchar30
Statevarchar2
Effective datetime 8
Underwritervarchar9
Statusvarchar8
Cleareddatetime 9
I'm losing my hair by the fist full... Somebody please stop the insanity!
View 2 Replies
View Related
Apr 20, 2005
Somewhere, either in the
aspx.vb code, or the stored procedure that's being called, it doesn't
like my code since I am getting the dreaded "syntax error converting
datetime from character string" error. I've been battling this
for two days - can someone spot what is wrong here?
The dtWdate variable is coming from a calendar control on my aspx page.
Please help - I've tried every conceivable way I can think to do this without success.
Lynnette
Here's the code:
Private Sub GetFLSAEmpInfoRecs()
'uses the AppSettings table to generate WHERE clause from the filter values
Dim dtWdate As DateTime = Calendar1.SelectedDate
Dim wValue As String = Session("svFilterValue")
Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
Dim cmd As New SqlCommand("usp_AddFLSA", cnn)
cmd.CommandType = CommandType.StoredProcedure
cnn.Open()
cmd.Parameters.Add(New
SqlParameter("@dtWDate", SqlDbType.DateTime)).Value =
Convert.ToDateTime(dtWdate)
cmd.Parameters.Add(New
SqlParameter("@whereString", SqlDbType.VarChar, 500)).Value = wValue
cmd.ExecuteNonQuery()
cnn.Close()
End Sub
And here is the stored procedure:
ALTER PROC usp_AddFLSA
@dtWdate datetime,
@whereString as varchar(255)
AS
DECLARE @strSQL as varchar(2000)
SET @strSQL =
'
INSERT INTO FLSAEmpInfo
( Emp_Number,
PT_ID,
Emp_Division,
Emp_DeptInfo,
Emp_Supervisor,
Emp_Location,
Emp_Union,
Emp_SG,
Emp_Shift,
WrkDate
)
SELECT
hcsoSharedTables.dbo.Employee2.Emp_Number,
hcsoSharedTables.dbo.Employee2.[ID],
hcsoSharedTables.dbo.Employee2.Division,
dbo.udf_getDeptInfo(hcsoSharedTables.dbo.employee2.Department,hcsosharedtables.dbo.employee2.Dept_Mgr,hcsosharedtables.dbo.employee2.job_dept_code),
hcsoSharedTables.dbo.Employee2.Job_Supervisor,
hcsoSharedTables.dbo.Employee2.Loc_Name,
hcsoSharedTables.dbo.Employee2.[Union],
hcsoSharedTables.dbo.Employee2.Sched_Group,
hcsoSharedTables.dbo.Employee2.Shift,' +
@dtWdate + ' ' + 'FROM hcsoSharedTables.dbo.Employee2' + @whereString
EXEC(@strSQL)
View 4 Replies
View Related
Nov 14, 2005
Hi all, The last week this was working, and today i was testing and all start with errors. This is my SQL sentence:command = New SqlCommand("update ctlg_users set u_last_access = '" & FormatDateTime(Now(), 1) & "' where u_id = " & cstr(l_u_id),oConn)Is there an error? why was before working and now don`t?, including an old ASP page that i´m still using fall back to the same error when is using FormatDateTime, í´ve tried in the ASPX page using datetime.now.tostring("dd/mm/yyyy") but i got an error too!! HEEEELP!Thanks :D
View 2 Replies
View Related
Nov 24, 2005
Hi,
I'm having a strange problem with the following stored proc:
Code:
sql
declare @orderBy tinyint
set @orderBy = 2
SELECT tblUsers.userID, firstName, lastName, dateRegistered, count(movieSessionID) As sessionsCount FROM tblUsers
LEFT JOIN tblMovieSessions on tblUsers.userID = tblmovieSessions.userID
Group By tblUsers.userID, firstName, lastName, dateRegistered
Order By Case
WHEN @orderBy = 2 THEN firstName
WHEN @orderBy = 3 THEN dateRegistered
WHEN @orderBy = 4 THEN count(movieSessionID)
else lastName
End desc
When I execute it with @orderBy = 1 or 2 I get the following error message: Quote: Server: Msg 241, Level 16, State 1, Line 5
Syntax error converting datetime from character string.
However, with @orderBy = 3 or 4 it executes successfully.
I don't understand why this change should have this impact because the error seems to be occuring on the first line (i.e. SELECT...).
If I take the dateRegistered column out of the query I get a new error message:
Quote:
Server: Msg 245, Level 16, State 1, Line 5
Syntax error converting the nvarchar value 'Adam' to a column of data type int.
I don't understand why it is trying to convert these columns during the query anyway, let alone how to solve the problem.
Anyone got any ideas?
View 1 Replies
View Related
Sep 29, 2005
I am using INSERT to replace about 160 fields for one company in the company table, all goes well until I come to the Rn_edit_date field which is a datetime datatype. When I insert the value of:
cast('2005-03-15 09:25:44.463' as datetime)
Error message as a result is: Syntax error converting datetime from character string.
NOTE: 2005-03-15 09:25:44.463 is the correct format which all other companies abide by, so I'm totally lost at where I'm going wrong?
Any help would be appreciated
View 5 Replies
View Related
Jun 22, 2007
I am having a problem on running the following queiry
select * from my_table where unitclass_id=247
and Year(cast (column1 as datetime))=Year(dateadd(wk,0,getdate()) )
column1 type nvarchar(10) and stores date in format like 2007-06-22.
I am trying to select all records for the current year from my_table and getting an error
when running that queiry
Syntax error converting datetime from character string.
Please help
Thank you
View 8 Replies
View Related
Mar 6, 2008
I have a field that has dates that are currently set as a string. Dates are as follows:
Apr 01 2006
Aug 20 2006
Aug 20 2006
I did an isdate() function on the field to see if it can be converted and it came back as true; however, I have attempted several different ways to convert the field to a date field and it continues to give me a syntax error. Any assistance would be great!!!
I tried:
convert(datetime,cast(New_Date as varchar),100)
convert(varchar(25),New_Date,107)
Both gave me syntax errors.
View 10 Replies
View Related
Nov 14, 2007
When I convert this data into a datetime, i get the foll error.
Syntax error converting datetime from character string.
SELECT convert(datetime,'09/2007')
Basically I have a field in a tbl that has varchar datatype, and is in this format of mm-yyyy. I need to compare this value to current date and so I convert it to datetime. but it errs
Pl help
View 3 Replies
View Related
Jul 20, 2005
CREATE PROCEDURE dbo.Synchronization_GetNewRecords(@item varchar(50),@last datetime)ASSET NOCOUNT ONDECLARE @sql nvarchar(4000)SET @sql = 'SELECT * FROM ' + @item + ' WHERE LastUpdated >' + @lastEXEC sp_executesql @sql, N'@Type varchar(50), @Last datetime', @item, @lastThis is my SP. Very simple. But it is throwing the error in the subject line.Any help would be greatly appreciated.
View 1 Replies
View Related
May 22, 2008
i have a stored proc running against the Northwind DB
Code Snippet
CREATE PROCEDURE CustomPaging
@intOrderID varchar(10),
@strCustomerID varchar(10),
@intEmployeeID int,
@dteOrderDate datetime,
@startRowIndex int,
@maximumRows int,
@SortOrder int
AS
DECLARE @TempItems TABLE
(
ID int IDENTITY,
OrderID int
)
DECLARE @maxRow int
SET @maxRow = (@startRowIndex + @maximumRows) - 1
SET ROWCOUNT @maxRow
INSERT INTO @TempItems (OrderID)
SELECT OrderID
FROM Orders o
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
ORDER BY CASE @SortOrder WHEN 0 THEN OrderID
WHEN 1 THEN c.CompanyName
WHEN 2 THEN e.FirstName
WHEN 3 THEN o.OrderDate
WHEN 4 THEN o.RequiredDate
WHEN 5 THEN o.ShippedDate END
SET ROWCOUNT @maximumRows
SELECT o.OrderID, CompanyName, e.FirstName + ' ' + e.LastName EmployeeName, o.OrderDate, o.RequiredDate, o.ShippedDate
FROM @TempItems t
INNER JOIN Orders o ON o.OrderID = t.OrderID
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
WHERE
t.[ID] >= @startRowIndex
AND
(o.OrderID LIKE @intOrderID OR @intOrderID = '')
AND
(o.CustomerID = @strCustomerID OR @strCustomerID='')
AND
(o.EmployeeID = @intEmployeeID OR @intEmployeeID = 0)
AND
(o.OrderDate = @dteOrderDate OR @dteOrderDate IS NULL)
SET ROWCOUNT 0
GO
when i execute the sp with @sortOrder = 0, it works fine
exec CustomPaging '','',0,null, 1, 10, 0
but if i try anything else (@SortOrder=1) i get the error message
exec CustomPaging '','',0,null, 1, 10, 1
Syntax error converting datetime from character string.
but im unsure what is causing this problem? and how to sort it?
Any help is greatly appreciated.
Cheers.
Craig
View 13 Replies
View Related
Jul 12, 2007
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
View 4 Replies
View Related
Jan 7, 2004
Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO
Thanx in front
Cya
View 14 Replies
View Related
Jul 15, 2014
All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:
Msg 295, Level 16, State 3, Line 25: Conversion failed when converting character string to small date-time data type..I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.
USE [SCIR_DataMart_FromProd_06_20_2014]
GO
IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL
DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]
[code]....
View 9 Replies
View Related
Oct 14, 2005
Hi,
This is my complete trigger.
ALTER TRIGGER DeleteTriggerC_Middleware_Exception ON dbo.C_Middleware_Exception AFTER DELETE AS
BEGIN
DECLARE @RowCount AS VARCHAR
SET @RowCount = @@ROWCOUNT
DECLARE @TableName_Deleted AS VARCHAR(50)
DECLARE @ErrorMsg_Deleted AS VARCHAR(255)
DECLARE @AddlErrMsg_Deleted AS VARCHAR(255)
DECLARE @SubjectArea_Deleted AS VARCHAR(25)
DECLARE @CHKPT_Deleted AS VARCHAR(10)
SELECT @TableName_Deleted = table_name , @ErrorMsg_Deleted = cast(error_msg AS varchar(255)) , @AddlErrMsg_Deleted = cast(addl_error_msg AS varchar(255)),
@SubjectArea_Deleted = cast(SUBJECT_AREA AS varchar(25)), @CHKPT_Deleted = cast(CHKPT AS varchar(255))FROM DELETED
--where subject_area = 'inventory'
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE Table_Name = @TableName_Deleted
IF @@ROWCOUNT = 0
BEGIN
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @ErrorMsg_Deleted like Error_Msg and Subject_Area IS NULL and ChkPoint IS NULL
END
IF @@ROWCOUNT = 0
BEGIN
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @AddlErrMsg_Deleted like Addl_Error_Msg
END
IF @@ROWCOUNT = 0
BEGIN
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @ErrorMsg_Deleted like Error_Msg and Subject_Area = @SubjectArea_Deleted and ChkPoint = @CHKPT_Deleted
END
END
when i am executing the follwing query i am gettin systax error.
Query :delete from dbo.c_middleware_exception where subject_area = 'eap_room'
Error :
Server: Msg 245, Level 16, State 1, Procedure DeleteTriggerC_Middleware_Exception, Line 17
Syntax error converting the varchar value '*' to a column of data type int.
what could be the solution.
Thanks in advance
yvnsmca
View 1 Replies
View Related
Jul 20, 2005
I have an inventory database that Im trying to create a report out ofthe IP address are a lookup on a seperat table but I keep getting theabove error can I change the table row to something to fix this orwhat.SELECT i.INVENTORY_ITEM_ID AS [IP Address],i.HOST_NAME AS [ServerName], '' AS Flag, i.MEMO AS Comments, 'Seattle' AS City, 'Washington'AS State,CASE WHEN fv.value = 'EL EET1410' THEN '1111 3rdAve.' WHEN fv.value = 'EL WFL17' THEN '999 3rd Ave.' ELSE '' END AS[Address 1],' ' AS [Address 2], CASE WHEN fv.value = 'ELEET1410' THEN '1111' WHEN fv.value = 'EL WFL17' THEN '2222' ELSE ''END AS [Building ID],fv.VALUE AS Building, '98101' AS Zip,CASE WHEN fv.value = 'EL EET1410' THEN '14' WHENfv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Computing FacilityLevel],CASE WHEN fv.value = 'EL EET1410' THEN '14' WHENfv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Bldg Floor],CASE WHEN fv.value = 'EL EET1410' THEN 'C028'WHEN fv.value = 'EL WFL17' THEN 'C123' ELSE '' END AS WSPID,i.LOCATION_IN_FACILITY AS Location,i.SERIAL_NUMBER AS [Serial Number], i.ASSET_TAG AS [Asset Tag], ' ' AS[Second Asset Tag(s)],vv.VALUE AS Manufacture, mv.VALUE AS Model,'Yes' AS [Rack Mountable], rv.VALUE AS [Rack Units], 'DEV' AS [ServerEnviroment],lv.VALUE AS [Sever Type], ov.VALUE AS OS,CASE WHEN lv.value = 'Server - Intel Blade' THEN'Windows' WHEN lv.value = 'Server - Intel' THEN 'Windows' WHENlv.value = 'Server - Unix' THEN 'Unix'ELSE '' END AS [OS Type], '19x28' AS Footprint,ev.VALUE AS [Technical Owner of Server], 'N/A' AS [SLA Category],i.ON_BOARD_NIC_PORT_COUNT AS [NW Connectionquantity], 'Devlopment/Test Machine' AS [Application(s) Name],ev.VALUE AS [PW Contact],'N/A' AS RTO, 'N/A' AS RPO, 'No' AS [Is BoxClustered?], 'N/A' AS [Buisness Critical],CASE WHEN mv.value = 'Proliant DL580G1' THEN'90lbs' WHEN mv.value = 'Proliant DL360G2' THEN '85lbs' WHEN mv.value= 'Enterprise 220R' THEN'45lbs' ELSE '' END AS Weight,i.LAST_MODIFIED_DATE AS [Date of Last Install], 'No' AS [DedicatedCircuit]FROM dbo.INVENTORY_ITEM i LEFT OUTER JOINdbo.IP_TO_INVENTORY pv ON pv.IP_ADDRESS = i.INVENTORY_ITEM_IDLEFT OUTER JOINdbo.LOOKUP_VALUE lv ON lv.LOOKUP_VALUE_ID =i.DEVICE_TYPE_ID LEFT OUTER JOINdbo.LOOKUP_VALUE fv ON fv.LOOKUP_VALUE_ID =i.FACILITY_ID LEFT OUTER JOINdbo.LOOKUP_VALUE vv ON vv.LOOKUP_VALUE_ID =i.VENDOR_ID LEFT OUTER JOINdbo.LOOKUP_VALUE mv ON mv.LOOKUP_VALUE_ID =i.MODEL_ID LEFT OUTER JOINdbo.LOOKUP_VALUE ov ON ov.LOOKUP_VALUE_ID =i.SOFTWARE_VERSION_ID LEFT OUTER JOINdbo.LOOKUP_VALUE ev ON ev.LOOKUP_VALUE_ID =i.CHECKED_OUT_BY_ID LEFT OUTER JOINdbo.LOOKUP_VALUE rv ON rv.LOOKUP_VALUE_ID =i.U_HEIGHT_ID
View 2 Replies
View Related
Aug 29, 2007
SQL server 2000
Note : GRP_ID integer
declare @SchDnGRP VARCHAR(50)
SET @SchDnGRP='29,30'
SELECT USR_ID_ARY from AD_USR_GRP Where GRP_ID in(@SchDnGRP)
i got error like
Syntax error converting the varchar value '29,30' to a column of data type int.
if need any sql server Configuration requie, please advice to me
View 5 Replies
View Related
Feb 10, 2006
I have a table(tab1) with a column(col1) of type varchar. I insert a row with an integer value(1). And when i query the table using the sql, select col1 from tab1 where col1 = 1, it works fine.
But after i insert a varchar, say 'a' and then do the same query, i get an error message saying, "Syntax error converting the varchar value 'a' to a column of data type int.". Why is this so? Please reply.
View 11 Replies
View Related
Apr 20, 2007
Hi, all
I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code.
First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]">
</asp:SqlDataSource>
And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany"
DataTextField="DisplayName" DataValueField="PayrollCompanyID">
</asp:DropDownList>
Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code:
1 Sub BindData()
2
3 Dim ds As New DataSet
4 Dim sda As SqlClient.SqlDataAdapter
5 Dim strSQL As String
6 Dim strCon As String
7
8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _
9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _
10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)"
11
12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD"
13
14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon)
15
16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString()))
17
18 sda.Fill(ds, "rsrc_Personnel")
19
20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel")
21 dgPersonnel.DataBind()
22
23 End Sub
24
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well.
What am I missing?
Thanks for anyone's help!
Cappela07
View 2 Replies
View Related
Nov 4, 2005
Well I am doing a simple insert through a stored procedure..., but I am getting this error. In my table I have the UserID as a VARCHAR 100. I am storing from a textbox that will have number because its there email. But I am not converting anything. Do I need to convert in this scenario??
Server Error in '/pickem' Application.
Syntax error converting the varchar value 'johndoe@hotmail.com' to a column of data type int. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'johndoe@hotmail.com' to a column of data type int.Source Error:
Line 113:
Line 114: dbConnection.Open
Line 115: Dim Dr As System.Data.IDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Line 116:
Line 117: Dim rowsAffected As Integer = 0
Here is the code: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("connString"))
Dim myCommand as New SqlCommand("prcAddTeam", dbConnection) myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("@UserID", SqlDbType.Varchar, 100).Value = TRIM(emailBox.Text) myCommand.Parameters.Add("@TeamName", SqlDbType.Varchar, 40).Value = TRIM(teamBox.Text) myCommand.Parameters.Add("@Address1", SqlDbType.Varchar, 50).Value = TRIM(addressBox.Text) myCommand.Parameters.Add("@Address2", SqlDbType.Varchar, 50).Value = TRIM(address2Box.Text) myCommand.Parameters.Add("@City", SqlDbType.Varchar, 50).Value = TRIM(cityBox.Text) myCommand.Parameters.Add("@State", SqlDbType.Varchar, 30).Value = stateList1.SelectedItem.Value myCommand.Parameters.Add("@Zip", SqlDbType.Varchar, 11).Value = TRIM(zipBox.Text) myCommand.Parameters.Add("@FirstName", SqlDbType.Varchar, 50).Value = TRIM(firstNameBox.Text) myCommand.Parameters.Add("@LastName", SqlDbType.Varchar, 50).Value = TRIM(lastnameBox.Text)
dbConnection.Open Dim Dr As System.Data.IDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Dim rowsAffected As Integer = 0
dbConnection.OpenStored procedure:CREATE PROCEDURE prcAddTeam @UserID VARCHAR(100) ,@TeamName VARCHAR(40) ,@Address1 VARCHAR(50) ,@Address2 VARCHAR(50) ,@City VARCHAR(50) ,@State VARCHAR(30) ,@Zip VARCHAR(11) ,@FirstName VARCHAR(50) ,@LastName VARCHAR(50) ASSET NOCOUNT ONDECLARE @ID INTSET @ID = 0SELECT @ID = ISNULL((SELECT UserID from tblUserInfo where UserID = @UserID),0)IF @ID = 0 BEGIN INSERT INTO tblUserInfo (UserID,TeamName,Address1,Address2,City,State,Zip,FirstName,LastName,CreateDate) VALUES(@UserID,@TeamName,@Address1,@Address2,@City,@State,@Zip,@FirstName,@LastName,GetDate()) SET @ID = @@IDENTITY END
View 5 Replies
View Related
Aug 31, 2006
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse
View 3 Replies
View Related
Jun 4, 2007
Probably one of the easiest queries some have seen around, but good ol' MS and their dates. I can't get around this error on this simple query. It will be a job that will run probably several times a day. I haven't even gotten passed to the second part of it....I've also seen this error is a VERY popular error, even with advanced programmers it seems. WOW. Basically, I want to keep the row count to 2 days. Well, if you have any suggestions, I'd take 'em. Thanks!
Delete from tblCallIndexes where inum in
(select inum from tblCalls where StartedAt < 'DATEADD(d,-2,GETDATE())')
View 14 Replies
View Related
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
View Related
Dec 28, 2007
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query: "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_friendship into the columns userId and buddyId which are both uniqueidentifier fields. I tested the query using the same values (@Param1, @Param1) and (@Param2, @Param2) and found out that the problem is with Param2 because when I use Param1 the insert works perfectly. The value passed by Param2 is supposed to be the id of a user which I get from the datalist which selects * from aspnet_Users, so that <%#DataBinder.Eval(Container.DataItem, 'UserId')%> should also be an Id like Param1 is. Since both params are in the form of .toString(), I don't understand why one works but the other doesn't.As you can see in the code below, both of these parameters are dimmed as strings, so I don't understand why Param2 doesn't work. Can anyone please help? Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim MyCommand2 As SqlCommand Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() Dim add As String add = "<%#DataBinder.Eval(Container.DataItem, 'UserId')%>".ToString() Session("username") = User.Identity.Name Dim InsertCmd As String = "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)" MyCommand2 = New SqlCommand(InsertCmd, MyConnection) MyCommand2.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand2.Parameters.AddWithValue("@Param2", add) MyCommand2.Connection.Open() MyCommand2.ExecuteNonQuery() MyCommand2.Connection.Close() End Sub Thank you.
View 3 Replies
View Related
Jan 5, 2008
Hello, Im Getting an error (below) at the login page. The only control on
that page is a Login control, adn the error below comes when I click login. Does anyone know what Im doing wrong? This is my login page:<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" Title="myapp- Login" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentHeader" Runat="Server"> LOGIN</asp:Content><asp:Content ID="Content2" ContentPlaceHolderID="ContentBody" Runat="Server"> <table border="1" cellpadding="0" cellspacing="0" width="305"> <tr> <td class="bodyText" colspan="2" style="height: 20px; text-align: center;"> <p> <!-- TemplateBeginEditable name="mainText" --> <asp:Login ID="Login1" runat="server" TitleText="Existing Users Log In"> </asp:Login> </p> </td> </tr> <tr> <td style="text-align: right"> <asp:LinkButton ID="lbNewmembers" runat="server" OnClick="lbNewmembers_Click">New Member?</asp:LinkButton></td> <td> <asp:LinkButton ID="lbForgotPassword" runat="server" OnClick="lbForgotPassword_Click">Forgot Password?</asp:LinkButton></td> </tr> </table></asp:Content>Error:
Server Error in '/myApp' Application.
Conversion failed when converting from a character string to
uniqueidentifier. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Conversion failed when converting from a
character string to uniqueidentifier.Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.312;
ASP.NET Version:2.0.50727.833
View 1 Replies
View Related
Nov 16, 2015
I've imported a CSV file into a table in SQL Server 2012. It's a large file, 140,000+ rows, so I couldn't covert it to Excel first to preserve the date format due to Excel's row limit. In the CSV file, there were 3 column with date data in "31-Aug-09" format, and the import automatically transformed these in "31AUG09" format (varchar(50)) in SQL Server. Now I need to convert these 3 columns from varchar to datetime so I could work with them in date format.
I've tried several things,e.g,
select
convert(datetime,
right(admdate,4)+left(admdate,2)+substring(admdate,3,3))
or
select
convert(datetime,
substring(admdate,6,4)+substring(admdate,1,2)+substring(admdate,3,3))
but keep getting the same error message (Msg 241) "Conversion failed when converting date and/or time from character string".
View 4 Replies
View Related
