storing information from a sql stored precedure to variable in vb.net 2005

Question

Hi I am trying to pass a parameter username to a stored procedure in SQL and get 1 row back with 3 columns. Then i want to assign the 3 columns values to 3 diffrent variables and send them back to the call. can some1 please help me? here is what i got so far: Public Sub GetEmpInfo( ByRef ManagerName As String  ByRef mEmail As String  ByRef dept As String  ByVal username As String ) Dim strGetUserName As String = username Dim MyCommand As New SqlCommand( sp_aspx_mrt_EmpInfo  mySQLConnection) MyCommand.Parameters.Add( @NTlogin  SqlDbType.VarChar 50).Value = strGetUserName MyCommand.CommandType = CommandType.StoredProcedure Dim MyDataAdapter As New SqlDataAdapter(MyCommand) Dim myDataSet As New DataSet MyDataAdapter.Fill(myDataSet) ManagerName = CType (MyCommand.ExecuteScalar String ) End Sub this code was originally to put the info in a drop down list but now i am trying to put the info into vaiables instead. Please please help. -Charles

Charles p · Answer

i checked and the vaiable contains the correct info. I will try the try/catch block thingie today . not quite sure how but ill look it up.

Ryan Alford · Answer

first put a Try/Catch block around the code above and see if it is failing anywhere. the variables that are in the parenthesis are variables that are already defined and that you are sending to the procedure. make sure that none of the values are null or empty. that could cause a problem with the stored procedure. are you using MySQL or SQL Server?

Charles p · Answer

hey Ryan your the man . THanks again. I still dont got it working the way i want but with a little fiddling and taking the perameter out i was able to get the info into the variables. but now i need to get that perameter part working. I am assuming that the parameter isnt being passed to the stored procedure correctly but im not sure how to tell if my stored procedure isnt recieving the perameter. i know if i hard code the parameter in that everything works but if i use the perameter it fails. I am going to worry about it next week but if you got any ideas why the parameter isnt working im all ears. thnx again i got a question when i call the subprocedure i use something like: MySQL.GetEmpInfo(ManagerName mEmail dept username name email phone) are the variables in the parenthases variables im sending to the sub procedure or variables being returned?

Ryan Alford · Answer

try this: Dim strGetUserName As String = username Dim MyCommand As New SqlCommand(sp_aspx_mrt_EmpInfo mySQLConnection) Dim sqlParams As SqlParameterCollection = MyCommand.Parameters sqlParams.AddWithValue(@NTlogin strGetUserName) mySQLConnection.Open() Dim dr As SqlDataReader = MyCommand.ExecuteReader() If dr.Read() Then ManagerName = dr.GetString(dr.GetOrdinal(ColumnName)) End If

Charles p · Answer

thank you for the help ryan. I am still new to .net can some1 out there show me how to do it in VB.net and i would be greatful if you could help with remarks. i really want to understand the code

Ryan Alford · Answer

here is the C# code....you shouldn't have much of a problem getting it to VB.Net. I don't know the correct syntax for VB.Net string strGetUserName = username; SqlCommand MyCommand = new SqlCommand(sp_aspx_mrt_EmpInfo mySQLConnection); SqlParameterCollection sqlParams = MyCommand.Parameters; sqlParams.AddWithValue(@NTlogin strGetUserName); mySQLConnection.Open(); SqlDataReader dr = MyCommand.ExecuteReader(); if (dr.Read()) { ManagerName = dr.GetString(dr.GetOrdinal(ColumnName)); } sorry I couldn't do it in VB.Net but there are plenty of conversion sites. VB.Net should have all of those classes.