Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, that should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value for the identity
column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Monday, March 26, 2012
Error inserting record in IDENTITY column
Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Error inserting record in IDENTITY column
Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Error Including null value in a Numeric field
Hi All,
I'm migrating some SQL 2000 DTS to SSIS.
I am transfering data from a DB2 table to a SQL 2005 table using the OLE DB Source, Data Converstion then the OLE DB Destionation.
So, I have a numeric (Precision 3, Scale 2) field with NULL value in the DB2 table.
I'm trying to transfer these data to a SQL2005 table and I am receiving this error message below:
"[Destination Table TFACIL [18]] Error: There was an error with input column "COMB_OPPT_PRCT" (2865) on input "OLE DB Destination Input" (31). The column status returned was: "The value violated the integrity constraints for the column.". "
The field must accept null because of the APPLICATION ( i can't change it, im not the owner ).
Could someone help me?
Thanks in advance.
Regards,
Thiago
Check that the SQL table TFACIL.COMB_OPPT_PRCT
1) Has no CHECK constraints on it that would prevent NULL being loaded
2) is not defined as NOT NULL
Thursday, March 22, 2012
error in updating field thru stor_proc
I'm trying to update 'password' field in a table thru a stored procedure which is called from my program.
I'm passing the values for password and the key fields.
While debugging I can see the values in each parameter, but I get an error that the 'value for password was not passed'.
Here's the program snippet for calling the stored proc.
All three parameters are 'varchar' type
dbConn.Open();
dbConn.CreateParameters(3);
dbConn.AddParameters(0, Pwd, newPwd);
dbConn.AddParameters(1, Login, pwd_Login);
dbConn.AddParameters(2, IdNum, pwd_IdNum);
result = dbMgr.ExecuteNonQuery(CommandType.StoredProcedure, SP_UPDPWD);
--
EXEC @.return_value = [dbo].[usp_UsrMstUpdPwdParaPwdEmailCaseNum]
@.Pwd = N'3Sg7vqowIBRdfgdfgrgdjykFTjTFt5hfHfhFtFghzIG1haWRliBuYW1lPw==',
@.Login = N'xyz@.abc.com',
@.IdNumber = N'00009'
--
What am I missing?
Is Pwd a variable or constant set to "@.Pwd", login to "@.Login", IdNum to "@.IdNum", and "SP_UPDPWD" to the appropriate string for your stored procedure?
Also, is this using an IDBManager pattern?
Thanks,
John
|||Never mind. found the problem
The parameter for Password was not spelt right!
Error in stored procedure
I have a stored procedure that will copy data from one table to another, but
I am having problem doing so from a 'text' field from table 1 to table 2.
Here is my SP:
<%
Dim Addtemplates__FirmId
Addtemplates__FirmId = "4"
if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("FirmId")
Dim Addtemplates__CaseId
Addtemplates__CaseId = "0"
if(Request.Querystring("caseid") <> "") then Addtemplates__CaseId =
Request.Querystring("caseid")
Dim Addtemplates__SecLtr
Addtemplates__SecLtr = "0"
if(Request.Querystring("caseid") <> "") then Addtemplates__SecLtr =
Request.Querystring("caseid")
Dim Addtemplates__LtrName
Addtemplates__LtrName = "0"
if(Templatesfiltered("LtrName") <> "") then Addtemplates__LtrName =
Templatesfiltered("LtrName")
Dim Addtemplates__LtrBody
Addtemplates__LtrBody = "0"
if(Templatesfiltered("LtrBody") <> "") then Addtemplates__LtrBody =
Templatesfiltered("LtrBody")
%>
<%
set Addtemplates = Server.CreateObject("ADODB.Command")
Addtemplates.ActiveConnection = MM_eimmigration_STRING
Addtemplates.CommandText = "dbo.Addtemplatestocase"
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.RETURN_VALUE",
3, 4)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.FirmId", 3,
1,4,Addtemplates__FirmId)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.CaseId", 3,
1,4,Addtemplates__CaseId)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.SecLtr", 200,
1,50,Addtemplates__SecLtr)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrName", 200,
1,100,Addtemplates__LtrName)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrBody", 200,
1,16000,Addtemplates__LtrBody)
Addtemplates.CommandType = 4
Addtemplates.CommandTimeout = 0
Addtemplates.Prepared = true
Addtemplates.Execute()
%>
LtrBody is the field wich is 'text'. If I change it to be nvarchar 4000 it
works fine, but I need more space that is why I need it to be text.
This is the error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
Line # 128 is: Addtemplates.Execute()
Any clues with the information above ?
AleksBlob must be handled differently than regular string. See these for some
info.
HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/d_efau...b;en-us;1949_75
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/d_efau...;EN-US;q258_038
--
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I have a stored procedure that will copy data from one table to another,
>but I am having problem doing so from a 'text' field from table 1 to table
>2.
> Here is my SP:
> <%
> Dim Addtemplates__FirmId
> Addtemplates__FirmId = "4"
> if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("FirmId")
> Dim Addtemplates__CaseId
> Addtemplates__CaseId = "0"
> if(Request.Querystring("caseid") <> "") then Addtemplates__CaseId =
> Request.Querystring("caseid")
> Dim Addtemplates__SecLtr
> Addtemplates__SecLtr = "0"
> if(Request.Querystring("caseid") <> "") then Addtemplates__SecLtr =
> Request.Querystring("caseid")
> Dim Addtemplates__LtrName
> Addtemplates__LtrName = "0"
> if(Templatesfiltered("LtrName") <> "") then Addtemplates__LtrName =
> Templatesfiltered("LtrName")
> Dim Addtemplates__LtrBody
> Addtemplates__LtrBody = "0"
> if(Templatesfiltered("LtrBody") <> "") then Addtemplates__LtrBody =
> Templatesfiltered("LtrBody")
> %>
> <%
> set Addtemplates = Server.CreateObject("ADODB.Command")
> Addtemplates.ActiveConnection = MM_eimmigration_STRING
> Addtemplates.CommandText = "dbo.Addtemplatestocase"
> Addtemplates.Parameters.Append
> Addtemplates.CreateParameter("@.RETURN_VALUE", 3, 4)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.FirmId", 3,
> 1,4,Addtemplates__FirmId)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.CaseId", 3,
> 1,4,Addtemplates__CaseId)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.SecLtr",
> 200, 1,50,Addtemplates__SecLtr)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrName",
> 200, 1,100,Addtemplates__LtrName)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrBody",
> 200, 1,16000,Addtemplates__LtrBody)
> Addtemplates.CommandType = 4
> Addtemplates.CommandTimeout = 0
> Addtemplates.Prepared = true
> Addtemplates.Execute()
> %>
>
> --
> LtrBody is the field wich is 'text'. If I change it to be nvarchar 4000 it
> works fine, but I need more space that is why I need it to be text.
> This is the error:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
> Line # 128 is: Addtemplates.Execute()
> Any clues with the information above ?
> Aleks
>|||I get this with your link: The system cannot find the file specified.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
> Blob must be handled differently than regular string. See these for some
> info.
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> --
>
>
>
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||Also there is nothing on how to handle it with a stored procedure.
A
"oj" <nospam_ojngo@.home.com> wrote in message
news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
> Blob must be handled differently than regular string. See these for some
> info.
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> --
>
>
>
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||hmmm...blame it on copy & paste from archive via google. ;)
here are the corrected urls.
http://support.microsoft.com/kb/194975
http://support.microsoft.com/kb/258_038
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eo1h9AKUFHA.1796@.TK2MSFTNGP15.phx.gbl...
>I get this with your link: The system cannot find the file specified.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
>|||If you want to manipulate blob inside a sql stored proc, you would want to
use updatetext/writetext.
http://msdn.microsoft.com/library/e..._ua-uz_6i2c.asp
9" target="_blank">http://groups.google.co.uk/group/co...fbdf01
9
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eL9prBKUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Also there is nothing on how to handle it with a stored procedure.
> A
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
>
I am having problem doing so from a 'text' field from table 1 to table 2.
Here is my SP:
<%
Dim Addtemplates__FirmId
Addtemplates__FirmId = "4"
if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("FirmId")
Dim Addtemplates__CaseId
Addtemplates__CaseId = "0"
if(Request.Querystring("caseid") <> "") then Addtemplates__CaseId =
Request.Querystring("caseid")
Dim Addtemplates__SecLtr
Addtemplates__SecLtr = "0"
if(Request.Querystring("caseid") <> "") then Addtemplates__SecLtr =
Request.Querystring("caseid")
Dim Addtemplates__LtrName
Addtemplates__LtrName = "0"
if(Templatesfiltered("LtrName") <> "") then Addtemplates__LtrName =
Templatesfiltered("LtrName")
Dim Addtemplates__LtrBody
Addtemplates__LtrBody = "0"
if(Templatesfiltered("LtrBody") <> "") then Addtemplates__LtrBody =
Templatesfiltered("LtrBody")
%>
<%
set Addtemplates = Server.CreateObject("ADODB.Command")
Addtemplates.ActiveConnection = MM_eimmigration_STRING
Addtemplates.CommandText = "dbo.Addtemplatestocase"
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.RETURN_VALUE",
3, 4)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.FirmId", 3,
1,4,Addtemplates__FirmId)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.CaseId", 3,
1,4,Addtemplates__CaseId)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.SecLtr", 200,
1,50,Addtemplates__SecLtr)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrName", 200,
1,100,Addtemplates__LtrName)
Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrBody", 200,
1,16000,Addtemplates__LtrBody)
Addtemplates.CommandType = 4
Addtemplates.CommandTimeout = 0
Addtemplates.Prepared = true
Addtemplates.Execute()
%>
LtrBody is the field wich is 'text'. If I change it to be nvarchar 4000 it
works fine, but I need more space that is why I need it to be text.
This is the error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
Line # 128 is: Addtemplates.Execute()
Any clues with the information above ?
AleksBlob must be handled differently than regular string. See these for some
info.
HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/d_efau...b;en-us;1949_75
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/d_efau...;EN-US;q258_038
--
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I have a stored procedure that will copy data from one table to another,
>but I am having problem doing so from a 'text' field from table 1 to table
>2.
> Here is my SP:
> <%
> Dim Addtemplates__FirmId
> Addtemplates__FirmId = "4"
> if(Session("FirmId") <> "") then Addtemplates__FirmId = Session("FirmId")
> Dim Addtemplates__CaseId
> Addtemplates__CaseId = "0"
> if(Request.Querystring("caseid") <> "") then Addtemplates__CaseId =
> Request.Querystring("caseid")
> Dim Addtemplates__SecLtr
> Addtemplates__SecLtr = "0"
> if(Request.Querystring("caseid") <> "") then Addtemplates__SecLtr =
> Request.Querystring("caseid")
> Dim Addtemplates__LtrName
> Addtemplates__LtrName = "0"
> if(Templatesfiltered("LtrName") <> "") then Addtemplates__LtrName =
> Templatesfiltered("LtrName")
> Dim Addtemplates__LtrBody
> Addtemplates__LtrBody = "0"
> if(Templatesfiltered("LtrBody") <> "") then Addtemplates__LtrBody =
> Templatesfiltered("LtrBody")
> %>
> <%
> set Addtemplates = Server.CreateObject("ADODB.Command")
> Addtemplates.ActiveConnection = MM_eimmigration_STRING
> Addtemplates.CommandText = "dbo.Addtemplatestocase"
> Addtemplates.Parameters.Append
> Addtemplates.CreateParameter("@.RETURN_VALUE", 3, 4)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.FirmId", 3,
> 1,4,Addtemplates__FirmId)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.CaseId", 3,
> 1,4,Addtemplates__CaseId)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.SecLtr",
> 200, 1,50,Addtemplates__SecLtr)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrName",
> 200, 1,100,Addtemplates__LtrName)
> Addtemplates.Parameters.Append Addtemplates.CreateParameter("@.LtrBody",
> 200, 1,16000,Addtemplates__LtrBody)
> Addtemplates.CommandType = 4
> Addtemplates.CommandTimeout = 0
> Addtemplates.Prepared = true
> Addtemplates.Execute()
> %>
>
> --
> LtrBody is the field wich is 'text'. If I change it to be nvarchar 4000 it
> works fine, but I need more space that is why I need it to be text.
> This is the error:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
> Line # 128 is: Addtemplates.Execute()
> Any clues with the information above ?
> Aleks
>|||I get this with your link: The system cannot find the file specified.
"oj" <nospam_ojngo@.home.com> wrote in message
news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
> Blob must be handled differently than regular string. See these for some
> info.
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> --
>
>
>
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||Also there is nothing on how to handle it with a stored procedure.
A
"oj" <nospam_ojngo@.home.com> wrote in message
news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
> Blob must be handled differently than regular string. See these for some
> info.
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
> Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> --
>
>
>
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:OFWS3gFUFHA.3280@.TK2MSFTNGP09.phx.gbl...
>|||hmmm...blame it on copy & paste from archive via google. ;)
here are the corrected urls.
http://support.microsoft.com/kb/194975
http://support.microsoft.com/kb/258_038
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eo1h9AKUFHA.1796@.TK2MSFTNGP15.phx.gbl...
>I get this with your link: The system cannot find the file specified.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
>|||If you want to manipulate blob inside a sql stored proc, you would want to
use updatetext/writetext.
http://msdn.microsoft.com/library/e..._ua-uz_6i2c.asp
9" target="_blank">http://groups.google.co.uk/group/co...fbdf01
9
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eL9prBKUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Also there is nothing on how to handle it with a stored procedure.
> A
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OL2MYAHUFHA.260@.TK2MSFTNGP12.phx.gbl...
>
Wednesday, March 21, 2012
Error In SQL Statement
Hi, I m Trying TO use A sql insert Query but it showing an error
i m trying to insert value in Filed Name PNR from Str.text and Coresspond Field Name PNR1 valuse is 1 less than from pnr.text and PNR1 is a Auto Number Field
my code for insert query is
Dim q1As OleDb.OleDbCommand =New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)
and Error is Shown by browser is as follows
Server Error in '/WebApplication1' Application.
Missing semicolon (;) at end of SQL statement.
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.OleDb.OleDbException: Missing semicolon (;) at end of SQL statement.
Source Error:
Line 466: con.Open()Line 467: Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)Line 468: q1.ExecuteNonQuery()Line 469: con.Close()Line 470: End SubSource File:C:\Inetpub\wwwroot\WebApplication1\2.aspx.vb Line:468
Stack Trace:
[OleDbException (0x80040e14): Missing semicolon (;) at end of SQL statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66 WebApplication1._2.Button2_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\WebApplication1\2.aspx.vb:468 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
I have to ask this, even though it seems so simple. Since the error says the query is missing a semi-colon at the end of the SQL statement, did you add a semi-colon and test it?
Jeff
|||Is this SQL Server or Access?
In any case, your syntax is incorrect. Using a WHERE clause on an INSERT statement is invalid.
Also, use Parameters instead of concatenating UI-supplied text to SQL statements which will be executed.
Try modify you q1 definition.
does you pnr contain number and you would like to subtract 1 from it?
you have to cast you pnr. to integer next subtract 1 and next convert result to string and insert it into query or do it this way:
Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1=(" & pnr.Text & "-1) ",
Thanks
Monday, March 19, 2012
Error in SP using Dreamweaver
Hi,
I am calling a stored procedure from DreamWeaver, but I am getting an error,
perhaps someone can help (It has to do with the last field (text), if I use
nvarchar instead works fine.
SP:
CREATE procedure Addtemplatestocase
@.FirmId int ,@.CaseId int , @.SecLtr nvarchar(50), @.LtrName nvarchar(100),
@.LtrBody text
as
INSERT
INTO LetterMrgs (FirmId ,CaseId,SecLtr ,LtrName,LtrBody)
VALUES (@.FirmId ,@.CaseId, @.SecLtr , @.LtrName, @.LtrBody)
GO
Error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
Any help is appreciated.
AleksI use Dreamweaver all the time and use the integrated environment. Are you
using DW to try to create an SP? or calling an update sp? I would not
recomend this.
for this question:you seem to be attempting to insert a value that is larger
than the field size.
thanks
kes
"Aleks" wrote:
> Hi,
> I am calling a stored procedure from DreamWeaver, but I am getting an erro
r,
> perhaps someone can help (It has to do with the last field (text), if I us
e
> nvarchar instead works fine.
> SP:
> CREATE procedure Addtemplatestocase
> @.FirmId int ,@.CaseId int , @.SecLtr nvarchar(50), @.LtrName nvarchar(100),
> @.LtrBody text
> as
> INSERT
> INTO LetterMrgs (FirmId ,CaseId,SecLtr ,LtrName,LtrBody)
> VALUES (@.FirmId ,@.CaseId, @.SecLtr , @.LtrName, @.LtrBody)
> GO
>
> --
> Error:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
> Any help is appreciated.
> Aleks
>
>|||I know.
The stored procedure is in the Database (MS SQL 2000) I am using DW to call
the SP and pass on the paramters.
I am copying from a text field to a text field, so this should not happen. I
don't know what I am doing wrong.
Aleks
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>I use Dreamweaver all the time and use the integrated environment. Are you
> using DW to try to create an SP? or calling an update sp? I would not
> recomend this.
> for this question:you seem to be attempting to insert a value that is
> larger
> than the field size.
> thanks
> kes
> "Aleks" wrote:
>|||ok, let's try and break it down.
1. can you do the same operation out side of dream weaver?
"Aleks" wrote:
> I know.
> The stored procedure is in the Database (MS SQL 2000) I am using DW to cal
l
> the SP and pass on the paramters.
> I am copying from a text field to a text field, so this should not happen.
I
> don't know what I am doing wrong.
> Aleks
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>
>|||sorry this this posted back auto...
1. try it out of deamweaver
a. try it in wisql
b. create a cfm or asp.net quick template using the sqlname space
2. if they work it may have to do with the odbc driver. I've found that the
odbc namespace does not work as well as oledb or sqldb, but you don't have a
choice with DW. This is philosophy, but vs.net does work better with
asp.net.
I'm not a MS only person, i've used DW since it was HomeSite and still do
for all my CFM stuff and some asp.net where the code was not created in
vs.net, it works ok, but does not connect to sql as well.
thanks
kes
"Aleks" wrote:
> I know.
> The stored procedure is in the Database (MS SQL 2000) I am using DW to cal
l
> the SP and pass on the paramters.
> I am copying from a text field to a text field, so this should not happen.
I
> don't know what I am doing wrong.
> Aleks
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>
>
I am calling a stored procedure from DreamWeaver, but I am getting an error,
perhaps someone can help (It has to do with the last field (text), if I use
nvarchar instead works fine.
SP:
CREATE procedure Addtemplatestocase
@.FirmId int ,@.CaseId int , @.SecLtr nvarchar(50), @.LtrName nvarchar(100),
@.LtrBody text
as
INSERT
INTO LetterMrgs (FirmId ,CaseId,SecLtr ,LtrName,LtrBody)
VALUES (@.FirmId ,@.CaseId, @.SecLtr , @.LtrName, @.LtrBody)
GO
Error:
Technical Information (for support personnel)
a.. Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
Any help is appreciated.
AleksI use Dreamweaver all the time and use the integrated environment. Are you
using DW to try to create an SP? or calling an update sp? I would not
recomend this.
for this question:you seem to be attempting to insert a value that is larger
than the field size.
thanks
kes
"Aleks" wrote:
> Hi,
> I am calling a stored procedure from DreamWeaver, but I am getting an erro
r,
> perhaps someone can help (It has to do with the last field (text), if I us
e
> nvarchar instead works fine.
> SP:
> CREATE procedure Addtemplatestocase
> @.FirmId int ,@.CaseId int , @.SecLtr nvarchar(50), @.LtrName nvarchar(100),
> @.LtrBody text
> as
> INSERT
> INTO LetterMrgs (FirmId ,CaseId,SecLtr ,LtrName,LtrBody)
> VALUES (@.FirmId ,@.CaseId, @.SecLtr , @.LtrName, @.LtrBody)
> GO
>
> --
> Error:
> Technical Information (for support personnel)
> a.. Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /BlueDotBeta2005/Intranet/Cases/Documents/AddtemplatesSP.asp, line 128
> Any help is appreciated.
> Aleks
>
>|||I know.
The stored procedure is in the Database (MS SQL 2000) I am using DW to call
the SP and pass on the paramters.
I am copying from a text field to a text field, so this should not happen. I
don't know what I am doing wrong.
Aleks
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>I use Dreamweaver all the time and use the integrated environment. Are you
> using DW to try to create an SP? or calling an update sp? I would not
> recomend this.
> for this question:you seem to be attempting to insert a value that is
> larger
> than the field size.
> thanks
> kes
> "Aleks" wrote:
>|||ok, let's try and break it down.
1. can you do the same operation out side of dream weaver?
"Aleks" wrote:
> I know.
> The stored procedure is in the Database (MS SQL 2000) I am using DW to cal
l
> the SP and pass on the paramters.
> I am copying from a text field to a text field, so this should not happen.
I
> don't know what I am doing wrong.
> Aleks
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>
>|||sorry this this posted back auto...
1. try it out of deamweaver
a. try it in wisql
b. create a cfm or asp.net quick template using the sqlname space
2. if they work it may have to do with the odbc driver. I've found that the
odbc namespace does not work as well as oledb or sqldb, but you don't have a
choice with DW. This is philosophy, but vs.net does work better with
asp.net.
I'm not a MS only person, i've used DW since it was HomeSite and still do
for all my CFM stuff and some asp.net where the code was not created in
vs.net, it works ok, but does not connect to sql as well.
thanks
kes
"Aleks" wrote:
> I know.
> The stored procedure is in the Database (MS SQL 2000) I am using DW to cal
l
> the SP and pass on the paramters.
> I am copying from a text field to a text field, so this should not happen.
I
> don't know what I am doing wrong.
> Aleks
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:E24C59E3-5248-494B-A60D-F0F13B6EB148@.microsoft.com...
>
>
Sunday, March 11, 2012
Error in Reading Float data
I am using SQL Server 2000, VS 2003
I have Education table in which there is a field CGPA having float data type (null allowed) I retreive the data from SQL server using stroed proc and store it in SqlDataReader dr while reading if CGPA contains 0 then it raises an error that "Specified cast is not valid" other wise it does not raise any error.
while (dr.Read())
{
Education e = new Education();
e.EducationId = dr.GetInt32(0);
e.Country = dr.GetInt32(1);
e.InstitutionName = dr.GetString(2);
e.Grade = dr.GetString(3);
e.CGPA = dr.GetFloat(4); // ERROR HERE
e.Percentage = dr.GetFloat(5);
e.PassingYear = dr.GetString(6);
}
where as in Education CGPA is also the float property can any one tell me how to read 0 value of float from SQL server
GPA can be handled with Decimal or Numric data type, there are some tasks like complex calculus that require Float data type because the T-SQL functions are in Float but student grade is simple Arithmetic so you can use Decimal instead of Float. The reason Float is very unstable, long time SQL Server users use it for calculations but convert the value to Decimal for storage because you can set precision and scale with Decimal. Hope this helps.|||
Avoid floats, reals, single, doubles if at all possible in the database. They are imprecise numbers and well... They cause all kinds of weird issues. It's not SQL Server issues, it's just issues with those data types in general.
That said, I don't see how it's causing your problem. If you know the column names of the record format, try:
e.CGPA=dr("CGPA")
That assumes the field/column is named CGPA of course.
Or try:
Dim o as object
o=dr("CGPA")
e.CGPA=o
that way you can see what "o" is before you try and convert it to whatever type e.CGPA is.
|||Another option is a simple ANSI ALTER TABLE to change FLOAT to DECIMAL. Run a search for ALTER TABLE in SQL Server BOL (books online). Hope this helps.Wednesday, March 7, 2012
error in image field when using CASE statement
I've this Stored procedure on a SQLserver 2000 SP3:
SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra
this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..
--------
Microsoft SQL-DMO (ODBC SQLState: 42000)
--------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
--------
OK
--------
An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??So, are you saying that "sometimes" it works and sometimes it doesn't?
This should work "always":
SELECT *,CASE WHEN immagine IS NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra
But exclude the image field from the SELECT list, unless you really intend to use it.|||i must use it!!!
SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra
this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..
--------
Microsoft SQL-DMO (ODBC SQLState: 42000)
--------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
--------
OK
--------
An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??So, are you saying that "sometimes" it works and sometimes it doesn't?
This should work "always":
SELECT *,CASE WHEN immagine IS NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @.squadra
But exclude the image field from the SELECT list, unless you really intend to use it.|||i must use it!!!
error in execution time
hi all
i am working in sql reporting 2000.
In my report i have included field to display the execution time.
That time is not changing if i am running the report again & again.
Its only showing the time at which report was run first time in current Internet Explorer window.
how can i get current time in report?
plz help me.
In your text box, put this:
="Report Created On: " & Globals.ExecutionTime
|||Thanks for reply.
I am already using Globals.ExecutionTime to display execution time in my report .
But if i am rexecuting the report then also time is not getting updated.
If i am running other report & after that report if i am running this report then also its showing the last time at which the report was executed.
So what is the solutin for this?
Plz help me
|||The reports might have been cached on the server if you specifically have set it to keep the last run report. So it may not be running the report, but instead getting the last cached report. I haven't tinkered with the settings, but it's an idea. You might want to check the report properties and then the execution tab..Friday, February 24, 2012
error in calculated member
hi,
i want to create a calculated field in Analysis Services. But when I click on the Calculations tab, I get an error "Unexpected error occurred: 'Error in the application".
What seems to be the problem?
cherrie
Check the Calculate Script is properly there./*-- Aggregate leaf data --*/
Calculate
Thanks
Imran|||
hi,
sorry im new to this, how do i check that...
thanks!
cherrie
|||Dear Friend,
Do you still have the error?
Regards!
|||Hi,
Solved already.just versioning on dll.
Thanks!
|||Cherriesh,
When you resolve your problem with or without support of the comunity, always check the answer as resolved!
Thanks and I happy that you get it!
Regards!!
Wednesday, February 15, 2012
Error handling when one field value is bad
Here's an example of what I'm playing with:
update TestTable
set IntValue = Cast(StringValue, as int)
flag =
BEGIN TRY
0
END TRY
BEGIN CATCH
1
END CATCH
I'm trying to take a string value holding an integer and populate an integer field with that value. But, every so often, the string value does not contain an integer (e.g. '13.9" or "CA"). That error would cause the entire column UPDATE to be rolled back. i'm trying to get around it with some kind of "TRY CATCH" construct but I don't believe it can work.
What would you suggest?
Barkingdog
No, you are right this won′t work. For me, the best Exception handling is to prevent exceptions, in your case check the value for being numeric, there is a function ISNUMERIC which can do that for you. But be careful with that function, because it checks for currency numeric only, so the value "$" which is part of a currency expression will also evaluate to true.
See more details on the site: http://www.aspfaq.com/show.asp?id=2390
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)