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

Charles p

18y3.4k

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Charles p18y ago
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.
+1
Ryan Alford18y ago
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?
+1
Charles p18y ago
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?
+1
Ryan Alford18y ago
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
+1
Charles p18y ago
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
+1
Ryan Alford18y ago
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.
+1