GlobaLevel
asked on
ms sql server 2005/asp.net - find count of rows and roll that number into a varaible
I am writing a asp.net page in vb.net and I need to be able to run a query against a table to find the numbers of rows in the table and roll that number into a varaible...
so that I can do calculations on that number....so if there are 56 rows in the table...then I need to pass that so
label1.text = "You have " & total_number_rows & " in the table"
varaible is good too, if I need to use session
thanks!
so that I can do calculations on that number....so if there are 56 rows in the table...then I need to pass that so
label1.text = "You have " & total_number_rows & " in the table"
varaible is good too, if I need to use session
thanks!
1.
Dim cmdCustomer As New SqlCommand
2.
Dim daCustomer As New SqlDataAdapter
3.
Dim dsCustomer As New DataSet
4.
Dim dtCustomer As New DataTable
5.
6.
conn = GetConnect()
7.
Try
8.
cmdCustomer = conn.CreateCommand
9.
cmdCustomer.CommandText = "SELECT * FROM Customer where IDCustomer = '1' "
10.
daCustomer.SelectCommand = cmdCustomer
11.
daCustomer.Fill(dsCustomer, "Customer")
12.
dtCustomer = dsCustomer.Tables("Customer")
13.
MsgBox(dtCustomer.Rows.Count)
14.
Catch ex As Exception
15.
MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Connection Error !!")
16.
End Try
Not sure what you have for code as far as calling the query, but assuming you do your query as follows
You can then just do
dt.Rows.Count and that will return the number of rows in your query.
dim dt as new datatable
dim da as new SqlDataAdapter("SELECT * FROM tblUser", sqlconn.connectionstring)
da.fill(dt)
You can then just do
dt.Rows.Count and that will return the number of rows in your query.
call a sql server store procedure from your code.
store procedure can have output parameter or record set.
store procedure can have output parameter or record set.
ASKER
Dim camp_name As String
camp_name = DropDownList1.Text
Dim count_SMS_rows As String
Dim sqlConnection As String
SqlConnection = "Data Source=xx.xx.xx.xx;Initial
QuizWedge....I get a string/binary will be truncated issue here...the
Dim dt As New DataTable
Using sqlConn As New SqlConnection
sqlConn.ConnectionString = sqlConnection
Dim da As New SqlDataAdapter("SELECT * FROM Casdgn where tc_id = '" & tc_id & "' and " & _
" camdsgn_name = '" & camp_name & "'", sqlConn.ConnectionString)
da.Fill(dt)
count_SMS_rows = dt.Rows.Count
End Using
ASKER
himanshut...
I get the following error with your code:
Error: Microsoft.VisualBasic: Public memeber 'CreateCommand' on type 'String' not found
I get the following error with your code:
Error: Microsoft.VisualBasic: Public memeber 'CreateCommand' on type 'String' not found
ASKER
QuizWedge....I get a string/binary will be truncated issue here...the
Dim camp_name As String
camp_name = DropDownList1.Text
Dim count_SMS_rows As String
Dim sqlConnection As String
SqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=Teerrext;Trusted_c onnection= true;"
Dim dt As New DataTable
Using sqlConn As New SqlConnection
sqlConn.ConnectionString = sqlConnection
Dim da As New SqlDataAdapter("SELECT * FROM Casdgn where tc_id = '" & tc_id & "' and " & _
" camdsgn_name = '" & camp_name & "'", sqlConn.ConnectionString)
da.Fill(dt)
count_SMS_rows = dt.Rows.Count
End Using
Dim camp_name As String
camp_name = DropDownList1.Text
Dim count_SMS_rows As String
Dim sqlConnection As String
SqlConnection = "Data Source=xx.xx.xx.xx;Initial
Dim dt As New DataTable
Using sqlConn As New SqlConnection
sqlConn.ConnectionString = sqlConnection
Dim da As New SqlDataAdapter("SELECT * FROM Casdgn where tc_id = '" & tc_id & "' and " & _
" camdsgn_name = '" & camp_name & "'", sqlConn.ConnectionString)
da.Fill(dt)
count_SMS_rows = dt.Rows.Count
End Using
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@GlobaLevel, have you had a chance to try the tests that I suggested?
ASKER
Thanks
http://support.microsoft.com/kb/306574
http://stackoverflow.com/questions/723352/creating-a-stored-procedure-to-return-the-rowcount-of-another-stored-procedure
Put that as a Out Parameter
Then, set the out parameter to variable after Executing the SP:
http://www.developerfusion.com/samplechapter/91/stored-procedures/4/