SQl statement to show database info

Hi everyone,

I am trying to hand code a script to bring out information from a test access table. My test MS Access table is "TESTTABLE" COLOMN NAMES are ID,NAME, NUMBER - They really dont mean anything this is just a learning point.

How to I display this on a form in text boxes, I will only need to display One on a search. but if your code shows all data thats Cool too, I will just add a WHERE in the SQL statement

Thank you!

Dim queryString As String = "SELECT ID, NAME FROM TESTTABLE"

Using connection As New OleDb.OleDbConnection(connectionString)

Dim command As New OleDb.OleDbCommand(queryString, connection)

connection.Open()

Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()

While reader.Read()

Console.WriteLine(reader.GetInt32(0).ToString() + ", " _

+ reader.GetString(1))

End While

' always call Close when done reading.

reader.Close()

End Using

End Sub

Answer this question

SQl statement to show database info

Piotr Ignaczak

nhaas wrote:
I am getting connectionString is being used before a value has been assigned. I am confused by this error.

Great site for checking the format of your connection string: http://www.connectionstrings.com/
Andrew Buyan

can you post the innerexception of the exception

One thing I did notice is that you have the single quotes around the ID field value 1 - should just be 1 not '1'

You are using MS Access database correct
hr0nix

i have tracked down the error it is on this line:

Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()

I am not sure how to correct this "Crash"

Thanks for the help
Pat1111

Yes I am using MS access, I removed the single quotes and still got an error.

here is the whole innerexception:

A first chance exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll

System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>test search.vshost.exe</AppDomain><Exception><ExceptionType>System.IndexOutOfRangeException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Index was outside the bounds of the array.</Message><StackTrace> at System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)

at System.Data.OleDb.OleDbDataReader.GetString(Int32 ordinal)

at test_search.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\nhaas\My Documents\Visual Studio 2005\Projects\test search\test search\Form1.vb:line 44

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message&amp; m)

at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)

at System.Windows.Forms.Button.WndProc(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)

at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(ApplicationContext context)

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

at test_search.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)

at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.IndexOutOfRangeException: Index was outside the bounds of the array.

at System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)

at System.Data.OleDb.OleDbDataReader.GetString(Int32 ordinal)

at test_search.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\nhaas\My Documents\Visual Studio 2005\Projects\test search\test search\Form1.vb:line 44

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message&amp; m)

at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)

at System.Windows.Forms.Button.WndProc(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)

at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(ApplicationContext context)

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

at test_search.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)

at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>

A first chance exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll

System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>test search.vshost.exe</AppDomain><Exception><ExceptionType>System.IndexOutOfRangeException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Index was outside the bounds of the array.</Message><StackTrace> at System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)

at System.Data.OleDb.OleDbDataReader.GetString(Int32 ordinal)

at test_search.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\nhaas\My Documents\Visual Studio 2005\Projects\test search\test search\Form1.vb:line 44

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message&amp; m)

at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)

at System.Windows.Forms.Button.WndProc(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)

at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(ApplicationContext context)

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

at test_search.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)

at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.IndexOutOfRangeException: Index was outside the bounds of the array.

at System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)

at System.Data.OleDb.OleDbDataReader.GetString(Int32 ordinal)

at test_search.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\nhaas\My Documents\Visual Studio 2005\Projects\test search\test search\Form1.vb:line 44

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message&amp; m)

at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)

at System.Windows.Forms.Button.WndProc(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)

at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(ApplicationContext context)

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

at test_search.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)

at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>

thanks for your help
Sugan

the error is infact in your while reader.read(). The error is saying that you are trying to access a column that doesnt exist, since you only get returned 2 columns, you are trying to read 3 but that is not valid (IndexOutOfRangeException)

While reader.Read()

Me.TextBox1.Text = reader.GetInt32(0).ToString()

Me.TextBox2.Text = reader.GetString(1)

Me.TextBox3.Text = reader.GetString(2) //Most likely Error here

End While

So either add another column to get back in your query string/OleDbCommand string/SELECT statement or remove the red text :-)
fbdf
no worries, glad I could help :-)
MarcForsyth

If you use a where clause to limit the sql to return a single record then all you have to do is the following:

While reader.Read()

Me.Textbox1.text = reader.GetInt32(0).ToString()

me.TextBox1.text = reader.GetString(1)

End While
Steve_j_maas

thanks, but it did not help: I still get:

at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp; executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp; executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.ExecuteReader()

at test_search.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\nhaas\My Documents\Visual Studio 2005\Projects\test search\test search\Form1.vb:line 36

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message&amp; m)

at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)

at System.Windows.Forms.Button.WndProc(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)

at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(ApplicationContext context)

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)

at test_search.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)

at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>

Does anyone know of sample code that I can look at I just want to build a simple little search from a text box submited by a submit button that runs a SQL query to find the information..

thanks
cgraus

Thank you, I am trying to get this to work after a button click but it does not work. I have the 3 text boxes named textbox1, textbox2, and textbox3 on the form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim connectionString As String

Dim queryString As String = "SELECT ID, NAME FROM TESTTABLE WHERE ID='1'"

Using connection As New OleDb.OleDbConnection(connectionString)

Dim command As New OleDb.OleDbCommand(queryString, connection)

connection.Open()

Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()

While reader.Read()

Me.TextBox1.Text = reader.GetInt32(0).ToString()

Me.TextBox2.Text = reader.GetString(1)

Me.TextBox3.Text = reader.GetString(2)

End While

' always call Close when done reading.

reader.Close()

End Using

End Sub

I am getting connectionString is being used before a value has been assigned. I am confused by this error.
RCS300

You still need to set connectionString = "where you have testtable"
zhangchenxu

Man I feel bad, I should have caught that simple of an error!

thank you, ahmedilyas!
Andreas Kranister

OK forgot that, obvious one but now when I click the submit button, the application just stops. Is my location where my database wrong

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\test.mdb"

Dim queryString As String = "SELECT ID, NAME FROM TESTTABLE WHERE ID='1'"

Using connection As New OleDb.OleDbConnection(connectionString)

Dim command As New OleDb.OleDbCommand(queryString, connection)

connection.Open()

Dim reader As OleDb.OleDbDataReader = command.ExecuteReader()

While reader.Read()

Me.TextBox1.Text = reader.GetInt32(0).ToString()

Me.TextBox2.Text = reader.GetString(1)

Me.TextBox3.Text = reader.GetString(2)

End While

' always call Close when done reading.

reader.Close()

End Using

End Sub
friggityfraggity
try using this one:

Me.TextBox2.Text = reader.GetString(1).ToString

Me.TextBox3.Text = reader.GetString(2).ToString
SQl statement to show database info

Answer this question