Hi !
I am having a problem with MS SQL 2005
for a column
[DateInsert] [smalldatetime] NOT NULL
and that Query
INSERT INTO Users (DateInsert) VALUES (CONVERT(DATETIME,'2007-12-17',120))
I am getting a conversion error : nvarchar into smalldatetime
it seems that I am getting this error only with days > 12
2007-12-17 does not work but 2007-12-01 works
any idea ?
thank you
Hi,
when I convert a field into smalldatetime from nvarchar(50) in Sql Server 2000, i got a default value (1900-01-01 00:00:00) for that field.Actually value of that field to be changed as different values).I want to be convert bulk of records.please help me.
current data
pvdate(nvarchar(50))
------
12/03/2007
Data to be changed into
pvdate(smalldatetime)
--------------------
2007-03-12 12:00:00
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()
(...)
---
Hi, i´m have a problem with the Convert function in sql 7 enterprise
when i have the value '07-31-1967' format mm-dd-yyyy i recivied the error 296
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value
the sentence i use is
set @dfnacimien = (Convert(varchar(10),@dfnacimien,110))
Someone please help me!!!!
Hi, I'm making a webapplication in C# with MSSQL as database.
I've created a form to do an advanced search on books. The user can type in name, author, .... and he can also mark 2 dates from to Calendar objects (I made sure date one can not be bigger than date 2). I'm using smalldatetime as DBtype. The 2 selected values are DateTime in asp. The results are shown in a gridview. Since I added the feature I keep getting the same error and I can't find where it is. Here's some code:
1 public List GetBooks2(string invB,string titelB, string auteursB, string taalB, string uitgeverijB, string jaarB, string keywordsB, string categorieB, string standplaatsB, string ISBN,DateTime datum1, DateTime datum2, string sortExpression)
2 { //this is my method for the advanced search
3 using (SqlConnection oConn = new SqlConnection(_connectionString))
4 {
5 string strSql = "select * FROM Boek where 1 = 1";
6 if (!String.IsNullOrEmpty(invB)) strSql += " and (inventaris_nr like @invB)";
7 if (!String.IsNullOrEmpty(titelB)) strSql += " and (titel like @titelB)";
8 if (!String.IsNullOrEmpty(auteursB)) strSql += " and (auteurs like @auteursB)";
9 if (!String.IsNullOrEmpty(uitgeverijB)) strSql += " and (uitgeverij like @uitgeverijB)";
10 if (!String.IsNullOrEmpty(ISBN)) strSql += " and (ISBN10 like @ISBN or ISBN13 like @ISBN)";
11 if (!String.IsNullOrEmpty(standplaatsB)) strSql += " and (standplaats like @standplaatsB)";
12 if (!String.IsNullOrEmpty(jaarB)) strSql += " and (jaartal like @jaarB)";
13 if (!String.IsNullOrEmpty(keywordsB)) strSql += " and (keywords like @keywordsB)";
14 if (!String.IsNullOrEmpty(taalB))
15 if (taalB == "Andere")
16 strSql += " and (taal NOT IN ('nederlands', 'frans', 'engels', 'spaans', 'italiaans', 'duits'))";
17 if (taalB == "--Geen voorkeur--")
18 strSql += "";
19 else
20 strSql += " and (taal like @taalB)";
21
22 if (!String.IsNullOrEmpty(categorieB))
23 if (categorieB == "--Selecteer een categorie--")
24 strSql += "";
25 else
26 strSql += " and (categorie like @categorieB)";
27
28 if (datum1 == null)
29 strSql += "";
30 else
31 if (datum2 != null)
32 {
33
34 strSql+=" and datumB between @datum1 and @datum2";
35 }
36 else strSql+="";
37 if (!String.IsNullOrEmpty(sortBLOCKED EXPRESSION
38 strSql += " order by " + sortExpression;
39 else
40 strSql += " order by id";
41
42 SqlCommand oCmd = new SqlCommand(strSql, oConn);
43 oCmd.Parameters.Add(new SqlParameter("@invB", "%" + invB + "%"));
44 oCmd.Parameters.Add(new SqlParameter("@titelB", "%" + titelB + "%"));
45 oCmd.Parameters.Add(new SqlParameter("@auteursB", "%" + auteursB + "%"));
46 oCmd.Parameters.Add(new SqlParameter("@taalB", "%" + taalB + "%"));
47 oCmd.Parameters.Add(new SqlParameter("@uitgeverijB", "%" + uitgeverijB + "%"));
48 oCmd.Parameters.Add(new SqlParameter("@jaarB", "%" + jaarB + "%"));
49 oCmd.Parameters.Add(new SqlParameter("@keywordsB", "%" + keywordsB + "%"));
50 oCmd.Parameters.Add(new SqlParameter("@categorieB", categorieB ));
51 oCmd.Parameters.Add(new SqlParameter("@standplaatsB", "%" + standplaatsB + "%"));
52 oCmd.Parameters.Add(new SqlParameter("@ISBN", "%" + ISBN + "%"));
53 oCmd.Parameters.Add(new SqlParameter("@datum1", "%" + datum1 + "%"));
54 oCmd.Parameters.Add(new SqlParameter("@datum2", "%" + datum2 + "%"));
55
56
57 oConn.Open();
58 SqlDataReader oReader = oCmd.ExecuteReader();
59 List boeken = GetBoekCollectionFromReader(oReader);
60 oReader.Close();
61 return boeken;
62 }
63 }
I think that that method is correct, not sure though... The code for GetBoekCollectionFromReader(oReader) is this=1 protected List GetBoekCollectionFromReader(IDataReader oReader)
2 {
3 List boeken= new List();
4 while (oReader.Read()) //THIS IS WHERE THE ERROR APPEARS
5 {
6 boeken.Add(GetBoekFromReader(oReader));
7
8 }
9 return boeken;
10 }
That's the method where the error appears... Where should I place a breakpoint to get the exact location? To make sure all methods in this code are explained, here's the code for GetBoekFromReader(oReader))= 1 protected Boek GetBoekFromReader(IDataRecord oReader)
2 {
3 Boek boek = new Boek();
4 boek.idB= (int)oReader["id"];//id auto generated dus verplicht
5 if(oReader["inventaris_nr"] != DBNull.Value)
6 boek.Inventaris_nrB = (string)oReader["inventaris_nr"];
7 if (oReader["auteurs"] != DBNull.Value)
8 boek.AuteursB = (string)oReader["auteurs"];
9 boek.TitelB = (string)oReader["titel"];//titel verplicht
10 if (oReader["taal"] != DBNull.Value)
11 boek.TaalB = (string)oReader["taal"];
12 if (oReader["uitgeverij"] != DBNull.Value)
13 boek.UitgeverijB = (string)oReader["uitgeverij"];
14 if (oReader["aantal_p"] != DBNull.Value)
15 boek.Aantal_pB = (string)oReader["aantal_p"];
16 if (oReader["jaartal"] != DBNull.Value)
17 boek.JaartalB = (int)oReader["jaartal"];
18 if (oReader["keywords"] != DBNull.Value)
19 boek.KeywordsB = (string)oReader["keywords"];
20 if (oReader["categorie"] != DBNull.Value)
21 boek.CategorieB = (string)oReader["categorie"];
22 if (oReader["standplaats"] != DBNull.Value)
23 boek.StandplaatsB = (string)oReader["standplaats"];
24 if (oReader["ISBN13"] != DBNull.Value)
25 boek.ISBN13 = (string)oReader["ISBN13"];
26 if (oReader["ISBN10"] != DBNull.Value)
27 boek.ISBN10 = (string)oReader["ISBN10"];
28 if (oReader["URL"] != DBNull.Value)
29 boek.UrlB = (string)oReader["URL"];
30 if (oReader["username"] != DBNull.Value)
31 boek.UsernameB = (string)oReader["username"];
32 if (oReader["passwoord"] != DBNull.Value)
33 boek.PasswoordB = (string)oReader["passwoord"];
34 if (oReader["datumB"] != DBNull.Value)
35 boek.DatumBoek = (DateTime)oReader["datumB"];
36 if (oReader["status"] != DBNull.Value)
37 boek.StatusB = (string)oReader["status"];
38
39
40 return boek;
41 }
Conversion failed when converting datetime from character string.
That's the error I get by the way. It also appears when I do a search and I don't use the date function... for example when I only fill in the title textbox and I don't select any dates from the calendars...
Sorry for the long post, but I think it's the only way to get a clear view on it...
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!
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
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..... ?
Hi, I have created a database using VWD to keep values of urls and have structured it as...
Prefix (http://, network name), address(www.name.com), and name (name of address), the address field has been defined as a nvarchar(MAX).
Most of the addresses updated into the address field work, except something like: www.java-scripts.net/javascripts/Image-Rollover-Script.phtml.
I get this error:
Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.
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: Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.Source Error:
Line 1176: if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)
Line 1177: != System.Data.ConnectionState.Open)) {
Line 1178: this.Adapter.InsertCommand.Connection.Open();
Line 1179: }
Line 1180: try {
I can insert something like www.google.com into the addresses field without any errors. Any ideas why?If it is a nvarchar type it should be able to except all sorts of characters??
Hi,
Not sure if this is the correct forum as it relates to both SQL Express and VB Express?
Can you help with this please:
Using SQL Server Management Studio Express I can write the following SQL script which allows me to search the description column for data that contains the word "display". The declare @filter nvarchar(5) lets me set a fuzzy search level, how many characters need to match in the word.
This works fine, and changing the nvarchar(5) value allows me change the number of characters that need to match.
use ssop
declare @filter nvarchar(5)
set @filter='display'
select stockid,description
from stock
where description like '%' + @filter + '%'
The issue:
Within VB Express TableAdapter configuration wizard I can script the following and it works, however, I am unable to script the nvarchar(5) , how would I add this function into the script below?
SELECT stockID, partNo, description, qty, unitcost, location, min, image, date
FROM stock
WHERE (description LIKE '%' + @filter + '%')
Thankyou in advanced
RON
I am hitting error to convert nvarchar to numeric.
my data as below:
2721.000000000000
How can I convert to be just 2721?
Hi,
I used to work with Sql 2000 and I could easily import a text file into my database and then convert the fields (all nvarchar) to appropriate data types. However, this procedure does not work with Sql 2005.
After I import a text file to Sql 2005 and when I convert nvarchar to numeric, I get "Error converting data type nvarchar to numeric".
Could you please let me know how can I fix this issue. Thanks in advance for your reply.
Hi Group,I am new with SQL Server..I am working with SQL Server 2000.I am storing the date in a nvarchar column of atable.... Now I want toshow the data of Weekends..Everything is OK...But the problem isarising with Conversion of nvarchar to date...to identify theweekends...Like..Here DATEVALUE is a nvarchar column...But getting theerror..Value of DATEVALUE like dd-mm-yyyy...04-08-2004-----------------------------------------------------------Server: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type datetime.---------------------------------------------------------------------------Actual Query-------------------------------Select DATEVALUE,<Other Column Names> from Result whereDatepart(dw,convert(Datetime,DATEVALUE))<>1 andDatepart(dw,convert(Datetime,DATEVALUE))<>7-----------------------------------------------------------Thanks in advance..RegardsArijit Chatterjee
View 3 Replies View RelatedI'm trying to export data to an Excel destination. I'm receving the following error on a notes column which is an nvarchar(max) datatype.
Error: 0xC0202025 at Data Flow Task, Excel Destination [124]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
I realize that the charcater limit on an Excel cell is 32, 767 characters, but none of the test data that I'm using is close to that. I've also tried using ntext and receive the same error. When I change the datatype to nvarchar(4000), it works fine. I've also changed the destination to a flat file using nvarchar(max) and I don't receive the error. Unfortunately, our vendor specifies that they have to have an Excel file. Does anyone know what is causing this error, if it's a bug, or have any suggestions?
Thanks,
Wendy Schuman
Hi i keep getting this error when i search based on Team name (dropdownlist) or coach name(textbox). However it works when i make the search based on the region id(dropdownlist) here is my stored procedure;ALTER PROCEDURE [dbo].[stream_FindTeam]
-- Add the parameters for the stored procedure here
@coachName varchar(100),
@TeamName varchar(100),@regionID INT
AS
SELECT TeamID, coachName FROM Teams WHERE coachName LIKE @coachName OR TeamName= @TeamNameOR regionID = @regionID;
Here is my code;SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
conn.Open();SqlCommand command = new SqlCommand("stream_FindTeam", conn);
command.CommandType = CommandType.StoredProcedure;command.Parameters.AddWithValue("@coachName", coachName.Text);
command.Parameters.AddWithValue("@TeamName", TeamList.SelectedValue);command.Parameters.AddWithValue("@regionID", Region.SelectedValue);SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
HI,
I HAVE BEEN TRYING TO TRANSFORM AN OLD TABLE TO A NEW FORMAT AND CHANGE SOME OF THE DATATYPE FORMATS USED IN THE OLD ONE.
OUT OF WHICH ONE IS A COLUMN CALLED AS FORM_RECEIVE_DATE WHICH HAS NVARCHAR(41) AS DATATYPE IN THE OLD TABLE CREATED BY A PREVIOUS DBA (DON'T KNOW wHY?)
wHILE CONVERTING IT INTO DATATYPE DATETIME , I AM GETTING THIS ERROR :- "Arithmetic overflow error converting expression to data type datetime." i DON'T KNOW WHY
hERE ARE FEW EXAMPLES OF THE DATA CONTAINED IN THE PREVIOUS TABLE :-
05082003
05062003
05142003
COULD YOU PLS TELL ME A WAY TO SOLVE THIS ?
Hi,
I get "Error converting data type nvarchar to int." It is some kind of SQL exception. This is the code in my application.
SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
Object start;
string activityID = Session["activitygroupID"].ToString();
cmd.Parameters.AddWithValue("@ActivityGroupID", activityID);
cmd.CommandText = "get_startdate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
SqlDataReader reader = cmd.ExecuteReader(); //The code stops here!!!
start = reader["StartDate"];
sqlConnection1.Close();
The stored procedure on the SQL-server looks like so. StartDate is of DateTime type.
ALTER PROCEDURE [dbo].[get_startdate]
-- Add the parameters for the stored procedure here
@ActivityGroupID int
AS
BEGIN
SELECT StartDate
FROM dbo.CurrentGroup
WHERE ActivityGroupID = @ActivityGroupID
END
I appreciate any help!
I getting the above error can someone please help me solve it, here is the code:
public void InsertHost() { // TODO // - Call stored procedure to write to a log file writeToLog using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) { SqlCommand cmd = new SqlCommand("writeToLog", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@pAction", "action"); cmd.Parameters.AddWithValue("@pHostName", "Hostname"); cmd.Parameters.AddWithValue("@pUserNUm", "requestorID"); cn.Open(); cmd.ExecuteNonQuery(); } }
Here is the storedprocedure:
ALTER PROCEDURE dbo.writeToLog(@pAction varchar(10), @pUserNUm bigint, @pHostName varchar(25))AS INSERT INTO dbo.hostNameLog (action, requestorID, HostName)VALUES (@pAction, @pUserNUm, @pHostName)
Here is the table:
HostName - varchar, action - varchar, requestorID - bigint
I can't seem to find the error.
Hi
I have an insert stored procedure with a parameter @eventDate date type date time. I have written a web form that takes the textbox.text value and assigns to the @eventDate parameter using a querystring. Howevr when I run the application I get a SQL Exception error converting data type nvarchar to datetime.
I have tried dssqlInsertEvent.InsertParameters.add("EventDate", convert.todatetime(txtEventDate.text)); wbut received another error.
Any assistance would be greatly appreciated.
I am using MS SQL Server 2008R2 along with VB 2010.The first question is: why is it even trying to convert anything to numeric? I have NO numeric data types. And I don't have any nvarchar data types either. I'm very confused.Doesn't nchar include any and all characters, in any combination? Should I change everything to text data type? Maybe something else? Some values are going to be blank. The Lab/Source Lots have numbers, letters and dashes.
My stored procedure:
ALTER PROCEDURE dbo.MChemsInsert
(
@LabLot nchar(10),
@Chem nchar(50),
@Source nchar(50),
@SourceLot nchar(10),
[code]....
I am using rowVersion to detect rows that need to be re-extracted for my ETL. Â The first step in the ETL is to get MIN_ACTIVE_ROWVERSION() from the database, as well as the rowVersion from the last successful ETL run. Â Both of these values are saved in variables in SSIS. Â Since SSIS doesn't have an associated data type, these values are converted to nvarchar(512) before getting sent to SSIS. Â
I'm now trying to create a stored procedure to grab the data out of the source system. Â The sproc takes RowVersionMin and RowVersionMax as input parameters. Â Those parameters are then converted to varbinary(max), and I use them in a query to select the appropriate rows.
When I run the sproc in SSMS, it runs without a problem. Â However, when I try to use the sproc as a data source in SSIS, I get the following error: "Error converting data type nvarchar to varbinary". Why SSIS has a problem with this, but SSMS does not? Â The sproc code looks like this:
ALTER PROCEDURE [dbo].[dw_DimComment_DataLoad]
@RunType varchar(3),
@RowVersionMin nvarchar(512),
@RowVersionMax nvarchar(512)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
Hi,
I'm pretty desperat after trying solving the above problem in several days. I hope somone here can help me :-)
I have this stored procedure. When I try to Execute the storedprocedure where I have a date as parameter I get the above error.
I checked (many times), that the input parameter is a datetime format (30-05-2008) and also I checked the tabel and it is datetime format. I checked the table to see if it was english datetime format - but all the datetimes that are listet are listed in danish datetime format DD-MM-YYYY.
In the stored procedure I try this:
fProjectSkuTimeStamp >= @ReducedTime
I can't figure out where it gets that nvarchar from?
My stored procedure look like this:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
set NOCOUNT ON
go
ALTER PROCEDURE [dbo].[GetProjectSkuList]
@fProjectFId AS INTEGER = null, --ProjektId
@ReducedTime AS DATETIME --Tidsrum fra
AS
BEGIN
SELECT tProjectSku.fProjectSkuPId, tProjectSku.fCompanyFId, tProjectSku.fProjectFId,
tProjectSku.fProjectSkuDate,
tProjectSku.fOurContactFId, tProjectSku.fOurContactF2Id, tProjectSku.fOrderFId,
tProjectSku.fProjectSkuTimeStamp,
tProjectSku.fProjectSkuNote1, tProjectSku.fProjectSkuNote2, tProjectSku.fContactFId,
tProjectSku.fStoreTransactionsFId,
tProjectSku.fProjectSkuSalesPrice, tCompany.fCompanyName, tProject.fProjectName,
tOurContact.fOurContactFirstName + ' ' + tOurContact.fOurContactLastName AS CreatedBy,
tOurContact_1.fOurContactFirstName + ' ' + tOurContact_1.fOurContactLastName AS Responsible,
tContact.fContactFirstName + ' ' + tContact.fContactLastName AS ContactFullName,
tStoreTransactions.fStoreTransactionsCostPrice,
tStoreTransactions.fStoreTransactionsSerialNo
FROM tOurContact AS tOurContact_1 RIGHT OUTER JOIN
tContact RIGHT OUTER JOIN
tProjectSku LEFT OUTER JOIN
tStoreTransactions ON tProjectSku.fStoreTransactionsFId = tStoreTransactions.fStoreTransactionsPId ON
tContact.fContactPId = tProjectSku.fContactFId LEFT OUTER JOIN
tOrder ON tProjectSku.fOrderFId = tOrder.fOrderPId ON tOurContact_1.fOurContactPId = tProjectSku.fOurContactF2Id LEFT OUTER JOIN
tOurContact ON tProjectSku.fOurContactFId = tOurContact.fOurContactPId LEFT OUTER JOIN
tProject ON tProjectSku.fProjectFId = tProject.fProjectPId LEFT OUTER JOIN
tCompany ON tProjectSku.fCompanyFId = tCompany.fCompanyPId
WHERE
(tProjectSku.fProjectFId = @fProjectFId) AND
--fProjectSkuTimeStamp >= CONVERT(datetime, @ReducedTime, 120)
fProjectSkuTimeStamp >= @ReducedTime
ORDER BY tProjectSku.fProjectSkuPId DESC
END
Kind regards,
simsen :-)
In the following code I want to compare 2 values: AccessVal and SQLVal. The values are stored as nvarchars, so I'm isolating the numeric values in a subquery. Notice I'm only selecting 1 row. The commented line, where I compare the values, is throwing the error.
SELECT QA_AutoID, AccessVal, SQLVal
,ROUND(ABS(CONVERT(float, AccessVal,1)),0) as AccessFloat
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) as SQLFloat
FROM QA
WHERE QA_AutoID in (
SELECT TOP 1 QA_AutoID
FROM QA
WHERE ISNUMERIC(SQLVal) = 1 AND ISNUMERIC(AccessVal) = 1
)
--AND ROUND(ABS(CONVERT(float, AccessVal,1)),0) <> ROUND(ABS(CONVERT(float, SQLVal,1)),0)
ORDER BY ROUND(ABS(CONVERT(float, AccessVal,1)),0) DESC
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) DESC
Here is the output with the comparison commented out...
Here's what I get with the comparison line activated:
I've tried converting to numeric, int and bigint instead of float. I've tried CAST instead of CONVERT. Nothing works.
HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub
Hi, i have a table in sqlexpress named Contacts:
ID (int) -primary key-
name (varchar(30))
lastname (varchar(30))
phone (varchar(15))
fax (varchar(15))
desc (text)
In my default.aspx page, i have a GridView that has the conecction to this table. The GridView has the Editing and Deleting checkbox enabled but my problem is that i can't edit or delete any row when the page is running and the massage is this: "The data types text and nvarchar are incompatible in the equal to operator"
It would have to work, but i don't know what happen, Please, any help!
I am getting the above error when trying to add a dataset to my report. Here is my stored procedure below.
USE [HSIU_TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [ssrs].[spKEMHWaitTimeCaseList]
[Code] .....
Hi,
I can't seem to fix the following error in my stored procedure.
Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.
The line of code in my stored procedure that seems to be the problem is the following:
CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END
Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.
I had this question for quite a long time.
It seems the latter one don't take any extra storage space than the previous one.
As long as the real string length is less than 10.
Is that mean the latter one not cost anything?
I once heard the different is when they are in memory. But not sure of it.
Can anyone explain it and provide some official reference on it?
Thank.
/* INFO USED HERE WAS TAKEN FROM http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 */
DECLARE @X VARCHAR(10)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Num_Members SMALLINT
SELECT @X = 'x.dbo.v_NumberofMembers'
DECLARE @SQLString AS VARCHAR(500)
SET @SQLString = 'SELECT @Num_MembersOUT=Num_Members FROM @DB'
SET @ParmDefinition = '@Num_MembersOUT SMALLINT OUTPUT'
EXECUTE sp_executesql <-LINE 11
@SQLString,
@ParmDefinition,
@DB = @X,
@Num_MembersOUT = @Num_Members OUTPUT
Just Need Help On This Error
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I dont know why im getting a errrror b/c I followed http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 exactly
ALTER procedure [dbo].[findConsultantMail]
(
@PerID numeric(18,0),
@perMail nvarchar(100) OUTPUT
)
as
SELECT @perMail=PerMail FROM Personel
WHERE (PerID =@PerID)
return @perMail
I want to get Email address from sql database.
But whenever I executed stored procedure I get an error message
"Conversion failed when converting the nvarchar value 'xxxxxx@xxxxxx' to data type int"
If I want some numeric ID it is worked.
I also change my SP like this but results same.
ALTER procedure [dbo].[findConsultantMail]
(
@PerID nvarchar(18),
@perMail nvarchar(100) OUTPUT
)
as
SELECT @perMail=PerMail FROM Personel
WHERE (PerID =cast(@PerID as numeric(18,0)))
return cast(@perMail as nvarchar(100))
How can I get a string value form stored procedure.
When I run the following sql query:
"update table set price = price * 1.1 "..
I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."
The table is set to nvarchar, and i am just trying to make the prices go up 10%.
im getting this error while inserting into table (description of table given below)
mytable
mycode smallint identity,
mydataitem varchar(100),
mydatefrom smalldatetime,
mydateto smalldatetime,
myopcode smallint,
lastupdate smalldatetime
my sql statement was
INSERT INTO mytable
(mydataitem,mydatefrom,mydateto,myopcode,lastupdat
e)
VALUES
('SAMPLEDATA','01/01/2003','18/05/2003',1,'05/Mar/2004')
THE ERROR RETURNED IN MY CLIENT APP IS
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
AND THAT IN QA IS
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated.
can ne 1 point out the mistake in my sql statement ?
__________________
Cheers....
baburajv
I have an Execute SQL Task that pulls the max date from a sql table.
SELECT max(date_Idx) FROM dbo.FactDailyInventorySnapshot
This field is defined as a smalldatetime. I want to store this max date in a variable in SSIS called LastDate.
Then in a data flow, in an OLE DB Source, I want to use it as a parm in a sql command to compare to a smalldatetime field.
My question is, what type of variable do I declare it in in SSIS, and what is the correct parameter mapping inside of my sql query? I thought I had exhausted all combinations of variable types and parameter types. I have even tried casting it as different data types when I do the exec sql task of getting the max date. Any help would be appreciated. Thanks!