I’m using a MS Access database. I want to get first and last records that are in between 2 dates. But I get error when using a BETWEEN operator:
Syntax error in number in query expression ‘Datum_k BETWEEN 3.4.2017. AND 3.4.2017.’.
My code:
private void GetPrviZadnjiBrojRacuna()
{
OleDbCommand commandOD = new OleDbCommand("SELECT Dokument FROM DnevniPromet WHERE (Datum_k BETWEEN " + datumOd + " AND " + datumDo + ") ORDER BY [Datum_k] ASC", dataModel.CS);
OleDbCommand commandDO = new OleDbCommand("SELECT Dokument FROM DnevniPromet WHERE [Datum_k] >= " + datumOd + " AND [Datum_k] <= " + datumDo + " ORDER BY [Datum_k] DESC", dataModel.CS);
try
{
dataModel.DT.Clear();
OleDbDataAdapter ODbDA = new OleDbDataAdapter(commandOD);
if (!dataModel.CS.State.Equals(ConnectionState.Open))
{
dataModel.CS.Open();
}
// GET OD
ODbDA.Fill(dataModel.DT);
odRacuna = dataModel.DT.Rows[0].ToString();
// GET DO
ODbDA.SelectCommand = commandDO;
dataModel.DT.Clear();
ODbDA.Fill(dataModel.DT);
doRacuna = dataModel.DT.Rows[0].ToString();
dataModel.CS.Close();
dataModel.DataLoaded = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
marc_s
722k173 gold badges1320 silver badges1443 bronze badges
asked Apr 3, 2017 at 11:48
4
Addressing two issues with your code snippet:
- You should never use string interpolation to build a query. This lends itself to SQL injection. Many, many topics surrounding this. I suggest you read up.
- You have missed the
'single quotes around your date strings.
Using parameterised queries, you can kill two birds with one stone:
OleDbCommand commandOD = new OleDbCommand(@"
SELECT Dokument
FROM DnevniPromet
WHERE (Datum_k BETWEEN @datumOd AND @datumDo)
ORDER BY [Datum_k] ASC", dataModel.CS);
commandOD.Parameters.AddRange(new OleDbParameter[]
{
new OleDbParameter("@datumOd", datumOd),
new OleDbParameter("@datumDo", datumDo)
});
solujic
8851 gold badge16 silver badges40 bronze badges
answered Apr 3, 2017 at 11:57
Chris PickfordChris Pickford
8,5265 gold badges47 silver badges72 bronze badges
2
|
|
Originally Posted by techgnome
See more:
Hi Guys,
I am doing an Electronic Library Management System that includes counting of time for each student who logged in every time he/she goes or used e-library. But I’m having trouble in frmReserve.
Here is the error that I got:
Syntax error in date in query expression ‘tblReserve.studID=tblStudents.StudNumber and tblReserve.inDate =#00/30/2012#’.
Below are m
<pre lang="vb">
y codes:
this code is executed when the command cmdLoad button is clicked
Private Sub cmdLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoad.Click MsgBox(Today.ToString) paintStudList("SELECT tblReserve.pcNum, tblStudents.FullName, tblReserve.numOfTime FROM tblReserve,tblStudents where tblReserve.studID=tblStudents.StudNumber and tblReserve.inDate =#" & Today.ToString("mm/dd/yyyy") & "#", dgrid2, "PC NUMBER", "STUDENT NAME", "NUMBER OF TIME") dgrid2.Columns.Item(0).ReadOnly = True dgrid2.Columns.Item(1).ReadOnly = True End Sub
the following codes are executed if the SQL above is true
Sub paintStudList(ByVal newSQL As String, ByVal myGrid As System.Object, ByVal str1 As String, ByVal str2 As String, ByVal str3 As String) Dim adap1 As OleDbDataAdapter Dim dt1 As New DataTable Dim con As New OleDbConnection con.ConnectionString = "PROVIDER=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "Library2.mdb; JET OleDb:Database Password=3Musketeers" adap1 = New OleDbDataAdapter(newSQL, con) con.Open() adap1.Fill(dt1) myGrid.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing dt1.Columns.Item(0).ColumnName = str1 dt1.Columns.Item(1).ColumnName = str2 dt1.Columns.Item(2).ColumnName = str3 myGrid.DataSource = dt1 con.Close() End Sub
Hope someone could help me…
thanks in advance
Replace the «#» characters with quotes, and put the date into ISO format:
paintStudList("SELECT tblReserve.pcNum, tblStudents.FullName, tblReserve.numOfTime FROM tblReserve,tblStudents where tblReserve.studID=tblStudents.StudNumber and tblReserve.inDate ='" & Today.ToString("yyyy-MM-dd") & "'", dgrid2, "PC NUMBER", "STUDENT NAME", "NUMBER OF TIME")
If you used a parameterized query instead of this very shaky string concatentation, you wouldn’t have this problem at all and your code would be much more readable and maintainable.
VB.NET Parameterized Query[^]
Hi OriginalGriff,
I already resolved this by myself. The thing here is that, I did not noticed that the tblReserve.studID field on my database is in different data type (Text) whereas tblStudents.StudNumber is in Integer format. But still thanks for your quick response. 
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
- Remove From My Forums
-
Question
-
I have one select statement to execute on the access database on the click of a button. when i click on the button i am getting this error «Syntax error (missing operator) in query expression ‘Customer_Name = Summer Terry’.» The code that
i have written is pasted below and also the error log. Can anyone please find and correct the error in my code.using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Cyber_Application { public partial class RegularCustomer : Form { int flag = 0; string currentFile; string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Clients\S V Cyber Cafe\Cyber Application\Cyber Application\param.accdb; Jet OLEDB:Database Password=12345"; public RegularCustomer() { InitializeComponent(); } private void btnOK_Click(object sender, EventArgs e) { string address = ""; string date = ""; string InTime = ""; string OutTime = ""; date = DateTime.Now.ToShortDateString(); address = txtAddress.Text; InTime = txtInHour.Text + ":" + txtInMin.Text + " " + listBoxIn.SelectedItem.ToString(); OutTime = txtOutHour.Text + ":" + txtOutMin.Text + " " + listBoxOut.SelectedItem.ToString(); MessageBox.Show("Your address is:n" + address + "nToday's date is:n" + date + "nYour In Time is:n" + InTime + "nYour Out Time is:n" + OutTime); } private void btnCancel_Click(object sender, EventArgs e) { this.Close(); } private void addCustomers() { OleDbConnection cn = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand("select Customer_Name from Customers", cn); cn.Open(); OleDbDataReader dr = cmd.ExecuteReader(); if (dr != null) { while (dr.Read()) { cBoxCustomers.Items.Add(dr["Customer_Name"]); } } cBoxCustomers.SelectedIndex = 0; } private void RegularCustomer_Load(object sender, EventArgs e) { addCustomers(); btnOK.Enabled = false; btnBrowse.Enabled = false; btnSelectCustomer.Enabled = false; txtName.Enabled = false; txtAddress.Enabled = false; txtContactNo.Enabled = false; txtIdProof.Enabled = false; txtIssuedBy.Enabled = false; } private void cBoxCustomers_SelectedIndexChanged(object sender, EventArgs e) { if (cBoxCustomers.SelectedIndex == 0) { btnSelectCustomer.Enabled = false; } else { btnSelectCustomer.Enabled = true; } } private void btnSelectCustomer_Click(object sender, EventArgs e) { using (OleDbConnection cn = new OleDbConnection(connectionString)) { cn.Open(); string scmd = "select [Customer_Name], [Address], [Contact_No], [Gender], [Issued_By] from Regular_Customers where Customer_Name = " + cBoxCustomers.SelectedItem.ToString(); OleDbCommand cmd = new OleDbCommand(scmd, cn); OleDbDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { txtName.Text = sdr[0].ToString(); ; txtAddress.Text = sdr[1].ToString(); txtContactNo.Text = sdr[2].ToString(); if (sdr[3].ToString() == "M") { radioBtnMale.Select(); } if (sdr[3].ToString() == "F") { radioBtnFemale.Select(); } txtIssuedBy.Text = sdr[4].ToString(); } } } } }
The error code is:
System.Data.OleDb.OleDbException was unhandled Message=Syntax error (missing operator) in query expression 'Customer_Name = Summer Terry'. Source=Microsoft Office Access Database Engine ErrorCode=-2147217900 StackTrace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at Cyber_Application.RegularCustomer.btnSelectCustomer_Click(Object sender, EventArgs e) in E:ClientsS V Cyber CafeCyber ApplicationCyber ApplicationRegularCustomer.cs:line 90 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(Form mainForm) at Cyber_Application.Program.Main() in E:ClientsS V Cyber CafeCyber ApplicationCyber ApplicationProgram.cs:line 17 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:
-
Edited by
Monday, September 26, 2011 3:30 AM
-
Edited by
Answers
-
Instead of concatinating a string command, you would be better off using parameters. I’m doing inserts into Access, but the code below should give you a general idea, of how to use parameters. Then you don’t need to worry if you have all the
single quotes in the correct places.using (dal.DBManager db = new dal.DBManager(dal.DataProvider.OleDb, string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}{1};", this.db_Directory, this.db_FileName))) { db.Open(); foreach (DataRow row in dt.Rows) { this.CurCount++; this.TotalCount = (short)(((float)this.CurCount / (float)dt.Rows.Count) * 100); this.StatusMessage = string.Format(Msg, this.CurCount, dt.Rows.Count.ToString(), this.TotalCount.ToString()); db.Command.CommandText = "INSERT INTO [ProgramLanguage Join](ProgramID, LanguageID, AudioFormatID) VALUES(?, ?, ?)"; db.CreateParameters(3); db.AddParameters(0, "ProgramID", Convert.ToInt32(row["ProgramID"])); db.AddParameters(1, "LanguageID", Convert.ToInt16(row["LanguageID"])); db.AddParameters(2, "AudioFormatID", Convert.ToInt16(row["AudioFormatID"])); db.ExecuteNonQuery(CommandType.Text, db.Command.CommandText); worker.ReportProgress(this.TotalCount, StatusMessage); } db.Close(); }
-
Marked as answer by
Martin_Xie
Thursday, September 29, 2011 5:29 AM
-
Marked as answer by
-
enclose the value that you would like to pass with ( ‘ )… as in your code:
« + cBoxCustomers.SelectedItem.ToString();
should be: ‘« + cBoxCustomers.SelectedItem.ToString() +
«‘«;-
Edited by
RoninB
Monday, September 26, 2011 3:38 AM -
Marked as answer by
tapan.desai
Monday, September 26, 2011 6:17 AM
-
Edited by
-
hi Tapan,
please try this:
string scmd = "select [Customer_Name], [Address], [Contact_No], [Gender], [Issued_By] from Regular_Customers where Customer_Name = '" + cBoxCustomers.SelectedItem.ToString()+"'";
Regards, http://shwetamannjain.blogspot.com
-
Proposed as answer by
Shweta Jain (Lodha)
Monday, September 26, 2011 3:41 AM -
Marked as answer by
tapan.desai
Monday, September 26, 2011 6:17 AM
-
Proposed as answer by