VB2010 SQL Error in Select statement.

[Letsgetcoding]

Hey,

From what I can see this is all correct.

        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

[leinad31]

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

[Letsgetcoding]

Hey,

I adapted it to this?

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

And it didnt work.

[leinad31]

did you try giving the parameter a name?

[Letsgetcoding]

Hey,

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.

[Letsgetcoding]

Anything?

[leinad31]

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

[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

[Letsgetcoding]

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.

[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.

[abhijit]

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.

[Letsgetcoding]

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

What do you mean?

[Letsgetcoding]

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