Error during sqlDataSource.Update() instruction

Hi, I have such a problem:

I try to update (or insert) a row in my table and i fail althought i,ve read many posts here. I.ve created a button and "on_click" event to this button and want that event to update or insert a record in my table. I wrote:

protected void selectButton_Click(object sender, EventArgs e)
{
String taskID = projectsGridView.SelectedRow.Cells[0].Text;
usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@.task where [UserID]=1";
usersSqlDataSource.UpdateParameters.Add("task", taskID);
usersSqlDataSource.Update();
}

The application creates error in the last line of code (usersSqlDataSource.Update();) and i receive such an error:

You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is empty.

Pass in a valid dictionary for update or change your mode to OverwriteChanges.

For me it looks like there is a problem while setting parameters. Shall i change some properties of the sqlDataSource or GridView? Please help.. 

Not seeing the code for your SqlDataSource, it's hard to tell. However, I suspect that you need to change the SqlDataSource.ConflictDetection property to OverwriteChanges instead of CompareAllValues.

|||

Yes!! It works!!!

The beginnings are difficult, thank you very much ;)

Error converting data type varchar to numeric.

Hello,
I cannot get the following Insert Command work. I get the error:
Error converting data type varchar to numeric.
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: Error converting
data type varchar to numeric.
However 'rate' and 'maximum' variables are declared as Decimal
Dim rate As Decimal
Dim maximumAs Decimal
SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
RadioButtonList1.SelectedValue & "')"
SqlDataSource1.Insert()
CREATE TABLE Example(
userName nvarchar(50),
rate decimal(2, 2),
maximum decimal(6, 2),
ticket nchar(1)
)Try dropping the string delimiters (single quotes). Something like;
"INSERT INTO Example(userName, rate,
maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
RadioButtonList1.SelectedValue & "')"
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| Hello,
|
| I cannot get the following Insert Command work. I get the error:
|
|
| Error converting data type varchar to numeric.
| 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: Error converting
|
| data type varchar to numeric.
|
|
| However 'rate' and 'maximum' variables are declared as Decimal
|
|
| Dim rate As Decimal
| Dim maximumAs Decimal
|
|
| SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
| maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
| RadioButtonList1.SelectedValue & "')"
|
|
| SqlDataSource1.Insert()
|
|
| CREATE TABLE Example(
| userName nvarchar(50),
| rate decimal(2, 2),
| maximum decimal(6, 2),
| ticket nchar(1)
| )
||||Hi,
Already tried that. But this time I got the error:
There are fewer columns in the INSERT statement than values specified
in the VALUES clause. The number of values in the VALUES clause must
match the number of columns specified in the INSERT statement.
Dave Patrick wrote:
> Try dropping the string delimiters (single quotes). Something like;
> "INSERT INTO Example(userName, rate,
> maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
> RadioButtonList1.SelectedValue & "')"
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Dot Net Daddy" wrote:
> | Hello,
> |
> | I cannot get the following Insert Command work. I get the error:
> |
> |
> | Error converting data type varchar to numeric.
> | 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: Error converting
> |
> | data type varchar to numeric.
> |
> |
> | However 'rate' and 'maximum' variables are declared as Decimal
> |
> |
> | Dim rate As Decimal
> | Dim maximumAs Decimal
> |
> |
> | SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> | maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> | RadioButtonList1.SelectedValue & "')"
> |
> |
> | SqlDataSource1.Insert()
> |
> |
> | CREATE TABLE Example(
> | userName nvarchar(50),
> | rate decimal(2, 2),
> | maximum decimal(6, 2),
> | ticket nchar(1)
> | )
> ||||I'm not dotnet savvy but you might try something to the effect of;
MsgBox SqlDataSource1.InsertCommand
to see the actual SQL being passed.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| Hi,
|
| Already tried that. But this time I got the error:
|
| There are fewer columns in the INSERT statement than values specified
| in the VALUES clause. The number of values in the VALUES clause must
| match the number of columns specified in the INSERT statement.|||thank you so much.. that helped..
silly me.. I was passing the logon name to the database, which was
causing the problem..
thanks for your help...
Dot Net Daddy wrote:
> Hi,
> Already tried that. But this time I got the error:
> There are fewer columns in the INSERT statement than values specified
> in the VALUES clause. The number of values in the VALUES clause must
> match the number of columns specified in the INSERT statement.
>
>
> Dave Patrick wrote:
> > Try dropping the string delimiters (single quotes). Something like;
> >
> > "INSERT INTO Example(userName, rate,
> > maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
> > RadioButtonList1.SelectedValue & "')"
> >
> > --
> >
> > Regards,
> >
> > Dave Patrick ...Please no email replies - reply in newsgroup.
> > Microsoft Certified Professional
> > Microsoft MVP [Windows]
> > http://www.microsoft.com/protect
> >
> > "Dot Net Daddy" wrote:
> > | Hello,
> > |
> > | I cannot get the following Insert Command work. I get the error:
> > |
> > |
> > | Error converting data type varchar to numeric.
> > | 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: Error converting
> > |
> > | data type varchar to numeric.
> > |
> > |
> > | However 'rate' and 'maximum' variables are declared as Decimal
> > |
> > |
> > | Dim rate As Decimal
> > | Dim maximumAs Decimal
> > |
> > |
> > | SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> > | maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> > | RadioButtonList1.SelectedValue & "')"
> > |
> > |
> > | SqlDataSource1.Insert()
> > |
> > |
> > | CREATE TABLE Example(
> > | userName nvarchar(50),
> > | rate decimal(2, 2),
> > | maximum decimal(6, 2),
> > | ticket nchar(1)
> > | )
> > ||||Good to hear. You're welcome.
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| thank you so much.. that helped..
|
| silly me.. I was passing the logon name to the database, which was
| causing the problem..
|
| thanks for your help...|||I see you have found a resolution to your immediate problem but I want to
point out that this code has a serious vulnerability to SQL injection. I
strongly recommend that you use command parameters instead of string
concatenation and perhaps also use only stored procedures so that direct
table permissions are not needed.
Google "SQL injection" find many discussions on the topic.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1156041975.673742.64030@.h48g2000cwc.googlegroups.com...
> Hello,
> I cannot get the following Insert Command work. I get the error:
>
> Error converting data type varchar to numeric.
> 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: Error converting
> data type varchar to numeric.
>
> However 'rate' and 'maximum' variables are declared as Decimal
>
> Dim rate As Decimal
> Dim maximumAs Decimal
>
> SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> RadioButtonList1.SelectedValue & "')"
>
> SqlDataSource1.Insert()
>
> CREATE TABLE Example(
> userName nvarchar(50),
> rate decimal(2, 2),
> maximum decimal(6, 2),
> ticket nchar(1)
> )
>

Error converting data type varchar to numeric.

Hello,
I cannot get the following Insert Command work. I get the error:
Error converting data type varchar to numeric.
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: Error converting
data type varchar to numeric.
However 'rate' and 'maximum' variables are declared as Decimal
Dim rate As Decimal
Dim maximumAs Decimal
SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
RadioButtonList1.SelectedValue & "')"
SqlDataSource1.Insert()
CREATE TABLE Example(
userName nvarchar(50),
rate decimal(2, 2),
maximum decimal(6, 2),
ticket nchar(1)
)Try dropping the string delimiters (single quotes). Something like;
"INSERT INTO Example(userName, rate,
maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
RadioButtonList1.SelectedValue & "')"
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| Hello,
|
| I cannot get the following Insert Command work. I get the error:
|
|
| Error converting data type varchar to numeric.
| 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: Error converting
|
| data type varchar to numeric.
|
|
| However 'rate' and 'maximum' variables are declared as Decimal
|
|
| Dim rate As Decimal
| Dim maximumAs Decimal
|
|
| SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
| maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
| RadioButtonList1.SelectedValue & "')"
|
|
| SqlDataSource1.Insert()
|
|
| CREATE TABLE Example(
| userName nvarchar(50),
| rate decimal(2, 2),
| maximum decimal(6, 2),
| ticket nchar(1)
| )
||||Hi,
Already tried that. But this time I got the error:
There are fewer columns in the INSERT statement than values specified
in the VALUES clause. The number of values in the VALUES clause must
match the number of columns specified in the INSERT statement.
Dave Patrick wrote:
> Try dropping the string delimiters (single quotes). Something like;
> "INSERT INTO Example(userName, rate,
> maximum, ticket) VALUES('blabla'," & rate & "," & maximum & ",'" &
> RadioButtonList1.SelectedValue & "')"
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Dot Net Daddy" wrote:
> | Hello,
> |
> | I cannot get the following Insert Command work. I get the error:
> |
> |
> | Error converting data type varchar to numeric.
> | 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: Error converting
> |
> | data type varchar to numeric.
> |
> |
> | However 'rate' and 'maximum' variables are declared as Decimal
> |
> |
> | Dim rate As Decimal
> | Dim maximumAs Decimal
> |
> |
> | SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> | maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> | RadioButtonList1.SelectedValue & "')"
> |
> |
> | SqlDataSource1.Insert()
> |
> |
> | CREATE TABLE Example(
> | userName nvarchar(50),
> | rate decimal(2, 2),
> | maximum decimal(6, 2),
> | ticket nchar(1)
> | )
> ||||I'm not dotnet savvy but you might try something to the effect of;
MsgBox SqlDataSource1.InsertCommand
to see the actual SQL being passed.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| Hi,
|
| Already tried that. But this time I got the error:
|
| There are fewer columns in the INSERT statement than values specified
| in the VALUES clause. The number of values in the VALUES clause must
| match the number of columns specified in the INSERT statement.|||thank you so much.. that helped..
silly me.. I was passing the logon name to the database, which was
causing the problem..
thanks for your help...
Dot Net Daddy wrote:[vbcol=seagreen]
> Hi,
> Already tried that. But this time I got the error:
> There are fewer columns in the INSERT statement than values specified
> in the VALUES clause. The number of values in the VALUES clause must
> match the number of columns specified in the INSERT statement.
>
>
> Dave Patrick wrote:|||Good to hear. You're welcome.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Dot Net Daddy" wrote:
| thank you so much.. that helped..
|
| silly me.. I was passing the logon name to the database, which was
| causing the problem..
|
| thanks for your help...|||I see you have found a resolution to your immediate problem but I want to
point out that this code has a serious vulnerability to SQL injection. I
strongly recommend that you use command parameters instead of string
concatenation and perhaps also use only stored procedures so that direct
table permissions are not needed.
Google "SQL injection" find many discussions on the topic.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1156041975.673742.64030@.h48g2000cwc.googlegroups.com...
> Hello,
> I cannot get the following Insert Command work. I get the error:
>
> Error converting data type varchar to numeric.
> 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: Error converting
> data type varchar to numeric.
>
> However 'rate' and 'maximum' variables are declared as Decimal
>
> Dim rate As Decimal
> Dim maximumAs Decimal
>
> SqlDataSource1.InsertCommand = "INSERT INTO Example(userName, rate,
> maximum, ticket) VALUES('blabla','" & rate & "','" & maximum & "','" &
> RadioButtonList1.SelectedValue & "')"
>
> SqlDataSource1.Insert()
>
> CREATE TABLE Example(
> userName nvarchar(50),
> rate decimal(2, 2),
> maximum decimal(6, 2),
> ticket nchar(1)
> )
>

Error converting data type varchar to datetime

An application i am using creates an error.

Using SQL-profiler i catch this statement:

exec sp_executesql N'insert into RECORDING_SCHEDULE (programme_oid, capture_source_oid, filename, status, recording_group, recording_type, manual_channel_oid,
manual_start_time, manual_end_time, quality_level, pre_pad_minutes, post_pad_minutes) values (@.P1,@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P1 2)',N'@.P1 int,@.P2 int,@.P3
nvarchar(19),@.P4 int,@.P5 int,@.P6 int,@.P7 int,@.P8 datetime,@.P9 datetime,@.P10 int,@.P11 int,@.P12 int',177748,2,N'Lilla rda traktorn',4,40,5,16,''2006-06-21
17:00:00:000'',''2006-06-21 20:00:00:000'',1,1,2

After removing the double ' around the date and time parts i get the following error when running in QA:
Msg 8114, Level 16, State 5, Line 0
Error converting data type varchar to datetime.

If a remove the time portion it works.

The DDL for the table is:

CREATE TABLE [dbo].[RECORDING_SCHEDULE](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL CONSTRAINT [DF__RECORDING__quali__38996AB5] DEFAULT ((0)),
[pre_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__pre_p__398D8EEE] DEFAULT ((0)),
[post_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__post___3A81B327] DEFAULT ((0)),
CONSTRAINT [aaaaaRECORDING_SCHEDULE_PK] PRIMARY KEY NONCLUSTERED

What am I missing?Well, I corrected a type where you listed [@.P1 2] rather than the obviously intended [@.P12], but after that this code worked fine for me:CREATE TABLE [dbo].[RECORDING_SCHEDULETMP](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL,
[pre_pad_minutes] [int] NULL,
[post_pad_minutes] [int] NULL,
)

exec sp_executesql N'
insert into RECORDING_SCHEDULETMP
(programme_oid,
capture_source_oid,
filename,
status,
recording_group,
recording_type,
manual_channel_oid,
manual_start_time,
manual_end_time,
quality_level,
pre_pad_minutes,
post_pad_minutes)
values (@.P1,
@.P2,
@.P3,
@.P4,
@.P5,
@.P6,
@.P7,
@.P8,
@.P9,
@.P10,
@.P11,
@.P12)',
N'@.P1 int,
@.P2 int,
@.P3 nvarchar(19),
@.P4 int,
@.P5 int,
@.P6 int,
@.P7 int,
@.P8 datetime,
@.P9 datetime,
@.P10 int,
@.P11 int,
@.P12 int',
177748,
2,
N'Lilla rda traktorn',
4,
40,
5,
16,
'2006-06-21 17:00:00:000',
'2006-06-21 20:00:00:000',
1,
1,
2

drop table RECORDING_SCHEDULETMPTry cutting and pasting this to QA and see if it runs for you.|||Oh, I think I see the issue. You "removed" the double quotes, when you should have just changed them to single quotes, as in my sample code...

Error Control

hi,

I'm trying to insert files from one table to anotherone. The problem is that the source table doesnt have any primary key, and it has duplicated PK that the destination needs to be unique. It's possible to ignore this kind of errors? I am using an OLEDB Destination transformation, and trying to omit this error configuring the error output, but it doesnt work.

Thanks!

There are several ways to deal with duplicates. But first you have to tell how would you like to handle them.

You can use a sort transformation with the remove duplicates checked; the thing is that you don't have control over which row get passed and which gets discarded. If the rows are identical; then this approach is easy enough.

Notice that Sort transformation will cache all incoming rows before sending them to the out put, so memory consumptions and performance could suffer.

|||

Thnaks Rafael,

The problem is that i need to control these duplicated records. Is any other way to handle this situation? what i need is to omit this error , continue executing over the flow, and record it on the log.

|||

You could do an insert of the full data set and on the errors (rows with duplicates) use the error output to write them to a "duplicates" table.

|||

Albertoim wrote:

Thnaks Rafael,

The problem is that i need to control these duplicated records. Is any other way to handle this situation? what i need is to omit this error , continue executing over the flow, and record it on the log.

You still are not providing the details on how do you want to handle the duplicates. Do you want to send them all to the duplicate tables, or do you want to insert at least one and reject the rest?

E.g if you have set of 3 duplicate rows, do you want to insert 1 and log the other 2 as duplicates or you want to log the 3 of them and insert none?

For the first case, and when the RANK() function is available in the source DB, like Oracle or SS2005 I use this:

http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html

|||

I only want to insert the first record, all other duplicated records must by registered in the log (the error warning) not in other table, but it could be an alternative...

thanks both of you for your answers.

error comming at insert stmt

assume connection is established and photo cloumn as data type as image in sql table.

str_insert ="INSERT INTO SIS_TeachingStaff VALUES('TSMT2','V.RAJANIKANTH','LECTURER','TEACHING STAFF','Msc(Maths)','" & Emp_pic.Image &"' "

cmd =New SqlCommand(str_insert, conn)

cmd.Connection = conn

cmd.ExecuteNonQuery()

Error at insert stmt: Operator '&' is not defined for types 'string' and 'system.drawing.image'

Hi

")" is missed at the end .Try this:

str_insert ="INSERT INTO SIS_TeachingStaff VALUES('TSMT2','V.RAJANIKANTH','LECTURER','TEACHING STAFF','Msc(Maths)','" & Emp_pic.Image &"') "