Visual Basic :: .NET Table Update?

Execute Update &"UPDATE TABLE SET Help
I got a problem updating some values. Next you have my code. Where PO is the table i want to modify, also in that table I have a field called PO. I think everything is ok. You i will leave it for the experts.
Thanks for all your help
Code:

Private Sub GuardarBtn_Click()

Dim base As Connection, rst As Recordset, ruta As String
Set base = New Connection
Set rst = New Recordset

ruta = App.Path & "AVSIng.mdb"

If Text1.Text <> "" Then
base.CursorLocation = adUseClient

base.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & ruta
rst.Open "SELECT * FROM po WHERE PO Like '%" & buscar & "%'", base

If rst.EOF Then
n = MsgBox("The po number does not exsit, do you want to add it?", vbYesNo, "No PO with that number")

If n = 6 Then
base.Execute "INSERT INTO PO (po, poclient, descrip, destino, lastinfo, fechali, notes) Values ('" & Text1.Text & "', '" & Text2.Text & "', '" & Text4.Text & "', '" & Text5.Text & "', '" & Text6.Text & "', '" & Text7.Text & "', '" & Text8.Text & "')"
End If
Else
base.Execute "Update po SET poclient='" & Text2.Text & "', descrip='" & Text4.Text & "', destino='" & Text5.Text & "', lastinfo='" & Text6.Text & "', fechali='" & Text7.Text & "', notes='" & Text8.Text & "' WHERE po='" & Text1.Text & "' ;"

End If
End If

End Sub

Update Record Set = Update Table In Database!!??
I have implementing a VB6 program with SQL database.
As mention in the subject, I have used rs.update to update a field in the record set. However, I found that it automatically update the field in the table where the record set created. In fact, I want the record set field update only. The table will be updated only when the user click a confirm button. How can I do that?

Update An SQL Server Table With Values From An MS ACCESS Table
Hi everyone in the forum. I would like to create a VB script which updates a table in an SQL Server with values from a table from my local MS ACCESS. It would be very helpful if anyone could answer how to do that.
Pleeeeeeeeeeeease !!!

PS. I 'm sorry if a thread like that already exists and i didn't see it.

Update Records In Table 1 With Existing Record In Other Table
hi,

can somebody help me on how I can resolve my problem..here's my code first thing of all...

VB Code:
Dim rsdelSet rsdel = New ADODB.RecordsetDim rsupdSet rsupd = New ADODB.Recordset rsdel.Open "Select * from tblTrackOrder Where RawID Like '" & lblRawID & "'", goConn, adOpenDynamic, adLockOptimistic, adCmdText If rsdel.RecordCount <> 0 Then While Not rsdel.EOF rsdel.Delete rsdel.MoveNext Wend End If rsdel.Close rsupd.Open "Select * from tblContacts Where ContactID Like '" & SupplierID.Caption & "'", goConn, adOpenDynamic, adLockOptimistic, adCmdText If rsupd.RecordCount <> 0 Then rsupd.Fields("CRequest") = "N" rsupd.Update rsupd.Close Else rsupd.Close End If

in my table tblTrackOrder, I deleted a record that has been processed...

let say the remaining records in my tblTrackOrder are:

ID NAME
1 Robee
3 Wendy

now, I have to equate these records to my other table called tblContacts...and in my table tblContacts my records are:

ID NAME CRequest
1 Robee Y
2 Dundee Y
3 Wendy Y
4 Plagie Y

in tblTrackOrder I only have 2 records..now I want these 2 records to be the look up so I can edit the records at tblContacts...

i want the output to be like this:
ID NAME CRequest
1 Robee Y
2 Dundee N
3 Wendy Y
4 Plagie N

when a record exist in the tblTrackOrder, it's corresponding record in the tblContact should have a Y value in CRequest field...but when a record is not existing in the tblTrackOrder it's CRequest field value should be N...I hope I've shown enough on how I want my system to work..im having difficult time thinking..i hope somebody could help me out..many thanks

Update Table A Based On Criteria From Table B
Database: SQL Server 2005

I am trying to write a SQL Server stored procedure which will perform a bulk update on a table. I need to update records in the Game table based on the level of the teams playing in the game.

Game Table
GameID (PK)
HomeID (Related to Team table)
AwayID (Related to Team table)
GameApproved (1 or 0)

Team Table
TeamID (PK)
TeamName (varchar)
LevelID (Related to Level table)

Level Table
LevelID (PK)
LevelName (varchar)

A typical select statement to get the records I want to update would look like this:
Code:
SELECT
     g.GameID,
     g.HomeID,
     ht.TeamName Home,
     g.AwayID,
     at.TeamName Away,
     g.GameApproved
FROM
     Games g
          LEFT JOIN Team ht
                 ON g.HomeID = ht.TeamID
          LEFT JOIN Team at
                 ON g.AwayID = at.TeamID
WHERE
     ht.LevelID = @level or at.LevelID = @level

What I want to do is write an update statement that permits me to change the GameApproved field for all games that meet the criteria set in the select statement above.

Thanks in advance for any help.

Update Table With SQL
I need some assistance. Hope that you guy can help.
I am currently using Access as Database and VB for my interface.

I need to update the last record from one table A to another table B. It is necessary to have SQL command? If so, where do i store the SQL command? To my understand that I need to create query but how? Under query do i need to add Table A and B together or only one Table?

If i have added any table on the query but Query has not provide sufficient fields. I have 30 fields from table A to update to Table B.

Table A and Table B have same fields. It is just that i want to store the last or new record from Table A to B.

I have another Table C too and It need to update to the B as well.

I have attach a .doc file. Hope that you guy under the flow

I would appreciate if anyone can show me the SQL command as well. I am keen to learn.

Thank in advance

Cannot Update Table, Need A Help Here...
can't update, database or object is read only, im using microsoft dao3.51 as my reference, i open my db like this:

' connect thru the odbc
Set db = OpenDatabase("tables", dbDriverComplete, False, "ODBC")

is something wrong in my coding??? the read only is set to false, and when i set my recordset to it and try to add new property for my recordset it gives me an error... can someone help me, im trying to fix this...

Update Table
I have 2 tables A and B

A and B have columns trns_code and trns_grp

How to update B's trns_grp with reference from A, by matching A and B's trns_code?

Thanks!

Cannot Update A Table
This is absolutely driving me insane so any help would be appreciated. I'm trying to add new records to a table, this was the originial SQL statement:

adoDonorConnect.BeginTrans
adoDonorConnect.Execute ("INSERT INTO notepad_file (ID, SortDate, User, DisplayDate, Code, Note) VALUES('" & frmDonor.txtID.Text & "','','" & frmDonor.cboUser.Text & "','" & frmDonor.DTNoteDate.Value & "','" & frmDonor.cboCode.Text & "','" & frmDonor.txtNote.Text & "')")
adoDonorConnect.CommitTrans

It didn't error out or anything, it just didn't update the database so I decided to change the statement and just hardcode some values in for the variables and it again ran through fine but the database didn't update.

I finally thought that maybe the table was corrupt or something so I created a new table with the same fields but with no data in it and ran the code, everything looked fine but it still didn't update.

I next decided to just start with a table with one field, hardcode the value, and run the code like this and it worked:

adoDonorConnect.Execute ("INSERT INTO notepad_file (ID) VALUES ('99999999'))

So then I added a second field to the table and hardcoded two values and it worked just fine again:

adoDonorConnect.Execute ("INSERT INTO notepad_file (ID, SortDate) VALUES ('99999999','8/9/2004'))

I then added a third field and hardcoded a third value but this time it ran through the code just fine but didn't update the database:

adoDonorConnect.Execute ("INSERT INTO notepad_file (ID, SortDate, User) VALUES ('99999999','8/9/2004','JULIE'))

What's strange is that I'm using the above method to add new records to another table using the same connection and it works great. I've checked and rechecked the field names and sizes to be sure they can handle the data too.

I finally just changed it to add it through the .AddNew and .Update methods and that works but I'd rather update the tables the same way throughout the code if possible. Any help would be appreciated.

Thanks

Update Table?
Hi, I have this program in vb6 with access2k as backend. It was originally conceived for only 3 or 4 users, but it's turning out to be used by way more people. It uses userIDs to filter data in the database. Now, the database is stored on a server and everybody connects to it from their local computer. I was wondering: is it possible to have the users actually work on a separate database on their local computer and then only update the database on the server? It might be a stupid question, but I'd like to find out. Also if it is possible, how could that be done? I did some research and found an UPDATE function of SQL, but that would delete all the records and replace them with new ones, therefore deleting also other users records.

Thanks
Giana

Update A Table?
Try to update a table use ADO code and it give me error saying " it is read only" can't update

here is the string that I'm using

rs.Open strSQL, cnn1, adOpenKeyset, adLockOptimistic, adCmdText

It reading the file OK, but just want to update the fields while read the record.

Thanks!

Update The Table
sir, i have a problem i want to update the table in msflexgrid but i dont know how to read all the data in msflexgrid and update. thanks

Update A Table
i have open a foxpro table through vb by
set rsabc = dbabc.openrecordset ("abc",dbopentable)

now i what to update the foxpro table
i use
.edit
sososososos
dododododod
dododododso
.update

but it give me a error

please tell me how to update /edit a foxpro table from VB

Update Table Using ADO
I would like to update main table with the value in a temp table. How would I do this by using ADO. Is it safe to just give sql update statement and open this statement as recordset.
(Please don't look at the syntax of the sql string) I just want to know if this is a good approach.
for example
strSql="update maintable
set maintable.chkdate=temptable.chkdate,
maintable.chkno=temptable.chkno,etc etc..
where maintable.id = temptable.id";
rsUpdate.open, strsql cn

Or is there a better way to do this..

How To Update Table
hai everyone,
i have a form whose values i have to store in 2 tables like master table and detail table.
for master there is one record and i am updating through sp.

for details table i have a grid in form which consists of items for particular order each record has 4 fields

how should i update these records using sp
like should i traverse loop send record by record to sp in that case 4 params are required
or should i pass as one string with comma seperators of all record.
which is the best way and please give snippet if avl.

thanks in advance

Vb6 How To Update Fox Pro Table
I have a DED connection with fox pro table. When I del a record by vb. This
record is being selected but not deleted in the table. How can I update the table
I need a command like pack in fox pro to delete the record. (Update) is not working.
Likewise, when I add a record the table is not being update until i close the program.

Any help pl.

Thank you in advance

Table Won't Update
Im using a dataadapter and dataset in an Employee Class to perform updates. The code runs a select stored procedure to select the employee, and an update SP to update the information. I can run the update SP from server explorer and it will update the database table successfully, but not from the program.

here is the code to send the changes to the DB:
'oEmployee is an instance of Employee
'employeeDA is the DataAdapter
'SELECTED is the Dataset

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Update the Employee in the Database

        Try
            oEmployee.EmployeeDA.UpdateCommand.Parameters.Add("@EmployeeID", oEmployee.EmployeeID)
            oEmployee.EmployeeDA.UpdateCommand.Parameters.Add("@ParkingID", oEmployee.ParkingID)
            oEmployee.EmployeeDA.UpdateCommand.Parameters.Add("@PermitNumber", oEmployee.PermitNumber)
            oEmployee.EmployeeDA.UpdateCommand.Parameters.Add("@PermitReturn", oEmployee.PermitReturn)
            oEmployee.EmployeeDA.UpdateCommand.Parameters.Add("@BadgeID", oEmployee.BadgeID)
            oEmployee.EmployeeDA.UpdateCommand.Parameters.Add("@BadgeReturn", oEmployee.BadgeReturn)
           oEmployee.EmployeeDA.UpdateCommand.Parameters.Add("@Credential", oEmployee.Credential)

        oEmployee.EmployeeDA.Update(oEmployee.SELECTED)

        Catch eInsertException As Exception
            MsgBox(eInsertException.ToString, MsgBoxStyle.OKOnly)
            Throw eInsertException
        Finally
            Splatgirl.Close()
        End Try
        SelectedEmployee.Dispose()
        SelectedEmployee = Nothing
        Splatgirl.Close()
        MsgBox("Employee updated", MsgBoxStyle.OKOnly, "Update Complete")
        Me.Refresh()
        Me.Close()

    End Sub

Here is a listing of the update SP:

CREATE PROCEDURE [dbo].[UpdateEmployeeCS]

(
    @EmployeeID uniqueidentifier,
    @ParkingID char(10),
    @PermitNumber char(10),
    @PermitReturn char(10),
    @BadgeID char(10),
    @BadgeReturn char(10),
    @Credential char(10)

)

AS


UPDATE Employees SET

     [Permit Number] = @PermitNumber,
     [Parking ID] = @ParkingID,
     [Permit Return] = @PermitReturn,
     [Badge ID] = @BadgeID,
     [Badge Return] = @BadgeReturn,
     Credential = @Credential

WHERE (EmployeeID = @EmployeeID)
GO

This is the function that sets up the DataAdapter

Public Function GetData(ByVal empLname As String, ByVal empFname As String) As String
        Me.SPLATGIRL = New SqlConnection("workstation id=D5WQ1K81;packet size=4096;integrated security=SSPI;data source=SPLATGIRL;persist security info=False;initial catalog=MWPH Employee Processing")

        Me.EmployeeDA = New SqlDataAdapter("SelectedEmployee", SPLATGIRL)
        Dim dr As DataRow
        Dim DAUpdateCmd As New SqlCommand
        With DAUpdateCmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "UpdateEmployeeCS"
            .Parameters.Add("@EmployeeID", EmployeeID)

            .Connection = SPLATGIRL
        End With
        EmployeeDA.UpdateCommand = DAUpdateCmd
        With EmployeeDA
            .TableMappings.Add("Employees", "SELECTED")
            .MissingSchemaAction = MissingSchemaAction.AddWithKey
            .SelectCommand.CommandType = CommandType.StoredProcedure
            .SelectCommand.Parameters.Add("@LastName", empLname)
            .SelectCommand.Parameters.Add("@FirstName", empFname)
            .FillSchema(SELECTED, SchemaType.Source)
            .Fill(SELECTED)
        End With


    End Function

SO if anyone can help me figure out why this will not update the table , I would really appreciate it.

Im using VB.net 2003 and SQL Server 2000

Update Another Table
Dear Friends

I am new to VB and just learn the VB6.

Now i am working with ADO with SQL server.

I need the following to be done on click of button.

I have table Cutomer from nothwind database. I have
created the exact structure of same table as Custcopy.

I have a form Customer where all the data of customer is
displayed record by record.

I have place one command button on the form which should
create a copy of the current record in the table of
Custcopy.

Please help me with the code i will be thankful to you
for the same as it will prove me in my job.

I need your guidence..

Best regards
NiitMalad

Update Table
i want to insert a record in a table but with a zero value in all fields except for an ID.

the table consist of :

ID, Sep, Oct, Nov, Dec, Total, Paid, Balance where ID is a number, and the rest of the fields are currency.

so i want to add the following record for example:

15, 0,0,0,0,0,0,0

Dim temp As Integer
temp = 0

cnSchool.Execute "INSERT INTO tblTuition (StudentID, Sep, Oct, Nov, Dec, Total, Paid, Balance) VALUES ( " & txtStudentID & ", " & temp & ", " & temp & ", " & temp & " , " & temp & " , " & temp & ", " & temp & " , " & temp & ")"

i am getting A SENTAX ERROR.
any hint.

Thank you,

How Do I Update DB Table
Hi gurus!

I have a rudiment question.

There's DB table in which id, name, birth day fields exist.
I inserted wrong birth date,for example (1966=>1965, 1971=>1970....less 1 than actual date)
In this case, how I update all date which larger 1 than input data?
In the code listed below, error occurs in rs("exam_date").Value = dtExamDate
How can I solve this?

cn.Open DBENVIRONMENT & DBPATH
Set cmd.ActiveConnection = cn
rs.Source = "xray_patient_exam_history"
rs.Open "xray_patient_exam_history", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
cmd.CommandText = "SELECT exam_date, save_datetime, edit_datetime FROM xray_patient_exam_history"
Set rs = cmd.Execute
If rs.EOF Or rs.BOF Then
Set cmd = Nothing
Set rs = Nothing
cn.Close
Exit Sub
End If

rs.MoveFirst
nIndex = 0
Do Until rs.EOF
dtExamDate = rs("exam_date").Value
dtSaveDate = rs("save_datetime").Value
dtEditDate = rs("edit_datetime").Value
MyYear = Year(dtExamDate)
If MyYear = 2002 Then
dtExamDate = DateAdd("yyyy", 1, dtExamDate)
rs("exam_date").Value = dtExamDate ===> error occurs
End If
MyYear = Year(dtSaveDate)
If MyYear = 2002 Then
dtSaveDate = DateAdd("yyyy", 1, dtSaveDate)
rs("save_datetime").Value = dtSaveDate
End If
MyYear = Year(dtEditDate)
If MyYear = 2002 Then
dtEditDate = DateAdd("yyyy", 1, dtEditDate)
rs("edit_datetime").Value = dtEditDate
End If

rs.Update
rs.MoveNext
nIndex = nIndex + 1
Loop

[B][I]

UPDATE Help Needed , Can You Update A Field With A Field From Another Table ?
I want to update a field in table1 with a field from table2 both the fields are called age. Can some one show me how to do this I can't get it to work.

Thx, Locutus

Howto Update A Table With VBA
Ok i have recently learned howto execute a select statement and drop it in a string.

Code:
Dim lgnPassSQL as string
Dim rs as Object

lgnPassSQL = "SELECT * FROM tbl_UserRelated WHERE LastName = " & Chr(34) & Me.LgnUser.Value _
& Chr(34) & " And Password = " & Chr(34) & Me.OldLgnPass.Value & Chr(34) & ";"

Set rs = CurrentDb.OpenRecordset(lgnPassSQL)

But what if i want to update one field with VBA? or more than one..?

Code:
Dim NewPassSQL as string

NewPassSQL = "UPDATE tbl_UserRelated SET Password = " & Chr(34) & Me.NewLgnPass.Value _
& Chr(34) & " WHERE LastName = " & Chr(34) & Me.LgnUser.Value & Chr(34) & ";"

DoCmd.RunSQL (NewPassSQL) 'Here is where my problem starts.. maybe this approach is wrong.

I would love to know how i can update a table as you can see i'm just fooling around here, and i really don't have a cleu how i should do this from within VBA..

Use Variable To Update A Value In A Table...
I am trying to update the value of a field in a table based on the match of a value from a variable, with the matching value in the row of the table. The code is below.

If MachineNames = [Machine Name Listing]![Machine Name] Then
[Machine Name Listing]![used] = False
End If

I get error saying, can't find field named "Machine name listing". This is the name of the table, not the field. The field to be updated is called "used. How can I correct.

Many thanks in advance.

How Do I Get The Date Of The Last Table Update ?
I need to find out how to get the date a record was last added to a specific table. Possible even the last record added to a table relating to the Primary Key in another table if it's possible.

You see, I am designing a back to work interview system and could really use the facility to view the last time a sickness record was added for a specific employee.

Thanks in advance,

JabberWocky

Update Table In Access DB
Hi,

I was wondering if there was any way of using VB to update a table in a MS Access DB with values taken from Excel without having to open up the database on screen.

Could all this code be run in the background without the user knowing?

Thanks,
Liam

Can't Update Table After Edit
I've two queries:

Code:
data1.RecordSource = ( "Select * from Table1")
data1.Refresh

AND

Code:
data1.RecordSource = ("Select * from Table1, Table2 where Table1.CustName = 'SuperKMart' and Table2.ProdName = 'VCR')
data1.Refresh

There is text boxes that will show fields' info. from "data1" table (OrdNo ProdName Price Quantity) and there is a specific text box show value change in Quantity after add/delete. The problem is that if I use first code, there are no error, but if I use second code it gives error:

Run-time error '3027'
Can not update. Database or Object is read only

Actually I'd like to use second code since it'll give right query product's info. Anyone can help me this????

Thanks

Update Table After Edit
I've a text box that shows quantity of product. This text also show changing value of quantity after product is orderred.
My Products table has ProdNo ProdName Price Quantity

Basically, I have part of code as follow:

THIS CODE IS FOR CALCULATE QUANTITY

Code:
Private Sub Calculate_Click()
Dim a
a = InputBox("Input value: ", "Quantity","")
If a = "" or Val(a) = "0" Then
Msgbox "Input value can not be 0",vbOKOnly
Exit Sub
End If
If a > Val(Text1.Text) Then
Msgbox "Input value can not exceed current quantity", vbOKOnly
Calculate.SetFocus
End If
Products.Recordset.Edit
Text1.Text = Val(Text1.Text) - a
Products.Recordset.Update
End Sub

THIS CODE IS FOR SHOWING TEXT1 BOX CHANGING VALUE

Code:
sql = "Select * from Products"
adoProd.Open sql, adOpenStatic, adLockOptimistic

If not adoProd.EOF and not adoProd.BOF Then
Text1.Text = adoProd.Fields("Quantity")
End If

The Text1 box actually show quantity value being substracted from initial value. But when I go back Products table, it doesn't. It still shows initial quantity value

Any missing in my code

Thanks for help

Table Relationship - Update
I am now trying to Update two tables while creating an new entry in only one. I was under the impression that creating a relationship with Data Integrety on ould do that sort of thing, but it doesn't seem to work...

Table one as a Field named "Project Number" with all the details about the project. Table 2 as a field called "Project" which is the same number as "Project Number" so when the user creates a new project number in Table one, I would like Table 2 to reflect the new addition.

I tried with a relationship, but it doesnt update nor delete?

Anything special that I should do, or not do?

Multiple Table Update
hi

i have a problem when updating data via adodb.recordset when this recordset is link to 3 table

When update, "Cannot insert or update columns from multiple table" is display.

However, i go to the Enterprise manager there and create a view exactly the same as my recordset and update there, then no problem

Please help

SQL SERVER 2k: Update One Table From Another...
Hi all,

The query I have posted is blowing my mind. It keeps throwing:

Code:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix '#itoftemp' does not match with a table name or alias name used in the query.
I'm trying to update a value in the main table (itof_operator) with a value from a temp table (#itoftemp) where the operator_ID of both tables matches. For some reason, I can't seem to get this to work. Anyone have any ideas?

Thanks!

Code:
update itof_operator
set operator_state =
(select cost_center_number
from #itoftemp a, itof_operator b
where b.operator_ID = a.operator_id)
where itof_operator.operator_id = #itoftemp.operator_id

Update Table Only On Webserver
Hi..

Ive had a look at this forum today and only seem to be getting 3 pages in this section now? I was hoping to get some advice on updating a table in a database on a webserver from my app on my PC.

Ive just compiled a large database and if its correct its about 20 meg.. too big to upload and download all the time if im making changes eh?

At themoment I download the complete database in my app and copy the table over then upload the database again and this is ok..

But can i just somehow send the table from my apps database to the webservers database on its ownas a single table somehow?

Once again sorry if this has been asked but I dont seem to be seeing all the forums properly just now?

Im using VB6, Access Database on a windows server.

Thanks again
L

MySQL: Update A Table
Is it possible to update a whole table with only one query? I need to do some addition and multiplication with fixed values in a column.
I am using .movenext but want to speed all up a bit.

I also wonder how a temporary table, created by code works in a multiuser enviroment will be. I need to know if the users gets a own table to work with or if they will work with the same data (affect eachothers table).

/Anders

Not Able To Update The Table Using ADO - Very Urgent
Friends,

I am not able to update the table using ADO.
I tried many times but no use.
i hope somebody can help in this regarding

thanks in advance
sathyguy

Code:
If cmdsave.Caption = "Update" Then

If cmbprojcode.Text <> "" And cmbempno.Text <> "" And Val(txtgtotal.Text) <> 0 Then
cn.BeginTrans

cn.Execute "Update ginmast set gindate=#" & dpgindt.Value & "#, " _
& "projcode='" & cmbprojcode.Text & "',recd_empno='" & cmbempno.Text & "', " _
& "remarks='" & txtremarks.Text & "',tot_val=" & txtgtotal.Text & ", " _
& "user_id='" & LoginName & "',sys_date=#" & lbldate.Caption & "# where " _
& "ginno = " & txtginno.Text & ""

If Me.grid.TextMatrix(1, 1) <> "" And Me.grid.TextMatrix(1, 2) <> "" Then

Dim rschk1 As ADODB.Recordset
Set rschk1 = New ADODB.Recordset
rschk1.Open "select * from gindtl where ginno = " & txtginno.Text & " order by itemcode", cn, adOpenDynamic

'For i = 1 To Me.grid.Rows - 1 And (Not rschk1.EOF)
For i = grid.FixedRows To Me.grid.FixedRows - 1 And (Not rschk1.EOF)
If Me.grid.TextMatrix(i, 1) = "" Or Me.grid.TextMatrix(i, 2) = "" Then
MsgBox "Empty Record is not Allowed." & vbCr & "Cannot Save the Record" _
& vbCr & "Click the SNo and Press F12 to delete the empty row", vbOKOnly, "Stock Maintenance System"
GoTo 20
Else

ssql = "select * from gindtl"
With rsupdate
.Open ssql, cn, adOpenDynamic, adLockOptimistic, adCmdText
.Fields("itemcode") = Me.grid.TextMatrix(i, 1)
b1 = Me.grid.TextMatrix(i, 1)
.Fields("Del_Qty") = Trim(Me.grid.TextMatrix(i, 5))
a1 = Trim(Me.grid.TextMatrix(i, 5))

If rschk1itemcode <> Me.grid.TextMatrix(i, 1) Then
'cn.Execute ("update itemmast set stock=stock + " & Val(rschk1delqty) & " where itemcode='" & rschk1itemcode & "'")
cn.Execute ("update itemmast set stock=stock - " & Val(a1) & " where itemcode='" & Me.grid.TextMatrix(i, 1) & "'")
End If

.Fields("price") = Trim(Me.grid.TextMatrix(i, 7))
.Fields("total") = Trim(Me.grid.TextMatrix(i, 8)) .Fields("user_id") = LoginName
.Fields("sys_date") = lbldate.Caption
.Update
End With

rschk1.MoveNext
Next

MsgBox "Record Saved Successfully", vbOKOnly, "Stock Maintenance System"
cn.CommitTrans
cmdsave.Enabled = False
ControlDisable
Else
20 cn.RollbackTrans
End If
Else
If cmbprojcode.Text = "" Then
MsgBox "Select the Project Code", vbOKOnly, "Stock Maintenance System"
cmbprojcode.SetFocus
ElseIf cmbempno.Text = "" Then
MsgBox "Enter the Employee Number", vbOKOnly, "Stock Maintenance System"
cmbempno.SetFocus
ElseIf txtgtotal.Text = "" Then
MsgBox "No items to save", vbOKOnly, "Stock Maintenance System"
ElseIf txtgtotal.Text = 0 Then
MsgBox "Total should not be zero", vbOKOnly, "Stock Maintenance System"
End If

End If

End If

UPDATE Table With SQL Statement In VB
Set gcnnData = New ADODB.Connection
Set grstData = New ADODB.Recordset

intValue = 17
strValue = "LH"
gvntSelect = "UPDATE tblOrders SET [fTypeO] = " & intValue & " WHERE [fUser] = " & strValue & ";"
grstData.Open (gvntSelect), gcnnData, adOpenDynamic, adLockOptimistic
grstData.Update
grstData.Close

I am trying to update a table: tblOrders
using criteria that finds any fUser fields
that has the initials: LH
then update the fTypeO field to the number: 17

I keep getting a Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters
and it highlights the following:

grstData.Open (gvtnSelect), gcnnData, adOpenDynamic, adLockOptimistic

I took the above UPDATE select statement directly
out of Microsoft Access' SQL statement and not sure
what modifications I need for it to work.

Please help.

Thanks,
Stephen

Cannot Update Value In Table With A Null Value But Want To :( Help!
I am running a command Update button which uses a SQL update to update the values in a table.

The Update takes the values from what the user enters in textboxes and Updates the table with the appropriate values.

However, one of the fields I want it to be able to update with a null value but it will not allow me to.

The reason I am guessing is because this value is a foreign key... we shall call it txtForeignID

In the database I have set txtForeignID to allow Zero Length strings, but that has not made any difference.

It still demands that I enter a (valid) related value into the textbox so it can store it, it will not allow me to enter nothing.

Is there any way I can store nothing (not the word null, but nothing, no spaces, nadda, zip) in the textbox with removing my relationships between the tables ?

Thanks

Update Table In Other Database
Hello,
I´m trying to figure out what is wrong with this code:
====================
Set dbsBmLokaal = DBEngine.Workspaces(0).OpenDatabase("C:db2.mdb")
strSqlBm = "Update Tabel1 IN 'C:db1.mdb' "
strSqlBm = strSqlBm & " Set anaam = Tabel1.anaam "
strSqlBm = strSqlBm & " Where vnaam = Tabel1.vnaam "
dbsBmLokaal.Execute (strSqlBm)

====================
I have the code in a form in db1.mdb. I want to update Tabel1 from db1.mdb with the data of Tabel1 (yes, same name, same column names) from db2.mdb.

I suppose it has something to do with the where clause. What about the syntax?
I simply does not run, i.e. no error, no data update.

Any suggestions??

Regards,

Johan

Update Complete Col In Table
Hi.

How can I update a complete column in a table ?
I have a table with 2 columns: col_A and col_B and I want to add '1' to every cell of col_A.
Something like:
update tblName set col_A = ((select col_A from tblName)+1) ??

The +1 is less important the operation on all the cell is the issue.

thanks

Insert In One Table And Update In Another
Hi!

I have an Access Database with 3 tables named as tblStock, tblStockAdd and tblStockOut. The following are their fields:

for tblStock:
ProductID(PK)-------Text
ProductName----Text
PurchasePrice--- Currency
Quantity-------- Number
Unit-------------Text
EntryDate-------Date/Time
ReorderPoint-----Number

for tblStockAdd:
ProductID------Text
QuantityAdd----Number
DateAdd-------Date/Time

Note:here is link to ProductID in tblStock

for tblStockOut:
ProductID------Text
QuantityOut----Number
DateTimeOut---Date/Time

I want to keep track of the number of items added to a certain product in tblStock so i made tblStockAdd for that purpose. Below is my code to do it

Code:
sSQL = "INSERT INTO tblStockAdd(ProductID, QuantityAdd,DateAdd)" & _
"VALUES( '" & (txtProdID.Text) & "' , " & _
CLng(txtProdQty.Text) & ",'" & _
txtProdEntryDate.Text & "')"
oConn.Execute sSQL

I want at the same time to update tblStock. I know i am going to use UPDATE but i am getting an error using the code below.

Code:
sSQL = "UPDATE tblStock INNER JOIN tblStockAdd ON tblStock.ProductID = tblStockAdd.ProductID " & _
"SET tblStock.Quantity = [Quantity]+ " & txtProdQty.Text & ", " & _
"WHERE tblstock.ProductID = '" & rsStock.Fields("ProductID") & "'"
oConn.Execute sSQL

The erro message: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Am I doing it right or are there any better if not best way to do this? Any help is much appreciated and thanks in advance.

Update The MS Access Table ?
Hi,

If I want to add a new field to one of the table in the MDB file, what is the best way to do it in vb code? since I can't replace the existing table, it already have data in it and deployed to many user PC.

what is the best way to do it? how about add a new table to the MDB?

Thanks!

SQL Update Table Field
I got Table1 with F1 F2 F3 as the three columns. How do i write an sql statement to say update Table1.F3 with a sequence of n+2 values.

So that my F3 would have say 1, 3, 5, 7...n for each record.

My table 1 woudl look like this after the update.

F1 F2 F3
a b 1
c d 3
e x 5
f c 7

Not Able To Update Table Using ADO - Very Urgent
Friends,

I am not able to update the table. using ADO.

VB Code:
If cmdsave.Caption = "Update" Then If cmbprojcode.Text <> "" And cmbempno.Text <> "" And Val(txtgtotal.Text) <> 0 Then cn.BeginTrans cn.Execute "Update ginmast set gindate=#" & dpgindt.Value & "#, " _ & "projcode='" & cmbprojcode.Text & "',recd_empno='" & cmbempno.Text & "', " _ & "remarks='" & txtremarks.Text & "',tot_val=" & txtgtotal.Text & ", " _ & "user_id='" & LoginName & "',sys_date=#" & lbldate.Caption & "# where " _ & "ginno = " & txtginno.Text & "" If Me.grid.TextMatrix(1, 1) <> "" And Me.grid.TextMatrix(1, 2) <> "" Then Dim rschk1 As ADODB.Recordset Set rschk1 = New ADODB.Recordset rschk1.Open "select * from gindtl where ginno = " & txtginno.Text & " order by itemcode", cn, adOpenDynamic 'For i = 1 To Me.grid.Rows - 1 And (Not rschk1.EOF) For i = grid.FixedRows To Me.grid.FixedRows - 1 And (Not rschk1.EOF) If Me.grid.TextMatrix(i, 1) = "" Or Me.grid.TextMatrix(i, 2) = "" Then MsgBox "Empty Record is not Allowed." & vbCr & "Cannot Save the Record" _ & vbCr & "Click the SNo and Press F12 to delete the empty row", vbOKOnly, "Stock Maintenance System" GoTo 20 Else ssql = "select * from gindtl" With rsupdate .Open ssql, cn, adOpenDynamic, adLockOptimistic, adCmdText .Fields("itemcode") = Me.grid.TextMatrix(i, 1) b1 = Me.grid.TextMatrix(i, 1) .Fields("Del_Qty") = Trim(Me.grid.TextMatrix(i, 5)) a1 = Trim(Me.grid.TextMatrix(i, 5)) If rschk1itemcode <> Me.grid.TextMatrix(i, 1) Then 'cn.Execute ("update itemmast set stock=stock + " & Val(rschk1delqty) & " where itemcode='" & rschk1itemcode & "'") cn.Execute ("update itemmast set stock=stock - " & Val(a1) & " where itemcode='" & Me.grid.TextMatrix(i, 1) & "'") End If .Fields("price") = Trim(Me.grid.TextMatrix(i, 7)) .Fields("total") = Trim(Me.grid.TextMatrix(i, 8)) .Fields("user_id") = LoginName .Fields("sys_date") = lbldate.Caption .Update End With rschk1.MoveNext Next MsgBox "Record Saved Successfully", vbOKOnly, "Stock Maintenance System" cn.CommitTrans cmdsave.Enabled = False ControlDisable Else 20 cn.RollbackTrans End If Else If cmbprojcode.Text = "" Then MsgBox "Select the Project Code", vbOKOnly, "Stock Maintenance System" cmbprojcode.SetFocus ElseIf cmbempno.Text = "" Then MsgBox "Enter the Employee Number", vbOKOnly, "Stock Maintenance System" cmbempno.SetFocus ElseIf txtgtotal.Text = "" Then MsgBox "No items to save", vbOKOnly, "Stock Maintenance System" ElseIf txtgtotal.Text = 0 Then MsgBox "Total should not be zero", vbOKOnly, "Stock Maintenance System" End If End If End If