INSERT Statement

< BACK  NEXT >

[oR]

The SQL INSERT statement can be used as a convenient way of adding records to database tables. The general form of the INSERT statement is as follows:

 INSERT INTO <tablename> (<Field1>, <Field2>)     VALUES (<Value1>, <VALUE2>) 

If you are adding values for each of the fields in the table, and the fields are supplied in the same order as they occur in the table, you can use an alternate form of INSERT which does not require you to specify the field names:

 INSERT INTO <tablename>     VALUES (<Value1>, <VALUE2>) 

You need to be careful using this form of INSERT since the statement will fail if the table structure is changed by, for example, adding new fields.

For the customer added earlier in the chapter, the following insert statements can be used to add an order with two OrderDetail records associated with it.

 INSERT INTO Orders (     OrderNum, CustNum, Description, DateAdded)     VALUES(2000, 1, 'A First Order', '12-June-2000'); INSERT INTO OrderDetails(     OrderNum, Product, Quantity)     VALUES(2000, 'Chocolate Bars', 10); INSERT INTO OrderDetails(     OrderNum, Product, Quantity)     VALUES(2000, 'Ice Creams', 20); 

Note that a value is not supplied for OrderDetailNum in the table "OrderDetails." This is because this field is auto-increment, and SQL Server for Windows CE supplies the value. Listing 16.12 shows the ADO code for executing these INSERT statements.

Listing 16.12 The INSERT statement

 void Listing16_12() {   AdoNS::_ConnectionPtr pConnection;   if(!GetConnection(pConnection))       return;   _bstr_t bStrSQL(_T("INSERT INTO Orders ( \       OrderNum, CustNum, Description, DateAdded) \        VALUES(2000, 1, 'A First Order', \       '12-June-2000')"));   ExecuteSQL(pConnection, bStrSQL);   bStrSQL = _T("INSERT INTO OrderDetails( \       OrderNum, Product, Quantity) \       VALUES(2000, 'Chocolate Bars', 10)");   ExecuteSQL(pConnection, bStrSQL);   bStrSQL = _T("INSERT INTO OrderDetails(\       OrderNum, Product, Quantity) \       VALUES(2000, 'Ice Creams', 20)");   ExecuteSQL(pConnection, bStrSQL);   cout   _T("Record Added")   endl;   pConnection->Close(); } 

The records added in Listing 16.12 can be queried from the database using a SELECT statement with a JOIN, such as the following:

 SELECT * FROM Orders JOIN OrderDetails     ON (Orders.OrderNum = OrderDetails.OrderNum) 

The code in Listing 16.13 opens a recordset on this SELECT statement to return all the orders and related OrderDetails records. The opening of the recordset is very similar to Listing 16.6. The code to display the contents of the recordset is generic it can list the field names and values for any fields collection passed into it. A "for" loop is used to iterate across all the fields in the fields collection, using the GetItem and an integer index to obtain a pointer to each field. The name of the field is obtained through the "Name" property, and the value from the GetValue function. GetValue will return a VARIANT with the vt value containing an appropriate value for the underlying field in the table (such as VT_I4, VT_DATE, and so on). Since the data is to be displayed, the easiest thing to do is convert the VARIANT to a BSTR regardless of the origi- nal data type. The _variant_t class member "ChangeType" can do this, as follows:

 varValue.ChangeType(VT_BSTR, NULL); 

This function is passed the data type to convert the VARIANT to and a second parameter specifying where the converted VARIANT should be placed. Passing NULL specifies that the conversion should take place in situ, and the original variant value is replaced by the newly converted value.

Listing 16.13 The SELECT with JOIN statement

 void DisplayOrders(AdoNS::FieldsPtr & pFields) {   AdoNS::FieldPtr pField;   _variant_t varValue, varIndex, varStringValue;   _bstr_t bstrIndex;   for(short i = 0; i < pFields->Count; i++)   {     varIndex = i;     pField = pFields->GetItem(varIndex);     cout   (LPTSTR)pField->Name   _T(":");     varValue = pField->GetValue();     varValue.ChangeType(VT_BSTR, NULL);     cout   varValue.bstrVal   _T(" ");   }   cout   endl; } void Listing16_13() {   HRESULT hr;   AdoNS::_RecordsetPtr pRecordset;   _bstr_t bstrConnection(lpConnection);   _variant_t varConnection(bstrConnection);   _bstr_t bstrQuery(_T("SELECT * FROM Orders \     JOIN OrderDetails \     ON (Orders.OrderNum = OrderDetails.OrderNum)"));   _variant_t varQuery(bstrQuery);   hr = pRecordset.CreateInstance       (_T("ADOCE.Recordset.3.1"));   if(FAILED(hr))   {     cout   _T("Could not create recordset:")            hr   endl;     return;   }   // Open the base table and retrieve rows   //   hr = pRecordset->Open(varQuery,           varConnection,           AdoNS::adOpenStatic,           AdoNS::adLockReadOnly,           AdoNS::adCmdText);   if(FAILED(hr))   {       cout   _T("Could not open recordset")   endl;       return;   }   while(!pRecordset->GetA_EOF())   {       AdoNS::FieldsPtr pFields;       pFields = pRecordset->GetFields();       DisplayOrders(pFields);       pRecordset->MoveNext();   }   pRecordset->Close(); } 

< BACK  NEXT >