Check data in DB if not then exit with msg

Question

User810354248 posted

In my asp.net+VB+SQL web i have a code to check the data is already in Database and give msg

 

 Dim con As New SqlConnection(Str)
        con.Open()
        Str = "select userid from users"
        Dim SQLadp As New SqlDataAdapter(Str, con)
        Dim dt As New DataSet
        dt.Clear()
        SQLadp.Fill(dt, "users")
        For i As Integer = 0 To dt.Tables("users").Rows.Count - 1
            If newusertxt.Text = dt.Tables("users").Rows(i).Item("Userid") Then
                Page.ClientScript.RegisterStartupScript(Me.GetType, "Forms", "<script> alert('User ID already Exists ..........') </script>")
                newusertxt.Text = ""
                Exit Sub
            End If
        Next

For another page i want to check the value in text box (userid.text) is there in DB if not then show message and then exit

Answer 1

User2103319870 posted

For another page i want to check the value in text box (userid.text) is there in DB if not then show message and then exit

You need to pass the UserID as paratmer to code like below

  Dim con As New SqlConnection(Str)
        con.Open()
        'change the column name as per your table
        Str = "select userid from users WHERE userid = @UserID"
        Dim selectCMD As SqlCommand = New SqlCommand(Str, con)
        ' Add parameters and set values.
        selectCMD.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = userid.Text

        Dim SQLadp As New SqlDataAdapter
        'Assign the select command to SQLDataAdapter
        SQLadp.SelectCommand = selectCMD


        Dim dt As New DataSet
        dt.Clear()
        SQLadp.Fill(dt, "users")
        For i As Integer = 0 To dt.Tables("users").Rows.Count - 1
            If newusertxt.Text = dt.Tables("users").Rows(i).Item("Userid") Then
                Page.ClientScript.RegisterStartupScript(Me.GetType, "Forms", "<script> alert('User ID already Exists ..........') </script>")
                newusertxt.Text = ""
                Exit Sub
            End If
        Next

Answer 2

User-1716253493 posted

Dim con As New SqlConnection(Str)
        con.Open()
        Str = "select userid from users WHERE userid='" & userid.Text & "'"
        Dim SQLadp As New SqlDataAdapter(Str, con)
        Dim dt As New DataSet
        dt.Clear()
        SQLadp.Fill(dt, "users")
        If dt.Tables("users").Rows.Count > 0 Then
                Page.ClientScript.RegisterStartupScript(Me.GetType, "Forms", "<script> alert('User ID already Exists ..........') </script>")
                newusertxt.Text = ""
                Exit Sub
        End If

Answer 3

User810354248 posted

Thanks for the reply.

The code which i had posted in my post is working for me in a page.

In another page i want to check the text box value is there in database if not then show message and exit

eaxmple

 Table name = KPR_temp

kprid   KPR_No      Name    Items         Users

1        K/0001/17   baiju      Stappler     san1

the selected text in dropdownlist1 is K/0002/17

when the dropdownlist text index changed the code should search in DB table named KPR_Temp is the K/0002/17 is there

If not then show message and exit

if there then proceed to next code

Answer 4

User-707554951 posted

Hi Baiju EP,

Following working sample for your reference:

KPR_temp table in my database as below:

Code as below:

 <asp:DropDownList ID="DropDownList1" runat="server" 
                OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true">
                  <asp:ListItem Value="0" Text="Please select"> </asp:ListItem>
                <asp:ListItem Value="1" Text="K/0001/17"> </asp:ListItem>
                <asp:ListItem Value="2" Text="K/0002/17"> </asp:ListItem>
                <asp:ListItem Value="3" Text="G/0001/17"> </asp:ListItem>
            </asp:DropDownList>

 Code behind:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim kpr_no As String =(CType(sender, DropDownList)).SelectedItem.Text
    BindGrid(kpr_no)
End Sub

Private Sub BindGrid(ByVal KPR_No As String)
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT KPR_No from KPR_temp")
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Dim contains As Boolean = dt.AsEnumerable().Any(Function(row) KPR_No = row.Field(Of String)("KPR_No"))
                    If contains Then
                        Response.Write("Have data")
                    Else
                        Response.Write("No data")
                    End If
                End Using
            End Using
        End Using
    End Using
End Sub

Output:

Best regards

Cathy

Answer 5

User810354248 posted

Thanks it works. Sorry a little change in it

There is a little change in it . It was textbox in place of dropdownlist

how can i change it to texbox instead of dropdownlist

and the value is auto generated in textbox. like  K/0001/17 and when 01 is added then 02 will automatically come