troubleshooting Question

Get Count from SqlCommand executescalar (VB.NET)

Avatar of LearningToProgram
LearningToProgram asked on
Microsoft SQL ServerASP.NETVisual Basic.NET
10 Comments2 Solutions956 ViewsLast Modified:
Hi,
I am just starting to learn ASP.NET and sqlserver.
I am creating a web application that will allow a client to enter statistics into a database after they have logged into the website.
The insert query I've gotten working.
However, before I allow the insert to go through, I want to verify that the statistic hasn't already been entered into the database.
This is the error message I'm getting when I try to determine if the stat has already been entered by using a Select Count query
Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        If IsPostBack Then
            Dim clientid As String = Membership.GetUser().ProviderUserKey.ToString()

            Dim reccount As Integer
            Dim downloaded As Boolean
            downloaded = False
            Dim strConn As String = "xxxxx; "
              """"
           Dim strsql1 As String = "Select count(statid) from tblClients where [client]=@clientid  And [tblclients.datestat]=@datestats"

            Dim strsql2 As String = "insert into tblhestats ([Client], [Stat], [Amt], [Datestat], [Downloaded]) Values (@clientid, @stattype, @statvalue, @datestats, @downloaded )"
            Dim sqlconn As New SqlClient.SqlConnection
            sqlconn.ConnectionString = strConn
            sqlconn.Open()
            Dim mycommand2 As New SqlCommand(strsql1, sqlconn)
            Dim mycommand As New SqlCommand(strsql2, sqlconn)
            If (Page.IsValid) Then
                With mycommand2.Parameters
                    .AddWithValue("@clientid", clientid)
                    .AddWithValue("@datestats", CDate(datestat.Text))
                    Try
                        reccount = Convert.ToInt32(mycommand2.ExecuteScalar)
                        lblMessage.Text = reccount
                    Catch ex As Exception
                        lblMessage.Text = (ex.Message) ‘THIS IS WHERE I GET THE ERROR
                    End Try

                End With
                With mycommand.Parameters
                    .AddWithValue("@clientid", clientid)
                    .AddWithValue("@stattype", CLng(Me.ddstat.Text))
                    .AddWithValue("@statvalue", CLng(statval.Text))
                    .AddWithValue("@datestats", CDate(datestat.Text))
                    .AddWithValue("@downloaded", 0)

                End With
                 
                Me.Button1.Attributes("onclick") = "javascript:confirm(reply)"
 
                mycommand.ExecuteNonQuery()
            End If
         End If
    End Sub
End Class
 
: "Invalid column name 'client'. Invalid column name 'tblclients.datestat'. Invalid column name 'statid'."
I've confirmed that the column names are spelled correctly (and I tried it with and without the tablename just to see if that was the problem)
Can you give me some help with what I'm doing wrong?
Also, how can I change the title of the javascript message box from "This page at...."
(Don't know if it matters, but it's code-behind in the button_click event)
thanks
Paul
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros