Converting an int into a string in ASP.net (VB.net) - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Converting an int into a string in ASP.net (VB.net)

stokefanstokefan Posts: 60Member
Hi all,

What I'd like to do is pull a integer value from a column in a database and use the reulting value to equal a label's Text property.

What I've got so far is :

[code]

Sub Page_Load

GetNumberOfEntries() = NumberOfEntries.Text

End Sub

Function GetNumberOfEntries() As System.Data.SqlClient.SqlDataReader
Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT MAX [GuestbookEntries].[GuestBookEntriesID] FROM [GuestbookEntries]"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlConnection.Open
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function
[/code]

where NumberOfEntries is my label's name. I wonder if someone can help me please as I don't think the value returned by the function is of the right type?

Thanks.

Comments

  • iwilld0itiwilld0it Posts: 1,134Member
    : Hi all,
    :
    : What I'd like to do is pull a integer value from a column in a database and use the reulting value to equal a label's Text property.
    :
    : What I've got so far is :
    :
    : [code]
    :
    : Sub Page_Load
    :
    : GetNumberOfEntries() = NumberOfEntries.Text
    :
    : End Sub
    :
    : Function GetNumberOfEntries() As System.Data.SqlClient.SqlDataReader
    : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    :
    : Dim queryString As String = "SELECT MAX [GuestbookEntries].[GuestBookEntriesID] FROM [GuestbookEntries]"
    : Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
    :
    : sqlConnection.Open
    : Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
    :
    : Return dataReader
    : End Function
    : [/code]
    :
    : where NumberOfEntries is my label's name. I wonder if someone can help me please as I don't think the value returned by the function is of the right type?
    :
    : Thanks.
    :

    First Off lets shorten your code a bit by adding an import statement at the top of your code file (the very top)

    [code]
    Imports Systsem.Data
    Imports System.Data.SqlClient
    [/code]

    Now we condense your code ...

    [code]

    Sub Page_Load

    GetNumberOfEntries() = NumberOfEntries.Text

    End Sub

    Function GetNumberOfEntries() As SqlDataReader
    Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"

    Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)

    Dim queryString As String = "SELECT MAX([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"

    Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)

    sqlConnection.Open

    Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

    Return dataReader
    End Function
    [/code]

    Ok now that the code is condensed, we need to make some modifications. As it stands, you only are interested in the single value that the sql query is going to return. However, your function is trying to do too much by returning a SQLDataReader. I'll show you the modification and then explain.

    [code]

    Sub Page_Load
    NumberOfEntries.Text = GetNumberOfEntries.ToString()
    End Sub

    Function GetNumberOfEntries() As Integer
    Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"

    Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)

    Dim queryString As String = "SELECT COUNT([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"

    Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)

    sqlConnection.Open

    Dim count As Integer
    count = Convert.ToInt32(sqlCommand.ExecuteScalar)

    sqlConnection.Close

    Return count
    End Function
    [/code]


    First off, the GetNumberOfEntries function will return an integer now, which will be the record count. Next, I changed your sql statement to use the the COUNT() function instead of the MAX() function. Reason being that I am assuming you are looking for the number of records that exist rather than the largest GuestBookEntriesID. Furthermore, I eliminated the creation of the SqlDataReader, since it's overkill for what you are trying to do. You should notice the new function call below ...

    [code]
    count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    [/code]

    The ExecuteScalar function is a brilliant method of the SqlCommand object that returns the value of the first column of the first record in a resultset. This function is perfect for your scenario because your sql statement will return a resultset with one record and one column that will contain the number of records in your case. Even though we know an integer will be residing in that column, the nature of ExecuteScalar is to return it as a generic object datatype. That is why I use the call to Convert.ToInt32().

    After that, you essentially close the database connection and return the integer that we extracted.

    Finally you should see this line in the page load event, which essentially assigns the record count to the text box.

    [code]
    NumberOfEntries.Text = GetNumberOfEntries.ToString()
    [/code]

    The ToString() portion is there to convert the integer to a string since the Text property of the textbox expects a string.

    Hopefully, I have shed some light. Please do not hesitate to ask any further questions.

  • stokefanstokefan Posts: 60Member
    First of all, thanks so much I completely understood all of what you just di. Thanks for that.

    My question though :

    :Now we condense your code ...

    How come all the "System.Data.SqlClient." statements aren't needed?

    Thanks again.

    stokefan



    : : Hi all,
    : :
    : : What I'd like to do is pull a integer value from a column in a database and use the reulting value to equal a label's Text property.
    : :
    : : What I've got so far is :
    : :
    : : [code]
    : :
    : : Sub Page_Load
    : :
    : : GetNumberOfEntries() = NumberOfEntries.Text
    : :
    : : End Sub
    : :
    : : Function GetNumberOfEntries() As System.Data.SqlClient.SqlDataReader
    : : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : : Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    : :
    : : Dim queryString As String = "SELECT MAX [GuestbookEntries].[GuestBookEntriesID] FROM [GuestbookEntries]"
    : : Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
    : :
    : : sqlConnection.Open
    : : Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
    : :
    : : Return dataReader
    : : End Function
    : : [/code]
    : :
    : : where NumberOfEntries is my label's name. I wonder if someone can help me please as I don't think the value returned by the function is of the right type?
    : :
    : : Thanks.
    : :
    :
    : First Off lets shorten your code a bit by adding an import statement at the top of your code file (the very top)
    :
    : [code]
    : Imports Systsem.Data
    : Imports System.Data.SqlClient
    : [/code]
    :
    : Now we condense your code ...
    :
    : [code]
    :
    : Sub Page_Load
    :
    : GetNumberOfEntries() = NumberOfEntries.Text
    :
    : End Sub
    :
    : Function GetNumberOfEntries() As SqlDataReader
    : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    :
    : Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
    :
    : Dim queryString As String = "SELECT MAX([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"
    :
    : Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
    :
    : sqlConnection.Open
    :
    : Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
    :
    : Return dataReader
    : End Function
    : [/code]
    :
    : Ok now that the code is condensed, we need to make some modifications. As it stands, you only are interested in the single value that the sql query is going to return. However, your function is trying to do too much by returning a SQLDataReader. I'll show you the modification and then explain.
    :
    : [code]
    :
    : Sub Page_Load
    : NumberOfEntries.Text = GetNumberOfEntries.ToString()
    : End Sub
    :
    : Function GetNumberOfEntries() As Integer
    : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    :
    : Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
    :
    : Dim queryString As String = "SELECT COUNT([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"
    :
    : Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
    :
    : sqlConnection.Open
    :
    : Dim count As Integer
    : count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    :
    : sqlConnection.Close
    :
    : Return count
    : End Function
    : [/code]
    :
    :
    : First off, the GetNumberOfEntries function will return an integer now, which will be the record count. Next, I changed your sql statement to use the the COUNT() function instead of the MAX() function. Reason being that I am assuming you are looking for the number of records that exist rather than the largest GuestBookEntriesID. Furthermore, I eliminated the creation of the SqlDataReader, since it's overkill for what you are trying to do. You should notice the new function call below ...
    :
    : [code]
    : count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    : [/code]
    :
    : The ExecuteScalar function is a brilliant method of the SqlCommand object that returns the value of the first column of the first record in a resultset. This function is perfect for your scenario because your sql statement will return a resultset with one record and one column that will contain the number of records in your case. Even though we know an integer will be residing in that column, the nature of ExecuteScalar is to return it as a generic object datatype. That is why I use the call to Convert.ToInt32().
    :
    : After that, you essentially close the database connection and return the integer that we extracted.
    :
    : Finally you should see this line in the page load event, which essentially assigns the record count to the text box.
    :
    : [code]
    : NumberOfEntries.Text = GetNumberOfEntries.ToString()
    : [/code]
    :
    : The ToString() portion is there to convert the integer to a string since the Text property of the textbox expects a string.
    :
    : Hopefully, I have shed some light. Please do not hesitate to ask any further questions.
    :
    :

  • stokefanstokefan Posts: 60Member
    Hi again, I've just tried to run the code you suggested, and I get an error for the line

    [code]Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)[/code]

    saying " Compiler Error Message: BC30002: Type 'SqlConnection' is not defined."

    I don't know what that means?


    : : Hi all,
    : :
    : : What I'd like to do is pull a integer value from a column in a database and use the reulting value to equal a label's Text property.
    : :
    : : What I've got so far is :
    : :
    : : [code]
    : :
    : : Sub Page_Load
    : :
    : : GetNumberOfEntries() = NumberOfEntries.Text
    : :
    : : End Sub
    : :
    : : Function GetNumberOfEntries() As System.Data.SqlClient.SqlDataReader
    : : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : : Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    : :
    : : Dim queryString As String = "SELECT MAX [GuestbookEntries].[GuestBookEntriesID] FROM [GuestbookEntries]"
    : : Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
    : :
    : : sqlConnection.Open
    : : Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
    : :
    : : Return dataReader
    : : End Function
    : : [/code]
    : :
    : : where NumberOfEntries is my label's name. I wonder if someone can help me please as I don't think the value returned by the function is of the right type?
    : :
    : : Thanks.
    : :
    :
    : First Off lets shorten your code a bit by adding an import statement at the top of your code file (the very top)
    :
    : [code]
    : Imports Systsem.Data
    : Imports System.Data.SqlClient
    : [/code]
    :
    : Now we condense your code ...
    :
    : [code]
    :
    : Sub Page_Load
    :
    : GetNumberOfEntries() = NumberOfEntries.Text
    :
    : End Sub
    :
    : Function GetNumberOfEntries() As SqlDataReader
    : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    :
    : Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
    :
    : Dim queryString As String = "SELECT MAX([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"
    :
    : Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
    :
    : sqlConnection.Open
    :
    : Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
    :
    : Return dataReader
    : End Function
    : [/code]
    :
    : Ok now that the code is condensed, we need to make some modifications. As it stands, you only are interested in the single value that the sql query is going to return. However, your function is trying to do too much by returning a SQLDataReader. I'll show you the modification and then explain.
    :
    : [code]
    :
    : Sub Page_Load
    : NumberOfEntries.Text = GetNumberOfEntries.ToString()
    : End Sub
    :
    : Function GetNumberOfEntries() As Integer
    : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    :
    : Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
    :
    : Dim queryString As String = "SELECT COUNT([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"
    :
    : Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
    :
    : sqlConnection.Open
    :
    : Dim count As Integer
    : count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    :
    : sqlConnection.Close
    :
    : Return count
    : End Function
    : [/code]
    :
    :
    : First off, the GetNumberOfEntries function will return an integer now, which will be the record count. Next, I changed your sql statement to use the the COUNT() function instead of the MAX() function. Reason being that I am assuming you are looking for the number of records that exist rather than the largest GuestBookEntriesID. Furthermore, I eliminated the creation of the SqlDataReader, since it's overkill for what you are trying to do. You should notice the new function call below ...
    :
    : [code]
    : count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    : [/code]
    :
    : The ExecuteScalar function is a brilliant method of the SqlCommand object that returns the value of the first column of the first record in a resultset. This function is perfect for your scenario because your sql statement will return a resultset with one record and one column that will contain the number of records in your case. Even though we know an integer will be residing in that column, the nature of ExecuteScalar is to return it as a generic object datatype. That is why I use the call to Convert.ToInt32().
    :
    : After that, you essentially close the database connection and return the integer that we extracted.
    :
    : Finally you should see this line in the page load event, which essentially assigns the record count to the text box.
    :
    : [code]
    : NumberOfEntries.Text = GetNumberOfEntries.ToString()
    : [/code]
    :
    : The ToString() portion is there to convert the integer to a string since the Text property of the textbox expects a string.
    :
    : Hopefully, I have shed some light. Please do not hesitate to ask any further questions.
    :
    :

  • iwilld0itiwilld0it Posts: 1,134Member
    : First of all, thanks so much I completely understood all of what you just di. Thanks for that.
    :

    : How come all the "System.Data.SqlClient." statements aren't needed?

    The reason you do not need it, is because we put this line at the very top of the code file ...

    [code]
    Imports System.Data.SqlClient
    [/code]

    This is the way we kind of abbreviate object names in .NET (similar to JAVA.)

    This essentially allow us to reference any object in the SQLClient namespace without having to fully qualify a name. The compiler simply takes the object name, such as ...

    [code]
    SQLConnection
    [/code]

    and automatically converts it to ...

    [code]
    System.Data.SqlClient.SQLConnection
    [/code]

    based on the Imports statement.

    Another technique is to create a namespace alias like this ...

    [code]
    Imports SQL = System.Data.SqlClient
    [/code]

    Now you can reference objects from that namespace like so ...

    [code]
    Dim sqlConnect As New SQL.SQLConnection
    [/code]

    Again the compiler, based on the Imports statement, will automatically convert it to ...

    [code]
    Dim sqlConnect As New System.Data.SqlClient.SQLConnection
    [/code]

    All in all, using the Imports statement basically reduces the amount of typing.





  • iwilld0itiwilld0it Posts: 1,134Member
    Place these two lines of code at the very top of the code page ...

    [code]
    Imports System.Data
    Imports System.Data.SQLClient
    [/code]



    : Hi again, I've just tried to run the code you suggested, and I get an error for the line
    :
    : [code]Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)[/code]
    :
    : saying " Compiler Error Message: BC30002: Type 'SqlConnection' is not defined."
    :
    : I don't know what that means?
    :
    :
    : : : Hi all,
    : : :
    : : : What I'd like to do is pull a integer value from a column in a database and use the reulting value to equal a label's Text property.
    : : :
    : : : What I've got so far is :
    : : :
    : : : [code]
    : : :
    : : : Sub Page_Load
    : : :
    : : : GetNumberOfEntries() = NumberOfEntries.Text
    : : :
    : : : End Sub
    : : :
    : : : Function GetNumberOfEntries() As System.Data.SqlClient.SqlDataReader
    : : : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : : : Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    : : :
    : : : Dim queryString As String = "SELECT MAX [GuestbookEntries].[GuestBookEntriesID] FROM [GuestbookEntries]"
    : : : Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
    : : :
    : : : sqlConnection.Open
    : : : Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
    : : :
    : : : Return dataReader
    : : : End Function
    : : : [/code]
    : : :
    : : : where NumberOfEntries is my label's name. I wonder if someone can help me please as I don't think the value returned by the function is of the right type?
    : : :
    : : : Thanks.
    : : :
    : :
    : : First Off lets shorten your code a bit by adding an import statement at the top of your code file (the very top)
    : :
    : : [code]
    : : Imports Systsem.Data
    : : Imports System.Data.SqlClient
    : : [/code]
    : :
    : : Now we condense your code ...
    : :
    : : [code]
    : :
    : : Sub Page_Load
    : :
    : : GetNumberOfEntries() = NumberOfEntries.Text
    : :
    : : End Sub
    : :
    : : Function GetNumberOfEntries() As SqlDataReader
    : : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : :
    : : Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
    : :
    : : Dim queryString As String = "SELECT MAX([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"
    : :
    : : Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
    : :
    : : sqlConnection.Open
    : :
    : : Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
    : :
    : : Return dataReader
    : : End Function
    : : [/code]
    : :
    : : Ok now that the code is condensed, we need to make some modifications. As it stands, you only are interested in the single value that the sql query is going to return. However, your function is trying to do too much by returning a SQLDataReader. I'll show you the modification and then explain.
    : :
    : : [code]
    : :
    : : Sub Page_Load
    : : NumberOfEntries.Text = GetNumberOfEntries.ToString()
    : : End Sub
    : :
    : : Function GetNumberOfEntries() As Integer
    : : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : :
    : : Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
    : :
    : : Dim queryString As String = "SELECT COUNT([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"
    : :
    : : Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
    : :
    : : sqlConnection.Open
    : :
    : : Dim count As Integer
    : : count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    : :
    : : sqlConnection.Close
    : :
    : : Return count
    : : End Function
    : : [/code]
    : :
    : :
    : : First off, the GetNumberOfEntries function will return an integer now, which will be the record count. Next, I changed your sql statement to use the the COUNT() function instead of the MAX() function. Reason being that I am assuming you are looking for the number of records that exist rather than the largest GuestBookEntriesID. Furthermore, I eliminated the creation of the SqlDataReader, since it's overkill for what you are trying to do. You should notice the new function call below ...
    : :
    : : [code]
    : : count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    : : [/code]
    : :
    : : The ExecuteScalar function is a brilliant method of the SqlCommand object that returns the value of the first column of the first record in a resultset. This function is perfect for your scenario because your sql statement will return a resultset with one record and one column that will contain the number of records in your case. Even though we know an integer will be residing in that column, the nature of ExecuteScalar is to return it as a generic object datatype. That is why I use the call to Convert.ToInt32().
    : :
    : : After that, you essentially close the database connection and return the integer that we extracted.
    : :
    : : Finally you should see this line in the page load event, which essentially assigns the record count to the text box.
    : :
    : : [code]
    : : NumberOfEntries.Text = GetNumberOfEntries.ToString()
    : : [/code]
    : :
    : : The ToString() portion is there to convert the integer to a string since the Text property of the textbox expects a string.
    : :
    : : Hopefully, I have shed some light. Please do not hesitate to ask any further questions.
    : :
    : :
    :
    :

  • stokefanstokefan Posts: 60Member
    [b][red]This message was edited by stokefan at 2004-11-1 6:14:8[/red][/b][hr]
    Hi, I get a complier error when I do this that I need to put these statements in before any declarations, but as far as I can see I have done?

    [code]Imports System.Data
    Imports System.Data.SQLClient

    Sub Page_Load (Sender as object, e as EventArgs)

    NumberOfEntries.Text = GetNumberOfEntries.ToString()

    End Sub



    Function GetNumberOfEntries() As Integer

    Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"

    Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

    Dim queryString As String = "SELECT COUNT [GuestbookEntries].[GuestBookEntriesID] FROM [GuestbookEntries]"

    Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

    sqlConnection.Open

    Dim count as Integer

    count = Convert.ToInt32(SqlCommand.ExecuteScalar)

    sqlConnection.Close

    Return count


    End Function

    [/code]

    EDIT: might as well put the whole code in


    : Place these two lines of code at the very top of the code page ...
    :
    : [code]
    : Imports System.Data
    : Imports System.Data.SQLClient
    : [/code]
    :
    :
    :
    : : Hi again, I've just tried to run the code you suggested, and I get an error for the line
    : :
    : : [code]Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)[/code]
    : :
    : : saying " Compiler Error Message: BC30002: Type 'SqlConnection' is not defined."
    : :
    : : I don't know what that means?
    : :
    : :
    : : : : Hi all,
    : : : :
    : : : : What I'd like to do is pull a integer value from a column in a database and use the reulting value to equal a label's Text property.
    : : : :
    : : : : What I've got so far is :
    : : : :
    : : : : [code]
    : : : :
    : : : : Sub Page_Load
    : : : :
    : : : : GetNumberOfEntries() = NumberOfEntries.Text
    : : : :
    : : : : End Sub
    : : : :
    : : : : Function GetNumberOfEntries() As System.Data.SqlClient.SqlDataReader
    : : : : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : : : : Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    : : : :
    : : : : Dim queryString As String = "SELECT MAX [GuestbookEntries].[GuestBookEntriesID] FROM [GuestbookEntries]"
    : : : : Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
    : : : :
    : : : : sqlConnection.Open
    : : : : Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
    : : : :
    : : : : Return dataReader
    : : : : End Function
    : : : : [/code]
    : : : :
    : : : : where NumberOfEntries is my label's name. I wonder if someone can help me please as I don't think the value returned by the function is of the right type?
    : : : :
    : : : : Thanks.
    : : : :
    : : :
    : : : First Off lets shorten your code a bit by adding an import statement at the top of your code file (the very top)
    : : :
    : : : [code]
    : : : Imports Systsem.Data
    : : : Imports System.Data.SqlClient
    : : : [/code]
    : : :
    : : : Now we condense your code ...
    : : :
    : : : [code]
    : : :
    : : : Sub Page_Load
    : : :
    : : : GetNumberOfEntries() = NumberOfEntries.Text
    : : :
    : : : End Sub
    : : :
    : : : Function GetNumberOfEntries() As SqlDataReader
    : : : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : : :
    : : : Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
    : : :
    : : : Dim queryString As String = "SELECT MAX([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"
    : : :
    : : : Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
    : : :
    : : : sqlConnection.Open
    : : :
    : : : Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
    : : :
    : : : Return dataReader
    : : : End Function
    : : : [/code]
    : : :
    : : : Ok now that the code is condensed, we need to make some modifications. As it stands, you only are interested in the single value that the sql query is going to return. However, your function is trying to do too much by returning a SQLDataReader. I'll show you the modification and then explain.
    : : :
    : : : [code]
    : : :
    : : : Sub Page_Load
    : : : NumberOfEntries.Text = GetNumberOfEntries.ToString()
    : : : End Sub
    : : :
    : : : Function GetNumberOfEntries() As Integer
    : : : Dim connectionString As String = "server='localhost'; user id='sa'; password='password'; Database='ASPExamples'"
    : : :
    : : : Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
    : : :
    : : : Dim queryString As String = "SELECT COUNT([GuestbookEntries].[GuestBookEntriesID]) FROM [GuestbookEntries]"
    : : :
    : : : Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
    : : :
    : : : sqlConnection.Open
    : : :
    : : : Dim count As Integer
    : : : count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    : : :
    : : : sqlConnection.Close
    : : :
    : : : Return count
    : : : End Function
    : : : [/code]
    : : :
    : : :
    : : : First off, the GetNumberOfEntries function will return an integer now, which will be the record count. Next, I changed your sql statement to use the the COUNT() function instead of the MAX() function. Reason being that I am assuming you are looking for the number of records that exist rather than the largest GuestBookEntriesID. Furthermore, I eliminated the creation of the SqlDataReader, since it's overkill for what you are trying to do. You should notice the new function call below ...
    : : :
    : : : [code]
    : : : count = Convert.ToInt32(sqlCommand.ExecuteScalar)
    : : : [/code]
    : : :
    : : : The ExecuteScalar function is a brilliant method of the SqlCommand object that returns the value of the first column of the first record in a resultset. This function is perfect for your scenario because your sql statement will return a resultset with one record and one column that will contain the number of records in your case. Even though we know an integer will be residing in that column, the nature of ExecuteScalar is to return it as a generic object datatype. That is why I use the call to Convert.ToInt32().
    : : :
    : : : After that, you essentially close the database connection and return the integer that we extracted.
    : : :
    : : : Finally you should see this line in the page load event, which essentially assigns the record count to the text box.
    : : :
    : : : [code]
    : : : NumberOfEntries.Text = GetNumberOfEntries.ToString()
    : : : [/code]
    : : :
    : : : The ToString() portion is there to convert the integer to a string since the Text property of the textbox expects a string.
    : : :
    : : : Hopefully, I have shed some light. Please do not hesitate to ask any further questions.
    : : :
    : : :
    : :
    : :
    :
    :



  • iwilld0itiwilld0it Posts: 1,134Member
    This code ...

    [code]
    Imports System.Data
    Imports System.Data.SQLClient
    [/code]

    ... is suppose to go at the VERY top of the page, not the very top of the class ... like so ...

    [code]
    Imports System.Data
    Imports System.Data.SQLClient

    Public Class MyPage
    Inherits Page

    End Class
    [/code]

    NOT

    [code]
    Public Class MyPage
    Inherits Page

    Imports System.Data
    Imports System.Data.SQLClient

    End Class
    [/code]

  • noelingconoelingco Posts: 1Member
    I am new in ASP.net, is there a way to assign value to different labels using your function? How do I go about representing it in the
    GetNumberofEntries() function?

    thanks in advance.

    Noel
  • umapan123umapan123 Posts: 1Member
    convert.toint32(label1.text);
Sign In or Register to comment.