Getting numbers of rows from a database

Hi!

Can anybody help me please? I don't know what is wrong with the underline row...

        Dim UtilizadorID As String = HttpContext.Current.User.Identity.Name.ToString

Dim ProducaoSource As New SqlDataSource()
ProducaoSource.ConnectionString = ConfigurationManager.ConnectionStrings("seaempiresdbConnectionString1").ToString()

ProducaoSource.SelectCommandType = SqlDataSourceCommandType.Text
ProducaoSource.SelectCommand = "SELECT * FROM Producao WHERE ProdUserID = UtilizadorID"
TotalLinhas = ProducaoSource.Rows.Count()
If TotalLinhas = 0 Then

What I want is to determine if the user is already in a particular DataBase.
Thanks in advance. 


V EMPIRE
________________________________________________
"A piece of ground to born the entire world to die."
0
GonKas
2/5/2008 10:54:20 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

7 Replies
321 Views

Similar Articles

[PageSpeed] 11

Try this, 

Inside the SqlDataSource1_Selected event

Dim RecordCount as Integer = e.AffectedRows;


Always remember to click "mark as answered" when you get a reply which answers your question.
0
nsuneel
2/5/2008 11:16:23 PM

Your code is not making the database query.  With the SqlDataSource, you have to DataBind it to something for it to execute your SELECT command.  Or, you need to call ProducaoSource.Select, then check for the row count.


Good Luck and happy programming!

Let's build community! If my post helped you, mark it as the Answer and I will do the same
when you help me...this will help everybody!
0
dtrent
2/5/2008 11:18:25 PM

 Nsuneel: I tried your way but it gives me an error "BC30456: 'AffectedRows' is not a member of 'System.Web.UI.WebControls.SqlDataSource'."

Dtrent: Actually I don't want to bind to anything just return if there any records in that condition.

I've already tried this also, but no luck!

ProducaoSource.SelectCommand = "SELECT COUNT(*) AS 'TotalLinhas' FROM Producao WHERE (ProdUserID=UtilizadorID)"

It was supposed that the variable TotalLinhas was with the numer of records found but no it gives nothing at all!

Thanks for your help anyway! I hope that someone can help me out with this problem... Big Smile
 

 


V EMPIRE
________________________________________________
"A piece of ground to born the entire world to die."
0
GonKas
2/7/2008 12:26:22 AM

Hi,

Do you mean you don’t want the sqlDataSource to bind with any data presentation controls, and just want to get the row count, right?

I think nsuneel and dtrent are all right. You should call the Select method first which informs the SqlDataSource to execute a real querying. And then create a selected event and got the value from rowaffected property. See the following code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
   
    Dim conn_str As String = ConfigurationManager.ConnectionStrings("rb").ConnectionString
   
    Dim sd As New SqlDataSource()
    sd.ConnectionString = conn_str
    sd.ID = Guid.NewGuid().ToString()
   
    sd.SelectCommandType = SqlDataSourceCommandType.Text
    sd.SelectCommand = "select * from Users"
    AddHandler sd.Selected, AddressOf sd_Selected
   
    sd.[Select](DataSourceSelectArguments.Empty) 
    
   
End Sub

Private Sub sd_Selected(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
    Dim returns As Integer = e.AffectedRows
    Response.Write(returns.ToString())
End Sub

Thanks.


Michael Jin.
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Nai
2/8/2008 5:23:58 AM

i m not giving solution to your problem but an option with an example  to count the rows:

SqlCommand cmd12 = new SqlCommand("select count(photo_cat_id_int) from tbl_photo_gallery where photo_cat_id_int=" + catid, conn);

int count_photos = Convert.ToInt16(cmd12.ExecuteScalar());

0
roopsi_saxena
2/8/2008 8:28:05 AM

 Hi,

Yes they are right but because I'm just a beginner it's difficult to me to implement the sugestion.

Your code works fine but when I change "SELECT * FROM Users" to "SELECT * FROM Users WHERE ProdUserID = UtilizadorID" it returns always "0".

 There is any particular reason for that?

 
Thanks everybody!
 


V EMPIRE
________________________________________________
"A piece of ground to born the entire world to die."
0
GonKas
2/8/2008 11:19:13 PM

Hi,

Your code works fine but when I change "SELECT * FROM Users" to "SELECT * FROM Users WHERE ProdUserID = UtilizadorID" it returns always "0".

 There is any particular reason for that?

Since there's a parmater in your select command, you have to add a parameter to your selectparameter colleciton of sqldatasource, see the following code snippet:

sd.SelectCommand = "select * from Users where Field=@ParameterName"
' sd is the SqlDataSource object instance name.

Dim pm As New Parameter("ParameterName", TypeCode.[String], "HelloWorld")
sd.SelectParameters.Add(pm)

Thanks.


Michael Jin.
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Nai
2/11/2008 7:12:52 AM
Reply: