Get Count from SqlCommand executescalar (VB.NET)

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
 

Open in new window

: "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
LearningToProgramAsked:
Who is Participating?
 
jagssiduralaConnect With a Mentor Commented:
Please check your database name(where you defined all the tables) mentioned in connection string.

i.e., you are verifying tables in another database which you mentioned in connection string.
0
 
CodeCruiserCommented:
Can you show the connection string? Are you including a "Initial Catalog" with the database name in connection string?
0
 
Ashok KumarCommented:
first.,
- check the table (tblclients), columns(client,statid,datestat) in corresponding tables.
second,
- check if you have db connection string is working from with-in your application.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
BuggyCoderCommented:
It must be the issue with your connection string, here is the sample to be used:-

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Initial Catalog: Database Name you want to connect.

Another option is change your query as:-


Dim strsql1 As String = "Select count(statid) from MyDatabase.MySchema.tblClients where [client]=@clientid  And [tblclients.datestat]=@datestats"

Here MyDatabase=your database name
MySchema = your schema name (like dbo etc.)
0
 
LearningToProgramAuthor Commented:
The connection string works fine for the insert query.
Here is the connection string i'm using:
 Dim strConn As String = "Server=xx.doxx-.com;Database=he;Uid=xx;Password=xxxxxx; "
This is the format suggested by the hosting company.
When I tried using the query suggested by buggycoder, this is the error:
The multi-part identifier "he.dbo.client" could not be bound. The multi-part identifier "he.dbo.tblclients.datestat" could not be bound. Invalid column name 'statid'.
0
 
CodeCruiserCommented:
Have you tried using "Initial Catalog" in connection string?
0
 
LearningToProgramAuthor Commented:
Yes, tried the "initial catalog" -- got the same error
0
 
CmdoProg2Commented:
The brackets in your strsql1 string indicates you have a column litteral named "tblclients.datestat" on tblClients which is unusual.  Check your table has a "datestat" column.  If so, try removing the table name within the brackets.

   Dim strsql1 As String = "Select count(statid) from tblClients where [client]=@clientid  And [datestat]=@datestats"
0
 
CmdoProg2Connect With a Mentor Commented:
Could you please show the table structure for the tblClients table? The error you are getting is after sql has found your table and cannot find the columns in the sys.columns system view of the database.  Below is an example of what I'm looking for to assist you.

CREATE TABLE dbo.tblCountyEcoregions
 (
	CountyEcoregionID int NOT NULL IDENTITY (1, 1),
	CountyID int NOT NULL,
	EcoRegionIII_ID int NOT NULL,
	ploygonCoordinals varchar(2000) NOT NULL,
	isActive bit NOT NULL
	)  ON [PRIMARY]
GO

Open in new window

0
 
LearningToProgramAuthor Commented:
thanks for your help --- I had the name of the table wrong!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.