doubt: sql + ado.net + vb.net

[Charlie Parker]

Hi there,

I'm trying to recycle myself to VB.net. I'm trying to get this to work but
when I click the button a window pops up with "Could not find
ArgumentException" and "column 'reffactura' doesn't belong to the table".
Well my desktop language is spanish and in english this may differ a
little...

I have this:

Imports System.Data

Imports System.Data.SqlClient

Public Class Form2

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click



Dim GeoSQLConn As SqlClient.SqlConnection

GeoSQLConn = New SqlClient.SqlConnection()

GeoSQLConn.ConnectionString = "data source = DAVID-PORTATIL\David; initial
catalog = geoflux; integrated security = true"

GeoSQLConn.Open()

Dim GeoAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT reffactura
FROM ordre", GeoSQLConn)

Dim GeoSet As DataSet = New DataSet()

GeoAdapter.Fill(GeoSet)

Dim GeoTable As DataTable = New DataTable()

Dim nouOrdre As DataRow = GeoTable.NewRow()

nouOrdre("reffactura") = RefFactura.Text

GeoTable.Rows.Add(nouOrdre)

GeoAdapter.Update(GeoTable)

GeoTable.AcceptChanges()

End Sub

End Class

What's wrong?

Thanks in advance

 

[Norman Yuan]

See comments inline.

Hi there,

I'm trying to recycle myself to VB.net. I'm trying to get this to work but
when I click the button a window pops up with "Could not find
ArgumentException" and "column 'reffactura' doesn't belong to the table".
Well my desktop language is spanish and in english this may differ a
little...

I have this:

Imports System.Data

Imports System.Data.SqlClient

Public Class Form2

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click



Dim GeoSQLConn As SqlClient.SqlConnection

GeoSQLConn = New SqlClient.SqlConnection()

GeoSQLConn.ConnectionString = "data source = DAVID-PORTATIL\David; initial
catalog = geoflux; integrated security = true"

GeoSQLConn.Open()

Dim GeoAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT reffactura
FROM ordre", GeoSQLConn)

Dim GeoSet As DataSet = New DataSet()

GeoAdapter.Fill(GeoSet)

Dim GeoTable As DataTable = New DataTable()

You just created a new DataTable, which is blank DataTable without
DataColumn being defined.

I guess you meant for a table that is in the DataSet with data filled. So,
you should find the table in the DataSet, not creating a new, blank one, as
you did below.

Dim nouOrdre As DataRow = GeoTable.NewRow()

So, the DataRow you got here does not have item corresponding to any
DataColumn

nouOrdre("reffactura") = RefFactura.Text

Of course the DataRow does not have a Column called "reffactura", it has no
column at all.
Here is where you got the runtime error.

 

[Cor Ligthert[MVP]]

Charlie,

Your datatabe to use is in your case:

Geoset.Tables(0) 'much more possibilities to write this

Cor

 

[Charlie Parker]

Hello,

Thanks for your help, but I must confess I'm not getting the point.

Please, be patient... and correct me if I'm wrong:

1.-The connection:

Dim GeoSQLConn As SqlClient.SqlConnection

GeoSQLConn = New SqlClient.SqlConnection()

2.- The connection String:

GeoSQLConn.ConnectionString = "data source = DAVID-PORTATIL\David; initial
catalog = geoflux; integrated security = true"

3.- Open connection:

GeoSQLConn.Open()

4.- From now on I'm not very sure of anyting. Let's start supposing:

4.1.- I see in tutorials that to get data from a db I need a connection, a
dataadapter and a dataset. We've seen the connection, now let's get to the
dataadapter:

Dim GeoAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM ordre",
GeoSQLConn)

Question: In the Sql instruction should I use the column names separated by
commas or the asterisk is ok?

5.- The Dataset:

Dim GeoSet As DataSet = New DataSet()

GeoAdapter.Fill(GeoSet)

6.- Well as you can see this is a db called geoflux and the table is called
ordre. This db is empty. I have a form and want to fill in the db with the
text in the textboxes. "reffactura" is a column in Table "ordre". I want to
fill the column "reffactura" with RefFactura.text containt

Question: How should I proceed?

Thanks very much for your help. Can you recommend me some tutorial with
clear examples? I am not able to learn from msdn, I'm afraid.

 

[Cor Ligthert[MVP]]

Please, be patient... and correct me if I'm wrong:

1.-The connection:

Dim GeoSQLConn As SqlClient.SqlConnection

GeoSQLConn = New SqlClient.SqlConnection()

Dim GeoSQLConn As new SqlClient.SqlConnection(TheConnectionString)

2.- The connection String:

GeoSQLConn.ConnectionString = "data source = DAVID-PORTATIL\David; initial
catalog = geoflux; integrated security = true"

http://www.connectionstrings.com/

3.- Open connection:

GeoSQLConn.Open()

This should be done, only if you do something with that connection (not with
a dataadapter, that does it automaticly)

4.- From now on I'm not very sure of anyting. Let's start supposing:

4.1.- I see in tutorials that to get data from a db I need a connection, a
dataadapter and a dataset. We've seen the connection, now let's get to the
dataadapter:

Dim GeoAdapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
ordre", GeoSQLConn)

Question: In the Sql instruction should I use the column names separated
by commas or the asterisk is ok?

Commas, the * means all columns, there is no comma needed after the last
column

5.- The Dataset:

Dim GeoSet As DataSet = New DataSet()

Dim GeoSet as New DataSet
The way you use it is a kind of what you see done by C# people.
VB has shorter code for this

GeoAdapter.Fill(GeoSet)

6.- Well as you can see this is a db called geoflux and the table is
called ordre. This db is empty. I have a form and want to fill in the db
with the text in the textboxes. "reffactura" is a column in Table "ordre".
I want to fill the column "reffactura" with RefFactura.text containt

Question: How should I proceed?

Creating a form with a control on it, by instance a DataGridView
You can then add your table to that by
dim bs as new BindingSource
bs.DataSource = GeoSet.Tables(0)
dgv.DataSource = bs