I am having trouble retrieving data from a stored procedure that I have set up as a Data Adapter using VB.NET 2005. The Database is SQL Server 2000, and what I am trying to access is a simple Stored Procedure that will validate a users login access.
The Stored Procedure looks like this,
ALTER PROCEDURE [dbo].[spValidateLogin] @LoginID nvarchar(20) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT Approver, Admin FROM tuser WHERE ADLogingID = @LoginID END
Where you will pass in the @LoginID and determine if the person in the database, an approver, an admin, or both. The return value for this stored proc is a boolean for both approver and admin.
I have a dataset set up as AdjustmentsDataSet\spValidateLogin. I am trying to figure out in code how I can address this using a table adapter or how I can capture from a login form I have created the UserID and pass this value to the stored proc to return the current record, if any of the user.
I initially setup a Dataset and dragged the dataset onto a form. Then I deleted the data navigator and the toolbar that was created by default so that I could just pass values to the Stored Proc. I also have the table adapter setup to return a table "LoginData" with the two fields Approver & Admin when I pass a userID
But I am having difficulty addressing the table adapter in code and pulling back the data values for the UserID I'm passing to the DataSet.
Does anyone have any ideas on how I can call this stored proc and get the data values returned? I'm new to .net programming.
Here's a copy of my VB Function:
Public Class frmAdjustments Inherits System.Windows.Forms.Form Dim TheDatabase As System.Data.SqlClient.SqlConnection Dim ds As DataSet = New DataSet Dim da As New SqlDataAdapter Function ValidateLogin(ByVal UID As String, ByVal PWD As String, ByVal sqlBaseDataset As String) As String Dim ConnectionString As String Dim objDataView = New DataView(ds.Tables("TestAdjustments")) ConnectionString = "Data Source=126.96.36.199;Initial Catalog=Adjustments;User ID=Adjust;Password=adjustments" TheDatabase = New SqlClient.SqlConnection(ConnectionString) [B]If Me.SpValidateLoginTableAdapter.LoginData(UID) = 1 Then[/B] da.SelectCommand = New SqlCommand() da.SelectCommand.Connection = TheDatabase da.SelectCommand.CommandText = sqlBaseDataset da.SelectCommand.CommandType = CommandType.Text da.Fill(ds, "TestAdjustments") ValidateLogin = "Pass" Else MsgBox("Invalid Login", MsgBoxStyle.Critical, "Login Incorrect!") ValidateLogin = "Fail" End If End Function END Class
Any help would be Greatly Appreciated!