insert and search data

lgsoad

Member
Joined
Oct 17, 2012
Messages
5
Programming Experience
3-5
Hi guys, how are you doing?
Im having a vb.net exam next monday and im in serious need of your knowledge here cause our teacher ... just told us to "memorize" the code since he didnt had time to explain it, and, at least i, cant work that way.

so, basically here's the PDF he gave us with the assignment we need to memorize, more precisely the "producto" class:
http://www.filedropper.com/comohacerunabmen1capaconvisualbasicdotnet

I know its in spanish but you guys will understand what the code is doing, but ill give you guys a quick rundown:
basically i need to insert and search data into an Access DB from a windows form.

I dont know why we need to do in the middle a class that contains a few methods, among them the insert, update, erase, obtain type for a combofix and search)

Thing is, even tho it has a few comments in the first method, it doesnt describe much about whats the heck we're doing or how this works exactly.

My doubt begins in the insert method (bottom of page 13, where it reads 6.2.2.2)

For example, when it does:

12. Dim cmd_insertar = "INSERT INTO Productos (nombre, rubro_id, precio, stock, fechaInicioComercial) " & " VALUES (@nombre, @rubro_id, @precio, @stock, @fechaInicioComercial)"

I dont know what values im refering to for the INSERT INTO ( X , X, X ,X)
Are those the access registers? Same with the VALUES part
What am i referencing to with those values that begin with @, to the form, class, access?

Moving to:
29. comando.Parameters.Add(New OleDbParameter("@nombre", Me.Nombre))
30. comando.Parameters.Add(New OleDbParameter("@rubro_id", Me.Rubro))
31. comando.Parameters.Add(New OleDbParameter("@precio", Me.Precio))
32. comando.Parameters.Add(New OleDbParameter("@stock", Me.Stock))
33. comando.Parameters.Add(New OleDbParameter("@fechaInicioComercial", Me.FechaInicio))

I would like to know exactly what this line is for ("parameters.add(new oledbparameter..."), and talking about the values, the first one, same, what do the values that beging @ are referencing to? (form, class, access db).
The second one, that begins with me.X is making reference to the properties of the class, right?

And what does this one do exactly? sends the values?
38. comando.ExecuteNonQuery()

And from this ones i just dont understand anything at all:
42. Dim cmd_select = "select @@identity"
43. comando = New OleDbCommand(cmd_select, conexion)
44. Dim dt = New DataTable()
45. Dim da = New OleDbDataAdapter(comando)
46. da.Fill(dt)
47. Me.Codigo = Convert.ToInt32(dt.Rows(0)(0)) 'Obtengo el valor y se lo asigno al atributo Codigo.

51. conexion.Close()
54. Return Me.Codigo

What the heck is @@identity? what is a datatable? why am i using one for? what is that oledbdataadapter? how about da.fill and "convert.toint32(dt.rows...", what is going on?

And the same with the other methods cause they are not even commented.

Please lend me a hand, guys, im desperate.
Thanks a lot for reading and your help beforehand.
Big Hug.
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
In this line:
VB.NET:
Dim cmd_insertar = "INSERT INTO Productos (nombre, rubro_id, precio, stock, fechaInicioComercial) " & " VALUES (@nombre, @rubro_id, @precio, @stock, @fechaInicioComercial)"
'Productos' is the table to insert a record to, the first set of parentheses contains the list of columns in the specified table that the values will be inserted in and the second set of parentheses contains the values that will be inserted to those columns.

The @ symbol indicates a parameter in T-SQL. In VB you can write a method (Function or Sub) with parameters and then in the body of the method you use those parameters as place-holders for the actual value that will be used at run time. When you call the method you pass an argument to each parameter and the code is then executed using that value everywhere the parameter place-holder is found. SQL is basically the same. Each of those @ values is a parameter place-holder that will be replaced by an actual value when the code is executed. This section:
VB.NET:
comando.Parameters.Add(New OleDbParameter("@nombre", Me.Nombre))
comando.Parameters.Add(New OleDbParameter("@rubro_id", Me.Rubro))
comando.Parameters.Add(New OleDbParameter("@precio", Me.Precio))
comando.Parameters.Add(New OleDbParameter("@stock", Me.Stock))
comando.Parameters.Add(New OleDbParameter("@fechaInicioComercial", Me.FechaInicio))
is where those values are provided. That says that, wherever the @nombre parameter is found in the SQL code, replace it with the value of Me.Nombre. Similarly for the others. If you want to learn more about that subject, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

This line:
VB.NET:
comando.ExecuteNonQuery()
is then executing the SQL statement contained in the command. ExecuteNonQuery is used to execute a command that doesn't produce a result set, i.e. that contains no SELECT statement.

That table that you're inserting into obviously has a primary key column defined as an AutoNumber, because the rest of the code is dedicated to retrieving the auto-generated ID. @@IDENTITY is a special global variable that always contains the last ID auto-generated by the database. If you get its value immediately after inserting a record into a table with an AutoNumber column, you'll get the ID for that new record. That's a bit of a clumsy way to go about getting that value but it will work.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
It's like you didn't actually read my post.
That table that you're inserting into obviously has a primary key column defined as an AutoNumber, because the rest of the code is dedicated to retrieving the auto-generated ID. @@IDENTITY is a special global variable that always contains the last ID auto-generated by the database. If you get its value immediately after inserting a record into a table with an AutoNumber column, you'll get the ID for that new record. That's a bit of a clumsy way to go about getting that value but it will work.
The code already does it. If @@IDENTITY contains the last auto-generated ID and that code queries the database to get the value of @@IDENTITY, what do you expect the result to be?
 

lgsoad

Member
Joined
Oct 17, 2012
Messages
5
Programming Experience
3-5
sorry, i ment cause you said: "That's a bit of a clumsy way to go about getting that value but it will work."
thats why i asked how would YOU do it instead?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Ah, I see. Sorry about the misunderstanding. In that case, I would use a command containing that same SQL but using a data adapter to populate a DataTable with one value is over kill. I would just call ExecuteScalar on the command and it would return the value directly. If you want to save a whole DataTable that may contain more than one new record then here's how to get all the new IDs:

Retrieve Access AutoNumber Value After Insert
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Um, you what? Your profile says that you're using .NET 4.0 and that's only supported in VS 2010 or later. Either you're mistaken or your profile is incorrect. I won't be explaining it so, if it's the latter, you have three options:

1. Download and install a newer version of VB Express. VS has been created specifically so that you can install multiple versions so it won't hurt your existing installation in any way.
2. Don't open the solution/project but rather just open the individual code files, which are the same regardless of version. That way you can still read the code, even if you can't run it without creating a new project of your own.
3. Edit the SLN and VBPROJ files by hand to make them compatible with your version. It's not especially difficult and you could find instructions online, but it may still be a bit daunting for a beginner.

I'd suggest option 1. I'd suggest using the latest version anyway, unless you're required to use an older version for a course. Outside of course work I'd suggest using the latest version to get the latest features. All versions from 2005 support back to .NET 2.0, so Framework version is not an issue.
 

lgsoad

Member
Joined
Oct 17, 2012
Messages
5
Programming Experience
3-5
we're using an older version at school, cant open it, my profile is wrong, i never wrote anything in it so it must be a default thingy going on there.
which file should i open?
 
Top Bottom