Re: Multiple Tables SELECT command with Microsoft Access 2003

Tech-Archive recommends: Speed Up your PC by fixing your registry

Marina,

Thanks for the response, the ID is a string in the code but a number in
the table. I'll try and convert it and see if that does anything, then
I'll try the parameters.

Thanks again,

Matt
Marina Levit [MVP] wrote:
Is the ID a numeric, or a string? If it is a string, at the very least you
need to delimit it with single quotes so it can be treated as a string
literal. The better solution would be to use a parameterized query.

<matthew.macdonald-wallace@xxxxxxxxxxxxxxxxxxx> wrote in message
news:1164825335.129228.188750@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi all,

For various reasons I am accessing data from an access database with
multiple tables. I am unable to upgrade to SQL Server (even the
express edition), so I have to do this in access.

At the moment, my sql statement is as follows:

"SELECT * FROM software,vendors WHERE vendors.vendorId =
software.vendorId AND software.softwareId = " +
cmbExisitingSoftware.SelectedValue.ToString();

Where cmbExisitingSoftware.SelectedValue.ToString() is the software ID
from a combobox that has been populated from the database.

The issue I have is that when I try and execute this sql statement use
the code provided below, I get an error stating "No value given for one
or more parameters" when it tries to execute the datareader. the code
is as follows:

// create the connection
OleDbConnection dbcx = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=installTracker.mdb");
// open the connection
dbcx.Open();

// setup SQL statement
string sqlSoftwareDetails = "SELECT * FROM software,vendors
WHERE vendors.vendorId = software.vendorId AND software.softwareId = "
+ cmbExisitingSoftware.SelectedValue.ToString();
MessageBox.Show(sqlSoftwareDetails);
OleDbCommand cmdGetSoftware = new
OleDbCommand(sqlSoftwareDetails, dbcx);
OleDbDataReader drGetSoftware =
cmdGetSoftware.ExecuteReader();
while (drGetSoftware.Read())
{
txtDocumentationPath.Text =
drGetSoftware["softwareInstallDocumentationPath"].ToString();
}
dbcx.Close();


I anyone can help, that would be great!

Cheers,

Matt


.

Relevant Pages

  • Re: Error using " in .open with ADODB.Recordset
    ... recordset but I'm getting hung up on the SQL statement. ... query that has a string as a condition. ... Dim mrk As String ...
    (microsoft.public.data.ado)
  • Re: SQL Statement with LIKE %
    ... short string is contained within a bigger field in Access databases. ... But how would I set up the SQL statement if I was trying to find ... the vbscript code that results in the same statement. ... My real problem is that I have a string named AgentString as: ...
    (microsoft.public.inetserver.asp.general)
  • Re: Too few parameters, expected n when executing SQL from VBA
    ... desired SQL statement, either, so I suggest a completely different approach ... Public Sub TestQuery(ByVal strFieldName As String) ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help with SQL statement
    ... > Dim Product As String, TarWgt As Double, FileNum As ... > Exit Sub ... Your SQL statement doesn't work because it is not an UPDATE you need to ... directly from the combo box's Columnto the other text boxes, ...
    (microsoft.public.access.modulesdaovba)
  • RE: Get Values SQL in table
    ... > database engine won't be able to parse the string correctly before executing ... within the SQL statement or the table that is the data source. ... > See http://www.QBuilt.com for all your database needs. ...
    (microsoft.public.access.modulesdaovba)