Data Access Problem

[SFAid]

Hi All,

This is my stored procedure:

[B]CREATE PROCEDURE dbo.UpdateTraveller
(
@ConfirmCode numeric(18,0) ,
@TName nvarchar(50) OUTPUT,
@BDate nvarchar(50) OUTPUT,
@EDate nvarchar(50) OUTPUT,
@EveName nvarchar(50) OUTPUT,
@Desn nvarchar(50) OUTPUT,
@VSize nvarchar (50) OUTPUT

)
as

select @TName =Traveller_Name
from
Travel_Log_db 
where
Tvl_Request_id =@ConfirmCode

select @BDate =Travel_begin_date
from Travel_Log_db
where
Tvl_Request_id = @ConfirmCode

select @EDate= travel_end_date
from Travel_Log_db
where
Tvl_Request_id = @ConfirmCode

select @EveName =Event_Name
from Travel_Log_db
where
Tvl_request_id = @ConfirmCode

select @Desn=Destination
from Travel_Log_db
where 
Tvl_Request_id = @ConfirmCode

select @VSize=vehicle_size
from Travel_Log_db
where
Tvl_Request_id = @ConfirmCode
GO[/B]

and this is my code in vb. net for getting the values

[B]conn.Open()
            Dim command As SqlCommand = New SqlCommand("UpdateTraveller", conn)
            command.CommandType = CommandType.StoredProcedure
            ' ADD VALUES IN STORED PROCEDURE
            Dim test As Decimal = CDec(txtConfCode.Text)
            command.Parameters.Add("@ConfirmCode", SqlDbType.Decimal).Value = test

            command.Parameters.Add("@TName", SqlDbType.NVarChar)
            command.Parameters("@TName").Direction = ParameterDirection.Output

            command.Parameters.Add("@BDate", SqlDbType.NVarChar)
            command.Parameters("@BDate").Direction = ParameterDirection.Output

            command.Parameters.Add("@EDate", SqlDbType.NVarChar)
            command.Parameters("@EDate").Direction = ParameterDirection.Output

            command.Parameters.Add("@EveName", SqlDbType.NVarChar)
            command.Parameters("@EveName").Direction = ParameterDirection.Output

            command.Parameters.Add("@Desn", SqlDbType.NVarChar)
            command.Parameters("@Desn").Direction = ParameterDirection.Output

            command.Parameters.Add("@VSize", SqlDbType.NVarChar)
            command.Parameters("@VSize").Direction = ParameterDirection.Output

            command.ExecuteNonQuery()
            
           
            Dim tvlName As String = command.Parameters("@TName").Value.ToString()
            Dim BgDate As String = command.Parameters("@BDate").Value.ToString()
            Dim EnDate As String = command.Parameters("EDate").Value.ToString()
            Dim EvName As String = command.Parameters("@EName").Value.ToString()
            Dim Destn As String = command.Parameters("@Desn").Value.ToString()
            Dim Vehclsize As String = command.Parameters("@VSize").Value.ToString()

            txtDestinationUpd.Text = Destn.ToString()
            txtVehiclSzUpd.Text = Vehclsize.ToString()

            txtTvlNameUpd.Text = tvlName.ToString()
            tvtTvlBeginDate.Text = BgDate.ToString()
            txtTvlEndDate.Text = EnDate.ToString()
            txtTvlEventNameUpd.Text = EvName.ToString()[/B]

I want to get values from the database and insert into text boxes on page for updation. So I put in the confirmcode in the page and then click on search button and after that the above code runs.

But though the stored procedure works perfectly well in SQL Query Analyzer, I am getting an error

System.InvalidOperationException: String[1]: the Size property has an invalid size of 0. at System.Data.SqlClient.SqlParameter.Validate(Int32 index) at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters) at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.

Can you please help me out with this?

 

[jmcilhinney]

You have specified the size of your @ConfirmCode parameter as 18 in the sproc but you haven't done so when adding the parameter to your SqlCommand. It should be:

command.Parameters.Add("@ConfirmCode", SqlDbType.Decimal[B][U], 18[/U][/B]).Value = test

 

[cjard]

You can get the IDE to write code to help you access a stored procedure, it makes life a lot simpler. Read the Dw2 link in my sig,about stored procedures

 

[SFAid]

Still same error...

@ jmcilhinney

I tried doing that but still by executing the code below I get the same error.

I have kept the size of Tvl_request_id in Db as size 9 precision (18,0). So what exactly would be the changes I would be making in the Sp and the VB.NEt code?

Can you please help me out with this..thanks

@cjard

Thanks for your reply.. I am almost done with the project except for this niggling part.. I will definitely go through your links and in future will implement.. Thanks a lot..