SQL Insertion Problem

[coop123]

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

[finito]

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.

[coop123]

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

[coop123]

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

[finito]

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 ?

[finito]

P.S please you use Code Tagging.

your code is too hard to read like this.

Thanks

[kvprajapati]

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

[powerbox]

This is a better code.
You can even add the datatype per parameter.

[finito]

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.

[coop123]

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