• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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
0
jimbona27
Asked:
jimbona27
  • 24
  • 17
  • 7
1 Solution
 
Maxim10553Commented:
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()

     
0
 
Carl TawnSystems and Integration DeveloperCommented:
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 ?
0
 
jimbona27Author Commented:
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*
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Carl TawnSystems and Integration DeveloperCommented:
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
0
 
Carl TawnSystems and Integration DeveloperCommented:
If you are stuck for connection strings there are numerous examples for different databases here:

     http://www.connectionstrings.com
0
 
jimbona27Author Commented:
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;"
0
 
jimbona27Author Commented:
i get:

"End of statement expected"
0
 
Carl TawnSystems and Integration DeveloperCommented:
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;")
0
 
Carl TawnSystems and Integration DeveloperCommented:
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;")
0
 
jimbona27Author Commented:
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:
0
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
 
jimbona27Author Commented:
ok thanks, im not at work now, i'll check tomorrow and let you know asap.
0
 
jimbona27Author Commented:
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" />
0
 
jimbona27Author Commented:
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?
0
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
 
jimbona27Author Commented:
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" />


0
 
Carl TawnSystems and Integration DeveloperCommented:
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
0
 
jimbona27Author Commented:
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.
0
 
Carl TawnSystems and Integration DeveloperCommented:
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
0
 
jimbona27Author Commented:
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
 
0
 
Carl TawnSystems and Integration DeveloperCommented:
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.
0
 
jimbona27Author Commented:
ok thanks, i've done that.

im running the webpage in debug.
0
 
jimbona27Author Commented:
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)

0
 
Carl TawnSystems and Integration DeveloperCommented:
Its "ds" and "test", in the line "test.DataSource = ds.Tables(0).DefaultView", that you need to be looking at.
0
 
jimbona27Author Commented:
whilst the  test.DataSource = ds.Tables(0).DefaultView is highlighted the test variable = nothing

???
0
 
jimbona27Author Commented:
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
0
 
Carl TawnSystems and Integration DeveloperCommented:
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 ?
0
 
Maxim10553Commented:
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.

0
 
jimbona27Author Commented:
i got it working by removing a variable called tested which would make sense...!!!!
0
 
jimbona27Author Commented:
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.
0
 
Carl TawnSystems and Integration DeveloperCommented:
Probably not. Datagrid is a bit overkill for a single value.
0
 
jimbona27Author Commented:
ok, will an asp:label do the trick?
0
 
Carl TawnSystems and Integration DeveloperCommented:
Yup. Binding is probably a bit overkill for that as well, its probably just easier to set the Text property in code.
0
 
Maxim10553Commented:
do you need the code ?
0
 
jimbona27Author Commented:
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
0
 
Maxim10553Commented:
<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>
0
 
Maxim10553Commented:
all you need is the datareader, forget that other stuff i used it for the datagrid control
0
 
Maxim10553Commented:
sorry that line should read
dr = mycommand.ExecuteReader
0
 
jimbona27Author Commented:
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

0
 
jimbona27Author Commented:
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.
0
 
Carl TawnSystems and Integration DeveloperCommented:
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()
0
 
jimbona27Author Commented:
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
0
 
Carl TawnSystems and Integration DeveloperCommented:
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
0
 
jimbona27Author Commented:
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


0
 
Carl TawnSystems and Integration DeveloperCommented:
You don't. You would need to use ExecuteReader if you are returning records, or populate a DataSet using a DataAdapter.

ExecuteScalar will only return the first column of the first row, so its ideal for queries like "SELECT COUNT(*) FROM Somewhere".

So to read records would be something like:

        Dim myConnection As New SqlConnection("Your Connection String")
        Dim myCommand As New SqlCommand("SELECT name, email FROM YourTable", myConnection)

        myConnection.Open()
        Dim myReader As SqlDataReader = myCommand.ExecuteReader()

        If myReader.HasRows
            myReader.Read()
            Label1.Text = myReader("name").ToString()
            Label2.Text = myReader("email").ToString()
        End If

        myReader.Close()
        myConnection.Close()
0
 
jimbona27Author Commented:
thank you, thats really great. i'll just experiment with it for a bit.
0
 
Maxim10553Commented:
i love you
0
 
jimbona27Author Commented:
crap, i only quickly looked at the last comments, sorry, is it possible to split the points again!?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 24
  • 17
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now