Hello all,

Bit of a change of pace now. As opposed to the typical questions regarding
my Air Hockey game, am also working on a Photo Album which uses an Access
Database to store information about photos. This information is held inside
the database (photoDB.mdb) in a table called 'photos' - information being
recorded in there are photo information (photoID, location, phDate,
category, caption).

I have inputted my own photos (not via this method) and they all load
correctly. But when adding a new photo, the user needs to clarify if the
photo information is all correct - hence pressing the OK button (see code
below).

I, however, get a 'no value given for one or more required parameters' error
below. Anyone care to share where i've gone wrong? Also, it's hard to know
if this is more relevent in the CSharp newsgroup or the Access / SQL
newsgroup.

Thanks in advance.

PS - just to cover every area, the data types in Access database are as
follows: photoID (Text), location (Text), phDate (Date/Time), category
(Text), caption (Text)

----------------------------------------------------------------------------------------

private void btnOK_Click(object sender, System.EventArgs e)
{
try
{
newPhotoID = lblPhotoID.Text;
newLocation = newFileName.ToString();
newDate = DateTime.Now.Date;
newCategory = cboCategory.Text;
newCaption = txtCaption.Text;
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
+System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)
+ @"\photoDB.MDB";
string strSQL = "INSERT INTO photos (photoID, location, phDate,
category, caption) VALUES (newPhotoID, newLocation, newDate, newCategory,
newCaption);" ;

// create Objects of ADOConnection and ADOCommand
OleDbConnection myConn = new OleDbConnection(strDSN);
OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );
myConn.Open();
myCmd.ExecuteNonQuery();
myConn.Close();
}
catch (OleDbException ex)
{
MessageBox.Show("" + ex.Message);
}
}

That sounds like an SQL error telling you that you are not accounting
for all the fields in your INSERT statement.

Since it's an Access database, you can always to into the Query mode and
then select SQL and then cut and paste your SQL statement there to
"test" it.

You may be:

a) Not accounting for a primary key
b) Passing a null value where nulls are not accepted
c) Spelling the name of the field wrong

Brian Basquille wrote:
> Hello all,
>
> Bit of a change of pace now. As opposed to the typical questions regarding
> my Air Hockey game, am also working on a Photo Album which uses an Access
> Database to store information about photos. This information is held inside
> the database (photoDB.mdb) in a table called 'photos' - information being
> recorded in there are photo information (photoID, location, phDate,
> category, caption).
>
> I have inputted my own photos (not via this method) and they all load
> correctly. But when adding a new photo, the user needs to clarify if the
> photo information is all correct - hence pressing the OK button (see code
> below).
>
> I, however, get a 'no value given for one or more required parameters' error
> below. Anyone care to share where i've gone wrong? Also, it's hard to know
> if this is more relevent in the CSharp newsgroup or the Access / SQL
> newsgroup.
>
> Thanks in advance.
>
> PS - just to cover every area, the data types in Access database are as
> follows: photoID (Text), location (Text), phDate (Date/Time), category
> (Text), caption (Text)
>
> ----------------------------------------------------------------------------------------
>
> private void btnOK_Click(object sender, System.EventArgs e)
> {
> try
> {
> newPhotoID = lblPhotoID.Text;
> newLocation = newFileName.ToString();
> newDate = DateTime.Now.Date;
> newCategory = cboCategory.Text;
> newCaption = txtCaption.Text;
> string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
> +System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)
> + @"\photoDB.MDB";
> string strSQL = "INSERT INTO photos (photoID, location, phDate,
> category, caption) VALUES (newPhotoID, newLocation, newDate, newCategory,
> newCaption);" ;
>
> // create Objects of ADOConnection and ADOCommand
> OleDbConnection myConn = new OleDbConnection(strDSN);
> OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );
> myConn.Open();
> myCmd.ExecuteNonQuery();
> myConn.Close();
> }
> catch (OleDbException ex)
> {
> MessageBox.Show("" + ex.Message);
> }
> }
>
>
>

Cheers also for that, Eyewanda!

Have went through the SQL statement a hundred times and still can't figure
out what's wrong with it. Field names aren't wrong. Using breakpoints, i've
figured out there's definantly no nulls. And the primary key is accounted
for.

But i'll try testing the SQL statement in Query mode.

Thanks again!

"Eyeawanda Pondicherry" <(E-Mail Removed)> wrote in message
news:eNLDd.722$(E-Mail Removed)...
> That sounds like an SQL error telling you that you are not accounting for
> all the fields in your INSERT statement.
>
> Since it's an Access database, you can always to into the Query mode and
> then select SQL and then cut and paste your SQL statement there to "test"
> it.
>
> You may be:
>
> a) Not accounting for a primary key
> b) Passing a null value where nulls are not accepted
> c) Spelling the name of the field wrong
>
> Brian Basquille wrote:
>> Hello all,
>>
>> Bit of a change of pace now. As opposed to the typical questions
>> regarding my Air Hockey game, am also working on a Photo Album which uses
>> an Access Database to store information about photos. This information is
>> held inside the database (photoDB.mdb) in a table called 'photos' -
>> information being recorded in there are photo information (photoID,
>> location, phDate, category, caption).
>>
>> I have inputted my own photos (not via this method) and they all load
>> correctly. But when adding a new photo, the user needs to clarify if the
>> photo information is all correct - hence pressing the OK button (see code
>> below).
>>
>> I, however, get a 'no value given for one or more required parameters'
>> error below. Anyone care to share where i've gone wrong? Also, it's hard
>> to know if this is more relevent in the CSharp newsgroup or the Access /
>> SQL newsgroup.
>>
>> Thanks in advance.
>>
>> PS - just to cover every area, the data types in Access database are as
>> follows: photoID (Text), location (Text), phDate (Date/Time), category
>> (Text), caption (Text)
>>
>> ----------------------------------------------------------------------------------------
>>
>> private void btnOK_Click(object sender, System.EventArgs e)
>> {
>> try
>> {
>> newPhotoID = lblPhotoID.Text;
>> newLocation = newFileName.ToString();
>> newDate = DateTime.Now.Date;
>> newCategory = cboCategory.Text;
>> newCaption = txtCaption.Text;
>> string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
>> +System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)
>> + @"\photoDB.MDB";
>> string strSQL = "INSERT INTO photos (photoID, location, phDate,
>> category, caption) VALUES (newPhotoID, newLocation, newDate, newCategory,
>> newCaption);" ;
>>
>> // create Objects of ADOConnection and ADOCommand
>> OleDbConnection myConn = new OleDbConnection(strDSN);
>> OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );
>> myConn.Open();
>> myCmd.ExecuteNonQuery();
>> myConn.Close();
>> }
>> catch (OleDbException ex)
>> {
>> MessageBox.Show("" + ex.Message);
>> }
>> }
>>
>>
>>
>

hi...

if i recall correctly, i had this problem with a sql server project i was
working on. the error text is misleading. if you've checked all the values
and all the types and they are correct, make sure that the command object
and the connection object are "hooked up" as you think they are. i recall
having to do something like: sqlcommand sc = conn.createcommand(...) vs.
typing them together in some other way.

john


"Brian Basquille" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello all,
>
> Bit of a change of pace now. As opposed to the typical questions regarding
> my Air Hockey game, am also working on a Photo Album which uses an Access
> Database to store information about photos. This information is held
inside
> the database (photoDB.mdb) in a table called 'photos' - information being
> recorded in there are photo information (photoID, location, phDate,
> category, caption).
>
> I have inputted my own photos (not via this method) and they all load
> correctly. But when adding a new photo, the user needs to clarify if the
> photo information is all correct - hence pressing the OK button (see code
> below).
>
> I, however, get a 'no value given for one or more required parameters'
error
> below. Anyone care to share where i've gone wrong? Also, it's hard to know
> if this is more relevent in the CSharp newsgroup or the Access / SQL
> newsgroup.
>
> Thanks in advance.
>
> PS - just to cover every area, the data types in Access database are as
> follows: photoID (Text), location (Text), phDate (Date/Time), category
> (Text), caption (Text)
>
> --------------------------------------------------------------------------
--------------
>
> private void btnOK_Click(object sender, System.EventArgs e)
> {
> try
> {
> newPhotoID = lblPhotoID.Text;
> newLocation = newFileName.ToString();
> newDate = DateTime.Now.Date;
> newCategory = cboCategory.Text;
> newCaption = txtCaption.Text;
> string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
>
+System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAsse
mbly().Location)
> + @"\photoDB.MDB";
> string strSQL = "INSERT INTO photos (photoID, location, phDate,
> category, caption) VALUES (newPhotoID, newLocation, newDate, newCategory,
> newCaption);" ;
>
> // create Objects of ADOConnection and ADOCommand
> OleDbConnection myConn = new OleDbConnection(strDSN);
> OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );
> myConn.Open();
> myCmd.ExecuteNonQuery();
> myConn.Close();
> }
> catch (OleDbException ex)
> {
> MessageBox.Show("" + ex.Message);
> }
> }
>
>
>

The variables in your VALUES section of your SQL statement are not
automatically replaced with the local variable values.

The ideal way to do it would be to use parameters with a SQL command string
like this.

INSERT INTO photos (photoID, location, phDate, category, caption)
VALUES (?, ?, ?, ?, ?);

It could also be done with string concatenation, but not recommended.

INSERT INTO photos (photoID, location, phDate, category, caption)
VALUES (3, 'Office', #1/7/2005#, 'Project', 'My Office');

"Brian Basquille" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello all,
>
> Bit of a change of pace now. As opposed to the typical questions regarding
> my Air Hockey game, am also working on a Photo Album which uses an Access
> Database to store information about photos. This information is held
> inside the database (photoDB.mdb) in a table called 'photos' - information
> being recorded in there are photo information (photoID, location, phDate,
> category, caption).
>
> I have inputted my own photos (not via this method) and they all load
> correctly. But when adding a new photo, the user needs to clarify if the
> photo information is all correct - hence pressing the OK button (see code
> below).
>
> I, however, get a 'no value given for one or more required parameters'
> error below. Anyone care to share where i've gone wrong? Also, it's hard
> to know if this is more relevent in the CSharp newsgroup or the Access /
> SQL newsgroup.
>
> Thanks in advance.
>
> PS - just to cover every area, the data types in Access database are as
> follows: photoID (Text), location (Text), phDate (Date/Time), category
> (Text), caption (Text)
>
> ----------------------------------------------------------------------------------------
>
> private void btnOK_Click(object sender, System.EventArgs e)
> {
> try
> {
> newPhotoID = lblPhotoID.Text;
> newLocation = newFileName.ToString();
> newDate = DateTime.Now.Date;
> newCategory = cboCategory.Text;
> newCaption = txtCaption.Text;
> string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "
> +System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)
> + @"\photoDB.MDB";
> string strSQL = "INSERT INTO photos (photoID, location, phDate,
> category, caption) VALUES (newPhotoID, newLocation, newDate, newCategory,
> newCaption);" ;
>
> // create Objects of ADOConnection and ADOCommand
> OleDbConnection myConn = new OleDbConnection(strDSN);
> OleDbCommand myCmd = new OleDbCommand( strSQL, myConn );
> myConn.Open();
> myCmd.ExecuteNonQuery();
> myConn.Close();
> }
> catch (OleDbException ex)
> {
> MessageBox.Show("" + ex.Message);
> }
> }
>
>
>

Try something like:

string strSQL = "INSERT INTO photos (photoID, location, phDate,
category, caption) VALUES (" + newPhotoID + ", " + newLocation
+ ", " + newDate.ToString() + ", " + newCategory + ", " +
newCaption + ");" ;


Peter

Not quite that easy.... (The strings would need to be quoted..)

string strSQL = "INSERT INTO photos (photoID, location, phDate,
category, caption) VALUES ('" + newPhotoID + "', '" + newLocation
+ "', #" + newDate.ToString() + "#, '" + newCategory + "', '" +
newCaption + "');" ;


--
Truth,
James Curran
[erstwhile VC++ MVP]
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com

"Peter Gloor" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Try something like:
>
> string strSQL = "INSERT INTO photos (photoID, location, phDate,
> category, caption) VALUES (" + newPhotoID + ", " + newLocation
> + ", " + newDate.ToString() + ", " + newCategory + ", " +
> newCaption + ");" ;
>
>
> Peter
>
>

Could it also be that the DateTime.ToString() format isn't the format that
the datetime should be in when used in the SQL Insert command?

Just a thought...


"Peter Gloor" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Try something like:
>
> string strSQL = "INSERT INTO photos (photoID, location, phDate,
> category, caption) VALUES (" + newPhotoID + ", " + newLocation
> + ", " + newDate.ToString() + ", " + newCategory + ", " +
> newCaption + ");" ;
>
>
> Peter
>
>

CKa wrote:
> Could it also be that the DateTime.ToString() format isn't the format that
> the datetime should be in when used in the SQL Insert command?
>
> Just a thought...
>
>
> "Peter Gloor" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>>Try something like:
>>
>>string strSQL = "INSERT INTO photos (photoID, location, phDate,
>>category, caption) VALUES (" + newPhotoID + ", " + newLocation
>>+ ", " + newDate.ToString() + ", " + newCategory + ", " +
>>newCaption + ");" ;
>>
>>
>>Peter
>>
>>
>
>
>
get rid of the plus sign in front of newPhotoID. Perhaps it is confused.

It might be the semi-colon at the end of the SQL statement.
SQL doesn't need semi-colons to terminate a statement.

Hope that helps

Ged

MacKenzieMouse wrote:
> CKa wrote:
>
>> Could it also be that the DateTime.ToString() format isn't the format
>> that
>> the datetime should be in when used in the SQL Insert command?
>>
>> Just a thought...
>>
>>
>> "Peter Gloor" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>
>>> Try something like:
>>>
>>> string strSQL = "INSERT INTO photos (photoID, location, phDate,
>>> category, caption) VALUES (" + newPhotoID + ", " + newLocation
>>> + ", " + newDate.ToString() + ", " + newCategory + ", " +
>>> newCaption + ");" ;
>>>
>>>
>>> Peter
>>>
>>>
>>
>>
>>
> get rid of the plus sign in front of newPhotoID. Perhaps it is confused.