I am using Visual Web Developer 2010 Express with SQL 2008 Express. I am also using ASP.Net and VB for my code. I have a database that I want to connect to manually through the VB code behind portion of the page. I then would like to store the fields
for a specific record into several variables. However, I don't even think I can establish a connection properly. I have tried all kinds of variations all over the net and nothing has worked. I think if I can get a connection and output the query to a label
to see how the results are concatenated I can the parse the string and seperate out the fields I need into different variables. If there is an easier way to assign fields to variables let me know, however I still need to do the coding in VB.
This is what I have currently that doesn't work.
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
' Creating connection and command sting
Dim conStr As String = "Provider=.NET Framework Data Provider for SQL Server;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True"
Dim conn As SqlConnection = New SqlConnection(conStr)
Dim sqlStr As String = "SELECT * FROM Books"
' Create data adapter object
Dim da As SqlDataAdapter = New SqlDataAdapter(sqlStr, conn)
' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = New DataSet
da.Fill(ds, "Books")
' Attach dataset's DefaultView to the datagrid control
GridView1.DataSource = ds.DefaultViewManager
TextBox1.Text = "Author"
End Sub
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
' Creating connection and command sting
Dim conStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True"
Dim conn As SqlConnection = New SqlConnection(conStr)
Dim sqlStr As String = "SELECT * FROM Books"
' Create data adapter object
Dim da As SqlDataAdapter = New SqlDataAdapter(sqlStr, conn)
' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = New DataSet
da.Fill(ds, "Books")
' Attach dataset's DefaultView to the datagrid control
GridView1.DataSource = ds
End Sub
I am assuming that your database MtFirstDatabase.mdf is in App_Data directory in your website.
Please Mark As Answer if it helped.
MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011
Ok I read up in a book called Pro ASP.NET 4 in VB 2010 and figured out how to at least establish the connection. But now I need to store specific fields into specific variables. I will modify the query to only pull in one row. So for instance I want to
store Author, Title, and Pages into 3 variables of similar names. I don't want them to be in some grid view or list view. The purpose being, with the variables I can then send that info into the text attribute of text boxes. So if the user was say, on their
edit accounts page and they choose from a drop down box the account they wanted to edit, I could populate the text boxes with the data associated with that account.
Here is what I have currently that works.
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
' Retrieve the connection string from the web.config file.
Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' Create a new connection.
Dim con As SqlConnection = New SqlConnection(connectionString)
' Define a query command object
Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Employees", con)
Try
' Try to open the connection.
con.Open()
lblInfo1.Text = "<b>Server Version:</b> " & con.ServerVersion
lblInfo2.Text = "<b>Connection Is:</b> " & con.State.ToString()
Catch err As Exception
' Handle an error by displaying the information.
lblInfo3.Text = "Error reading the database. " & err.Message
Finally
' Either way, make sure the connection is properly closed.
' Even if the connection wasn't opened successfully,
' calling Close() won't cause an error.
con.Close()
lblInfo4.Text = "<b>Now Connection Is:</b> " & con.State.ToString()
End Try
End Sub
vb
Protected Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
Dim connString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
' find the value from database to fill dropdownlist
Dim queryString As String = "select ColumnName from TableName"
Dim connection As New SqlConnection(connString)
Dim command As New SqlCommand(queryString, connection)
Dim adapter As New SqlDataAdapter(command)
Dim t As New DataTable()
adapter.Fill(t)
DropDownList1.DataSource = t
DropDownList1.DataTextField = "ColumnName"
DropDownList1.DataValueField = "ColumnName"
DropDownList1.DataBind()
End If
End Sub
Protected Sub dll_OnselectedIndexChanged(sender As Object, e As EventArgs)
Dim connString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim queryString As String = "select ColumnName1, ColumnName2 from testing where ColumnName = @ColumnName"
Dim connection As New SqlConnection(connString)
Dim command As New SqlCommand(queryString, connection)
command.Parameters.AddWithValue("@ColumnName", DropDownList1.SelectedItem.ToString())
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
' suppose it have 2 textboxes in the page
While reader.Read()
TextBox1.Text = reader(0).ToString()
TextBox2.Text = reader(1).ToString()
End While
' Call Close when done reading.
reader.Close()
connection.Close()
End Sub
.aspx file
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack = "true" OnSelectedIndexChanged = "dll_OnselectedIndexChanged">
</asp:DropDownList><br />
<asp:Label ID="Label1" runat="server" Text="TextVALUE: "></asp:Label> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<asp:Label ID="Label2" runat="server" Text="TextVALUE: "></asp:Label> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</div>
</form>
</body>
Best Regards,
Please mark the replies as answers if they help or unmark if not.
Feedback to us
78coolbreeze
Member
3 Points
27 Posts
Newbie Completely Lost And Utterly Confussed...
Nov 17, 2012 01:57 AM|LINK
I am using Visual Web Developer 2010 Express with SQL 2008 Express. I am also using ASP.Net and VB for my code. I have a database that I want to connect to manually through the VB code behind portion of the page. I then would like to store the fields for a specific record into several variables. However, I don't even think I can establish a connection properly. I have tried all kinds of variations all over the net and nothing has worked. I think if I can get a connection and output the query to a label to see how the results are concatenated I can the parse the string and seperate out the fields I need into different variables. If there is an easier way to assign fields to variables let me know, however I still need to do the coding in VB.
This is what I have currently that doesn't work.
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load ' Creating connection and command sting Dim conStr As String = "Provider=.NET Framework Data Provider for SQL Server;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True;User Instance=True" Dim conn As SqlConnection = New SqlConnection(conStr) Dim sqlStr As String = "SELECT * FROM Books" ' Create data adapter object Dim da As SqlDataAdapter = New SqlDataAdapter(sqlStr, conn) ' Create a dataset object and fill with data using data adapter's Fill method Dim ds As DataSet = New DataSet da.Fill(ds, "Books") ' Attach dataset's DefaultView to the datagrid control GridView1.DataSource = ds.DefaultViewManager TextBox1.Text = "Author" End Subadeelehsan
All-Star
18319 Points
2746 Posts
Re: Newbie Completely Lost And Utterly Confussed...
Nov 17, 2012 08:43 AM|LINK
Try the following:
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load ' Creating connection and command sting Dim conStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyFirstDatabase.mdf;Integrated Security=True" Dim conn As SqlConnection = New SqlConnection(conStr) Dim sqlStr As String = "SELECT * FROM Books" ' Create data adapter object Dim da As SqlDataAdapter = New SqlDataAdapter(sqlStr, conn) ' Create a dataset object and fill with data using data adapter's Fill method Dim ds As DataSet = New DataSet da.Fill(ds, "Books") ' Attach dataset's DefaultView to the datagrid control GridView1.DataSource = ds End SubI am assuming that your database MtFirstDatabase.mdf is in App_Data directory in your website.
MCPD ASP.NET 4.0 and 3.5, MCTS WSS, MOSS, SharePoint 2010, MCT
Microsoft Community Contributor Award 2011
78coolbreeze
Member
3 Points
27 Posts
Re: Newbie Completely Lost And Utterly Confussed...
Nov 17, 2012 06:44 PM|LINK
Ok I read up in a book called Pro ASP.NET 4 in VB 2010 and figured out how to at least establish the connection. But now I need to store specific fields into specific variables. I will modify the query to only pull in one row. So for instance I want to store Author, Title, and Pages into 3 variables of similar names. I don't want them to be in some grid view or list view. The purpose being, with the variables I can then send that info into the text attribute of text boxes. So if the user was say, on their edit accounts page and they choose from a drop down box the account they wanted to edit, I could populate the text boxes with the data associated with that account.
Here is what I have currently that works.
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load ' Retrieve the connection string from the web.config file. Dim connectionString As String = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString ' Create a new connection. Dim con As SqlConnection = New SqlConnection(connectionString) ' Define a query command object Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM Employees", con) Try ' Try to open the connection. con.Open() lblInfo1.Text = "<b>Server Version:</b> " & con.ServerVersion lblInfo2.Text = "<b>Connection Is:</b> " & con.State.ToString() Catch err As Exception ' Handle an error by displaying the information. lblInfo3.Text = "Error reading the database. " & err.Message Finally ' Either way, make sure the connection is properly closed. ' Even if the connection wasn't opened successfully, ' calling Close() won't cause an error. con.Close() lblInfo4.Text = "<b>Now Connection Is:</b> " & con.State.ToString() End Try End SubChen Yu - MS...
All-Star
21600 Points
2493 Posts
Microsoft
Re: Newbie Completely Lost And Utterly Confussed...
Nov 22, 2012 08:50 AM|LINK
Hi,
You could do like this. Please check.
vb Protected Sub Page_Load(sender As Object, e As EventArgs) If Not IsPostBack Then Dim connString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString ' find the value from database to fill dropdownlist Dim queryString As String = "select ColumnName from TableName" Dim connection As New SqlConnection(connString) Dim command As New SqlCommand(queryString, connection) Dim adapter As New SqlDataAdapter(command) Dim t As New DataTable() adapter.Fill(t) DropDownList1.DataSource = t DropDownList1.DataTextField = "ColumnName" DropDownList1.DataValueField = "ColumnName" DropDownList1.DataBind() End If End Sub Protected Sub dll_OnselectedIndexChanged(sender As Object, e As EventArgs) Dim connString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString Dim queryString As String = "select ColumnName1, ColumnName2 from testing where ColumnName = @ColumnName" Dim connection As New SqlConnection(connString) Dim command As New SqlCommand(queryString, connection) command.Parameters.AddWithValue("@ColumnName", DropDownList1.SelectedItem.ToString()) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() ' suppose it have 2 textboxes in the page While reader.Read() TextBox1.Text = reader(0).ToString() TextBox2.Text = reader(1).ToString() End While ' Call Close when done reading. reader.Close() connection.Close() End Sub .aspx file <body> <form id="form1" runat="server"> <div> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack = "true" OnSelectedIndexChanged = "dll_OnselectedIndexChanged"> </asp:DropDownList><br /> <asp:Label ID="Label1" runat="server" Text="TextVALUE: "></asp:Label> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <br /> <asp:Label ID="Label2" runat="server" Text="TextVALUE: "></asp:Label> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> </div> </form> </body>Best Regards,
Feedback to us
Develop and promote your apps in Windows Store