Have you seen this post? It seems to be related to your query:
If you pass through the parameters via cmd.Parameters.Add()
then it'll convert the values for you. Their example is C# but the API should be the same.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hey all
I have no problem when all my access mdb are string!
I'm using Access database engine 2010 to connect to mdb.
The problem occurs when I have Boolean / Integer / Date fields:
New OleDbCommand("INSERT INTO tableblah (strfield, intfield, boolfield, datefield) VALUES ('" + strTextBoxX.Text + "' , '" + numIntegerInput.Value.ToString + "' , '" + boolCheckBoxX.Checked.ToString + "' , '" + DateTimePicker.Value.ToString + "')", MyConnection)
Seems that Yes/No field cannot get True/False? What should I pass in my command string?
About integer and date field how should I format it?
Thanks
Have you seen this post? It seems to be related to your query:
If you pass through the parameters via cmd.Parameters.Add()
then it'll convert the values for you. Their example is C# but the API should be the same.
Hi @StewartBW , Welcome to Microsoft Q&A,
When dealing with different data types in your Access database (such as Boolean, Integer, and Date fields), it's important to properly format the values in your SQL command string to ensure they are correctly interpreted by the database engine. Here's how you can adjust your command string:
-1
for True
and 0
for False
when inserting Boolean values into Yes/No fields.#yyyy-MM-dd#
is commonly used in SQL commands for dates in Access databases.Here's how you can adjust your command string to handle different data types:
string commandString = "INSERT INTO tableblah (strfield, intfield, boolfield, datefield) VALUES ('"
+ strTextBoxX.Text + "' , "
+ numIntegerInput.Value.ToString() + " , "
+ (boolCheckBoxX.Checked ? "-1" : "0") + " , #"
+ DateTimePicker.Value.ToString("yyyy-MM-dd") + "#)";
OleDbCommand command = new OleDbCommand(commandString, MyConnection);
This should ensure that the command string correctly handles different data types when inserting records into the Access database. As Paul mentioned, use parameterized queries to handle potential SQL injection vulnerabilities instead of concatenating values directly into SQL command strings.
Best Regards,
Jiale
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hello and welcome!
I would recommend using Dapper for this which you add to your project as an NuGet package here.
I put together a console project to show how to add a new record and read all records in this project. The same will work in a Windows Forms project.
To try this in your project
Class which represents a row in the table
public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime BirthDate { get; set; }
public bool Active { get; set; }
public int SomeNumber { get; set; }
}
Code which first adds a new record followed by reading all records and dumping the records using another NuGet package (you do not need this part).
using System.Data;
using System.Data.OleDb;
using Dapper;
using DapperForAccessDatabaseMdb.Classes;
#pragma warning disable CA1416
namespace DapperForAccessDatabaseMdb;
internal partial class Program
{
private static IDbConnection _cn;
private static void Main()
{
_cn = new OleDbConnection(ConnectionString());
Person person = new()
{
FirstName = "John",
LastName = "Doe",
BirthDate = new(1980, 1, 1),
Active = true,
SomeNumber = 123
};
Add(person);
var people = GetAll();
AnsiConsole.MarkupLine(ObjectDumper.Dump(people));
Console.ReadLine();
}
public static void Add(Person person)
{
const string statement =
"""
INSERT INTO Person
(
FirstName,LastName,BirthDate,Active,SomeNumber
)
VALUES
(
@FirstName,@LastName,@BirthDate,@Active,@SomeNumber
)
""";
_cn.Execute(statement, new
{
person.FirstName,
person.LastName,
person.BirthDate,
person.Active,
person.SomeNumber
});
}
public static List<Person> GetAll()
=> _cn.Query<Person>("SELECT * FROM Person").ToList();
}
Note there no need to open and close the connection, Dapper does this for you.