Solved

Get Count from SqlCommand executescalar (VB.NET)

Posted on 2012-03-19
11
753 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
  • 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now