A1VBCode Forums / Classic Visual Basic (VB 6 or earlier) / Database Programming

Delete item in database based on date field not working

Raycomp — Sat, 04 Feb 2012 15:57:39 GMT

I hope someone here might have the answer

I have a form from where I would like to delete all records before a specified date, the form has a text field and command button that should delete records in the database older than 3 months but it does not delete the records.

the code on my form is as follows

[code]Private Sub Form_Load()
Dim test As String

test = DateAdd("m", -3, Now) 'this uses the current date and subtract 3 months
Text1.Text = test
Text1.Text = Format$(test, "mm/dd/yyyy")

End Sub

Private Sub Command1_Click()

Dim cs As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim flds As New ADODB.Recordset

cs.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\Program Files\Hazmat Control\HazMat.mdb"
cs.Open

Dim date1 As String
cs.BeginTrans
cs.Execute "DELETE * From ControlDetailsA where Date <= " & Text1.Text 'date is the field in the access table
cs.CommitTrans

End Sub[/code]

If I however change the code to delete by control number then it is successful. Looks like I am missing something when trying to use the date field to select the records.

delete does not affect database!

maia_18 — Sun, 02 Oct 2011 12:53:41 GMT

Hi to all..
i have ha problem with my delete menustrip item...

when i click yes to the verification msgbox, the row i selected is deleted.
but when i click on another button , the row returns!!!
it does not seem to affect the underlying database..
i've seached google, but so far, i still haven't found an answer...
thanks in advance..

codecodecode:

Private Sub DeleteItemToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeleteItemToolStripMenuItem.Click
dgvLadies.DataSource = vbNull
dsLadies = New DataSet()

daProducts.Fill(dsLadies, "products")
dgvLadies.DataSource = dsLadies.Tables(0)
dgvLadies.Visible = True

If MsgBox("Do you want to delete this row ?", MessageBoxButtons.YesNo) = DialogResult.Yes Then
dsLadies.Tables("products").Rows.RemoveAt(dgvLadies.CurrentRow.Index)
dgvLadies.Rows.RemoveAt(dgvLadies.CurrentRow.Index)
End If
daProducts.Update(dsLadies, "products")
End Sub

vb6 and sql 2005 multi user problem

june1282 — Tue, 17 Jan 2012 01:58:15 GMT

Dear All !

I upgrade (vb6 and access)application to sql server 2005 application(changed back end to sql). I have 2 forms, one is "Voucher" and another one is "reprint voucher".
2 forms use same one database table (gridVoucher), which is link to LIST VIEW.

When 1st user use "voucher form" (she is opening new voucher for her customer ). and 2nd user view her previous voucher with "reprintVoucher form" at the same time. gridVoucher is replace by reprintVoucher form. Then 1st user save his voucher, stock
items wrong. I use INSERT INTO statement to save voucher data. (INSERT INTO VoucherDetail SELECT * FROM gridVoucher) at "voucher form".

I want to lock gridVoucher table when it is used by user.
Please give advise better suggestion for this problem.

Yours
june1282

speed up select distinct - access database and vb6 classic

sal21 — Tue, 17 Jan 2012 00:51:44 GMT

I need to speed up this sql select distinct:

my conn:

Public Sub APRI_CONNESSIONE3()

On Error GoTo Err_SomeName

Set CONN3 = Nothing
Set CONN3 = New ADODB.Connection
With CONN3
.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\REPORT_L0\DATABASE\" & MIO_DB & "-L0_TEST.mdb;Persist Security Info=False"
End With

Exit_SomeName:
Exit Sub

Err_SomeName:
MsgBox Err.Number & Err.Description
Resume Exit_SomeName

End Sub

my select distinct:

Set RST0 = Nothing
Set RST0 = New ADODB.Recordset
RST0.CursorLocation = adUseClient
'RST0.CacheSize = 500

SQL = "SELECT DT FROM L0_SI WHERE (CONTAB='" & TEST_CONTAB & "') GROUP BY DT"
DoEvents
RST0.Open SQL, CONN3, adOpenForwardOnly, adLockReadOnly, adCmdText

note:

- CONTAB and DT fields are indexed
- records in table are approx 1.500.xxx

Insert with Where clause statement

mjshort88 — Sun, 12 Jun 2011 08:53:34 GMT

Hello

I've been trying to get this SQL statement working for ages now and came across this website!

It's probably really simple but any help would be great.

"INSERT INTO Images (PicName) VALUES (" & TextBox1.Text & ") WHERE ImageID = " & Summary

I get an error message 'Missing ; at the end of SQL statement'

I'm using a Access database back end.

Thank you

how to connect vb to SQL

Mr.Emopaul — Tue, 22 Sep 2009 01:52:51 GMT

helo.. everyone...! I am Marpaul and i want to know how to connect SQL database to Visual basic.
i am bigginer for programming..Please help me..
my email add Mr.Emopaul@yahoo.com
thanks..

editing database entries

Teresalk — Mon, 19 Dec 2011 06:28:54 GMT

I need to change a value in my database lto a different value.
Lets say my table is called downloads and the 6th row is called surl

Now theres a url in there thats wrong but it retirn abouty more ythen 100 times how can i change hxxp://blabla.com/iphoney to hxxp://blablah.com

I never needed to edit anything begore lol so if someone can lend a hand would be cool

Connection

mohamedsamir3000 — Fri, 25 Dec 2009 12:19:43 GMT

Hi everyone,

I want to know how can I connect to an external database via vb.net.

Thank you

Any Column Select

shers — Tue, 08 Nov 2011 12:48:16 GMT

Hi,

I have a value which I don't know which column this belongs to in the table. So is there a way I get get those rows that contain this value using query?

Thanks

problem in the connection of Oracle DataBase

hassaco — Wed, 27 Apr 2011 04:07:53 GMT

Hi ,
i have visual basic6 application, and i instaled it as setup on the client

this application should connect remotely via a network with oracle database
put on another device
i used this connection string :

If oconn.State = 1 Then oconn.Close
oconn.Open "Provider=msdaora;Data Source=orcl;User Id=FIN;Password=FIN;"

i have this error in the image attached

so ...please help me to solve this problem
or
what i need to download (tools,provider)
to let vb application to connect to oracle database remotely

thankx 4 all

Multiple Bulk Inserts

davrook — Sat, 27 Aug 2011 06:00:14 GMT

i have a need to perform multiple bulk inserts into 3 different tables within a transaction so that if any 1 of the 3 fails then ALL are rolled back.

i have read up on this article which implies it can be done http://msdn.microsoft.com/en-us/library/tchktcdk(v=VS.90).aspx

but it doesnt really show how do what i need to do...

anyone any experience here would be greatly appreciated....

Read Filemaker database

SDK — Mon, 13 Jun 2011 09:58:39 GMT

How to read filemaker database from windows OS using vb

Out of Present Range

vasanthsr_23 — Wed, 13 Apr 2005 10:32:57 GMT

Hi i got an error while inserting into database
4/11/2005 10:34:27 AM Err Num: -2147352566 Err Desc: Out of present range.
Actually i was able to insert many records but suddenly i got this message. What does this mean. Could anyone help me please.

Link More than 1 table to Datareport

Naas Si Ahmed — Tue, 17 May 2011 11:08:47 GMT

Can I link two tables and more for one datareport But I want it By ADO technologie
I'm waiting

createing 2 or more tables

daveofgv — Sun, 24 Apr 2011 00:14:07 GMT

I might just be one of the few that just can't get it.......

If someone can explain it to me in a 2nd grade level (step by step) I would be very appreciative.....

I have 2 tables:

table 1: first_name, last_name, ssn, city

table 2: charge, chargecity, chargestate, chargeamount (which these are for charges when someone is arrested)

I can create one table and everything is just fine in my windows form (visual studio 2008 pro)

However, when I create 2 tables (like above) I can not get the "charges" to associate with the last_name. I have to add another entry which would cause duplicate records for one person.

Example: when a person goes to jail they get a charge (DWI). When the person goes to jail again they get another charge but I can't seem to find how to add it to the person already in my database. If last_name is the primary key I don't understand how to add another charge and it show up under the persons name in my datagrid.

If I had one table everything would be fine, however, I would have to keep changing the charge instead of creating a "running total"

Am I not getting something here???

Hope this is simple to read

thanks

daveofv

how to add data into textboxes

cerberus478 — Sun, 17 Apr 2011 06:50:09 GMT

Hi

I'm using Access 2003 and vb.net 2005

I was able to link a combobox with my database so now I need to know how I can link the information on my combobox to a listbox and then to a textbox. What I'm trying to do is when I select an item from my combobox it has to appear in the listbox and then I click on the calculate buttonn and the price of the item appears in the textbox. Is there a way to do it?

Need Urgent Help - Text to Grid

gunapriyan — Tue, 15 Mar 2011 06:25:43 GMT

Dear Friends,

Once again i am coming with a new request.

Now i am doing one Estate Project using VB6 and MS-SQL.

Now i am facing one problem now.

I am enclosing herewith a screenshot for your kind perusal.

I need 2 things.

1. There are 4 Text boxes and 3 combo boxes...Some calculations is also there. After complete the 1st entry, if i press "Add+" Button, the values must go to Grid and text boxes must clear. Now 2nd Entry, Again i will press "Add+" button, the 2nd row values must go to Grid....Likewise, "n" no. of rows must go to grid.

2. Consider, i have enter 38 or 50 rows, Now i will press "Update" button, the grid values will store into appropriate fields in Database.

3. After successful saved, in future if need, i must edit a particular row in that grid. If i click a particular row in grid, the values will come to appropriate text boxes and combo boxes.

I need code help for the above needs. I am expecting your valueble reply soon.

Thanks & Regards,

Guna

Database management

G-Raha — Fri, 11 Mar 2011 04:54:23 GMT

How can i configure the refresh method of a database ,i have tried severally and still keep on getting runtime errors

Datagrid

Kulfi_Kulfi — Thu, 23 Dec 2010 13:24:22 GMT

when i select a row how can i know that this row is selected tell me by the coding.

thanks

VS 2008 dataset bound datagridview not showing the data inserted by other form

jazzyb — Tue, 14 Dec 2010 06:55:26 GMT

I am creating one application using visual basic.net and sqlce
I have two forms name Form1 and Form2.

I used Form1 to add data to database using OLeDb command
I used Form2 to show data in datagridview by using dataset

I used following code:

FFORM1 CODE is as following

Private Sub Add_data()

Try

Dim DBConn As OleDbConnection
Dim DBInsert As New OleDbCommand

DBConn = New OleDbConnection(Myconnection_String)
DBInsert.Connection = DBConn
DBInsert.Connection.Open()

DBInsert.CommandText = "INSERT INTO user(name, designation, age) VALUES (" + textbox1.text + "," + textbox2.text+ "," + textbox3.text + ")"

DBInsert.ExecuteNonQuery()

MsgBox("Data added successfully.", MsgBoxStyle.OkOnly)

DBConn.Close()
DBConn.Dispose()
Catch ex As System.Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub

This code insert the data succussfully

FORM 2 : In visual studio 2008 designer I added a dataset named 'mydataset' and tableadapter 'usertableadapter' and succesfully created Fill query. I added one datagridview to form2 and in design mode bind it to the mydataset and its binding source

ON FORM2 I CODE IS AS FOLLOWING.

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Me.userTableAdapter.Fill(Me.myDataSet.user)
Me.DataGridView1.Refresh()
End Sub

My Problem is :

Form2 Datagridview show the old data only, it do not show the recently added data by form1. I mean when I build my application datagridview show the data which is already in the database, but it does not show the data which is added by the form1 during run time.

I have tried mydataset.acceptchanges(), mydataset.getchanges() but all these not worked for me

Some one suggested me to fill the datagridview at run time by using OLeDB command. Then I created Form3 to fill the datagridview2 by using OLedb command and datareader and found that Form3 shows the recently(added by Form1 at run time) added data.

Use of Designer dataset is required for me , because I am using the similar dataset to create report.

How my Form2 dataset,datagridview can show the latest modified data from the database.

Thanks

Take data from database on to a label or function for calculation

girishnair12345 — Sun, 12 Dec 2010 03:09:23 GMT

Hi
I am working on my college project and i need some help

I have made a table and data in it
now i need to dynamically retrieve data from specific cell in that table which is used for calculation and output is showed on table

Please tell me using a blank for and with maximum comments as possible

pleeeeeeeeeeeese help anyone

Accessing Query created in MS Access using visual basic 6.0

raja_980 — Fri, 25 Jun 2010 02:28:19 GMT

I need a solution to access query created in MS Access using Visual basic 6.0.

Request you to please provide me the VB code for the same.

Many thanks in advance.

Regards,

Raja

raja_980@rediffmail.com

visual basic.net

tawfiiq — Thu, 04 Feb 2010 02:31:40 GMT

hi dear

i am new to vb.net , and i want to add date and time (DateTimePicker1) to my form
then when i save the record a wrong date is saved to the table , my database is sql 2005

Code: " & Format(DateTimePicker1.Value, "dd/mm/yy") & ") "

Result: 1/1/1900 12:00:00 AM

i much liked to help.............

How to use insert into with two set

smart sout — Sat, 06 Nov 2010 09:19:19 GMT

Hi I am new to Vb6 programing.

plz help me

cn.Execute "update ctm set id=(id+1)where id =" & Val(Text1.Text)

he is work but

i need use

cn.Execute "update ctm set id=(id+1)where id =" & Val(Text1.Text) and id=(id)where id < " & val(text1.text)"

how use the update in ...

update the ctm table

set

the id field id add one

if id = text1.text

and donot add one if the id < text1.text

plz help me

i wait the reply

bye

Search

Kulfi_Kulfi — Sat, 23 Oct 2010 01:58:34 GMT

i have a text.box and what ever i enter there i want search in the database i created.

can any one tell me how.

thanks

Activating Access 2007?

macroman — Mon, 20 Sep 2010 13:33:22 GMT

Hi,

I recently 'upgraded' to the Office 2007 suite and I was trying to update a VB6.0 db program to handle tables in Access2007. At one point the code sets an object variable:

Set mdb1 = GetObject(fileToOpen)

fileToOpen is the full path to an Access file, whether in the old .mdb or the new .accdb format. Anyway, when that statement is executed, Access opens. Access 2002 didn't open at all but I was able to get a list of tables, select one and access its data. If I leave Access open the program executes and when the program finishes, Access closes also.

My question is what do I need to do to keep Access from opening or becoming visible? Its not a big deal but it is kind of annoying.

database changing bindingsource at runtime

timark2000 — Sun, 19 Sep 2010 09:15:21 GMT

HAVE ASSOCIATED A MICROSOFT ACCESS DATABASE TO A FORM USING A DATASET/BINDINGSOURCE/DATAVIEWGRID/BINDINGNAVIGATOR

EACH TIME A DIFFERENT STUDENT APPEARS AFTER USING THE BINDINGNAVIGATOR IT IS NECESSARY FOR THE PROGRAM TO LIST CLASSTIME FOR THIS STUDENT IN A NEW DATAVIEWGRID. FOR THIS I HAVE ADDED A DATASET/BINDINGSOURCE/DATAVIEWGRID

I HAVE A SUB AS FOLLOWS:-

Private Sub RefreshClassTime()

Dim sQuery As String = ""

SQuery = "SELECT [Description],[Lesson], [Location], [StudentID], [CourseCode] From [ClassTime] Where ((([StudentID]) = " & Val(txtStudentID.Text) & ")) And (([CourseCode]) = " & Val(txtCourseCode.Text) & ") ORDER BY [Description];"

BindingSource_Classtime.DataSource = SQUERY

End Sub

Using this code is not working as just assigning the squery to the bindingsouce_classtime.datasource is NOT WORKING

Can anybody help me with this problem. I am new to vb.net

Thank you in anticipation

Problem Inserting values into Sql server

itsmystuff6 — Tue, 24 Aug 2010 04:16:19 GMT

Hi I am new to Vb6 programing.

I have two text boxes on the form. textX and TextY.the user will enter integer values in the text boxes.
I want to connect to sql server database "Temphome" and then insert values in table "coordinates".There are two int fields in the table X ,Y.

How can I do that.I can find the connection code on the internet but having problem finding a simple code which only connects
and insert these values.

thanks
ALI

Saving and retrieving image in Ms sql server 2008 using vb.net

almauly — Wed, 11 Aug 2010 16:07:14 GMT

hi guys
i need help on how to save and retrieve image in ms sql server 2008 using vb.net, i have tried to use FILESTREAM but it does not store the image

datagrid with dao

phi — Tue, 20 Apr 2010 12:58:54 GMT

i want to display the data in datagrid by using dao . how to write the code in vb6 .

Add a new record in Access

samuvk — Wed, 02 Jun 2010 11:47:28 GMT

I having problems adding new records:

I created a form, where I created all the objects with the option --> Remember the value for late use.

Then when I create a buttom to introduce all the values in the combobox, etc I selected the option --> Record Operations --> Add New Records

But I don't know what happend that when I press the button, the table don't accept the records and the information it is not recorded into the table.

Only if I am going to the table and create records, the form will modify the records already created, but the form never create new records.

Anyone please can help me on that.

Thank you very much

Crystal Report

zieqco — Fri, 14 May 2010 03:22:36 GMT

dear all,
ihave tried this code to open my crystal report, but this code dont work. I use ms Acces with password protected. this is my code I use :
With crBayar
.ReportFileName = App.Path & "\Report\BAYAR.rpt"
.Connect = App.Path & "\data\finance.mdb"
.DiscardSavedData = True
.RetrieveDataFiles
.DiscardSavedData = True
.ReplaceSelectionFormula ("{Tb_Bayar.Bayar} >=" & Val(txtTotalFrom) & " and {Tb_Bayar.Bayar} <=" & Val(txtTotalto) & "")
.Destination = crptToWindow
.PrintFileType = crptCrystal
.WindowState = crptMaximized
.WindowMaxButton = False
.WindowMinButton = False
.Action = 1
End With

please help me, asa soon as posible

recognize number plate from video

ayuz — Sun, 25 Apr 2010 04:05:32 GMT

I need a little advice on how best to read/recognize a number plate and for a program to recognise the digits of that number plate, which it will then run through a database. I have no problem with capturing an video from a live feed but i then need the program/coding/method how to pull out the number plate on the textbox and at the same time all the details of vehicle will appear on interface of the system in visual basic 6.0. I had used Halcon 7.0 to capturing a video and microsoft access as a database.

Database problem in a program I wrote and have used several years

atora — Tue, 13 Apr 2010 23:18:32 GMT

I wrote a program a few years back and have used it without any problems. The program stores everything entered to a Microsoft Database.

Yesterday when I tried to use the program it would not work and I was refered to a created Log stating "Cannot Load Control DB Grid; License not found.

How do I correct this problem?

Atora

odbc/sql server

marwa580 — Mon, 29 Mar 2010 21:09:23 GMT

hi
what's a ODBC ?
it is necessary to install it to do the connection to the database sql server?
(I use vb.net as a langage of programmation)

Insert Statment Error

zulli-mlt — Fri, 26 Mar 2010 04:45:56 GMT

[
Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Users .... ") 'This here, declares where the database is located

Dim dt As New DataTable()
Dim str As String
Dim com As OleDb.OleDbCommand

Dim user_name As String = txtusername.Text
Dim pass_word As String = txtpassword.Text
'Dim application As String = txtapplication.Text

'try statement
Try
'declaration stage, declare all database components and strings

con.Open()

str = "insert into password(pass,username) values('" & pass_word & "','" & user_name & "')"

lblInfo.Text = "saved"

com = New Data.OleDb.OleDbCommand(str, con)
'execute the query
com.ExecuteNonQuery()
'close the connection
con.Close()

'if there is an error during the process ...
Catch ex As Exception
'warn the user that username is already taken
lblInfo.Text = "Username already exits. Please Try Another Username."

End Try
End Sub
/]

hi all, above is my code which is giving me some problems. actually im trying to insert into a MS ACCESS database, but for some reason im getting an error; Syntax error in INSERT INTO statement.

the thing is, when i paste the SQL statement into an SQL query it executes without any problems.

any help please?

please note im a bigginer

thanks
mark

SAVING AND RETRIEVING IMAGES INTO ACCESS USING VB6

jessiekidfernando — Sat, 27 Feb 2010 00:14:21 GMT

HELP GUYS. I JUST WANT TO KNOW HOW TO SAVE IMAGES INTO ACCESS USING VB6. I'M 3RD YEAR COLLEGE NOW. AND WERE WORKING NOW WITH DSS.. THANK YOU. AND GODBLESS...

Lan base system

belat — Wed, 17 Feb 2010 00:17:46 GMT

i just want to ask if how to create lan based

system with mysql database and vb 6???

thanks..

how to create digital signature in forms

belat — Tue, 16 Feb 2010 07:57:22 GMT

hey...i just want to ask if

how to create digital signatures for form in

visual basic....

thanks

Database back up and restore

belat — Mon, 15 Feb 2010 00:22:28 GMT

good day! i just want to ask

if how to back up and restore mysql database using visual basic 6

thanks..