How to pass a Variable/Function into SQL Query?

I am attempting to Insert a Row on a Table, but I cannot seem to pass the TASK_ID as a value/variable/function into the SQL Statement. Throughout the application, to acquire TASK_ID, I use:

Dim taskID = ds.Tables("ReminderTable").Rows(Count).Field(Of Integer)("TASK_ID")

Some helpful context-code :

da.Fill(ds, "ReminderTable")

Count = ds.Tables("ReminderTable").Rows.Count - 1

Here is what I’m trying to do:

da.InsertCommand = New OleDbCommand("INSERT INTO ACTIVITY (TASK_ID, ACTIVITY_CDE, ACTIVITY_NOTE) VALUES (3827, 1, 'Reminder Sent: ' & Now)", Connection)

I keep receiving the error

No value given for one or more required parameters.

when I replace 3827 with a variable/function/reference.

The statement works as-is, but I need to replace “3827” in the above statement with the TASK_ID, but it won’t accept my variable, a function or the location that I use elsewhere throughout the project to reference the same thing.

I’m working in Visual Studio 2019, using a Microsoft Access Database

Answer

Use String Interpolation

da.InsertCommand = New OleDbCommand(
$"INSERT INTO ACTIVITY (TASK_ID, ACTIVITY_CDE, ACTIVITY_NOTE) 
  VALUES ({taskID}, 1, 'Reminder Sent: ' & Now())", Connection)

If getting the date and time from .NET, you would be able to add that with string interpolation as well

da.InsertCommand = New OleDbCommand(
$"INSERT INTO ACTIVITY (TASK_ID, ACTIVITY_CDE, ACTIVITY_NOTE) 
  VALUES ({taskID}, 1, 'Reminder Sent: {DateTime.Now}')", Connection)

Advertisement

Answer