-
Aug 7th, 2015, 06:50 AM
#1
Thread Starter
Addicted Member
Help- Show query in a form/ODBC connection
Hi,
I need help please.
this code this to work giving the successful connection, How do I add a sql and the result appears on the form?( example: select *from xpto where user=peter)
Code:
Public Class Form1
Dim connection As New Odbc.OdbcConnection("Dsn=informix1;db=system;host=test1;serv=onlinetcp1;srvr=onlinetcp1;pro=onsoctcp;uid=admin;Pwd=pass")
Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Lblstatus.Click
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
connection.Open()
If ConnectionState.Open = ConnectionState.Open Then
MsgBox("connection sucessfully", MsgBoxStyle.OkOnly & MsgBoxStyle.Information, "Information connect Db")
Lblstatus.Text = "Connect odbc OK"
Lblstatus.ForeColor = Color.Green
Else
MsgBox("can not connect DB", MsgBoxStyle.OkOnly & MsgBoxStyle.Critical, "Information connect Db")
Lblstatus.Text = "can not connect odbc"
Lblstatus.ForeColor = Color.Red
End If
End Sub
End Class
Thks a lot,
Regards,
Cachado
-
Aug 7th, 2015, 07:11 AM
#2
Re: Help- Show query in a form/ODBC connection
In short, you create a command object, set the commandtext, add any parameters you need, and then execute the command, put the results into a datatable or a dataset... or you create an adaptor object, set it's command properties, and use it to fill a datatable or dataset. You can then bind the datatable to a DataGridView to display the results.
In the Database section you'll find a Database FAQ & Tutorial thread that has a lot of this info in it. Might want to check it out.
-tg
-
Aug 7th, 2015, 09:09 AM
#3
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Thks,
I'll try
Regards,
Cachado
Originally Posted by techgnome
In short, you create a command object, set the commandtext, add any parameters you need, and then execute the command, put the results into a datatable or a dataset... or you create an adaptor object, set it's command properties, and use it to fill a datatable or dataset. You can then bind the datatable to a DataGridView to display the results.
In the Database section you'll find a Database FAQ & Tutorial thread that has a lot of this info in it. Might want to check it out.
-tg
-
Aug 7th, 2015, 09:21 AM
#4
Re: Help- Show query in a form/ODBC connection
I notice that you have the event handler generated for a Label's click event. I would not advise of this considering that Labels are designed to be a read-only viewable object as opposed to an interactive object. With my $0.02 being said on that subject...
You will need to do a few things that are important to data handling. First you will want to wrap your code in a Try/Catch error handling statement. This is because data exceptions thrown outside of a Try/Catch are very tough to decipher what's going on.
Next you'll want to wrap everything(except for your Connection object) that implements iDisposable in Using statements. This is because you do not want data objects lingering around undisposed. As far as the Connection object, remember that Try/Catch that you'll use? Well you'll also have a Finally in your Try/Catch and do all the cleanup of your Connection object there.
You will also want to parameterize your query. While small projects like this it won't particularly matter, but it's better for you to start with best practices rather than the opposite. Parameterized queries prevent things such as SQL injections.
Finally, because you're using the all wildcard in your SQL command(*) you'll need to use a command method that returns multiple values.... So this excludes ExecuteScalar. Depending on what you're wanting to do, using ExecuteReader is probably your best option.
Here is a quick example of everything that I suggested:
Code:
'Declare the connection object
Dim con As OleDbConnection
'Wrap code in Try/Catch
Try
'Set the connection object to a new instance
con = New OleDbConnection("Dsn=informix1;db=system;host=test1;serv=onlinetcp1;srvr=onlinetcp1;pro=onsoctcp;uid=admin;Pwd=pass")
'Create a new instance of the command object
Using cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [xpto] WHERE [user]=@user", con)
cmd.Parameters.AddWithValue("@user", "peter")
'Open the connection
con.Open()
'Use ExecuteReader to return a multiple value
Using reader As OleDbReader = cmd.ExecuteReader()
'Use your reader object here
End Using
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try
Last edited by dday9; Aug 7th, 2015 at 09:24 AM.
Reason: Fixed Indents
-
Aug 7th, 2015, 09:54 AM
#5
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Thank you very mutch but sorry i´m a newbye;
I have some questions, where do I put the code on the form? I need to create a datagrid in form or a listbox for example to show the sql result?
-
Aug 7th, 2015, 10:03 AM
#6
Re: Help- Show query in a form/ODBC connection
If you want to display your code in a DataGridView then you will want to bind your DataGridView to a BindingSource and bind your BindingSource to a DataTable. You will then use the code that I provided to populate the DataTable. Only, you will need to adjust my code from using a DataReader to use a DataAdapter. You will put all the code inside of the Form's load event.
So drag a DataGridView and BindingSource from your toolbox onto your form. Then use the following code to populate everything:
Code:
'Bind the DataGridView to the BindingSource
DataGridView1.DataSource = BindingSource1
'Declare the connection object
Dim con As OleDbConnection
'Wrap code in Try/Catch
Try
'Set the connection object to a new instance
con = New OleDbConnection("Dsn=informix1;db=system;host=test1;serv=onlinetcp1;srvr=onlinetcp1;pro=onsoctcp;uid=admin;Pwd=pass")
'Create a new instance of the command object
Using cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [xpto] WHERE [user]=@user", con)
cmd.Parameters.AddWithValue("@user", "peter")
'Open the connection
con.Open()
'Use ExecuteReader to return a multiple value
Using adapter As OleDbDataAdapter = New OleDbDataAdapter(cmd, con)
'Populate a DataTable from a DataAdapter
Dim dt As DataTable = New DataTable
adapter.Fill(dt)
Bind the BindingSource to the DataTable
BindingSource1.DataSource = dt
End Using
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try
-
Aug 7th, 2015, 10:06 AM
#7
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Originally Posted by dday9
If you want to display your code in a DataGridView then you will want to bind your DataGridView to a BindingSource and bind your BindingSource to a DataTable. You will then use the code that I provided to populate the DataTable. Only, you will need to adjust my code from using a DataReader to use a DataAdapter. You will put all the code inside of the Form's load event.
So drag a DataGridView and BindingSource from your toolbox onto your form. Then use the following code to populate everything:
Code:
'Bind the DataGridView to the BindingSource
DataGridView1.DataSource = BindingSource1
'Declare the connection object
Dim con As OleDbConnection
'Wrap code in Try/Catch
Try
'Set the connection object to a new instance
con = New OleDbConnection("Dsn=informix1;db=system;host=test1;serv=onlinetcp1;srvr=onlinetcp1;pro=onsoctcp;uid=admin;Pwd=pass")
'Create a new instance of the command object
Using cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [xpto] WHERE [user]=@user", con)
cmd.Parameters.AddWithValue("@user", "peter")
'Open the connection
con.Open()
'Use ExecuteReader to return a multiple value
Using adapter As OleDbDataAdapter = New OleDbDataAdapter(cmd, con)
'Populate a DataTable from a DataAdapter
Dim dt As DataTable = New DataTable
adapter.Fill(dt)
Bind the BindingSource to the DataTable
BindingSource1.DataSource = dt
End Using
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try
You are fantastic, I will try.
Thank you very much for your help.
-
Aug 7th, 2015, 10:15 AM
#8
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Appears the next errors :
Error 1 Name 'BindingSource1' is not declared. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 5 36 Teste_connection_sql_odbc
Error 2 Type 'OleDbConnection' is not defined. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 8 20 Teste_connection_sql_odbc
Error 3 Type 'OleDbConnection' is not defined. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 12 23 Teste_connection_sql_odbc
Error 4 Type 'OleDbCommand' is not defined. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 15 26 Teste_connection_sql_odbc
Regards,
Cachado
-
Aug 7th, 2015, 10:18 AM
#9
Re: Help- Show query in a form/ODBC connection
Error 1 Name 'BindingSource1' is not declared. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 5 36 Teste_connection_sql_odbc
You will need to add a BindingSource to your form as you did with your DataGridView. If you've changed the name of your BindingSource then you'll also need to change the name in your code.
Error 2 Type 'OleDbConnection' is not defined. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 8 20 Teste_connection_sql_odbc
Error 3 Type 'OleDbConnection' is not defined. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 12 23 Teste_connection_sql_odbc
Error 4 Type 'OleDbCommand' is not defined. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 15 26 Teste_connection_sql_odbc
You need to make some reference to the OleDb namespace. Either by typing out OleDb before each OleDb object or by adding the following code at the very top of your code:
Code:
Imports System.Data.OleDb
-
Aug 7th, 2015, 10:59 AM
#10
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Error 1 'BindingSource' is a type and cannot be used as an expression. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 6 36 Teste_connection_sql_odbc
Error 2 Overload resolution failed because no accessible 'New' can be called with these arguments: 'Public Sub New(selectCommandText As String, selectConnection As System.Data.OleDb.OleDbConnection)': Value of type 'System.Data.OleDb.OleDbCommand' cannot be converted to 'String'. 'Public Sub New(selectCommandText As String, selectConnectionString As String)': Value of type 'System.Data.OleDb.OleDbCommand' cannot be converted to 'String'.
'Public Sub New(selectCommandText As String, selectConnectionString As String)': Value of type 'System.Data.OleDb.OleDbConnection' cannot be converted to 'String'. \Teste_connection_sql_odbc\Form1.vb 23 53 Teste_connection_sql_odbc
Error 3 Reference to a non-shared member requires an object reference. \Temporary Projects\Teste_connection_sql_odbc\Form1.vb 29 21 Teste_connection_sql_odbc
-
Aug 7th, 2015, 11:09 AM
#11
Re: Help- Show query in a form/ODBC connection
I forgot the apostrophe in my code, which you should've caught:
Code:
'Bind the DataGridView to the BindingSource
DataGridView1.DataSource = BindingSource1
'Declare the connection object
Dim con As OleDbConnection
'Wrap code in Try/Catch
Try
'Set the connection object to a new instance
con = New OleDbConnection("Dsn=informix1;db=system;host=test1;serv=onlinetcp1;srvr=onlinetcp1;pro=onsoctcp;uid=admin;Pwd=pass")
'Create a new instance of the command object
Using cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [xpto] WHERE [user]=@user", con)
cmd.Parameters.AddWithValue("@user", "peter")
'Open the connection
con.Open()
'Use ExecuteReader to return a multiple value
Using adapter As OleDbDataAdapter = New OleDbDataAdapter(cmd)
'Populate a DataTable from a DataAdapter
Dim dt As DataTable = New DataTable
adapter.Fill(dt)
'Bind the BindingSource to the DataTable
BindingSource1.DataSource = dt
End Using
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try
Also, I'm free-typing all of this. A quick reference to MSDN would've shown that I was using the OleDbCommand and OleDbConnection for the new constructor of the DataAdapter when it should've been just the OleDbCommand
-
Aug 7th, 2015, 11:17 AM
#12
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Thanks for your patience
The code is not like the following instructions:
Error 1 'BindingSource' is a type and cannot be used as an expression.
DataGridView1.DataSource = BindingSource
and
Error 2 Reference to a non-shared member requires an object reference.
BindingSource.DataSource = dt
-
Aug 7th, 2015, 11:23 AM
#13
Re: Help- Show query in a form/ODBC connection
That is because you have just BindingSource where as the word BindingSource needs to be the name of your BindingSource component that you drug from your toolbox to your form.
-
Aug 7th, 2015, 11:36 AM
#14
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Originally Posted by dday9
That is because you have just BindingSource where as the word BindingSource needs to be the name of your BindingSource component that you drug from your toolbox to your form.
By now run without error but do not appear in the datagrid
Hummmm
-
Aug 7th, 2015, 12:37 PM
#15
Re: Help- Show query in a form/ODBC connection
Could you copy/paste your code here in between code tags:
[CODE][/CODE]
And then post a screen shot of what your form looks like in the designer view?
-
Aug 7th, 2015, 01:08 PM
#16
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Ok.... thks..
If you think there is a better or easier way to display a query tell me. The ultimate goal will be to perform more than one query via menus or buttons
Code:
Imports System.Data.OleDb
Public Class Form1
Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
'Bind the DataGridView to the BindingSource
DataGridView1.DataSource = BindingSource1
'Declare the connection object
Dim con As OleDbConnection
'Wrap code in Try/Catch
Try
'Set the connection object to a new instance
con = New OleDbConnection("Dsn=informix1;db=system;host=test;serv=onlinetcp1;srvr=onlinetcp;pro=onsoctcp;uid=admin;Pwd=pass99")
'Create a new instance of the command object
Using cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [suser] WHERE [user_id]=@user", con)
cmd.Parameters.AddWithValue("@user", "jcachado")
'Open the connection
con.Open()
'Use ExecuteReader to return a multiple value
Using adapter As OleDbDataAdapter = New OleDbDataAdapter(cmd)
'Populate a DataTable from a DataAdapter
Dim dt As DataTable = New DataTable
adapter.Fill(dt)
'Bind the BindingSource to the DataTable
BindingSource1 = BindingSource1
End Using
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
End Class
-
Aug 7th, 2015, 01:14 PM
#17
Re: Help- Show query in a form/ODBC connection
You have your code in DataGridView1.CellContentClick, so the code will not run until content in a cell for DataGridView1 is clicked. You need to move your code to your form's load event like you have in your original post.
-
Aug 7th, 2015, 01:45 PM
#18
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Sorry, but dont have nothing
-
Aug 7th, 2015, 01:56 PM
#19
Re: Help- Show query in a form/ODBC connection
Code:
'Bind the BindingSource to the DataTable
BindingSource1 = BindingSource1
the comment says one thing, then you do something completely different...
you did this:
Code:
'Assign 12 to the MonthCount
12=12
What you should be doing is setting the datasource of the bindingsourse to the datatable...
-tg
-
Aug 7th, 2015, 03:13 PM
#20
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
-
Aug 7th, 2015, 03:54 PM
#21
Re: Help- Show query in a form/ODBC connection
That is because you're trying to set the BindingSource equal to the DataTable when instead my code that I provided stated that you need to set the BindingSource's DataSource as the DataTable:
Code:
BindingSource1.DataSource = dt
-
Aug 7th, 2015, 04:08 PM
#22
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
I'm about to give up unfortunately
Code:
Imports System.Data.OleDb
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Bind the DataGridView to the BindingSource
DataGridView1.DataSource = BindingSource1
'Declare the connection object
Dim con As OleDbConnection
'Wrap code in Try/Catch
Try
'Set the connection object to a new instance
con = New OleDbConnection("Dsn=informix1;db=system;host=test;serv=onlinetcp1;srvr=onlinetcp1;pro=onsoctcp;uid=admin;Pwd=pass99")
'Create a new instance of the command object
Using cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [suser] WHERE [user_id]=@user", con)
cmd.Parameters.AddWithValue("@user", "jcachado")
'Open the connection
con.Open()
'Use ExecuteReader to return a multiple value
Using adapter As OleDbDataAdapter = New OleDbDataAdapter(cmd)
'Populate a DataTable from a DataAdapter
Dim dt As DataTable = New DataTable
adapter.Fill(dt)
'Bind the BindingSource to the DataTable
BindingSource1.DataSource = dt
End Using
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try
End Sub
End Class
Last edited by jcachado; Aug 7th, 2015 at 04:16 PM.
-
Aug 7th, 2015, 04:57 PM
#23
Re: Help- Show query in a form/ODBC connection
What does your watch window say because an exception may be occurring, but I do not see where you have your watch window up to catch those errors.
-
Aug 7th, 2015, 05:08 PM
#24
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Originally Posted by dday9
What does your watch window say because an exception may be occurring, but I do not see where you have your watch window up to catch those errors.
The error is not be returning any results of the query.
-
Aug 7th, 2015, 05:13 PM
#25
Re: Help- Show query in a form/ODBC connection
Could you post the exact exception along with the schema of your database?
-
Aug 8th, 2015, 06:26 AM
#26
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Hi,
The variable "con" has a underline in code, may be the problem here?
Variable con is user before it has been assigned a value. A null reference exception could result at runtime
-
Aug 9th, 2015, 07:31 PM
#27
Re: Help- Show query in a form/ODBC connection
That is not an exception, rather a warning. You can get rid of it by setting con to nothing when it is initially declared.
Also, while I don't generally do this... Could you zip up your project, remove any binaries, and upload it here? What I'll do is go over it and see what's causing the issue.
-
Aug 10th, 2015, 03:37 AM
#28
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Originally Posted by dday9
That is not an exception, rather a warning. You can get rid of it by setting con to nothing when it is initially declared.
Also, while I don't generally do this... Could you zip up your project, remove any binaries, and upload it here? What I'll do is go over it and see what's causing the issue.
Thank you very much, I am very grateful.
https://www.dropbox.com/s/bf4os6zlal..._odbc.zip?dl=0
-
Aug 11th, 2015, 11:26 AM
#29
Thread Starter
Addicted Member
Re: Help- Show query in a form/ODBC connection
Hello dday9, forgot to me?
One detail this is a ODBC connection!
Thks,
Cachado
Last edited by jcachado; Aug 11th, 2015 at 02:23 PM.
-
Aug 12th, 2015, 10:11 AM
#30
Thread Starter
Addicted Member
[SOLVED]Re: Help- Show query in a form/ODBC connection
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|