INSERT into Access Database using C# from ASP page

[Guest]

The field is too small to accept the amount of data you attempted to add. Try
inserting or pasting less data. Is the error message I get when it gets to
dbCmd.ExecuteNonQuery();. I have 15 fields to insert, therefore I did one
field at the time and found that the 13th field is the one causing the
problem, this field has size 20 and data I am entering has size is 1. I took
the INSERT command and entered it from Access Query and it worked, but not
from ASP page using C#. Please help I thank you for your assistance. I found
the similar error in
http://www.error-bank.com/microsoft.public.dotnet.languages.vb.1/196223_Thread.aspx. But there were no solutions posted.

 

[Sylvain Lafontaine]

Please post the structure of your table(s) and the insert query that you are
trying to use.

S. L.

 

[Guest]

"INSERT INTO support (call_type, serial_no, [date], contact_person, " +
"duration, problem, solution, billable, platform, current_version,
update_by, " +
"last_update, issue_type, open_call, analyst) SELECT " +
"@strCallType, @strSerial, @strLastUpdate, @strContact, @intDuration, " +
"@strProblem, @strSolution, @intBillable, @strPlatform, @strVersion, " +
"@strSupport, @strLastUpdate, @strIssueType, @intOpenCall, " +
"analyst.analyst FROM analyst WHERE analyst.analyst = '" + strSupport + "'";

OleDbCommand gdCmd = new OleDbCommand(insertSQL,dbConn);
gdCmd.Parameters.Add("@call_type",OleDbType.VarChar, 15).Value = strCallType;
gdCmd.Parameters.Add("@serial_no", OleDbType.VarChar, 20).Value = strSerial;
gdCmd.Parameters.Add("@[date]", OleDbType.Date, 20).Value = strLastUpdate;
gdCmd.Parameters.Add("@contact_person", OleDbType.VarChar, 50).Value =
strContact;
gdCmd.Parameters.Add("@duration", OleDbType.Numeric, 50).Value = intDuration;
gdCmd.Parameters.Add("@problem", OleDbType.VarChar, 100).Value = strProblem;
gdCmd.Parameters.Add("@solution", OleDbType.VarChar, 100).Value = strSolution;
gdCmd.Parameters.Add("@billable", OleDbType.VarChar, 2).Value = intBillable;
gdCmd.Parameters.Add("@platform", OleDbType.VarChar, 1).Value = strPlatform;
gdCmd.Parameters.Add("@current_version", OleDbType.VarChar, 10).Value =
strVersion;
gdCmd.Parameters.Add("@update_by", OleDbType.VarChar, 50).Value = strSupport;
gdCmd.Parameters.Add("@last_update", OleDbType.Date, 20).Value =
strLastUpdate;
gdCmd.Parameters.Add("@issue_type", OleDbType.VarChar, 20).Value =
strIssueType;
gdCmd.Parameters.Add("@open_call", OleDbType.VarChar, 1).Value = intOpenCall;

 

[Sylvain Lafontaine]

This will take some time to analyse because I'm more familiar with SqlType
than with OleDbType. However, I found the following facts a little strange:

1- what is the purpose of your SELECT statement? The only value that you
are inserting from the table analyst is analyst but this is also the value
of the WHERE clause (« WHERE analyst.analyst = '" + strSupport + "'"; ») and
of the parameter @strSupport.

Using a SELECT might be a nice trick to convert values between different
types but when you got an error message stating that the type of one of
these parameters is incorrect, this little trick is not so nice anymore.

2- All the parameters in your SELECT command don't have the same name as the
parameters that you are adding in the Parameters collection. For exemple,
@strCallType is replaced with @call_Type and @strLastUpdate is replaced with
@[date]. Obviously, the second serie comes from the names of the fields of
your Insert statement, excerpt for the last one, analys, which is coming
from analyst.analyst.

3- The size of a DateTime field in SQL-Server is 8 bytes, not 20.

This will take some time to analyse this situation and to know what ADO.NET
is really doing with your Parameters collection.

S. L.

"INSERT INTO support (call_type, serial_no, [date], contact_person, " +
"duration, problem, solution, billable, platform, current_version,
update_by, " +
"last_update, issue_type, open_call, analyst) SELECT " +
"@strCallType, @strSerial, @strLastUpdate, @strContact, @intDuration, " +
"@strProblem, @strSolution, @intBillable, @strPlatform, @strVersion, " +
"@strSupport, @strLastUpdate, @strIssueType, @intOpenCall, " +
"analyst.analyst FROM analyst WHERE analyst.analyst = '" + strSupport +
"'";

OleDbCommand gdCmd = new OleDbCommand(insertSQL,dbConn);
gdCmd.Parameters.Add("@call_type",OleDbType.VarChar, 15).Value =
strCallType;
gdCmd.Parameters.Add("@serial_no", OleDbType.VarChar, 20).Value =
strSerial;
gdCmd.Parameters.Add("@[date]", OleDbType.Date, 20).Value = strLastUpdate;
gdCmd.Parameters.Add("@contact_person", OleDbType.VarChar, 50).Value =
strContact;
gdCmd.Parameters.Add("@duration", OleDbType.Numeric, 50).Value =
intDuration;
gdCmd.Parameters.Add("@problem", OleDbType.VarChar, 100).Value =
strProblem;
gdCmd.Parameters.Add("@solution", OleDbType.VarChar, 100).Value =
strSolution;
gdCmd.Parameters.Add("@billable", OleDbType.VarChar, 2).Value =
intBillable;
gdCmd.Parameters.Add("@platform", OleDbType.VarChar, 1).Value =
strPlatform;
gdCmd.Parameters.Add("@current_version", OleDbType.VarChar, 10).Value =
strVersion;
gdCmd.Parameters.Add("@update_by", OleDbType.VarChar, 50).Value =
strSupport;
gdCmd.Parameters.Add("@last_update", OleDbType.Date, 20).Value =
strLastUpdate;
gdCmd.Parameters.Add("@issue_type", OleDbType.VarChar, 20).Value =
strIssueType;
gdCmd.Parameters.Add("@open_call", OleDbType.VarChar, 1).Value =
intOpenCall;

Please post the structure of your table(s) and the insert query that you
are
trying to use.

S. L.

 

[Guest]

The reason I am using SELECT is that the Analyst table is bound to the
support table. Therefore I cannot make a simple INSERT table VALUES (),
because Access wants it referenced using the SELECT statement. Secondly the
Date as you can see I want to keep track of time as well, that is why I am
using 20. Below is what the actual SQL looks like before it inserts in Access.

INSERT INTO support (call_type, serial_no, [date], contact_person, duration,
problem, solution, billable, platform, current_version, update_by,
last_update, issue_type, open_call, analyst) SELECT 'email', '12345',
'11/22/2004 5:50:51 PM', 'Upload Page', '0', 'Email was sent to client.
(e-mail address removed) 11/22/2004 17:11:51', 'Client has uploaded their data on the ftp
site', '0', 'W', '7.03b', 'ZakM', '11/22/2004 5:50:51 PM', 'Internet /
Download', '1', analyst.analyst FROM analyst WHERE analyst.analyst = 'ZakM';

 

[Guest]

Thank you for your input. The problem was the order/position of OleDb
Parameter.

The reason I am using SELECT is that the Analyst table is bound to the
support table. Therefore I cannot make a simple INSERT table VALUES (),
because Access wants it referenced using the SELECT statement. Secondly the
Date as you can see I want to keep track of time as well, that is why I am
using 20. Below is what the actual SQL looks like before it inserts in Access.

INSERT INTO support (call_type, serial_no, [date], contact_person, duration,
problem, solution, billable, platform, current_version, update_by,
last_update, issue_type, open_call, analyst) SELECT 'email', '12345',
'11/22/2004 5:50:51 PM', 'Upload Page', '0', 'Email was sent to client.
(e-mail address removed) 11/22/2004 17:11:51', 'Client has uploaded their data on the ftp
site', '0', 'W', '7.03b', 'ZakM', '11/22/2004 5:50:51 PM', 'Internet /
Download', '1', analyst.analyst FROM analyst WHERE analyst.analyst = 'ZakM';

This will take some time to analyse because I'm more familiar with SqlType
than with OleDbType. However, I found the following facts a little strange:

1- what is the purpose of your SELECT statement? The only value that you
are inserting from the table analyst is analyst but this is also the value
of the WHERE clause (« WHERE analyst.analyst = '" + strSupport + "'"; ») and
of the parameter @strSupport.

Using a SELECT might be a nice trick to convert values between different
types but when you got an error message stating that the type of one of
these parameters is incorrect, this little trick is not so nice anymore.

2- All the parameters in your SELECT command don't have the same name as the
parameters that you are adding in the Parameters collection. For exemple,
@strCallType is replaced with @call_Type and @strLastUpdate is replaced with
@[date]. Obviously, the second serie comes from the names of the fields of
your Insert statement, excerpt for the last one, analys, which is coming
from analyst.analyst.

3- The size of a DateTime field in SQL-Server is 8 bytes, not 20.

This will take some time to analyse this situation and to know what ADO.NET
is really doing with your Parameters collection.

S. L.