Hello Forum,
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.

Details:
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=90.152.60.72;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!

>I am having trouble retrieving data from a stored procedure that I have set up as a Data Adapter using VB.NET 2005.

Dim Cn as New SqlConnection(ConnectionString)
Dim Cmd as new SqlCommand()
cmd.Connection=Cn
cmd.CommandType=CommandType.StoredProcedure
cmd.CommandText="spValidateLogin"

cmd.Parameters.AddWithValue("@LoginID",UID)

Dim Adp as New SqlDataAdapter(cmd)

//Run Stored-proce & populate datatable object.
Dim Dt as new DataTable
Adp.Fill(dt)

if dt.Rows.Count=1 Then
   'Found
else
    'Not Found
End If
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.