Stored Procedures SQL & VB.Net

[DarkConsultant]

Hello again,

Fallen at the first fence ...

I have set up a stored procedure -

CREATE PROCEDURE dbo.GetDBRecord
(
@MainUIN int
)
AS
SELECT        MainUIN, MainPhotograph, MainTitle, MainFirstName, MainMiddleName, MainLastName, MainAddress1, MainAddress2, MainAddress3, MainTown, 
                         MainCounty, MainPostCode, MainCountry, MainCompanyName, MainWorkPhone, MainWorkExtension, MainHomePhone, MainMobilePhone, 
                         MainFaxNumber, MainEmailAddress, MainBirthDate, MainProfileReceived, MainMaritalStatus, MainSpouseName, MainChildrensNames, MainMentor, 
                         MainWhenAchievedConsultant, MainWhenAchievedSeniorConsultant, MainTotalProjectsSubmitted, MainTotalProjectsPassed, MainRecordManager, 
                         MainRecordCreated, MainRecordCreator
FROM            Main
WHERE        (MainUIN = @MainUIN)
RETURN

and in my vb code ...

        Using _MyConnection = New SqlClient.SqlConnection(ConStr)
            _MyConnection.Open()
            _MySQLCommand = New SqlClient.SqlCommand("GetDBRecord", _MyConnection)
            _MySQLCommand.CommandType = CommandType.StoredProcedure
            _MySQLCommand.Parameters.Add(New SqlClient.SqlParameter("@MainUIN", SqlDbType.Int)).Value = CurrentRecordUIN
            _MyDataReader = _MySQLCommand.ExecuteReader()
            While _MyDataReader.Read
                ' Read data iteration
            End While

But there is no data returned.
I have spent 4 hours searching for an answer to this and according to all I have read it should work but doesn't. I know I have missed something vital but please what is it?

TIA

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[RiverGuy]

Are you sure you have your CurrentRecordUIN set to the correct value?

 

[DarkConsultant]

Yep, it is (int) 1000 which is the first record MainUIN.

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//

 

[RiverGuy]

Just for grins, try it with a CommandType.Text and execute with some hard coded syntax such as EXEC GetDBRecord 1000. This will help narrow down whether its in the database or the application.

 

[jebenson]

Does the stored procedure work if you call it in something like Query Analyzer?


And on a different subject...

I recommend that you take this line of code:

_MySQLCommand.Parameters.Add(New SqlClient.SqlParameter("@MainUIN", SqlDbType.Int)).Value = CurrentRecordUIN


and break it out something like this:

Dim paramMainUIN As SqlClient.SqlParameter

paramMainUIN = New SqlClient.SqlParameter("@MainUIN", SqlDbType.Int)

_MySQLCommand.Parameters.Add(paramMainUIN )

_MySQLCommand.Parameters("@MainUIN").Value = CurrentRecordUIN


In college I had a professor who called code like what you wrote "clever"...and counted points off if you used it. His reasoning was that taking the (very little) extra time and effort to write 4 or 5 lines of "non-clever" code is worth it because it makes your code so much better in terms of readability, maintainability and ease of debugging.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[DarkConsultant]

jebenson,

Upon splitting the offending line, the sun came out and shone, all rainclouds disappeared and data was returned.

Thanks for your help.

DarkConsultant

http://www.contactconsultants.co.uk

Live long and prosper \\//