connecting sql database to asp.net using vb

Question

i have a table in database name user (username,userid,country). i want to retrieve the data in asp.net. but i have problem in connecting sql database. this is my code

Public Sub connect()
    Dim DatabaseName As String = "user"
    Dim server As String = "loalhost"
    Dim userName As String = "me"
    Dim password As String = " "
    If Not conn Is Nothing Then conn.Close()
    conn.ConnectionString = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false", server, userName, password, DatabaseName)
End Sub

i try this to retrieve the data using this code but error

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Try
        conn.Open()
    Catch ex As Exception
    End Try
    Dim cmd As New SqlCommand(String.Format("SELECT username FROM user"))
    cmd.ExecuteNonQuery()
    conn.Close()
End Sub

1) You can use an [SqlConnectionStringBuilder](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder(v=vs.110).aspx). 2) It's "localhost", not "loalhost". — Andrew Morton, Sep 25 '16 at 12:28

score 1 · Answer 1 · edited May 23 '17 at 11:48

You need to use ExecuteReader to get all the data...as ExecuteNonQuery() is used to update database

            SqlCommand cmd1 = new SqlCommand(query1, con);

            SqlDataReader reader = cmd1.ExecuteReader();
            //GridView2.DataSource = reader;
            //GridView2.DataBind();
            while (reader.Read())
            {
               String username =Convert.ToString(reader["username"]);
               // ServicePointDetails t = new ServicePointDetails();
               // t.ServicePointID = Convert.ToString(reader["ServicePointID"]);
               // t.ServicePointName = Convert.ToString(reader["ServicePointName"]);
              //  servicePointDetails_list.Add(t);
            }

for more info ExecuteNonQuery

vb.net

Dim data As SqlDataReader
Dim command As New SqlCommand
command.CommandText = sqlquery
command.Connection = conn
data = cmd.ExecuteReader()
While data.Read
    If data.HasRows = True Then
        Dim userName As String
        userName = data.Item("username")
        //Pupulate a list/do something else 
    End If
End While

score 1 · Answer 2 · answered Sep 25 '16 at 09:45

I think your tablename is incorrect: Database is User and what is the table name? Provide correct table name in query

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Try
        conn.Open()
    Catch ex As Exception
    End Try
    Dim cmd As New SqlCommand(String.Format("SELECT username FROM yourtablename"))
    cmd.ExecuteNonQuery()
    conn.Close()
End Sub

score 0 · Answer 3 · answered Sep 26 '16 at 21:13

0

Remove the String.Format

Dim cmd As New SqlCommand("SELECT username FROM user")

Is the table name USERS not USER?

Why are you using String.Format for the connection string?

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
...
Dim strConnection As String = ConfigurationManager.ConnectionStrings("Something").ConnectionString
Dim con As SqlConnection = New SqlConnection(strConnection)

Dim da As New SqlDataAdapter("SELECT [username] FROM YourTableNameHere", con)
ds = New DataSet
da.Fill(ds, "MyDataTableName")
da.Dispose()

Dataset (ds) now contains a list of usernames and if in a function, return the results:

Return ds

answered Sep 26 '16 at 21:13

MyNameHere

21
3

There is an error at Dim strConnection As String = ConfigurationManager.ConnectionStrings("Something").ConnectionString. ConfigurationManager are not declared. but i have added imports system.configuration. it still doesnt work – zzprog Sep 27 '16 at 00:37
ConfigurationManager does not work since i use vb 2003 – zzprog Sep 27 '16 at 00:57
What version of Framework? Version 2 uses it slightly differently, see: https://msdn.microsoft.com/en-us/library/system.configuration.configurationmanager.connectionstrings(v=vs.80).aspx – MyNameHere Sep 27 '16 at 13:46

connecting sql database to asp.net using vb

3 Answers3