Help with database newbie

[phreaky]

I'm using the A_Jam tutorial for handling databases (it´s a excelent tutorial!), but I ahve some problems, I´m trying to do the following: I'm inserting an user and I want to see if the user already exists in the DB, if os, a MsgBox should appear saying that "The user is already on the DB", if not, then the user must be inserted into DB and a MsgBox saying "User inserted!". With the tutorial I already made the insertion, my problem is that I can´t check if the user is in the database or not, in fact, it gives me an error I don´t understand, here´s part of the code I used:

 

Dim sql, ci As String
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\" & "Hardvards.mdb")
conn.Open()

Dim sqlsrch As New OleDb.OleDbCommand("SELECT FROM Cliente * WHERE Cedula_cliente = " + Cliente_cedula_numero_text.Text, conn)
Dim search As OleDb.OleDbDataReader = sqlsrch.ExecuteReader()
Dim sqlcmd As New OleDb.OleDbCommand("INSERT INTO Cliente (Nombre_cliente, Apellido_cliente, Cedula_cliente, Direccion_cliente, Telefono_cliente, Celular_cliente) VALUES ('" + Cliente_nombre_text.Text + "','" + Cliente_apellido_text.Text + "','" + Cliente_cedula_numero_text.Text + "','" + Cliente_direccion_text.Text + "','" + Cliente_codigotelefono_text.Text + Cliente_numerotelefono_text.Text + "','" + Cliente_codigocelular_text.Text + Cliente_numerocelular_text.Text + "')", conn)
While search.Read
   ci = search(4)
   If ci = Cliente_cedula_numero_text.Text Then
        MsgBox("Cliente ya existe")
   Else
        sqlcmd.ExecuteNonQuery()
        conn.Close()
        sqlcmd.Dispose()
        MsgBox("Cliente Insertado")
   End If
End While

 

And the error I get when I run and I try to insert into DB is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

 

So, what I´m doing wrong? and...how it´s I´m supposed to do to check if the record is already on the DB?

[bri189a]

You should close your connection after the loop, not during it, and unless you need to dispose for lack of resources, let the garbage collector take care of it for you since as soon as you are out of that function the referance will be dropped and the GC will mark it for collection.

 

Also you can't close a connection while a reader is open, so that is the cause for your error.

 

tenga un bien dia

[HJB417]

I'm using the A_Jam tutorial for handling databases (it´s a excelent tutorial!), but I ahve some problems, I´m trying to do the following: I'm inserting an user and I want to see if the user already exists in the DB, if os, a MsgBox should appear saying that "The user is already on the DB", if not, then the user must be inserted into DB and a MsgBox saying "User inserted!". With the tutorial I already made the insertion, my problem is that I can´t check if the user is in the database or not, in fact, it gives me an error I don´t understand, here´s part of the code I used:

 

Dim sql, ci As String
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\" & "Hardvards.mdb")
conn.Open()

Dim sqlsrch As New OleDb.OleDbCommand("SELECT FROM Cliente * WHERE Cedula_cliente = " + Cliente_cedula_numero_text.Text, conn)
Dim search As OleDb.OleDbDataReader = sqlsrch.ExecuteReader()
Dim sqlcmd As New OleDb.OleDbCommand("INSERT INTO Cliente (Nombre_cliente, Apellido_cliente, Cedula_cliente, Direccion_cliente, Telefono_cliente, Celular_cliente) VALUES ('" + Cliente_nombre_text.Text + "','" + Cliente_apellido_text.Text + "','" + Cliente_cedula_numero_text.Text + "','" + Cliente_direccion_text.Text + "','" + Cliente_codigotelefono_text.Text + Cliente_numerotelefono_text.Text + "','" + Cliente_codigocelular_text.Text + Cliente_numerocelular_text.Text + "')", conn)
While search.Read
   ci = search(4)
   If ci = Cliente_cedula_numero_text.Text Then
        MsgBox("Cliente ya existe")
   Else
        sqlcmd.ExecuteNonQuery()
        conn.Close()
        sqlcmd.Dispose()
        MsgBox("Cliente Insertado")
   End If
End While

 

And the error I get when I run and I try to insert into DB is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

 

So, what I´m doing wrong? and...how it´s I´m supposed to do to check if the record is already on the DB?

 

I don't know vb code, but here's the generics of what needs to be done

 

#1) make sure you put the OleDbCommand in a USING statement/block

 

1) create the oledbcommand

2) set the command text to "SELECT COUNT(*) FROM Cliente WHERE Cedula_cliente = " + Cliente_cedula_numero_text.Text"

3) call executescalar(). It will return an Int32

4) if the value returned is more than zero, do MsgBox("Cliente ya existe")

otherwise set the commandtext to INSERT INTO Cliente (Nombre_cliente, Apellido_cliente, Cedula_cliente, Direccion_cliente, Telefono_cliente, Celular_cliente) VALUES ('" + Cliente_nombre_text.Text + "','" + Cliente_apellido_text.Text + "','" + Cliente_cedula_numero_text.Text + "','" + Cliente_direccion_text.Text + "','" + Cliente_codigotelefono_text.Text + Cliente_numerotelefono_text.Text + "','" + Cliente_codigocelular_text.Text + Cliente_numerocelular_text.Text + "')", and call ExecuteNonQuery

 

ALso, you really need to get in the habit of using connection pooling and parameters.

[Joe Mamma]

VB doesnt have the using construct. . .

 

One of the many deficiencies of VB.

[georgepatotk]

Dim sql, ci As String
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\" & "Hardvards.mdb")
conn.Open()

Dim sqlsrch As New OleDb.OleDbCommand("SELECT FROM Cliente * WHERE Cedula_cliente = " + Cliente_cedula_numero_text.Text, conn)
Dim search As OleDb.OleDbDataReader = sqlsrch.ExecuteReader()
Dim sqlcmd As New OleDb.OleDbCommand("INSERT INTO Cliente (Nombre_cliente, Apellido_cliente, Cedula_cliente, Direccion_cliente, Telefono_cliente, Celular_cliente) VALUES ('" + Cliente_nombre_text.Text + "','" + Cliente_apellido_text.Text + "','" + Cliente_cedula_numero_text.Text + "','" + Cliente_direccion_text.Text + "','" + Cliente_codigotelefono_text.Text + Cliente_numerotelefono_text.Text + "','" + Cliente_codigocelular_text.Text + Cliente_numerocelular_text.Text + "')", conn)


if search.HasRow then

       MsgBox("Cliente ya existe")
Else
        sqlcmd.ExecuteNonQuery()
        sqlcmd.Dispose()
        MsgBox("Cliente Insertado")
End if
conn.Close()

 

try this

[HJB417]

Ok,

try finally then.

Also, I didn't see that he opened the connection in that code, so he'll have to open the connection, and in a finally block, close and dispose it.

 

And as for the post above. You shouldn't be using a data reader for that select statement, you should be using aggregate functions such as 'count' in conjunction with executescalar. You also didn't close and/or dispose the data reader.

 

And depending on how the database is used, you'll need transactions if you'll ever have a scenario where a row gets inserted between the return of your select statement and before the insert statement.

[phreaky]

Dim sql, ci As String
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\" & "Hardvards.mdb")
conn.Open()

Dim sqlsrch As New OleDb.OleDbCommand("SELECT FROM Cliente * WHERE Cedula_cliente = " + Cliente_cedula_numero_text.Text, conn)
Dim search As OleDb.OleDbDataReader = sqlsrch.ExecuteReader()
Dim sqlcmd As New OleDb.OleDbCommand("INSERT INTO Cliente (Nombre_cliente, Apellido_cliente, Cedula_cliente, Direccion_cliente, Telefono_cliente, Celular_cliente) VALUES ('" + Cliente_nombre_text.Text + "','" + Cliente_apellido_text.Text + "','" + Cliente_cedula_numero_text.Text + "','" + Cliente_direccion_text.Text + "','" + Cliente_codigotelefono_text.Text + Cliente_numerotelefono_text.Text + "','" + Cliente_codigocelular_text.Text + Cliente_numerocelular_text.Text + "')", conn)


if search.HasRow then

       MsgBox("Cliente ya existe")
Else
        sqlcmd.ExecuteNonQuery()
        sqlcmd.Dispose()
        MsgBox("Cliente Insertado")
End if
conn.Close()

 

try this

 

 

Hmmm....I don´t know George, I haven't tried your code but...as I can understand it will check only if the DB has ANY record, I need to know if it has the record I´m looking for (specifically). Anyway, I appreciate your help and Iwill try your code.

[georgepatotk]

To check whether the queried db has any record

if search.HasRows then

[VBAHole22]

I believe the VB.NET equivalent of using is Imports.

[PlausiblyDamp]

In C# using is actually used for two differerent purposes one is the equivalent of VB's imports the other is just a nice clean way to ensure .Dispose is called on objects that require their cleanup to be run at a known point (DB connections, file handles etc).

In the next version VB also gains the Using command though.