Start Free Trial

asked on

rookie - getting database values out using ASP.NET

hi everybody,

Im using Microsoft Visual Basic.net 2003.

my problem is that Im trying to retrieve database data using .net for the first time.

I've created a new webform and added the following code:

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
BindData()
End Sub

Sub BindData()
'1. Create a connection
Dim myConnection as New SqlConnection("HERE")

'2. Create the command object, passing in the SQL string
Dim myCommand as New SqlCommand("SELECT * FROM ADMIN", myConnection)

'Set the datagrid's datasource to the datareader and databind
myConnection.Open()
dgPopularFAQs.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
dgPopularFAQs.DataBind()
End Sub

</script>
<asp:datagrid id="test" runat="server" />

My problem is that I dont know how to create the connection string or bind it to the test id. So within the environment I've gone Tools - Connect to Database - I've selected the right database, username etc.. so now within the environment I get a panel titled data connections with all my tables from the database. Looking at the properties it does mention the connection string. copying and pasting this into the connectstring bit above, denoted with 'HERE' it doesnt work.

please advise, many thanks

here is an example

Dim cn As New SqlConnection = "Data Source=IPGOESHERE;Initial Catalog=catalogname;User ID=sa;Password=pass;providerName=System.Data.SqlClient"
Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter()
Dim ds As New DataSet
Dim dt As DataTable
Dim dv As DataView

With cmd
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "SELECT * FROM ADMIN"
End With

da.SelectCommand = cmd
da.Fill(ds)
dt = ds.Tables(0)
dv = New DataView(dt)
dg.DataSource = dv
dg.DataBind()

What was the connection string that it gave you ?
What database are you connecting to ? (SQL Server, Access, Oracle, etc)
Did you get any errors ? Or did it simply not return any data ?

ASKER

ms sql...

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=;Data Source=;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;Tag with column collation when possible=False

*details remove*

Ok, two things:

1) You don't need the "Provider=SQLOLEDB.1" part, because that is implied through the use of the SqlConnection objects.
2) "Data Source=" is blank. This needs to specify the name (or IP) of your database server.

So, you SqlConnection declaration would be something like (in its simplest form):

Dim myConnection As New SqlConnection("Data Source=MyServer;UID=Username;PWD=password;") '// If you are supplying username and password

Dim myConnection As New SqlConnection("Data Source=MyServer;Integrated Security=SSPI;") '// If you are using Windows authentication

If you are stuck for connection strings there are numerous examples for different databases here:

http://www.connectionstrings.com

ASKER

ok thanks, so this is correct?

Dim cn As New SqlConnection = "Persist Security Info=False;User ID=sa;Initial Catalog=test;Data Source=1.1.1.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=2.2.2.2;Use Encryption for Data=False;Tag with column collation when possible=False;"

ASKER

i get:

"End of statement expected"

Try:

Dim cn As SqlConnection = New SqlConnection("Persist Security Info=False;User ID=sa;Initial Catalog=test;Data Source=1.1.1.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=2.2.2.2;Use Encryption for Data=False;Tag with column collation when possible=False;")

Or:

Dim cn As New SqlConnectio("Persist Security Info=False;User ID=sa;Initial Catalog=test;Data Source=1.1.1.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=2.2.2.2;Use Encryption for Data=False;Tag with column collation when possible=False;")

Oops, slight typo on that second one, should have been:

Dim cn As New SqlConnection("Persist Security Info=False;User ID=sa;Initial Catalog=test;Data Source=1.1.1.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=2.2.2.2;Use Encryption for Data=False;Tag with column collation when possible=False;")

ASKER

thank you for this, that seems to of corrected that problem although not it complains dg is not declared when used in this line:

dg.DataSource = dv

Doing this:

Dim dg

then it complains: Keyword not supported: 'use procedure for prepare'. for the string:

Dim cn As SqlConnection = New SqlConnection("Persist Security Info=False;User ID=sa;Initial Catalog=test;Data Source=1111;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=1111;Use Encryption for Data=False;Tag with column collation when possible=False;")
Line 10:

In your original post your datagrid was called:

dgPopularFAQs, rather than just dg. Change your code to match.

If the connection string complains about the "Use procedure for prepare" attribute, then remove it.

ASKER

ok thanks, im not at work now, i'll check tomorrow and let you know asap.

ASKER

thanks again.

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
BindData()
End Sub

Sub BindData()
Dim cn As SqlConnection = New SqlConnection("Persist Security Info=False;User ID=;Initial Catalog=;Data Source=;Packet Size=4096;Workstation ID=;")

Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter()
Dim ds As New DataSet
Dim dt As DataTable
Dim dv As DataView
Dim dgPopularFAQs

With cmd
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "SELECT * FROM ADMIN"
End With

da.SelectCommand = cmd
da.Fill(ds)
dt = ds.Tables(0)
dv = New DataView(dt)
dgPopularFAQs.DataSource = dv
dgPopularFAQs.DataBind()
End Sub

</script>
<asp:datagrid id="dgPopularFAQsPopularFAQs" runat="server" />

ASKER

i have as above although I get the following problem:

Object variable or With block variable not set.

for this line:

dgPopularFAQs.DataSource = dv

any ideas?

Add a breakpoint on the line "dgPopularFAQs.DataSource = dv" and see if either one is null.

I'm guessing that the id in <asp:datagrid id="dgPopularFAQsPopularFAQs" runat="server" /> is just a typo.

ASKER

good morning,

this is the entire file (name is now test, sorry to keep changing, this is probably easier though):

<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
BindData()
End Sub

Sub BindData()
Dim cn As SqlConnection = New SqlConnection("Persist Security Info=False;User ID=xxxx;Initial Catalog=xxxx;Data Source=xxxx;Packet Size=4096;Workstation ID=xxxx;")

Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter()
Dim ds As New DataSet
Dim dt As DataTable
Dim dv As DataView
Dim test

With cmd
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "SELECT * FROM ADMIN"
End With

da.SelectCommand = cmd
da.Fill(ds)
dt = ds.Tables(0)
dv = New DataView(dt)
test.DataSource = dv
test.DataBind()
End Sub

</script>
<asp:datagrid id="test" runat="server" />

Either put a breakpoint on the line causing the error and check that both sides have a value. Alternatively you could try changing to either:

test.DataSource = ds.Tables(0)

Or:

test.DataSource = ds.Tables(0).DefaultView

ASKER

this is what I get:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object variable or With block variable not set.

Source Error:

Line 27: dv = New DataView(dt)
Line 28: 'test.DataSource = dv
Line 29: test.DataSource = ds.Tables(0).DefaultView
Line 30: test.DataBind()
Line 31:

--------------

P.S.

when inserting a break point on the line and then hitting F5 I get Error while trying to run project: unable to start debugging on the web server. debugging failed because integrated windows authentication is enabled.

Try changing:

test.DataSource = ds.Tables(0).DefaultView
test.DataBind()

To:

If Not ds.Tables(0) Is Nothing Then
test.DataSource = ds.Tables(0).DefaultView
test.DataBind()
Else
Response.Write("Table is null")
End If

ASKER

its highlighting line 30: Exception Details: System.NullReferenceException: Object variable or With block variable not set.

Source Error:

Line 28:
Line 29: If Not ds.Tables(0) Is Nothing Then
Line 30: test.DataSource = ds.Tables(0).DefaultView
Line 31: test.DataBind()
Line 32: Else

Sounds like there is possibly a problem with your datagrid. You really need to get the debugger working to check. If you are testing on your local Web server, then go into IIS bring up the properties for the Virtual Directory where your app lives and go to the Directory Security tab. Uncheck the "Integrated Windows Authentication" option.

ASKER

ok thanks, i've done that.

im running the webpage in debug.

ASKER

looking in the locals for da a lot of them, i.e. Container is set to nothing when the debug pointer is over da.Fill(ds)

Its "ds" and "test", in the line "test.DataSource = ds.Tables(0).DefaultView", that you need to be looking at.

ASKER

whilst the test.DataSource = ds.Tables(0).DefaultView is highlighted the test variable = nothing

???

ASKER

can I use this?

<script language="vb" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
BindData()
End Sub

Sub BindData()
Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings(""))
Dim myCommand as New SqlCommand("SELECT * FROM ADMIN, myConnection")
myConnection.Open()
test.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
test.DataBind()
End Sub

</script>

<asp:datagrid id="test" runat="server" />

although my attempts state: The ConnectionString property has not been initialized

It sounds like its not recognising the DataGrid tag. It does actually look incorrect, the built-in controls tag names don't usually appear in lowercase.

Did you code the control manually or drag it from the Toolbox ?

you could set the connection string in the web.config file and access that way (I think that is the way your going with the above code). That way you could access it from any web page on your site. The code would be similar to what you have above.

Dim cn As New SqlConnection(System.Web.Configuration.WebConfigurationManager.AppSettings.Item("TestConnectionString"))

you would need to make an entry for the connection string in the web.config file like this
<add key="TestConnectionString" value="Data Source=IPGOESHERE;Initial Catalog=catalogname;User Id=sa;Password=password" />

it would go between the <appSettings> tags.

ASKER

i got it working by removing a variable called tested which would make sense...!!!!

ASKER

ok i'll award the points in a sec but can you just indicate what I should do:

I have the following:

<asp:DataGrid id="test" runat="server"></asp:DataGrid>

which displays my table of data nicely although now I've changed my query to only return one value, i.e SELECT COUNT(*).... but with this control I get the column header, how can I remove this from view? and should I be using a datagrid for a single return value?

thank you.

Probably not. Datagrid is a bit overkill for a single value.

ASKER

ok, will an asp:label do the trick?

Yup. Binding is probably a bit overkill for that as well, its probably just easier to set the Text property in code.

do you need the code ?

ASKER

ok, i have the following code, is this the right logic?:

With cmd
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "SELECT count()..."
End With

da.SelectCommand = cmd
da.Fill(ds)
dt = ds.Tables(0)
dv = New DataView(dt)

If Not ds.Tables(0) Is Nothing Then
'test.DataSource = ds.Tables(0).DefaultView ' removed
'test.DataBind() ' removed
label1.text = da.Fill(ds)
Else
Response.Write("Table is null")
End If

<script language="vb" runat="server">
Sub Page_Load(sender as Object, e as EventArgs)
BindData()
End Sub

Sub BindData()
Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings(""))
Dim myCommand as New SqlCommand("SELECT * FROM ADMIN, myConnection")
Dim dr as sqldatareader
myConnection.Open()
dr = .ExecuteReader

If dr.Read Then
strlabel.text = dr.Item(0).ToString
end if

End Sub

</script>

all you need is the datareader, forget that other stuff i used it for the datagrid control

sorry that line should read
dr = mycommand.ExecuteReader

ASKER

I have the following:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

Dim cn As New SqlConnection("...")

Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
Dim dv As DataView

With cmd
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "SELECT count(*) ..."
End With

da.SelectCommand = cmd
da.Fill(ds)
dt = ds.Tables(0)
dv = New DataView(dt)

If Not ds.Tables(0) Is Nothing Then
test.DataSource = ds.Tables(0).DefaultView
test.DataBind()
Else
Response.Write("Table is null")
End If
End Sub

ASKER

not too sure what to do because myConnection.Open() is held within the With and your example has: dr = mycommand.ExecuteReader but when trying it complains about syntax.

Actually you only need to call ExecuteScalar() on the command. A DataReader, although lower overhead than a DataSet, is still more overhead than you need.

Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings(""))
Dim myCommand as New SqlCommand("SELECT * FROM ADMIN, myConnection")
myConnection.Open()

Dim val As Integer = myCommand.ExecuteScalar()

ASKER

ExecuteReader: Connection property has not been initialized

code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

Dim myConnection As New SqlConnection("...")
Dim myCommand As New SqlCommand("SELECT count(*)...")
Dim val As String

myConnection.Open()
val = myCommand.ExecuteScalar()
week.Text = val

End Sub

You shouldn't be getting that error from the code you posted, because it isn' calling ExecuteReader().

You code should be something like:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim myConnection As New SqlConnection("Your Connection String")
Dim myCommand As New SqlCommand("SELECT count(*) FROM YourTable", myConnection)
Dim val As Integer '// Integer because thats what ExecuteScalar() returns

myConnection.Open()
val = myCommand.ExecuteScalar()
week.Text = val.ToString()

End Sub

ASKER

ok thanks for this so far. just reading up on ExecuteScalar

if it returns one cell, one row great, but my query is SELECT name, email FROM....

how can I say

label1.text = myCommand.ExecuteScalar("name").toString
label2.text = myCommand.ExecuteScalar("email").toString

thank you

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

thank you, thats really great. i'll just experiment with it for a bit.

i love you

ASKER

crap, i only quickly looked at the last comments, sorry, is it possible to split the points again!?