Google Apps Script Get Textbox Value Insert into Database

Question

Can someone please help me figure out how to get the value of a textbox to write to a MS SQL database? I have the database connection working PERFECTLY, now I just need to determine how to grab the value of the textbox and write it to the database with Google Apps Script.

Right now the code below will only insert the word TEXT to the database and not the value of the textbox.

function doGet() {
  var app = UiApp.createApplication();

  var panel = app.createAbsolutePanel();
  var label = app.createLabel('Test Textbox');
  var text = app.createTextBox();
  var submit = app.createButton('Submit');

  panel.add(label);
  panel.add(text);
  panel.add(submit);

  app.add(panel);

  // Handlers
  var handler = app.createServerClickHandler('write');
  handler.addCallbackElement(panel);
  submit.addClickHandler(handler);

  return app;
} 

// submit text to database
function write(e) {
  var app = UiApp.getActiveApplication();
  var text = e.parameter.text;

  var conn = Jdbc.getConnection("jdbc:sqlserver://IPAddress:1433;" +
   "databaseName=name;userusername;password=password;");

  var stmt = conn.createStatement();
  var sql = "INSERT INTO dbo.tbl_Student (studentFName) VALUES ('text')";
  var count = stmt.executeUpdate(sql,1)//pass in any integer to get auto incremented IDs back

  // check to see any auto generated keys come back. that is optional. 
  var rs = stmt.getGeneratedKeys();
  while(rs.next()) {
    Logger.log(rs.getString(1));
  }

  rs.close();
  stmt.close();
  conn.close();    

  return app.close();
}

Answer 1

In order to get the value of the textbox passed in, you need to create a name for the textbox:

Modify var text = app.createTextBox(); to var text = app.createTextBox().setName('mytb');.

Then, in your handler function, you can retrieve this by var text = e.parameter.mytb;.

Also, I think you are just inserting literal word 'text' into your db. Instead, try creating the string with the text variable like this:

var sql = "INSERT INTO dbo.tbl_Student (studentFName) VALUES ("+text+")";

Answer 2

The code that worked:

function doGet() {
  var app = UiApp.createApplication();

  var panel = app.createAbsolutePanel();
  var label = app.createLabel('Test Textbox');
  var text = app.createTextBox().setName('mytb').setId('mytb');
  var submit = app.createButton('Submit');

  panel.add(label);
  panel.add(text);
  panel.add(submit);

  app.add(panel);

  // Handlers
  var handler = app.createServerClickHandler('write');
  handler.addCallbackElement(text);
  submit.addClickHandler(handler);

  return app;
}  

// submit text to database
function write(e) {
  var app = UiApp.getActiveApplication();
  var textbox = app.getElementById('mytb');
  var text = e.parameter.mytb;

  var conn = Jdbc.getConnection("jdbc:sqlserver://IPAddress;" +
   "databaseName=database;user=user;password=password;");

  var stmt = conn.createStatement();
  var sql = "INSERT INTO dbo.tbl_Student (studentFName) VALUES ('"+text+"')";
  var count = stmt.executeUpdate(sql,1)//pass in any integer to get auto incremented IDs back

  // check to see any auto generated keys come back. that is optional. 
  var rs = stmt.getGeneratedKeys();
  while(rs.next()) {
    Logger.log(rs.getString(1));
  }

  rs.close();
  stmt.close();
  conn.close();

  return app.close();
}