What is wrong in the SQL statemnet

Question

0.00/5 (No votes)

See more:

VB.NET

CmpCommand = New OleDbCommand("Select * FROM tblCompetitie WHERE((verID)=myVerID) ORDER BY comOmsch", BiljartConnection)
CmpCommand.Parameters.Add(MyVerID)
CmpAdapter = New OleDbDataAdapter
CmpAdapter.SelectCommand = CmpCommand
CmpTable = New DataTable()
CmpAdapter.Fill(CmpTable)

Now I get the error: The OleDbParameterCollection only accepts objects of type OleDbParameter which are not-null and no objects of Int64.'

VerID and myVerID are long integers
myVerId is a saved primary key of a related table and verID = the Foreign key in the current table. I only want the records with the FK equal to the saved PK.

What I have tried:

I tryed several other defnitions of the where clause (with or without parenthesis, ect.)
All the examples are for C# and not for VB

Posted 18-Sep-21 0:28am

Daan Assenberg

Updated 18-Sep-21 6:47am

Dave Kreskowiak

v4

Add a Solution

Comments

Richard MacCutchan 18-Sep-21 6:42am

You forgot to add the parameter values (myVerID and comOmsch) to the CmpCommand object.

Daan Assenberg 18-Sep-21 7:42am

How do you mean? myVerid and VerID contain values to my opinion.

Richard MacCutchan 18-Sep-21 7:51am

Yes but you have merely added those names inside the SQL command, not the values.

Daan Assenberg 18-Sep-21 10:43am

To me it is not clear how to specify parameters in the right way. I have little experience with databases in VB and the examples are in C#.

Richard MacCutchan 18-Sep-21 11:56am

Then use the dropdown at the top of the page to switch the sample code to VB. You will benefit greatly if you make regular use of the MSDN documentation.

Daan Assenberg 18-Sep-21 11:07am

Richard, thanks for your help, but I will solve this in another way. I thought this was a simple question, but seems to be quit complicated.

Richard MacCutchan 18-Sep-21 11:54am

There is nothing complicated, and this is not a database issue. You are passing a string as the SQL command but have not included the actual values of the parameters. Follow the link I gave you below and start to do it the safe and correct way.

Richard MacCutchan 18-Sep-21 12:26pm

I have no idea what you database schema looks like but you need to make sure that your parameter and dbtypes are the same. See OleDbType Enum (System.Data.OleDb) | Microsoft Docs[^] for the relevant mappings.

Daan Assenberg 20-Sep-21 3:32am

Richard, thanks I finally succeeded.

2 solutions

Solution 2

VB

CmpCommand.Parameters.Add(MyVerID)

You cannot add a number as a parameter, you must tell the system, which parameter you are referring to, what type it is and what its value is. See OleDbParameter Class (System.Data.OleDb) | Microsoft Docs[^] for a simple example.

Posted 18-Sep-21 6:47am

Richard MacCutchan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Accepted Answer · 2021-09-18T01:53:00

Solution 1

See OleDbCommand.Parameters Property (System.Data.OleDb) | Microsoft Docs[^] for the correct way to add property values to an OleDbCommand.

Posted 18-Sep-21 1:53am

Richard MacCutchan