Execute Scalar method

bmains · October 20th, 2003, 09:51 AM

Hello,

I'm trying to use the ExecuteScalar() method for a SQLCommand object (through a data adapter). My code is below; please note that SQLConn is a drag-and-drop connection:

Code:

Dim objAdapter As New SqlDataAdapter()
        objAdapter.InsertCommand = New SqlCommand("CreateRole", SQLConn)
        objAdapter.InsertCommand.CommandType = CommandType.StoredProcedure

        objAdapter.InsertCommand.Parameters.Add("@RoleName", SqlDbType.VarChar).Value = "DBA"
        objAdapter.InsertCommand.Parameters.Add("@RoleDescription", SqlDbType.VarBinary).Value = "Database Admin"

        Dim objValue As Object

        SQLConn.Open()
        objValue = objAdapter.InsertCommand.ExecuteScalar()
        SQLConn.Close()

        lblID.Text = objValue.ToString()

I'm trying to return a value, so I have an insert parameter, retrieve the ID (as "set @RoleID = @@identity"), and select the role ID value ("select @RoleID"). When I run the code, I get the error:

Invalid cast from System.String to System.Byte[].

Any idea why?

Thanks,

Brian Mains

bmains · October 20th, 2003, 02:14 PM

For anybody that has read this, I've found the answer off of another technical resource. The problem was the output parameter as declared above. For it to work it has to be:

Code:

dim param as SQLParameter = objAdapter.InsertCommand.Parameters.Add(new SQLParameter("@RoleID", SQLDBType.Decimal, 18))
param.Direction = ParameterDirection.Output

October 11th, 2008, 11:41 PM

follow this url.

http://vb.net-informations.com/ado.n...sqlcommand.htm