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. 2) It's "localhost", not "loalhost". — Andrew Morton
– Andrew Morton, Commented Sep 25, 2016 at 12:28

Community · Accepted Answer · 2017-05-23 11:48:28Z

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

Kannan Kandasamy · Accepted Answer · 2016-09-25 09:45:57Z

1

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

answered Sep 25, 2016 at 9:45

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Comments

MyNameHere · Accepted Answer · 2016-09-26 21:13:55Z

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, 2016 at 21:13

MyNameHere

213 bronze badges

3 Comments

zzprog Over a year ago

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 Over a year ago

ConfigurationManager does not work since i use vb 2003

MyNameHere Over a year ago

What version of Framework? Version 2 uses it slightly differently, see: msdn.microsoft.com/en-us/library/…

Collectives™ on Stack Overflow

connecting sql database to asp.net using vb

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related