Member Avatar for andy0787

Can someone plese have a look at this for me and tell me why @userid is giving me a scaler error....

Protected Sub recordcount_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles recordcount.Click



        



        Dim con As New Data.SqlClient.SqlConnection()
        con.ConnectionString = ConfigurationManager.ConnectionStrings()("ConnectionString").ConnectionString
       


        Dim cmd As New Data.SqlClient.SqlCommand()
        cmd.CommandText = "select count (*) from images where (userid=@Userid)"
        cmd.CommandType = Data.CommandType.Text
        cmd.Connection = con


        con.Open()
        Dim result As Integer = cmd.ExecuteScalar()
        con.Close()
        If result > 0 Then
            count.Text = result

            If result < 0 Then
                MsgBox(result)

            End If
        End If






    End Sub

this is the form

<%@ Page Language="VB" debug="true" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    
    
    <form id="form1" runat="server">
    <h1>Test Site for running code</h1>
    
    <div>
    
    <asp:textbox ID="textbox1" runat="server"></asp:textbox><br /><br />

<asp:textbox id="textbox2" runat="server"></asp:textbox><br /><br />
<asp:button runat="server" text="Button" onclick="Unnamed2_Click" />

 
       

 
    </div>
    
     <asp:TextBox ID="Userid" runat="server" 
        Text='<%# Bind("Userid") %>'>
        </asp:TextBox>
    
    
    
    
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        
        SelectCommand="SELECT [id], [Imagename], [Userid] FROM [images] WHERE ([Userid] = @Userid)">
        <SelectParameters>
            <asp:ControlParameter ControlID="Userid" Name="Userid" PropertyName="Text" 
                Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>
    
       
    
    <asp:Label ID="count" runat="server" Text="Label"></asp:Label>
    
       
    
    <asp:Button ID="recordcount" runat="server" Text="count" />
    
    </form>
</body>
</html>

i have tried all sorts of combinations to resolve it ....am i being stupid and maybe it isn't possible to get the data from a textbox for a query? all i'm trying to do is count how many files a member has uploaded?

look forward to your answers thanks
andy

  • 2 Contributors
  • 4 Replies
  • 664 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by andy0787
Member Avatar for kvprajapati

You have to add a parameter.

Protected Sub recordcount_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles recordcount.Click
        Dim con As New Data.SqlClient.SqlConnection()
        con.ConnectionString = ConfigurationManager.ConnectionStrings()("ConnectionString").ConnectionString

        Dim cmd As New Data.SqlClient.SqlCommand()
        cmd.CommandText = "select count (*) from images where  userid=@Userid"
        cmd.CommandType = Data.CommandType.Text
        cmd.Connection = con

        cmd.Parameters.AddWithValue("@Userid","a0001")
        con.Open()
        Dim result as integer = cmd.ExecuteScalar()
        con.Close()
        If result > 0 Then
            count.Text = result

            If result < 0 Then
                MsgBox(result)
            End If
        End If
   End Sub
Member Avatar for andy0787

Thanks for taking the time to look at this for me. i tried what you said with the a in front of userid it throws up....""""Conversion failed when converting the nvarchar value 'a0001' to data type int.""". If i remove the a then it counts the files but only the ones that relate to whatever the last digits are..eg... at the moment id 1 has 3 records so it reads 3.....if i change it to "0099" then it shows 1 record for id number 99 which is correct but it will only change the count after reloading the page.. which means half of it is working but i need to be able to type any id number in the textbox "userid" and count them records.. look forward to your reply many thanks andy

Member Avatar for kvprajapati 
cmd.Parameters.AddWithValue("@Userid",Userid.Text)
Member Avatar for andy0787

that is fantastic thanks so much you've been a big help

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.