#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    64
    Rep Power
    12

    Error C++ variable as parameter in SQL


    Here is the code I use to connect to the database:

    DBconnection->ConnectionString=S"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Auslieferverwaltung.mdb;";

    long mynumber=472;

    OleDbDataAdapter* daTitles = new OleDbDataAdapter("SELECT USER.* FROM AV INNER JOIN USER ON [AV].[KD-Nr]=[USER].[Kd-Nr] WHERE [AV].[Serialnumber]='%mynumber%'", DBconnection);

    DataSet* dsTitles=new DataSet();

    xxxx daTitles->Fill(dsTitles);

    dataGrid1->DataSource=dsTitles->Tables->Item[0]->DefaultView;

    The line I marked with xxx causes an error! What is wrong or how must I change the code to use a C++ variable as a parameter in sql?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    USA
    Posts
    144
    Rep Power
    12
    Hi SirX,

    I had a similar problem and passed a variable to the SQL statement in this way:

    int myInteger = 5;

    CString sSql;
    sSql.Format("SELECT * FROM TableName WHERE FieldName = '%i' ", myInteger) ;

    recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL);

    myInteger will be passed into the statement, if you want to pass a string variable you can use %s instead of the %i like above.

    I know the way you're opening your database is different but I hope this gives you some useful info.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    64
    Rep Power
    12
    How can I use a recordset? What must I include?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    USA
    Posts
    144
    Rep Power
    12
    Ok well it looks like you were using a microsoft access database so this could work for you. I use DAO which is marked as depracated but works fine with access and is pretty simple to use.

    //be sure to include:
    #include afxdao.h

    //create your db and recordset objects:
    CDaoDatabase database;
    CDaoRecordset recordset(&database);

    //open your database:
    database.Open("C:\\path\\dbName.mdb");

    //now create a string and format it with whatever SQL statements you need:

    CString sSql;
    sSql.Format("SELECT * FROM TableName WHERE FieldName = 'DesiredStringFieldValue'");

    //then supply that SQL string to the recordset open command:
    recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL);

    //now you have the desired recordset open so you can extract field info for example from it or whatever you want to do using GetFieldValue or SetFieldValue

    //then make sure you close the recordset and db connection
    recordset.Close();
    database.Close();

    If you have any more questions about that let me know I was real frustrated when I had to start working with it. Hope it helps.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    64
    Rep Power
    12
    I could not use this way in my program so I created a new one for testing. But I never worked with recordset so I do not know anything about it. May you please give me some examples how to get data into a datagrid, how to store new data into the database or how to navigate through the recordset?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    USA
    Posts
    144
    Rep Power
    12
    SirX -

    I'm not sure what you mean by getting data into a data grid, but here are examples of :

    1)opening a desired recordset
    2)getting a field value from that recordset
    3)setting a field value in that recordset

    For an example let's make up a fictional table in a database. The database will be called "tutorial.mdb" and there will be one table in the database called "Students". This is what the table looks like:

    StudentName StudentID

    Jo 567
    Susan 568

    It has 2 fields called StudentName and StudentID.

    As before:

    //create your db and recordset objects
    CDaoDatabase database;
    CDaoRecordset recordset(&database);

    //create a SQL string to get the desired recordset open
    //this one will be used to open a recordset from the students table where the StudentName == Susan
    CString sSql;
    sSql.Format("SELECT * FROM Students WHERE StudentName= 'Susan'");

    //Now actually open the database then the recordset
    database.Open("C:\\MyDocuments\\tutorial.mdb");
    recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL);

    //Now you have this recordset open - let's find out what the StudentID of Susan is.
    long tempStudentID = recordset.GetFieldValue("StudentID").lVal;
    //Now tempStudentID holds the number '568'.

    //But maybe that student name is wrong and you need to change it
    //We can change the name of Susan to Beth easily
    recordset.Edit();
    recordset.SetFieldValue("StudentName", "Beth");
    //Now issue the recordset update command since we changed a value
    recordset.Update();

    //Now maybe you want to add a whole new student into the table
    //We still have the other recordset open, adding a new record will just stick it at the appropriate place in the table with no effect on the already open record!
    recordset.AddNew();
    recordset.SetFieldValue("StudentName","Brian");
    recordset.SetFieldValue("StudentID", (long)569);
    recordset.Update;
    //Now you should see a whole new entry in the table for this student

    //close the recordset and database
    recordset.Close();
    database.Close();

    That should give you some info, there are many useful SQL commands you can use in you SQL statement, and don't forget that you can pass variables in all the above statements too:

    //passing a CString variable
    CString nameVariable = "Billy Bob";
    CString sSql1;
    sSql1.Format("SELECT * FROM Students WHERE StudentName = '%s' ", nameVariable);

    int studentIDVariable;
    studentIDVariable= 574;
    //passing an int variable
    CString sSql2;
    sSql2.Format("SELECT * FROM Students WHERE StudentID = '%i' ", studentIDVariable) ;

    //and passing string and numeric variables to SetFieldValue:
    CString f;
    f.Format("Hello");
    recordset.Edit();
    recordset.SetFieldValue("Greeting", (LPCSTR)f);
    recordset.Update();

    long g;
    g = 2003;
    recordset.Edit();
    recordset.SetFieldValue("CurrentYear", g);
    recordset.Update();

    and don't forget to add this to stdafx.h or none of the above will work!!! :

    #ifndef _AFX_NO_DAO_SUPPORT
    #include <afxdao.h> // MFC DAO database classes
    #endif // _AFX_NO_DAO_SUPPORT

    Let me know if this helps and if you have more questions.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    64
    Rep Power
    12

    Thumbs up


    Thanks that helped alot:) I get an error after the program is closed but it seems to be no real problem because the changes are made correctly to the database.

    I used a dataGrid-Object to show the results of the SQL-commands. How do you make your results visible to the user of your program? (For example on feature of my program is to show the user all the data which are in on of the tables of the database.)


    What is wrong with this code:

    CDaoDatabase database;
    CDaoRecordset recordset(&database);

    long lNumber=574;
    CString sSql;
    sSql.Format("SELECT Customer. *FROM Exportversion INNER JOIN Customer ON[Exportversion].[Customer-Number]=[Customer].[Customer-Number] WHERE [Exportversion].[Serialnumber]='%i'",lNumber);

    database.Open("Export.mdb");
    recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL);
    recordset.Close();
    database.Close();


    "recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL)"-> causes an error
    Last edited by SirX; August 7th, 2003 at 03:52 AM.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    64
    Rep Power
    12
    I have found a way to use a variable in my code version too:

    OleDbConnection* DBConnec=new OleDbConnection();
    DBConnec->ConnectionString=S"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Export.mdb;";

    OleDbDataAdapter* DbA = new OleDbDataAdapter();
    String *test=S"SELECT * FROM [XXX]";

    test=test->Replace(S"XXX",comboBox1->Text);
    DbAd->SelectCommand=new OleDbCommand(test, DBConnec);

    DataSet* DSet=new DataSet();
    DbAd->Fill(DSet);
    dataGrid1->DataSource=DSet->Tables->Item[0];

IMN logo majestic logo threadwatch logo seochat tools logo