string sqlInsert = "INSERT INTO Transaction " +
                "(VIN, Price, TranDate, TranType) " +
                "VALUES('" + VIN + "', '" + price + "', '" + aDate + "', '" + transType + "')";
            // check SQL string in Output window for debugging
            Console.WriteLine(sqlInsert);
            myDataAdapter = new OleDbDataAdapter();
            //insert new account into database
            try
            {
                myDataAdapter.InsertCommand = new OleDbCommand(sqlInsert);
                myAccessConn = ConnectionClass.getConnection();
                if (myAccessConn.State == ConnectionState.Closed)
                {
                    myAccessConn.Open();
                }
                myDataAdapter.InsertCommand.Connection = myAccessConn;
                myDataAdapter.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: Insert in DA failed");
                Console.WriteLine(e);
            }

Edited 6 Years Ago by __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

What error are you getting

remove ' ' for price unless its of type string

"', " + price + ", '"

Shouldn't make a difference you should still get whatever error ur getting.

What error are you getting

remove ' ' for price unless its of type string

"', " + price + ", '"

Shouldn't make a difference you should still get whatever error ur getting.

/////////////////////////////////////////////////////////////////////////////////////////

Syntax error in INSERT INTO statement.
This is to save a transaction from a person buying a car and returning the transaction ID from access.

public string save(Transaction aTransaction)
        {
            //get values from Transaction instance
            string transID = "";
            string VIN = aTransaction.getVIN();
            decimal price = aTransaction.getPrice();
            DateTime aDate = aTransaction.getDate();
            string transType = aTransaction.getVehicleType();

            //get back the new Transaction number

            string sqlInsert = "INSERT INTO Transaction " +
                "(VIN, Price, TranDate, TranType) " +
                "VALUES('" + VIN + "', '" + price + "', '" + aDate + "', '" + transType + "')";
            // check SQL string in Output window for debugging
            Console.WriteLine(sqlInsert);
            myDataAdapter = new OleDbDataAdapter();
            //insert new customer into database
            try
            {
                myDataAdapter.InsertCommand = new OleDbCommand(sqlInsert);
                myAccessConn = ConnectionClass.getConnection();
                if (myAccessConn.State == ConnectionState.Closed)
                {
                    myAccessConn.Open();
                }
                myDataAdapter.InsertCommand.Connection = myAccessConn;
                myDataAdapter.InsertCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: Insert in DA failed");
                Console.WriteLine(e);
            }
            //get back the new transaction ID assigned by DBMS
            try
            {
                // get highest transID from database, which is the new transID
                string sqlSelect = "SELECT top 1 TransID FROM Transaction order by TransID DESC";
                // check SQL string in Output window
                Console.WriteLine(sqlSelect);
                myAccessConn = ConnectionClass.getConnection();
                myDataAdapter = new OleDbDataAdapter(sqlSelect, myAccessConn);
                if (myAccessConn.State == ConnectionState.Closed)
                {
                    myAccessConn.Open();
                }
                myDataSet = new DataSet();
                myDataAdapter.Fill(myDataSet, "transactionID");
                DataRowCollection dra = myDataSet.Tables["transactionID"].Rows;
                foreach (DataRow dr in dra)
                {
                    transID = dr[0].ToString();
                    Console.WriteLine(transID);
                }

                //return the new trans ID
                Console.WriteLine(transID);
                return transID;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: Failed to get new customer ID");
                return "";
            }
            finally
            {
                myAccessConn.Close();
            }

Edited 6 Years Ago by __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

/////////////////////////////////////////////////////////////////////////////////////////

Attachments Capture.PNG 3.92 KB

take off all the try statements to see which line the error occurs.

shouldn't

SELECT top 1 TransID FROM Transaction order by TransID DESC

be

SELECT MAX(TransID) + 1 FROM Transaction order by TransID DESC

aDate what format do you have it in? yyyymmdd ?

Try to understand the use of Parameters.

OleDbCommand cmd=new OleDbCommand();
cmd.CommandText="insert into Transaction  (vin,price,trandate,trantype) values (@vin,@price,@trandate,@trantype)";

cmd.Connection=ConnectionClass.getConnection();

cmd.Parameters.AddWithValue("@vin",VIN);
cmd.Parameters.AddWithValue("@price",price);
cmd.Parameters.AddWithValue("@trandate",aDate);
cmd.Parameters.AddWithValue("@trantype",transType);
            
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

wow adatapost, didn't know you could do that.

I still find this method is faster tho, but harder to debug.

you will need
using System.Data.SqlClient;
using System.Data.SqlTypes;

new SqlCommand("insert into Transaction  (vin, price, trandate, trantype) values ('"+vin+"', "+price+" , '"+trandate+"', '" +trantype+"')", myAccessConn).ExecuteNonQuery();

This way you take your reliance on Dataset off, by working directly with the SQLServer.


To pull data you would

SqlDataReader sqlSelect = new SqlCommand("SELECT top 1 TransID FROM Transaction order by TransID DESC").ExecuterReader();

while (sqlSelect.Read())
{
  
     transID = sqlSelect.getInt32(0);
     Console.WriteLine(transID);
}

you can make it even shorter

transID = new SqlCommand("SELECT top 1 TransID FROM Transaction order by TransID DESC").ExecuteScaler().toString()
Console.WriteLine(transID);

This code is much faster cause there is no middle man (dataSet), but it is much harder to debug.

Thanks for all the assistance. They were all very helpful.

This question has already been answered. Start a new discussion instead.