adoroxin

I have a program with a connection to a SQL database. How would I code to have my program say set the text of a label to a value in the database

Re: Visual Basic Language Reading from a database

TilakGopi

Hi,

If you are able to retrieve the data from database, DataRows will have the values of actual table in database.So

label.Text = DataRows(RowIndex)(ColumnIndex).ToString()

shouldl do what you wanted.

Thanks,

Ch.T.Gopi Kumar.






Re: Visual Basic Language Reading from a database

Cristian_t

There are many ways to to this, depending on the value you want to retrieve. In most cases ExecuteScalar method is the best choise, but you can also use ExecuteNonQuery or even ExecuteReader. I'll give you some examples.

Visual Basic

Imports System.Data.SqlClient

Module Program

Sub Main()

Console.WriteLine(DataAcess.GetSqlServerDate())

Console.WriteLine(DataAcess.GetMostExpensiveProduct)

Console.WriteLine(DataAcess.GetCustomerContactName("ALFKI"))

End Sub

End Module

'' All method in this class relates to Northwind database.

'' You can get it from here:

'' http://www.microsoft.com/downloads/details.aspx FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en

Class DataAcess

''' <summary>

''' Gets the current date and time from SQLServer. (using ExecuteScalar).

''' </summary>

Public Shared Function GetSqlServerDate() As DateTime

Using connection As SqlConnection = CreateConnection()

Using command As New SqlCommand("SELECT GETDATE() AS CurrentDate", connection)

connection.Open()

Return CType(command.ExecuteScalar(), DateTime)

End Using

End Using

End Function

''' <summary>

''' Gets the price of the most expensive product

''' from the Products table. (using ExecuteReader and output parameters).

''' </summary>

''' <returns></returns>

''' <remarks></remarks>

Public Shared Function GetMostExpensiveProduct() As Decimal

Using connection As SqlConnection = CreateConnection()

Using command As New SqlCommand("SELECT MAX(UnitPrice) AS Price FROM Products", connection)

connection.Open()

Using reader As SqlDataReader = command.ExecuteReader()

If reader.Read() Then

Return reader.GetDecimal(0) ' CType(reader("Price"), Decimal)

Else

' There are no rows in the Products table.

Return 0

End If

End Using

End Using

End Using

End Function

''' <summary>

''' Get ContactName for a Customer. (using ExecuteNonQuery and output parameters).

''' </summary>

Public Shared Function GetCustomerContactName(ByVal customerID As String) As String

Using connection As SqlConnection = CreateConnection()

Using command As New SqlCommand("SELECT @Customer_ContactName = ContactName FROM Customers WHERE CustomerID = @customerID", connection)

command.Parameters.AddWithValue("@customerID", customerID)

command.Parameters.Add("@Customer_ContactName", SqlDbType.NVarChar, 30).Direction = ParameterDirection.Output

connection.Open()

command.ExecuteNonQuery()

Dim contactName As Object = command.Parameters("@Customer_ContactName").Value

If Not Convert.IsDBNull(contactName) Then

Return CType(contactName, String)

Else

Return "<Not found>"

End If

End Using

End Using

End Function

''' <summary>

''' Creates a new connection.

''' </summary>

Private Shared Function CreateConnection() As SqlConnection

'This might be different depending on your SQL Server Instance name and Machine.

Return New SqlConnection("Data Source=(local); Initial Catalog=Northwind; Integrated Security=SSPI;")

End Function

End Class






Re: Visual Basic Language Reading from a database

adoroxin

When ever try asnd use those it always says dataacess is not declared and the same with the createconnection parts.

How about this.

I have a connection to a database. I have a table in that database with the name nova1713 and a column of user. How would I get my program to change the text of a label to the value of user, and then have my program change that value to another one once I clikc a button





Re: Visual Basic Language Reading from a database

Cristian_t

I don't understand why that previous code snippet won't work for you, the code should work if you copy/paste it into any project. Anyway here's the code for taking the user name from the database:

Visual Basic - Get user name from database

Imports System.Data.SqlClient

Module Program

Function GetUserName(ByVal userID As Integer) As String

Using conn As New SqlConnection("your_connection_string")

Using command As New SqlCommand("SELECT user FROM nova1713 WHERE UserID = @userID", conn)

command.Parameters.AddWithValue("@userID", userID)

conn.Open()

Dim userName As String = command.ExecuteScalar()

Return userName

End Using

End Using

Return String.Empty

End Function

Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click

' Get user name and put it onto the label userNameLabel.

userNamelabel.Text = GetUserName(1)

End Sub

End Module

In this example I presumed your table has a column UserID and it's a primary key.

You should change the connection string to something more appropriate for your needs.