Inserting from VB.NET to SQL Compact
-
Not sure if this belongs here or the databases forum. Interesting little problem this one. I am creating a list (of string) of statements to be inserted into a database table in SQL Compact (v3.1)
listParams.Add("INSERT INTO [SYS_tbl_SelectText] ([SQLTitle],[SQLStatement]) VALUES ('APPTHISTORY','SELECT SDD_tbl_Appointments.AppointmentDate AS [Appt Date], SDD_tbl_Appointments.AppointmentStart AS [Starts], SDD_tbl_Appointments.AppointmentEnd AS [Ends], (SDD_tbl_Employees.FirstName+' '+SDD_tbl_Employees.LastName) AS [With], SDD_tbl_Treatments.Description AS [Treatment], SDD_tbl_Appointments.FullyPaid AS [Paid], SDD_tbl_Appointments.ValueGross AS [Value], SDD_tbl_Appointments.GrossOutStanding AS [Value Outstanding], SDD_tbl_Appointments.GoodsOutstanding AS [Goods Outstanding] FROM SDD_tbl_Appointments INNER JOIN SDD_tbl_Employees ON SDD_tbl_Appointments.EmployeeID = SDD_tbl_Employees.EmployeeID INNER JOIN SDD_tbl_Treatments ON SDD_tbl_Appointments.TreatCode = SDD_tbl_Treatments.ID')")It goes into the list just fine, but when the contents of the list are executed, this particular one causes an error: There was an error parsing the query. [ Token line number = 1,Token line offset = 276,Token in error = +SDD_tbl_Employees.LastName) AS [With], SDD_tbl_Treatments.Description AS [Treatment], SDD_tbl_Appointments.FullyPaid AS [Paid], ] Any ideas how to resolve this? It seems it does not like the +' '+ -
Not sure if this belongs here or the databases forum. Interesting little problem this one. I am creating a list (of string) of statements to be inserted into a database table in SQL Compact (v3.1)
listParams.Add("INSERT INTO [SYS_tbl_SelectText] ([SQLTitle],[SQLStatement]) VALUES ('APPTHISTORY','SELECT SDD_tbl_Appointments.AppointmentDate AS [Appt Date], SDD_tbl_Appointments.AppointmentStart AS [Starts], SDD_tbl_Appointments.AppointmentEnd AS [Ends], (SDD_tbl_Employees.FirstName+' '+SDD_tbl_Employees.LastName) AS [With], SDD_tbl_Treatments.Description AS [Treatment], SDD_tbl_Appointments.FullyPaid AS [Paid], SDD_tbl_Appointments.ValueGross AS [Value], SDD_tbl_Appointments.GrossOutStanding AS [Value Outstanding], SDD_tbl_Appointments.GoodsOutstanding AS [Goods Outstanding] FROM SDD_tbl_Appointments INNER JOIN SDD_tbl_Employees ON SDD_tbl_Appointments.EmployeeID = SDD_tbl_Employees.EmployeeID INNER JOIN SDD_tbl_Treatments ON SDD_tbl_Appointments.TreatCode = SDD_tbl_Treatments.ID')")It goes into the list just fine, but when the contents of the list are executed, this particular one causes an error: There was an error parsing the query. [ Token line number = 1,Token line offset = 276,Token in error = +SDD_tbl_Employees.LastName) AS [With], SDD_tbl_Treatments.Description AS [Treatment], SDD_tbl_Appointments.FullyPaid AS [Paid], ] Any ideas how to resolve this? It seems it does not like the +' '+I don't know about SQL Compact, though I assume the syntax is standard T-SQL, but from looking at your code you have a select statement inside quotes. Starting from
TheComputerMan wrote:
'SELECT SDD_tbl_Appointments.AppointmentDate
so the
+' '+will act as a terminator for that string. Try using+ CHAR(13) +instead of+' '+.If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
-
I don't know about SQL Compact, though I assume the syntax is standard T-SQL, but from looking at your code you have a select statement inside quotes. Starting from
TheComputerMan wrote:
'SELECT SDD_tbl_Appointments.AppointmentDate
so the
+' '+will act as a terminator for that string. Try using+ CHAR(13) +instead of+' '+.If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk
Hi, yes this is placing a statement text into a database table. There are many ' characters in the statements but I only get a problem when using the +. Incidentally the identical code in SQL Server 2005 (as opposed to Compact) does not cause a problem - this is where the SELECT statement is used in the same format as part of an execute to create a stored procedure. In this the +' '+ goes straight in with no problem, but I have not tried storing it I must admit, only executing it.