Data from access database to a String

Garnie

I am trying to make a status report so i can see amount of users in the database, but i can't really figure out how to get the data it returns into a string or int.

This is the code i have made so far but got stuck and can't figure out how to get past this

Any help will be appriciated

Code Snippet

Private Sub GetStatusFromDB()
Try
Dim strSQL As String = _
"Select COUNT(*) FROM [Person-table]"
Dim commSelectStatus As New OleDbCommand(strSQL, dbConn)
Dim daAudstyr As New OleDbDataAdapter(commSelectStatus)
Dim dtAudstyr As New DataTable("")
daAudstyr.Fill(dtAudstyr)

lblBruger.Text = "Antal brugere: "
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

ahmedilyas

A Datatable holds records, just like in the form of a database. it has rows and columns. What you may want to do is iterate through each row and column and get the values that you want and append them to a string using a StringBuilder.

Is there a reason why you are not using a DataGridView to bind your results to In addition, you maybe better of using a Sql/OleDbDataReader to read rows as its faster than filling a datatable using the Sql/OleDbDataReader , unless of course you are needing to bind the results to a DataGridView in which case you need to bind the DataTable to the DataGridView:

Me.theDataGridView.DataSource = dtAudstyr.DefaultView

Now, to use the Sql/OleDataReader, we keep reading until no more records are left to be read, and obtain the column values via index or columnName.

Dim dataReader as OleDbDataReader = commSelectStatus.ExecuteReader(CommandBehavior.CloseConnection)

Dim builder as new System.Text.StringBuilder()

while dataReader.Read()

builder.Append(dataReader("ColumnName"))

builder.Append(Environment.NewLine)

end while

Me.lblBruger.Text = builder.ToString()

This will append the values for the columns you specify in the dataReader into a stringbuilder and finally show the results in your label.

Does this help

Garnie

When i replace ("ColumnName")) with the actual column name, it gives me an error like this "Error: ID" and ID is my column name, also tried with other names but none would work.

ahmedilyas

Hmm I wouldn't understand why. You can also refer to it by index, what happens if you refer to it by index

can you show the code you are using correctly and the names of the fields you have, exactly as they are (It may well be case sensitive)

In addition - can we have the exact error message

Garnie

Thanks for the help, i got it working by using the int number of the Column, instead of the name . .

The code ended up looking like this :

For some reason the Code Snippet does'nt work, but it ended up looking like this, and it's working

Private Sub GetStatusFromDB()
Try
Dim strSQL As String = _
"Select COUNT(*) FROM [Person-table]"
Dim commSelectStatus As New OleDbCommand(strSQL, dbConn)
Dim dataReader As OleDbDataReader = commSelectStatus.ExecuteReader(CommandBehavior.CloseConnection)
Dim builder As New System.Text.StringBuilder()
While dataReader.Read()

builder.Append(dataReader(0))
builder.Append(Environment.NewLine)
End While
Me.lblBruger.Text = builder.ToString()
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
End Class

Again thanks for your help

ahmedilyas

No worries, glad I could help :-)

Baba urf Sivaji

When u r using command object there is no need to use dataadapter and dataset because executenonquery of command object returns no of records affected by the query

dim con as oledbconnection,i as int16

try

con=new oledbconnection
con.connectionstring=specifyconnectionstring here
Dim strSQL As String = "Select COUNT(*) FROM [Person-table]"
Dim commSelectStatus As New OleDbCommand(strSQL, Con)
con.open
i=cmd.executenonquery
msgbox i
con.close
catch ex as exception
 msgbox ex.message
 con.close
end try


If u r haing any doubts or if u get any error plz post it so that i will try to give one more 
solution

Thank u
Baba

Data from access database to a String

Re: Data from access database to a String

Re: Data from access database to a String

Re: Data from access database to a String

Re: Data from access database to a String

Re: Data from access database to a String

Re: Data from access database to a String