2

I'm attempting to create a piece of software that allows a customer to upload data generated by a piece of equipment into their own database.

To do this I designed a dataset in Visual Studio 2015 based on a *.mdb file with the desired tables. The table adapter wizard did a wonderful job of designing table adapters and queries for the strongly typed dataset. All types in this table are Text except for the Date, and Time fields, which are Date/Time. The statement generated was as follows:

INSERT INTO `tblVoltechRunID` (`RunID`, `Date`, `Time`, `ATUnitID`, `PartID`, `FixtureID`, `OperatorID`, `BatchID`, `TransformerSerialNo`, `OverallResult`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

However, when switching to an SQL database, the grave accents caused the insert statement to fail due to a syntax error. Removing the grave accents manually caused the query to succeed.

Is it possible to use visual studio's dataset designer to make a cross platform compatible TableAdapter?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Jonathan Riger
  • 33
  • 7

2 Answers2

1

Is it possible to use visual studio's dataset designer to make a cross platform compatible TableAdapter?

You might be able to take advantage of the fact that ...

  1. Both Access SQL and T-SQL support square brackets as delimiters for table and column names, and
  2. System.Data.OleDb allows named parameters, although it ignores the names and treats parameters as strictly positional

... and hack the generated CommandText for the InsertCommand to

INSERT INTO [tblVoltechRunID] 
([RunID], [Date], [Time], [ATUnitID], [PartID], [FixtureID], [OperatorID], [BatchID], [TransformerSerialNo], [OverallResult]) 
VALUES 
(@RunID, @Date, @Time, @ATUnitID, @PartID, @FixtureID, @OperatorID, @BatchID, @TransformerSerialNo, @OverallResult)

I just tested something like that with a TableAdapter for a table in an Access database and it worked for me, although it broke the Designer View of the TableAdapter (and, to be honest, it all seems a bit too dodgy for my liking).

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks for the great feedback. I agree hacking the generated command text is dodgy, however the command text is generated like this if an SQL database is used so perhaps hacking will not be required. – Jonathan Riger Oct 27 '16 at 16:17
0

? is not valid name parameter for your SqlCommand.Parameters. You should use name parameters.

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn; //your SqlConnection object

cmd.CommandText = @"
INSERT INTO tblVoltechRunID 
     (RunID, Date, Time, ATUnitID, PartID, FixtureID, OperatorID, BatchID, TransformerSerialNo, OverallResult) 
VALUES 
     (@RunID, @Date, @Time, @AtUnitID, @PartID, @FixtureID, @OperatorID, @BatchID, @TransformerSerialNo, @)";

cmd.Parameters.AddWithValue("RunID", runID); //runID value which you want to put in RunID variable
//define other parameters in same way
mybirthname
  • 17,949
  • 3
  • 31
  • 55
  • Good point about the parameter names, but `SqlCommand#ExecuteNonQuery()` will still throw an exception when it encounters the backquotes around the column names. – Gord Thompson Oct 25 '16 at 22:41
  • @GordThompson ahh I remember that you can escape fields like this here, it looks like I'm mistaken. – mybirthname Oct 26 '16 at 06:24