Solved

Record not created

Posted on 2014-07-21
25
171 Views
Last Modified: 2014-07-27
Hi,
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) 
                    {
                        ...

Open in new window

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 %> />
                    ...

Open in new window

why can't I insert the record, as there is no record created within Access table, after the execution of the above codes?
0
Comment
Question by:HuaMinChen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 6
  • 5
25 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40210912
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...
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40210931
I don't know why I got no record inserted, after running the above codes.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40210932
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:HuaMinChen
ID: 40210970
The method is just having the above codes. I don't know why record is not created, after running the above lines.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40211148
The method is just having the above codes.
Nope. this does not even compile..
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40211215
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();
            }
        }

Open in new window

0
 
LVL 33

Expert Comment

by:ste5an
ID: 40212404
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:  
 // 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);
	} 
}

Open in new window


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).
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40213362
Thanks. I think it is not a good idea to change the codes above.

I see that

lb_ite_id.Text.Trim()
Request.Form["tb_p1_desc"]
lb_feat.Text

Open in new window

are all having values inside. But I don't know why my codes are not inserting the record successfully.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40214058
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???
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40215863
The point is just why no record has not been inserted
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40215865
Correction: The problem is just why no record has been inserted
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40216731
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\ite_DB.accdb

 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?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40218494
Hi,
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) 

Open in new window


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);
		...

Open in new window

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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40218594
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
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40218618
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

Open in new window

due to this line
           
return File.ReadAllBytes(HttpServerUtility.MapPath(fileName));
            ...

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40218627
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;
string path;
   path = System.IO.Path.GetDirectoryName( 
      System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase );
   MessageBox.Show( path );

Open in new window

0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40218634
It is one web application (.Net) but I got the problem when building it.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40218638
the please use Server.MapPath(fileName) instead
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40218732
Sorry I still get his

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)

Open in new window

while

lb_ite_id.Text
Request.Form["tb_p1_desc"]
lb_feat.Text

Open in new window

are respectively having these values inside.

18
房间1
t737.png 

Open in new window


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";
                        ...

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 245 total points
ID: 40219145
no need to be sorry, but as you notice, the previous error has been solved.

this one is simple:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters%28v=vs.110%29.aspx
The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

Open in new window

hence:
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);

Open in new window

0
 
LVL 33

Expert Comment

by:ste5an
ID: 40219168
Please, please, please: Don't use user input directly. Validate it.
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40221205
Many thanks Guy Hengel.

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)
                {
                    ...

Open in new window

while

lb_ite_id.Text
Request.Form["tb_p1_desc"]
lb_feat.Text

Open in new window


                                         
are respectively having these values inside.

18
房间1
t737.png 

Open in new window

I still get this exception and stacktrace

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)

Open in new window

0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40221232
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)
                {
                    ...

Open in new window

0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40222238
Please omit my last reply above. Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
asp web application 3 42
Check a field in Access on a Timer 1 32
parsing question on dynamic concatenation 3 34
Nested forach loop to linq 3 29
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question