Solved

Get Count from SqlCommand executescalar (VB.NET)

Posted on 2012-03-19
11
770 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 250 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 250 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 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