Peter Chan
asked on
Record not created
Hi,
Here are the codes
Here are the codes
private byte[] ImageToStream(string fileName)
{
return File.ReadAllBytes(fileName);
}
cmd6 = new OleDbCommand("insert into ite_feat (ord_key,ite_id,feat_desc,feat_file,add_dt) values (1,@ite_id,@feat_desc,@feat_file,date)", conn2);
cmd6.Parameters.Add("@ite_id", OleDbType.VarChar).Value = lb_ite_id.Text.Trim();
cmd6.Parameters.Add("@feat_desc", OleDbType.VarWChar).Value = Request.Form["tb_p1_desc"];
cmd6.Parameters.Add("@feat_file", OleDbType.Binary).Value = ImageToStream(lb_feat.Text);
if (cmd6.ExecuteNonQuery() < 1)
{
...
to insert the record into Access table, while at the moment, I am able to show the picture within the Markup page like <img src=<%=lb_pict.Text %> />
...
why can't I insert the record, as there is no record created within Access table, after the execution of the above codes?
unless you get an error that you hide by a generic catch statement, impossible to tell apart from double-checking the table and column names...
ASKER
I don't know why I got no record inserted, after running the above codes.
Please, don't x-post..
Can you post the entire method? Can you post the table definition? How do you check, whether the insert has worked or not?
Can you post the entire method? Can you post the table definition? How do you check, whether the insert has worked or not?
ASKER
The method is just having the above codes. I don't know why record is not created, after running the above lines.
The method is just having the above codes.Nope. this does not even compile..
ASKER
Here is the full codes
protected void save_det_rec(string ret_msg)
{
OleDbConnection conn2;
conn2 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=????;Data Source=C:\inetpub\VS2012\ite_DB.accdb");
OleDbCommand cmd6 = null;
ret_msg = "";
conn2.Open();
try
{
cmd6 = new OleDbCommand("insert into ite_feat (ord_key,ite_id,feat_desc,feat_file,add_dt) values (1,@ite_id,@feat_desc,@feat_file,date)", conn2);
cmd6.Parameters.Add("@ite_id", OleDbType.VarChar).Value = lb_ite_id.Text.Trim();
cmd6.Parameters.Add("@feat_desc", OleDbType.VarWChar).Value = Request.Form["tb_p1_desc"];
cmd6.Parameters.Add("@feat_file", OleDbType.Binary).Value = ImageToStream(lb_feat.Text);
if (cmd6.ExecuteNonQuery() < 1)
{
ret_msg = "failed";
return;
}
catch (Exception ex)
{
ret_msg = "ex 1";
return;
}
finally
{
conn2.Close();
}
}
Well, you don't want to answer my questions: How do you test whether the row was inserted or not?
You should also refactor your code:
You're using an Access database file for a web site, this may cause later own concurrency issues. btw, gets your database file overwritten by your deployment process?
I would recommend that you use SQL Server (Express is free).
You should also refactor your code:
// Provider class (factory method).
// This allows you to keep and use your connection string at exactly one place.
public class OleDbConnectionProvider
{
public static OleDbProvider CreateConnection()
{
const string CONNECTION_STRING = @"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=????;Data Source=C:\inetpub\VS2012\ite_DB.accdb";
return new OleDbConnection(CONNECTION_STRING);
}
}
class WhatEver
{
protected bool AddItemFeature(int itemID, string featureDescription, string imageFileName, out string resultMessage)
{
// TODO: Error handling due to ImageToStream() method.
const string SQL_COMMAND = "INSERT INTO ite_feat ( ord_key, ite_id, feat_desc, feat_file, add_dt) VALUES ( 1, @ite_id, @feat_desc, @feat_file, Date);";
bool result = false;
resultMessage = "D'oh?";
using (OleDbConnection connection = OleDbConnectionProvider.CreateConnection())
{
using (OleDbCommand command = new OleDbCommand(SQL_COMMAND))
{
command.Parameters.Add("@ite_id", OleDbType.Integer).Value = itemID;
command.Parameters.Add("@feat_desc", OleDbType.VarWChar).Value = featureDescription;
command.Parameters.Add("@feat_file", OleDbType.Binary).Value = ImageToStream(imageFileName);
try
{
result = (command.ExecuteNonQuery() == 1);
if (result
{
resultMessage = "Item added.";
}
else
{
resultMessage = "Item not added.";
}
}
catch (Exception exception)
{
resultMessage = exception.Message;
}
}
}
return result;
}
private object ImageToStream(string imageFileName)
{
// TODO: Error handling.
return File.ReadAllBytes(imageFileName);
}
}
You're using an Access database file for a web site, this may cause later own concurrency issues. btw, gets your database file overwritten by your deployment process?
I would recommend that you use SQL Server (Express is free).
ASKER
Thanks. I think it is not a good idea to change the codes above.
I see that
I see that
lb_ite_id.Text.Trim()
Request.Form["tb_p1_desc"]
lb_feat.Text
are all having values inside. But I don't know why my codes are not inserting the record successfully.
It's a really good idea, because it is a really bad idea to insert non-validated input from users...
But I don't know why my codes are not inserting the record successfully.Gosh, HOW DO YOU TEST THIS???
ASKER
The point is just why no record has not been inserted
ASKER
Correction: The problem is just why no record has been inserted
Dear HuaMinChen,
it is "your" problem that the record is not inserted, which we have understood.
it is "our" problem that you are not telling us the information we need.
you are our eyes in regards to your problem, code, etc...
so far, you where not really collaborative in regards to that part of the problem-solving process.
we (the voluntary people trying to help) cannot solve the problem if you don't tell us the things.
just 1 quick idea: your code (connection string) refers to this file:
Data Source=C:\inetpub\VS2012\i te_DB.accd b
1 simple "gotcha" would be that you are not looking into THAT database, but in another one...
so again : HOW EXACTLY do you check that the record is not inserted.
also, are you 200% sure there error/exception is not hidden anywhere down the lines (which can happen quite easily in a web application ...)
does the code work for other tables? what if you don't fill the field for feat_file column, does it work then?
it is "your" problem that the record is not inserted, which we have understood.
it is "our" problem that you are not telling us the information we need.
you are our eyes in regards to your problem, code, etc...
so far, you where not really collaborative in regards to that part of the problem-solving process.
we (the voluntary people trying to help) cannot solve the problem if you don't tell us the things.
just 1 quick idea: your code (connection string) refers to this file:
Data Source=C:\inetpub\VS2012\i
1 simple "gotcha" would be that you are not looking into THAT database, but in another one...
so again : HOW EXACTLY do you check that the record is not inserted.
also, are you 200% sure there error/exception is not hidden anywhere down the lines (which can happen quite easily in a web application ...)
does the code work for other tables? what if you don't fill the field for feat_file column, does it work then?
ASKER
Hi,
I get this exception and stacktrace
how to correct the last line below
C:\inetpub\VS2012\App7
I get this exception and stacktrace
Could not find file 'c:\windows\system32\inetsrv\t737.png'. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.File.InternalReadAllBytes(String path, Boolean checkHost) at App7._Default.save_det_rec(String ret_msg)Could not find file 'c:\windows\system32\inetsrv\t737.png'. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost) at System.IO.File.InternalReadAllBytes(String path, Boolean checkHost) at App7._Default.save_det_rec(String ret_msg)
how to correct the last line below
private byte[] ImageToStream(string fileName)
{
return File.ReadAllBytes(fileName);
}
...
cmd6.Parameters.Add("@feat_file", OleDbType.Binary).Value = ImageToStream(lb_feat.Text);
...
for referring to the relevant picture file, as the file does reside in this folder, to which the project was deployed?C:\inetpub\VS2012\App7
see, now we are speaking.
the issue is that visibly, lb_feat.Text is only containing the file NAME, and not the file PATH.
to solve that, you can use this:
http://msdn.microsoft.com/en-us/library/system.web.httpserverutility.mappath%28v=vs.110%29.aspx
private byte[] ImageToStream(string fileName)
{
return File.ReadAllBytes( HttpServerUtility.MapPath( fileName) );
}
this should properly set the folder of you web page
the issue is that visibly, lb_feat.Text is only containing the file NAME, and not the file PATH.
to solve that, you can use this:
http://msdn.microsoft.com/en-us/library/system.web.httpserverutility.mappath%28v=vs.110%29.aspx
private byte[] ImageToStream(string fileName)
{
return File.ReadAllBytes( HttpServerUtility.MapPath(
}
this should properly set the folder of you web page
ASKER
Sorry, I get this
Error 2 An object reference is required for the non-static field, method, or property 'System.Web.HttpServerUtility.MapPath(string)' C:\App7\App7\Default.aspx.cs 41 38 App7
due to this linereturn File.ReadAllBytes(HttpServerUtility.MapPath(fileName));
...
is this is not a web application (asp.net)?
if it is a web application, the above should work. please check which object/value is null at that place...
if it's not a web application (plain C#), you need to get the application path like this;
if it is a web application, the above should work. please check which object/value is null at that place...
if it's not a web application (plain C#), you need to get the application path like this;
string path;
path = System.IO.Path.GetDirectoryName(
System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase );
MessageBox.Show( path );
ASKER
It is one web application (.Net) but I got the problem when building it.
the please use Server.MapPath(fileName) instead
ASKER
Sorry I still get his
based on these codes.
No value given for one or more required parameters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at App7._Default.save_det_rec(String ret_msg)
whilelb_ite_id.Text
Request.Form["tb_p1_desc"]
lb_feat.Text
are respectively having these values inside.18
房间1
t737.png
based on these codes.
cmd6 = new OleDbCommand("insert into ite_feat (ord_key,ite_id,feat_desc,feat_file,add_dt) values (1,@ite_id,@feat_desc,@feat_file,date)", conn2);
cmd6.Parameters.Add("@ite_id", OleDbType.Integer).Value =Convert.ToInt32(lb_ite_id.Text.Trim());
cmd6.Parameters.Add("@feat_desc", OleDbType.VarWChar).Value = Request.Form["tb_p1_desc"];
cmd6.Parameters.Add("@feat_file", OleDbType.Binary).Value = ImageToStream(lb_feat.Text);
if (cmd6.ExecuteNonQuery() < 1)
{
ret_msg = "failed";
...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please, please, please: Don't use user input directly. Validate it.
ASKER
Many thanks Guy Hengel.
with these codes
are respectively having these values inside.
with these codes
conn2.Open();
try
{
cmd6 = new OleDbCommand("insert into ite_feat (ord_key,ite_id,feat_desc,feat_file,add_dt) values (1,?,?,?,date)", conn2);
cmd6.Parameters.Add("@ite_id", OleDbType.Integer).Value =Convert.ToInt32(lb_ite_id.Text.Trim());
cmd6.Parameters.Add("@feat_desc", OleDbType.VarWChar).Value = Request.Form["tb_p1_desc"];
cmd6.Parameters.Add("@feat_file", OleDbType.Binary).Value = ImageToStream(lb_feat.Text);
if (cmd6.ExecuteNonQuery() < 1) // if it returned 1 it means one (or more) row has been updated, otherwise no change made in DB
{
ret_msg = "failed";
...
}
}
catch (SqlException ex)
{
...
whilelb_ite_id.Text
Request.Form["tb_p1_desc"]
lb_feat.Text
are respectively having these values inside.
18
房间1
t737.png
I still get this exception and stacktraceNo value given for one or more required parameters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at App7._Default.save_det_rec(String ret_msg)
ASKER
With these codes actually
private byte[] ImageToStream(string fileName)
{
return File.ReadAllBytes(Server.MapPath(fileName));
}
conn2.Open();
try
{
cmd6 = new OleDbCommand("insert into ite_feat (ord_key,ite_id,feat_desc,feat_file,add_dt) values (1,?,?,?,date())", conn2);
cmd6.Parameters.Add("@ite_id", OleDbType.Integer).Value =Convert.ToInt32(lb_ite_id.Text.Trim());
cmd6.Parameters.Add("@feat_desc", OleDbType.VarWChar).Value = Request.Form["tb_p1_desc"];
cmd6.Parameters.Add("@feat_file", OleDbType.Binary).Value = ImageToStream(lb_feat.Text);
if (cmd6.ExecuteNonQuery() < 1)
{
ret_msg = "failed";
...
}
}
catch (SqlException ex)
{
...
ASKER
Please omit my last reply above. Thanks