How do I check if SQL string is a copy?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm working in VB.net with an ADOBD connection to an Access database. With a SAVE button click I want to be able to check if there is an instance of the file already in the database before it is saved in the database. How can I do this? I've played around with this and can not figure it out. I've tried to diplay the results in a DataGrid, if there is such an instance, but yet again I can not get it to work properly. Please can someone help. I'am new to all this, and enjoy playing around with this language

Thank
 
Doug said:
I'm working in VB.net with an ADOBD connection to an Access
database. With a SAVE button click I want to be able to check if
there is an instance of the file already in the database before it is
saved in the database. How can I do this? I've played around
with this and can not figure it out. I've tried to diplay the results in
a DataGrid, if there is such an instance, but yet again I can not get it
to work properly. Please can someone help. I'am new to all this,
and enjoy playing around with this language.
Click to expand...

I'm not exactly sure what you're asking. When you press a Save button,
you want to store a string containing a filename to your database? And you
want to see if it's already there before you add it?

If you just want a SQL query to check, it's probably better to avoid
complicated classes like the DataSet and DataGrid. There are some
less-visible but sometimes more-useful methods of data objects that are
lighter weight, faster, and easier to use. Here's the code I would try:

Dim myConnection As OleDbConnection
myConnection = New OleDbConnection("Insert Connection String Here")
myConnection.Open()

Dim queryString As String
queryString = "SELECT COUNT(*) FROM MyTable" & _
" WHERE FileName='" & [Whatever].ToString.Replace("'", "''")
& "'"

Dim myCommand As OleDbCommand
myCommand = New OleDbCommand(queryString, myConnection)

' ExecuteScalar returns the first column of the first row of the query,
' for situations just like this where there's only one value we care about.
Dim matchingRows As Integer = CInt(myCommand.ExecuteScalar)
myConnection.Close()

If matchingRows = 0 Then
' Add the row to the database
End If

If this isn't the problem you're having, let me know and I'll see what I
can do!
Jeremy
 
Hi Doug,

I do not totally agree with him, but I also advice you to use adonet and not
adobd (he did not tell that but he uses that).

But his sentence about the dataset and datagrid I do not agree, and
therefore also to show it to him I changed his example to show how simple a
dataset and a datagrid works.

Dim myConnection As OleDbConnection
myConnection = New OleDbConnection("Insert Connection String Here")
Dim queryString As String
queryString = "SELECT COUNT(*) FROM MyTable" & _
" WHERE FileName='" & [Whatever].ToString.Replace("'", "''")
& "'"

Dim da As New OleDbAdapter(queryString, myConnection)
dim ds as new dataset
da.fill(ds)
datagrid1.datasource = ds

I hope this helps,

Cor
 
Cor

Thanks, but I now how to populate the datagrid. My question is how can I find out if there is already a copy of a database file or not before the connection command for saving is performed. I don't want "2" copies of the same file in the same database

Thank
 
Hi Doug,

I really did not understand what you mean with files in a database, because
normaly a database holds tables and tables holds rows and rows hold items
(columns). Not files, so probably I do not understand you well.

While writing that, I got an idea but it really is a riddle when this is the
solution,

Maybe ou are creating your own tables using ADODB and want to know if the
table already exist. For this you can do something with OleDb as.

Dim Conn As New OleDblConnection(connString)
Dim cmd As OleDblCommand
cmd = New OleDbCommand("select count(*) from tblMessages", Conn)
cmd.Connection.Open()
Dim count As Integer = CInt(cmd.ExecuteScalar())

Or you are creating a completly new accessfile

That you can see with a fileinfo.exist

If it is one of those, than I find myself very clever.

Cor
Thanks, but I now how to populate the datagrid. My question is how can I
Click to expand...
find out if there is already a copy of a database file or not before the
connection command for saving is performed. I don't want "2" copies of the
same file in the same database.
 
Back
Top