CODEWITHSUNDEEP
sqlvb.netms-access-2007oledb
user961627
user961627

Reputation: 12747

Parameter missing in ExecuteNonQuery command, using in VB.NET and Access

I have a very simple database in Access 2007 that I'm connecting to using VB 2010. There are two tables, MenuItems and Orders, and Orders.orderDate is of type "Date".

I'm running the following code in one of my VB forms (the connection string and everything else is fine):

  sql = "SELECT OrderDate, MenuItem FROM MenuItems, Orders WHERE Orders.itemID = MenuItem.ID AND Orders.orderDate BETWEEN '" + fromDate + "' AND '" + toDate + "'"
  Dim cmd As New OleDb.OleDbCommand(sql, con)
  Dim count As Integer = cmd.ExecuteNonQuery()

But I get an error that:

System.Data.OleDb.OleDbException (0x80040E10): value wan't given for one or more of the required parameters

Nothing seems to be missing. I've used the same code for another query, except the sql was different. But I think my sql is simple enough. Here's the sql that was generated in one instance (I've double checked, all table and column names are correct):

SELECT OrderDate, MenuItem From MenuItems, Orders WHERE Orders.itemID = MenuItem.ID AND Orders.orderDate BETWEEN '11/21/2012' AND '11/24/2012'

Upvotes: 0

Views: 2767

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112682

You should use parametrized queries for at least two reasons.

  1. You don't have to worry about date (and other) literals and locale problems.

  2. You don't have to worry about SQL injection attacks, where someone enters malicious code in a text box that turns a SQL statement into a harmful one.

Change your statement to

sql = "SELECT Orders.OrderDate, MenuItems.MenuItem " & _
    "FROM MenuItems INNER JOIN Orders ON MenuItems.ID = Orders.itemID " & _
    "WHERE Orders.orderDate BETWEEN ? AND ?"

Then execute the command like this

Dim fromDate, toDate As DateTime

fromDate = DateTime.Parse(fromDateTextBox.Text)
toDate = DateTime.Parse(toDateTextBox.Text)

Dim dataset As New DataSet()
Using conn As New OleDbConnection(connectionString)
    Using adapter As New OleDbDataAdapter()
        Dim cmd As New OleDbCommand(sql, conn)
        cmd.Parameters.Add("?", fromDate)
        cmd.Parameters.Add("?", toDate)
        adapter.SelectCommand =  cmd
        adapter.Fill(dataset)
    End Using
End Using

Upvotes: 1

user961627
user961627

Reputation: 12747

The issue was a mis-spelled table. (MenuItem instead of MenuItems), but it didn't solve the question, I still got an error. It turned out to be a problem with matching formats between the database and the datepicker values being used as query parameters.

So I made sure I saved to the database in short Date Format:

sql = "INSERT INTO Orders(itemID, OrderDate) VALUES('" + ListBox1.SelectedValue.ToString() + "','" + FormatDateTime(OrderDate.Value, DateFormat.ShortDate) + "')"

Upvotes: 1

syed mohsin
syed mohsin

Reputation: 2938

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement. Since you are using Select statement, you should be using oledbDataAdapter and Fil DataSet for use. 

Dim conn As New OleDbConnection(con)
Dim adapter As New OleDbDataAdapter()
sql = "SELECT OrderDate, MenuItem FROM MenuItems, Orders WHERE Orders.itemID = MenuItem.ID AND Orders.orderDate BETWEEN '" + fromDate + "' AND '" + toDate + "'"
adapter.SelectCommand = new OleDbCommand(sql, con)
adapter.Fill(dataset)
Return dataset

Upvotes: 1

Related Questions