Jump to content

Return database data as JSON


madsovenielsen

Recommended Posts

Hello

Imports System.Web.Script.SerializationImports System.Data.SqlClientPublic Class jsonTest	Dim objSQLConnection As SqlConnection	Dim objSQLCommand As SqlCommand	Dim objSQLDataReader As SqlDataReader	Dim objJSONStringBuilder As StringBuilder	Public Function returnJson() As String		Dim col1 As New ArrayList		Dim col2 As New ArrayList		objJSONStringBuilder = New StringBuilder()		Dim connString As String = System.Configuration.ConfigurationManager.ConnectionStrings("VUCConnectionString").ConnectionString		objSQLConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(connString))		objSQLCommand = New SqlCommand("select col1, col2 from table1", objSQLConnection)		objSQLCommand.Connection.Open()		objSQLDataReader = objSQLCommand.ExecuteReader()		While objSQLDataReader.Read()			col1.Add(objSQLDataReader("col1"))		End While		objSQLDataReader.Close()		objSQLCommand.Connection.Close()		Dim serializer As New JavaScriptSerializer()		Dim arrayJson As String = serializer.Serialize(col1)		Return arrayJson	End FunctionEnd ClassPublic Class wwwroot_Default	Inherits System.Web.UI.Page	Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load		Dim jsontest As New jsonTest()		Dim test As String = jsontest.returnJson()		jsonOutput.Text = test	End SubEnd Class

I am trying to return JSON data from the database, this is _so_ easy with PHP, but i am forced to use ASP.NET I am gettting the following error from the code above:

The ConnectionString property has not been initialized.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.InvalidOperationException: The ConnectionString property has not been initialized.Source Error:Line 22:		 objSQLCommand = New SqlCommand("select col1, col2 from table1", objSQLConnection)Line 23:Line 24:		 objSQLCommand.Connection.Open()Line 25:		 objSQLDataReader = objSQLCommand.ExecuteReader()Line 26: Source File: [url="file://\WebfilefswebsitesDATAPOOL006u12ekgqwwwrootDefault.aspx.vb"]\\Webfilefs\websites\DATAPOOL006\u12ekgq\wwwroot\Default.aspx.vb[/url]    Line: 24

Everything seems fine in the editor, and i know the connection string is working, at least on other sites. Any suggestions are most welcome, and please, if there is an easier way of doing this, ala PHP, please enlighten me. Kind regards Mads Nielsen

Edited by madsovenielsen
Link to comment
Share on other sites

  • 1 month later...

Probably not that much help Dim connect As New SqlConnection("CONNECT STRING HERE")Dim sqlTest As New SqlCommand()Dim query1 As [string] = "SELECT * FROM test WHERE user = 'testUser'"sqlTest.Connection = connectsqlTest.CommandType = System.Data.CommandType.TextsqlTest.CommandText = query1Dim sqlTestReader As SqlDataReadersqlTestReader = sqlTest.ExecuteReader()While sqlTestReader.Read() Dim result As [string] = sqlTestReader.GetString(0)End While Have you tried it without using the web config? Above I made a short query, I also defined the commandType, although I doubt this really matters. I am usually on an Oracle database but the namespace objects are really similar. Also, the code above is translated from C# to VB. Using http://converter.telerik.com/

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...