0

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
5
Views
7 Years
Discussion Span
Last Post by andy0787
0

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
0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.