VB2010 SQL Error in Select statement.

Hey,

From what I can see this is all correct.

Code:

---

        Dim cn As New ADODB.Connection
        cn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";user id=admin;Jet OLEDB:Database Password=police10;"
        cn.Open()

        Dim cmd = New ADODB.Command
        Dim rs = New ADODB.Recordset

        With cmd
            .ActiveConnection = cn
            .CommandType = CommandType.Text
            .CommandText = "SELECT colTitle FROM tblSongs WHERE colArtist=?"
            .CreateParameter(, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 40, "Beach boys")
            .Prepared = True
            rs = .Execute
        End With

---

Error location is bold. It says "No value given for one or more required parameters."

Thanks,
Jessee

You need to append parameter to parameters collection http://www.vbforums.com/showthread.php?t=629066

Hey,

I adapted it to this?

Code:

---

.Parameters.Append(.CreateParameter(, ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 100, frmSelection.lstArtist.Text))

---

And it didnt work.

did you try giving the parameter a name?

Hey,

Code:

---

With cmd
            .ActiveConnection = cn
            .CommandType = CommandType.Text
            .CommandText = "SELECT colTitle FROM tblSongs WHERE colArtist= Artist"
            .Parameters.Append(.CreateParameter("Artist", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 100, frmSelection.lstArtist.Text))
            .Prepared = True
            rs = .Execute
        End With

---

And I get this error:
Unable to cast COM object of type 'System.__ComObject' to class type 'ADODB.InternalParameter'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Anything?

Try to do it similar to sample. And make sure you are not nesting With blocks.

Question: If this is in VS2010... then you're using .NET, right? Why? Why? WHYYY are you using ADO then? Should be using ADO.NET... would make things soooo much easier.

-tg

Quote:

---

Originally Posted by techgnome

Question: If this is in VS2010... then you're using .NET, right? Why? Why? WHYYY are you using ADO then? Should be using ADO.NET... would make things soooo much easier.
-tg

---

Hey,

Im just so accustomed to the way I was using it in VB6, and cant grasp how to do it in vs 2010.

Tried doing it using datareader I think it was, but gave up on that as I had no idea what it was and how to use it.

The problem is that this:-
"SELECT colTitle FROM tblSongs WHERE colArtist= Artist"
Should still be this:-
"SELECT colTitle FROM tblSongs WHERE colArtist=?"
ADO doesn't actually recognise a parm name in the sql string itself. Instead it assigns parameter value in sequence, so the first question mark will get assigned the first parameter in the collection. Perverse, I know, but there you have it. I think Leinad was telling you to name the parm in the collection, not the sql string.

TG is right, though, ADO.Net is MUCH better and well worth investing a bit of time in learning. Check out the tutorials on this forum. They were enough for me to get my head round the switch and I came from ADO too. It's really not that hard.

Quote:

---

Originally Posted by Letsgetcoding

Hey,

Im just so accustomed to the way I was using it in VB6, and cant grasp how to do it in vs 2010.

Tried doing it using datareader I think it was, but gave up on that as I had no idea what it was and how to use it.

---

You just highlighted what's ailing some of the IT departments across the world. :blush:

Quote:

---

Originally Posted by abhijit

You just highlighted what's ailing some of the IT departments across the world. :blush:

---

What do you mean?

Quote:

---

Originally Posted by FunkyDexter

The problem is that this:-
"SELECT colTitle FROM tblSongs WHERE colArtist= Artist"
Should still be this:-
"SELECT colTitle FROM tblSongs WHERE colArtist=?"
ADO doesn't actually recognise a parm name in the sql string itself. Instead it assigns parameter value in sequence, so the first question mark will get assigned the first parameter in the collection. Perverse, I know, but there you have it. I think Leinad was telling you to name the parm in the collection, not the sql string.

TG is right, though, ADO.Net is MUCH better and well worth investing a bit of time in learning. Check out the tutorials on this forum. They were enough for me to get my head round the switch and I came from ADO too. It's really not that hard.

---

Hey,

Can you please link me to the appropriate tutorial?

Thanks!
Jessee