Anyone tell me what's wrong with this SQL statement?

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

re: Anyone tell me what's wrong with this SQL statement?

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:[color=blue]
> 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);
> }
> }
>
>
>[/color]

re: Anyone tell me what's wrong with this SQL statement?

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" <outsourcers.r@us.india> wrote in message
news:eNLDd.722$pZ4.140@newsread1.news.pas.earthlin k.net...[color=blue]
> 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:[color=green]
>> 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);
>> }
>> }
>>
>>
>>[/color]
>[/color]

re: Anyone tell me what's wrong with this SQL statement?

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" <replytogroup@please.com> wrote in message
news:Or2Co2T9EHA.3376@TK2MSFTNGP12.phx.gbl...[color=blue]
> 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[/color]
inside[color=blue]
> 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'[/color]
error[color=blue]
> 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)
>
> --------------------------------------------------------------------------[/color]
--------------[color=blue]
>
> 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= "
>[/color]
+System.IO.Path.GetDirectoryName(System.Reflection .Assembly.GetExecutingAsse
mbly().Location)[color=blue]
> + @"\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);
> }
> }
>
>
>[/color]

re: Anyone tell me what's wrong with this SQL statement?

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" <replytogroup@please.com> wrote in message
news:Or2Co2T9EHA.3376@TK2MSFTNGP12.phx.gbl...[color=blue]
> 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);
> }
> }
>
>
>[/color]

re: Anyone tell me what's wrong with this SQL statement?

Try something like:

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


Peter

re: Anyone tell me what's wrong with this SQL statement?

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" <p_gloor@hotmail.com> wrote in message
news:OtOt2Cw9EHA.1396@tk2msftngp13.phx.gbl...[color=blue]
> Try something like:
>
> string strSQL = "INSERT INTO photos (photoID, location, phDate,
> category, caption) VALUES (" + newPhotoID + ", " + newLocation
> + ", " + newDate.ToString() + ", " + newCategory + ", " +
> newCaption + ");" ;
>
>
> Peter
>
>[/color]

re: Anyone tell me what's wrong with this SQL statement?

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" <p_gloor@hotmail.com> wrote in message
news:OtOt2Cw9EHA.1396@tk2msftngp13.phx.gbl...[color=blue]
> Try something like:
>
> string strSQL = "INSERT INTO photos (photoID, location, phDate,
> category, caption) VALUES (" + newPhotoID + ", " + newLocation
> + ", " + newDate.ToString() + ", " + newCategory + ", " +
> newCaption + ");" ;
>
>
> Peter
>
>[/color]

re: Anyone tell me what's wrong with this SQL statement?

CKa wrote:[color=blue]
> 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" <p_gloor@hotmail.com> wrote in message
> news:OtOt2Cw9EHA.1396@tk2msftngp13.phx.gbl...
>[color=green]
>>Try something like:
>>
>>string strSQL = "INSERT INTO photos (photoID, location, phDate,
>>category, caption) VALUES (" + newPhotoID + ", " + newLocation
>>+ ", " + newDate.ToString() + ", " + newCategory + ", " +
>>newCaption + ");" ;
>>
>>
>>Peter
>>
>>[/color]
>
>
>[/color]
get rid of the plus sign in front of newPhotoID. Perhaps it is confused.