asked on

Cannot fill datatable.Using VB.Net,SQL procedure

CODE OF VB.Net
dim dsUserLogin as new Dataset
daLogin.SelectCommand = GetSelectCommand(objConnection, UserName, Password)
.Fill(dsUserLogin,DSLogin.TestLoginTable)
End With
If dsUserLogin.Tables(0).Rows.Count = 1 Then
bol = True
Else
bol = False

End If
Private Function GetSelectCommand(ByRef objConnection As SqlConnection, ByVal strUser As String, ByVal strPassword As String) As SqlCommand

If bolUsernameCheck = True Then
cmdSelect = New SqlCommand("SP_CheckValid", objConnection)
Else
cmdSelect = New SqlCommand("SP_CheckUserExist", objConnection)
End If

cmdSelect.CommandType = CommandType.StoredProcedure

Try
With cmdSelect.Parameters

.Add(New SqlParameter(Param_UserName, strUser))
.Add(New SqlParameter(Param_Password, strPassword))
GetSelectCommand = cmdSelect

If bolUsernameCheck = True Then

Dim i As Integer = cmdSelect.ExecuteScalar().ToString()
GetSelectCommand = cmdSelect
Else
objConnection.Open()
cmdSelect.ExecuteNonQuery()
Return cmdSelect
End If

End With
Catch ex As Exception

End Try
End Function
DSLogin.TestLoginTable is a class in which data columns of datatable dsUSerLogin are defined.
CODE OF SQL PROCEDURE

CREATE Procedure SP_CheckUserExist
@Username varchar(100),
@password varchar(100)

as

if exists(select * from UserLogin where upper(username)= upper(@username))

Begin
       select 0
end
else

Begin
       insert into userlogin(username,pwd) values(@username,@password)
select 1
end
GO

sarang8180

ASKER

Please post something.

sarang8180

ASKER

Why no body is answering?I need this answer.Urgently

Pratima

try this

  Dim strConnectionString As String = "connetion string here"
        Dim objConnection As New SqlConnection(strConnectionString)
        Dim cmdSelect As SqlClient.SqlCommand
        If bolUsernameCheck = True Then
            cmdSelect = New SqlCommand("SP_CheckValid", objConnection)
        Else
            cmdSelect = New SqlCommand("SP_CheckUserExist", objConnection)
        End If
        cmdSelect.CommandType = CommandType.StoredProcedure
        cmdSelect.Parameters.Add("@Username ", SqlDbType.VarChar, 100).Value = Usernam
        cmdSelect.Parameters.Add("@Password ", SqlDbType.VarChar, 100).Value = Password
        If bolUsernameCheck = True Then
 
            Dim i As Integer = cmdSelect.ExecuteScalar().ToString()
 
        Else
            objConnection.Open()
            cmdSelect.ExecuteNonQuery()
            objConnection.Close()
        End If

Open in new window

sarang8180

ASKER

Actually, my stored procedure is not returning anything that is why datat table is not getting filled.

Pratima

yes . then whats the problem ?

sarang8180

ASKER

i want stored procedure to return user name and password.Fill it in a datat table.how to do it in store d procedure

Pratima

Dim strConnectionString As String = "connetion string here"
Dim objConnection As New SqlConnection(strConnectionString)
Dim cmdSelect As SqlClient.SqlCommand
If bolUsernameCheck = True Then
cmdSelect = New SqlCommand("SP_CheckValid", objConnection)
Else
cmdSelect = New SqlCommand("SP_CheckUserExist", objConnection)
End If
cmdSelect.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.Add("@Username ", SqlDbType.VarChar, 100).Value = Usernam
cmdSelect.Parameters.Add("@Password ", SqlDbType.VarChar, 100).Value = Password
If bolUsernameCheck = True Then

Dim i As Integer = cmdSelect.ExecuteScalar().ToString()

Else
Dim dacmd As New SqlClient.SqlDataAdapter(cmdSelec)
Dim dscmd As New DataSet
dacmd.Fill(dscmd)
'Here you will ger dscmd.tables(0)
End If

CREATE Procedure SP_CheckUserExist
@Username varchar(100),
@password varchar(100)

as

if exists(select * from UserLogin where upper(username)= upper(@username))

Begin
select username,password from UserLogin where upper(username)= upper(@username)
end
else

Begin
insert into userlogin(username,pwd) values(@username,@password)
select username,password from UserLogin where upper(username)= upper(@username)
end

sarang8180

ASKER

how to catch return value 1 from sql stored procedure in vb.net.What parameters i have to give in vb.net

Pratima

dod you mean you need username password as well as values o and 1 also ????

then try this

CREATE Procedure SP_CheckUserExist
@Username varchar(100),
@password varchar(100)

as

if exists(select * from UserLogin where upper(username)= upper(@username))

Begin
select 0 as val , username,password from UserLogin where upper(username)= upper(@username)
end
else

Begin
insert into userlogin(username,pwd) values(@username,@password)
select 1 as val ,username,password from UserLogin where upper(username)= upper(@username)
end

Pratima

and then on Front end

Dim dacmd As New SqlClient.SqlDataAdapter(cmdSelec)
Dim dscmd As New DataSet
dacmd.Fill(dscmd)

after this

ds.Tables(0).Rows(0).Item("val")

will return you 0 or 1

sarang8180

ASKER

i don't want username and password.I want only value val

it is giving an error that it is not converting string value 'val' into integer
when I am writing

dim i as integer
i=convert.ToInt32(dsUserLogin.Tables(1).Rows(0).Item("val"))

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sarang8180

ASKER

I'am going on lunch.Will be back later.

sarang8180

ASKER

It is still showing error.Specified cast is not valid

sarang8180

ASKER

what to do?
Not working: .i=convert.ToInt32(dsUserLogin.Tables(0).Rows(0).Item("val"))

sarang8180

ASKER

Specified cast is not valid.Error is showing

Pratima

Can you check what value you rae getting for

dsUserLogin.Tables(0).Rows(0).Item("val")

Pratima

Dim strConnectionString As String = "connetion string here"
Dim objConnection As New SqlConnection(strConnectionString)
Dim cmdSelect As SqlClient.SqlCommand

cmdSelect = New SqlCommand("SP_CheckUserExist", objConnection)

cmdSelect.CommandType = CommandType.StoredProcedure
cmdSelect.Parameters.Add("@Username ", SqlDbType.VarChar, 100).Value = Usernam
cmdSelect.Parameters.Add("@Password ", SqlDbType.VarChar, 100).Value = Password

Dim dacmd As New SqlClient.SqlDataAdapter(cmdSelec)
Dim dscmd As New DataSet
dacmd.Fill(dscmd)

sarang8180

ASKER

it's working fine .Thank you very much

sarang8180

ASKER

are u staying in mumbai?

Pratima

no , in Pune. Can you please close the question

sarang8180

ASKER

are u staying in mumbai?

sarang8180

ASKER

Please tell me is there any thing logical error in this code.Because whenever I'am trying put new record in textbox.Value of val it is showing 0 only.

dsUserLogin.Tables(DSLogin.TestLoginTable).Rows(0).Item("val")

CREATE Procedure SP_CheckUserExist
@Username varchar(100),
@password varchar(100)

as

if exists(select * from UserLogin where upper(username)= upper(@username) )
Begin
select 0 as val
End
else
begin

insert into UserLogin(username,pwd) values(@username,@password)
select 1 as val
end
GO