?
Solved

Get Count from SqlCommand executescalar (VB.NET)

Posted on 2012-03-19
11
Medium Priority
?
787 Views
Last Modified: 2012-04-02
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
0
Comment
Question by:LearningToProgram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37740190
Can you show the connection string? Are you including a "Initial Catalog" with the database name in connection string?
0
 
LVL 6

Expert Comment

by:Ashok Kumar
ID: 37740636
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
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37740999
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

Author Comment

by:LearningToProgram
ID: 37743225
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37743870
Have you tried using "Initial Catalog" in connection string?
0
 

Author Comment

by:LearningToProgram
ID: 37744885
Yes, tried the "initial catalog" -- got the same error
0
 
LVL 12

Expert Comment

by:CmdoProg2
ID: 37786804
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
 
LVL 12

Accepted Solution

by:
jagssidurala earned 1000 total points
ID: 37786910
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
 
LVL 12

Assisted Solution

by:CmdoProg2
CmdoProg2 earned 1000 total points
ID: 37788164
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
 

Author Closing Comment

by:LearningToProgram
ID: 37796895
thanks for your help --- I had the name of the table wrong!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question