Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Need A Little Help Creating My Update Command Programmatically


Can anyone please help with creating this Update command ? When it comes to Parameters I always seem to have a problem.

I have a Header Table an a Detail Table, I need to Update Column 3 in each detail record with the contents of Column 3 in its respective Header record.

Here is my routine so far,

Code:
Private Sub Update_Header_Details()

Dim Det_Count As Integer = 0
Dim Head_Count As Integer = 0
Dim i As Integer = 0
Dim j As Integer = 0
Dim Head_DS As DataSet = New DataSet("tbl_Accpac_InvHeader")Dim Head_DA As SqlDataAdapter = New SqlDataAdapter("Select * from tbl_Accpac_InvHeader",Del_Conn)

Head_Count = Head_DA.Fill(Head_DS, "tbl_Accpac_InvHeader")

Dim Detail_UDComm As SqlCommand = New SqlCommand()
Dim Det_Conn As SqlConnection = New SqlConnection

Det_Conn.ConnectionString = My.Settings.VIH_Conn
Detail_UDComm.Connection = Det_Conn

     For i = 0 To Head_Count - 1

           Dim Detail_DS As DataSet = New DataSet("tbl_Accpac_InvDetails")
           Dim selCMD As SqlCommand = New SqlCommand

           selCMD.CommandText = "Select * from tbl_Accpac_InvDetails Where Field8 = " + "'" + Head_DS.Tables(0).Rows(i).Item(4) + "'"

           Dim Detail_DA As SqlDataAdapter = New SqlDataAdapter(selCMD.CommandText, Del_Conn)
           Dim Det_UDCMD As SqlCommand = New SqlCommand()
           
        Det_UDCMD.CommandType = CommandType.Text
           Detail_UDComm.CommandText = "Update tbl_Accpac_InvDetails SET Field2 WHERE Field2 = @Field2 "
           Detail_UDComm.Parameters.Add("@Field2", SqlDbType.Char, 255, "Field2").Value = Head_DS.Tables(0).Rows(i).Item(2)
           Detail_DA.UpdateCommand = Detail_UDComm

           Det_Count = Detail_DA.Fill(Detail_DS, "tbl_Accpac_InvDetails")

           For j = 0 To Det_Count - 1
                   Detail_DS.Tables(0).Rows(j).Item(2) = Head_DS.Tables(0).Rows(i).Item(2)
           Next

           Detail_DA.Update(Detail_DS, "tbl_Accpac_InvDetails")
           Detail_UDComm.Parameters.Clear()
           Detail_DA.Dispose()
           Detail_DS.Dispose()
           selCMD.Dispose()
           Det_UDCMD.Dispose()

     Next

End Sub

Thanks in advance

tattoo




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Programmatically Update A Query In Access
G'morning All,

I have a VB application with an Access DB.  The DB has some save queries, one of which is called vEntryList and looks like this:

CODESELECT Registration.EventID, Racer.RacerID, Racer.amanum, Racer.TempNumber, Racer.fname, Racer.lname, Registration.Comp, Racer.addr, Racer.city, Racer.state, Racer.zip, Brands.BrandName, Classes.Description, Classes.SortOrder, Classes.EngSize, Classes.CYear, Registration.BikeSize, Registration.status, Registration.ClassID, sponsors.Sponsors
FROM Brands RIGHT JOIN (Classes INNER JOIN (Racer INNER JOIN (Registration LEFT JOIN sponsors ON (Registration.RacerID = sponsors.RacerId) AND (Registration.ClassID = sponsors.ClassId) AND (Registration.EventID = sponsors.EventId)) ON Racer.RacerID = Registration.RacerID) ON Classes.ClassId = Registration.ClassID) ON Brands.id = Registration.BrandID
ORDER BY Classes.SortOrder, Racer.lname, Racer.fname;

Creating An Dsn Through Vb Programmatically
Respected sirs,
I want to create a key {to create an DSN for ODBC) in the windows 98 registry using VB6.0 programmatically.

After getting tips from this site I wrote the code as follows

Private Sub Command1_Click()
SaveSetting "HKEY_CURRENT_USERSoftwareODBCODBC.INI", "kar", "BufferSize", "65535"
MsgBox "done"
End Sub

when run the above code creates the above key but not in the path as i have specified above.
It creates the key in

HKEY_CURRENT_USERSoftwareVB and VBAProgram SettingsHKEY_CURRENT_USERSoftwareODBCODBC.INIk ar
which is the default path

but I want the key to be in the path
HKEY_CURRENT_USERSoftwareODBCODBC.INIkar

Is there any way to change the default path .
If yes please send me the code.

thanking you
and waiting for a code
J.Anandarajeshwaran
Binary Technologies
Madurai.
India

Creating/Registering A DSN Programmatically
I am in the process of converting a project to use ADO instead of RDO. This project creates a DSN "on the fly" using the "rdoRegisterDataSource" method of the RDOEngine object. Is there an equivalent ADO method, or some way to do this that does not use RDO?

Programmatically Creating A .ZIP File
How can I programmatically create and append files to a compression file, specifically a .zip file?

Creating Database Programmatically???
Dear Sir,

     I want to create an access database (MDB 7.0) absolutely programmatically. Starting from creating the database to create all the tables and all types of fields along with all the constraints. Can anybody please help me?

Programmatically Restarting Windows In Safe Mode To Update Firmware
I know there are API's to reboot the system,
InitiateSystemShutdown() is just one of them.

However, I need to update firmware in an embedded device, and there's no way I want to attempt this with Windows running.

I'm thinking I need to reboot in Safe Mode or force a reboot to the F8 screen.

This URL makes mention of using environment variables during a reboot
[URL=http://support.microsoft.com/kb/315222]

Safe Mode with Command Prompt (SAFEBOOT_OPTION=Minimal(AlternateShell)): This option is the same as Safe mode, except that Cmd.exe starts instead of Windows Explorer.

or
An environment variable is set when you use one of the Safe Boot options. The environment variable is SAFEBOOT_OPTION. This variable is set to either Network or to Minimal.


I've tried manually going into Control Panel, creating those environment variables, then use Ctrl-Alt-Delete to reboot. Oddly enough, the system reboots, and those vars are no longer in control panel. T
The system did not go into Safe Mode as I had expected. I've been pouring over the MSDN, but I'm obviously using the wrong terminology for searching.

1. Would someone point me to some code/docs which shows how to "programmatically" reboot a machine in Safe Mode (or console only)?

2. Is there a way to programmatically reboot an XP machine so it reboots into the "Windows Advanced Options Menu (The F8 screen)?

3. Is it possible to programmatically create an entry in the
"Windows Advanced Options Menu (The F8 screen), so the user can select
"Download firmware to My Device"?

Allow Zero Length When Programmatically Creating A Database
Code:
ADOXcatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DirPath
ADOXcatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& DirPath

ADOXtable .Name = "CompanyDetails"
ADOXtable .Columns.Append "Name", adVarWChar, 50
ADOXtable .Columns.Append "Address", adVarWChar, 255
ADOXtable .Columns.Append "Phone", adVarWChar, 20
ADOXtable .Columns.Append "Fax", adVarWChar, 20

ADOXcatalog.Tables.Append ADOXtable

Set ADOXtable = Nothing
Set ADOXcatalog= Nothing
I want to allow each field to have zero length, how do I do this?

Creating Dsn Name Programmatically(M.S.Access Driver)
hai

how to create data source name(dsn) programmatically in vb(Microsoft access driver)
thanku

Programmatically Creating A Database With Tables.
How can I programmatically create a database and tables selecting the information I want to send to the table from my existing database?
i.e.
Database Name: TestData.mdb

Table1: Customer
Send this info to the new table :
SELECT * FROM Customer WHERE RepCode = " & myRep, cn, .., ..

Table2: SALES
Send all sales but then I wont display the sale unless the sales customercode is found in the customer file

Creating An Access Database Programmatically.
How does one determine if a database exists, then create it if necessary?

Thanks,
Harold


***You can't change your past, but you can change your future***

Creating DataGrid Programmatically Using WebControls
Hi All,

I am trying to Make a component which will make the datagrid programmatically. So I have created a class myDataGrid.vb and in that I am instantiating and creating a DataGrid.

Dim myDataGrid As New DataGrid()
Dim DeleteColumn As New ButtonColumn()
Dim EditColumn As New EditCommandColumn()

With myDataGrid
.BackColor = System.Drawing.Color.White
.HeaderStyle.Font.Size = System.Web.UI.WebControls.FontUnit.XSmall
.HeaderStyle.Font.Bold = True
.BorderStyle = BorderStyle.Solid
.ShowFooter = False
.CellPadding = "1"
End With

With DeleteColumn
.ButtonType = ButtonColumnType.LinkButton
.Text = "Delete"
End With
myDataGrid.Columns.Add(DeleteColumn)

With EditColumn
.ButtonType = ButtonColumnType.LinkButton
.UpdateText = "Post"
.CancelText = "Cancel"
.EditText = "Edit"
End With
myDataGrid.Columns.Add(EditColumn)

Now I have a Procedure which will execute delete:

Sub DeleteLine(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
  'Delete statements
End Sub

My problem is How to add to tell the program to go and execute DeleteLine procedure when the user will click on Delete linkButton on the Grid ????

I tried
AddHandler myDataGrid.DeleteCommand, AddressOf Me.DeleteLine
this doesn't work

I have worked with datagrid and I used datagrid.ondeleteCommand property to specify the procedure to be executed. But when I use the .vb class, ondeleteCommand property is not displayed.

I want to execute the procedure DeleteLine and EditLine depending on whether the user clicks on Delete LinkBUtton or Edit LinkButton.

Please reply,
Thanks a million,
Rishi Chaturvedi
rishi2@rediffmail.com

Programmatically Creating Word Docs + RTF?
hi,
    i wanted to create some reports for my client and dynamcally create some sort of docs in RTF's and Ms Word Docs so he can take the reports any where and view them or even print them ..

    I know that using automation you can create an instance of Word and input text etc ... the problem is that the clients wont neccesarily have MS word so i need to use some sort of libary ?? any ideas ??

    The same goes for RTF? -- i thought of using the Rick Text Box control but i'm having trouble saving the pics --> it desnt support JPEG and when i insert BMP;'s the file size rockets!

cheers!

best regards,
                    sniGGer -- the one & only
                    V. Sandeep Koniki
                    Me want MCSD!!!
                    
__________________________________________________________
There is ALWAYS a way out ... its usually staring you in the face!

Programmatically Controlling The AT Command/Scheduling
Hi,

I'm trying to control the Window's AT command to enable me to provide sheduling functionality in a user interface for an app. on NT4.

I need to be able to interrogate current scheduled items, then either amend or remove them, as well as creating entirely new ones from scratch.
The clunkiest way seems to be using Shell, I also need to be able to capture shelled reponses>

I'm sure that there must be a reasonable way to do this, but I haven't sussed it yet - anyone have any suggestions?

Cheers, and thanks in advance,
Kevin.

kparker03@hotmail.com

Creating Workbooks(with Macros And Userforms) Programmatically
hi
I ve a template(.xlt) file.The file contains some macros and a user form.When I open the xlt file i get the user form..

In some other workbook I ve written code to create new workbooks using the above mentioned template file as the template...

Though my code creates the new workbook but that workbook does not have the user form...

I want the userform to be available in each workbook I create...

Any one help please

ADO - SQL - UPDATE Command
ellow

i want to update my records in a database. i am (trying to ) using ADO...
I know the basic SQl command's, but i am stuck with this ...

Myconn.execute ("UPDATE tblmembers SET Sessionkey=Key WHERE User=struser")

i want to update the Sessionkey-record where my User=strUser
I want to use the variables "Key and Struser in my SQL line... Is it possible ?

grz Xzo

SQL UPDATE Command
I'm having a interesting problem.. I can't seem to get VB to do a UPDATE command. ie UPDATE [Table] SET field1 = 'xxx' WHERE field1 = 'xx'. I keep getting a mismatch error everytime I send the command to my SQL Server. I know the UPDATE command I used is correct, I can use Access to link to the database and do a update command there..
Any help or suggestions would be appreciated.

Update Command (ADO 2.8)
Hi people! i have problems with UPDATE COMMAND!
I get an error when i executes it. I think there is no possibility of updating a record against an MS Access data base, am i right?
is it possible? I exectued the recordsetīs support method (Rst.Supports(adUpdate) ) and it returns FALSE.
what can i do?


Code:
Dim ConnConsultas As New ADODB.Connection, Rst As New ADODB.Recordset, sTexto As String

On Error GoTo ERRh

'open conn
ConnConsultas.Provider = "Microsoft.Jet.OLEDB.4.0"
ConnConsultas.Open App.Path & "BasesConsultas.mdb"

'open rst
With Rst
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.Open "SELECT * FROM METASEGMENTOS", ConnConsultas

If Rst.RecordCount > 0 Then Rst.MoveFirst
MsgBox Rst.Supports(adUpdate) 'does it supports update? it returns FALSE

While Not Rst.EOF
sTexto = "before: " & Rst.Fields("MetaSegmento_Longitud").Value

'update
Rst.Update "MetaSegmento_Longitud", 8
'------------I GET AN ERROR HERE

sTexto = sTexto & " - after: " & Rst.Fields("MetaSegmento_Longitud").Value
MsgBox sTexto
Rst.MoveNext
Wend

End With

SQL Update Command - HELP!!!!
Hi Guys

I am tryin to update a table in msaccess

strUpdate = ""
strUpdate = "Update into [MaintOccur]([Date],[Unit No],[Quote No],Details,Contractor,Authorisation, [Date Start], [Date End], [Checked by], [Lessee Invoiced], Amount, Vat, Total, Remarks)"
strUpdate = strInsert & "Values(" & "'" & Text1.Text & "'" ' memo field'
strUpdate = strInsert & "," & "'" & Text2.Text & "'"
strUpdate = strInsert & "," & "'" & Text3.Text & "'"
strUpdate = strInsert & "," & "'" & RichTextBox1.Text & "'"
strUpdate = strInsert & "," & "'" & Text4.Text & "'"
strUpdate = strInsert & "," & "'" & Text5.Text & "'"
strUpdate = strInsert & "," & "'" & Str(DTPicker1.Value) & "'" 'memo field
strUpdate = strInsert & "," & "'" & Str(DTPicker2.Value) & "'" ' memo field
strUpdate = strInsert & "," & "'" & Text6.Text & "'"
strUpdate = strInsert & "," & "'" & Text7.Text & "'"
strUpdate = strInsert & "," & "'" & Text8.Text & "'" ' number field
strUpdate = strInsert & "," & "'" & Text9.Text & "'" ' number field
strUpdate = strInsert & "," & "'" & Text10.Text & "'" ' number field
strInsert = strInsert & "," & "'" & RichTextBox2.Text & "'" & ")"
cmd.CommandText = strUpdate
cmd.Execute

Its giving me an error - INVALID SQL Statement. Any ideas on wat to do?

Thanks a million

Creating A Web Update Utility
Hey guys, i made a software system which is already utilized by some people, (meaning already installed on their PC's) but i am still working on the system and constantly release updates which i post to my website.

Now what i would like to do is create a feature that will automatically go to my site and check if a software update is available, and if so, prompt to download and install it.

I found a link to the software which does just that: ( http://www.synopsis-software.com/ ) But i do not want to use any third party software in my system, any ideas on how this could be accomplished?

So far i was thinking of a fairly simple solution:
1) Ill create a txt file and place it in some folder on my server

2) inside the text file i will put in some number (ex: 1000)

3) Every time i release a new update i will increment the number inside the file (Ex: 1001)

4) Once the user who is already using my software will try to check for available updates, my software will have to go to the site, and read the txt file, if the number within the text file is bigger then the number stored in the already installed software database. then it will prompt to download and install the update.

5) Once the update was installed, the number in the software database will be incremented to the number inside the text file residing on the server.


PROBLEM: i do not know how to implement STEP 4.

thank you for any help, links, articles, suggestions..etc

Creating An Update Routine....
I need to have an automatic "Check for Update" routine which will run in a NT Service.
This routine needs to have two separate ways to set it:
One - Every ___ seconds check for update.
Or Two - At [HH:MM:SS] check for update.



For One, I was thinking of having a variable in the registry which would hold a minuet size, lets say it's 30.

What is the most efficient way of accomplishing this?
I was thinking about having a timer and just letting that do the work, but is there a more efficient way?
Because, with a timer I would have to set a global variable to 30 with the timer set to 60000.
Then every time the timer runs out decrease the variable by one until the variable is zero.
Then do an update and reset the global to 30.



As far as Number Two, I don't have a clue...

Is there an API that you can set for an HH:MM:SS and it will fire off when this time is reached?



Thanks in advance...

- Eric

UPDATE Command With A DataGridView
hey, my problem is that if i use the command builder to update the datagrid view, my project works fine but what i really would like to do is update the table myelf from my own update command.

Works:

Code:
dim daInfo as new oledbDataAdapter
dim cb as new oledbCommandBuilder(daInfo)
daInfo.update(tblInfo)

Problems:

Code:
dim daInfo as new oledbDataAdapter
dim cmdUpdate as new oledbCommand("UPDATE tblInfo SET Name = ? WHERE Id = ?)
daInfo.UpdateCommand = cmdUpdate
daInfo.update(tblInfo)
dGridView.DataSource = tblInfo

the second version is a simplified version but basically i have used a generic SQL command to update and wants values for each fields. How do i correct this so that my update command will update entire datagridview???

Problems With UPDATE Sql Command
Code:
Dim MyConn As ADODB.Connection
Dim myRecSet As ADODB.Recordset

Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=microsoft.jet.oledb.4.0;data source=db.mdb;"
MyConn.Open



MyConn.Execute ("UPDATE cars SET Date_of_last_fitting = #" & Now() & "#, comments = '" & txtComments & "' WHERE registration = '" & carSelected & "'")

MyConn.Close

its not updating, does anyone know why?

What's Wrong With This Update Command??
it gives me an error : Syntax Error (comma) in query Expression ...

i can't see what's wrong...

someone help !!

Tec,Tec1,GrupoEco,GrupoEco1,Central,Central1 are string

Pot, Pot1 are single

MyDBConn.Execute ("UPDATE Centrais_Termicas_PT SET PotÍncia_Instalada='" & Pot1 & "', Grupo_Econůmico='" & GrupoEco1 & "', Tecnologia='" & Tec1 & "', Central='" & Central1 & "' WHERE PotÍncia_Instalada='" & Pot & "', Grupo_Econůmico='" & GrupoEco & "', Tecnologia='" & Tec & "', Central='" & Central & "'")


Thanks all

SQL: UPDATE Command Problems
Could anyone tell me if there is any limit to the number of the fields that UPDATE command can update? I have a problem where the code below faild with the run time error 3144 (Syntax error in UPDATE statement), whereas if I take some of the fields out, it works.


SQLString = "UPDATE StationDet2 " _
& "SET NoRegularA = " & Check1 & ", " _
& "NoRegularB = " & Check2 & ", " _
& "NoRegularC = " & Check3 & ", " _
& "SuperS = " & Distributori(0) & ", " _
& "SuperD = " & Distributori(1) & ", " _
& "SuperM = " & Distributori(2) & ", " _
& "SSpbS = " & Distributori(3) & ", " _
& "SSpbD = " & Distributori(4) & ", " _
& "SSpbM = " & Distributori(5) & ", " _
& "GasolioS = " & Distributori(6) & ", " _
& "GasolioD = " & Distributori(7) & ", " _
& "GasolioM = " & Distributori(8) & ", " _
& "GPLS = " & Distributori(9) & ", " _
& "GPLD = " & Distributori(10) & ", " _
& "MetanoS = " & Distributori(11) & ", " _
& "MetanoD = " & Distributori(12) & " " _
& "WHERE Station_ID = " & wkStationID(ListChoice) & ";"

db.Execute SQLString

Strange Update Command ...
Hi, people!

Iím trying to cope with an odd problem:
Iím using the following line in order to update a table field:
DB.Execute ("UPDATE MyTable SET MyField = " & Pesq(0) & " where counter = " & Val(TxtCounter))

Where:
DB is a database variable
Pesq(0) is the first column of a recordset variable
Counter is the table Primarykey and
TxtCounter is a text box which contains the Counter field.

The problem is:
The MyField is always updated to 2, even when Pesq(0) value = 1.
In order to be sure of that I added a line before the command db.execute:

Msgbox Pesq(0)
DB.Execute ("UPDATE MyTable SET MyField = " & Pesq(0) & " where counter = " & Val(TxtCounter))

So, the message box shows 1, and MyField is updated to 2. :-|
Iím afraid I havenít quit gathered whatís going on.
Any missing command, perhaps?

Thanks in advance,
Roselene

Oops...
I'd better say that:
So, the message box shows 1, and MyField is NOT updated at all. :-|
So, the Update line isn't work wrong, it isn't working at all, even tough there are no error messages.

(The field is updated to 2 regarding another problem and I'll see about it...)

Thanks!


[This message has been edited by Roselene (edited 02-02-2000).]

Problem Using UPDATE Sql Command
Hi all

I've got a vb 2005 form with textboxes for data from fields and buttons:  read, write, update, del

I've got it querying an access database, and grabbing data from a table.

Read works, if record already present, does a SELECT
write works, if record NOT present, does an INSERT
del works, the record is gone after I click this, does DELETE

but for the life of me I can't get UPDATE to work.
I've tried single and double quotes, ';' at end or not,
and just one or two values in the database.  I've looked in SQL for Dummies, syntax appears to be OK.

E.G.
UPDATE Nodes SET showid = showid.text WHERE nodeid = '7';

and then same command execute logic as in other buttons.

I actually stop and put the sql it is sending into a message box when it hits the UPDATE command.  Looks OK.

What is weirder is that it doesn't appear to be erroring in sql as it never hits the 'catch' area.   When I try write button (with 'INSERT' SQL command behind it) it does hit catch area under its button if exists.

Any ideas?  I'm at Witt's End, in a maze of twisty little passages, totally lost.

Creating A Installshield Update Pack
I have to create a maintenance pack that will basically overwrite the VB exe file and some report files. I also have a few new files to include. I wanted to use installshield to do it but I only want the new files to be included on the UNINSTALL.

Does anyone have any idea of to do this or has a article that talks about this.

Thanks........

bontyboy

SQL Update Command Doesn't Work?
Here is the code i'm referring to (i've cut a lot of stuff out for simplicity):

Code:
Set rs = db.OpenRecordset("oiQuery") ' query in access 2000 db

rs.AddNew
CID = rs!cID
rs!Serial = cmbRSerial.Text
rs.Update
rs.Close
EditSerials 'calls the edit sub
End Sub

Private Sub EditSerials()
sqlQuery = "UPDATE Serials SET sCid = '" & CID & "' WHERE sSerial = '" & cmbRSerial.Text & "'"
db.Execute (sqlQuery)
End Sub

It's supposed to take the customer id (CID) and add it to the Serials table where the serial number selected from the combo box (cmbRSerial.Text) equals the serial number in the table.

For some reason my UPDATE statement doesn't update the record when it's written like this.

If i change the WHERE clause to a number that i know exists in the serials table like 1101, then it updates the record fine. Example:

Code:
"UPDATE Serials SET sCid = '" & CID & "' WHERE sSerial = 1101"

I've even tried an assignment statement:

Code:
SerialNum = 1101
"UPDATE Serials SET sCid = '" & CID & "' WHERE sSerial = '" &SerialNum &"'"
And it doesn't work either, but my assignment statement for the CID works?

I'm guessing it's something about it being in the WHERE clause, but i'm not sure.

Anyone know what's going on here??

thanks

Problem With The UPDATE Command In VBA (MS Access)
Hi :
I am having a problem and I can't seem to figure out what is wrong.
This is it:

I have a table with a memo field called MonthComment. It also has a field called Historic_comments
I have a form containing 2 text boxes. One text box called Historic_comments and the other called New_comments. The Historic_comments textbox contain any comment that was on a record at the time the form is loaded, and this information comes from the MonthComment field in the Table.
Persons can now type comments in the New_comments textbox. The system will check if a comment existed before, and if it does, it will combine the old comment with the new comment and put this combined data in the Historic_comments textbox. It will then take it from the data from the Historic_comment textbox and store it in the MonthComment field in the table.
I use the Dcount function to check if there is a comment for the record. Each record in this table must have a comment, so if a comment does not exist, it means that the record does not exist in this table. I store the result in a variable called Comment_exist.
I use the following command to do the saving:



If comment_exist > 0 Then 'comment exist, so update the comment
DoCmd.RunSQL "Update tblComments set MonthComment = forms!wk12rp!historic_comments where customer_code = forms![Get list of customers]!cust_num"

Else 'comment doesnt exist: Add the customer code and the comment
DoCmd.RunSQL "Insert into tblComments (customer_code, monthComment) values (cust_num,new_comments)"


End If

I am having two problems with this: The first is with the Insert Into command. If the comment for the record is about 128 or less characters, this works fine. However, if the comment is longer than that, it gives me a "Invalid argument" error.
The other problem is with the Update command. If the comment is short, it works fine. If you input a series of short comments (eg. Called and left message) it will keep updating the MonthComment field until the total length of the field is to be about 128 characters. My taking on this is that, since the MonthComment field is a Memo field, it should accept any length string. Am I wrong? What am I doing wrong? How can I get around this?

I really need your help in the shortest possible time.


Thanks
Taffy

Data Adapter Update Command
I am trying to create oledb data adapter update command

I have created the select command successfully, data gets filled to the data grid but i make some change to the data and want to write back the changes howeover it won't work because may update command might not be correct any help will be welcomed

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Common

Public Class Form1
    Friend WithEvents cmdSelectionpat As New OleDbCommand()
    Friend WithEvents cmdInsertpat As New OleDbCommand()
    Friend WithEvents cmdUpdatepat As New OleDbCommand()
    Friend WithEvents cmdDeletepat As New OleDbCommand()

    Dim dspat As New DataSet()
    Dim dbconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source='C:ant_tp_stats.mdb'")
    Friend WithEvents dapat As New OleDbDataAdapter()
    Dim parameter As OleDbParameter

public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.

        dapat.SelectCommand = cmdSelectionpat
        dapat.InsertCommand = cmdInsertpat
        dapat.UpdateCommand = cmdUpdatepat
        dapat.DeleteCommand = cmdDeletepat

'the select command
        cmdSelectionpat.CommandText = "select patno,pfirstname,psurname from tb_patient"

' the update command
        cmdUpdatepat.CommandText = "UPDATE    tb_patient SET PatNo = ?, pFirstName = ?, pSurname = ?" _
         & "WHERE (PatNo = ?)"
        cmdUpdatepat.Parameters.Add("patno", OleDbType.BigInt, 8, "patno")
        cmdUpdatepat.Parameters.Add("pfirstname", OleDbType.VarChar, 8, "pfirstname")
        cmdUpdatepat.Parameters.Add("psurname", OleDbType.VarChar, 8, "psurname")
      
  cmdSelectionpat.Connection = dbconn
        cmdUpdatepat.Connection = dbconn
end sub

 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'update button

       dapat.Update(dspat)
    End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'button to fill the data grid with the selection
       
        Me.dbconn.Open()
        dspat.Clear()
        dapat.Fill(dspat)
        Me.Dgvpatients.DataSource = dspat.Tables(0)
              Me.OleDbConn.Close()
    End Sub


end class

I get an error Saying no value given one or more parameters
 

How To: Update 2 Or More Fields Using .execute Of The Command Obj
with only one field, i can create the SQL statement like:

strQuery = "UPDATE tblname SET fld1 = 'value' WHERE ..."

then assign strQuery to command obj's .CommandText and finally, execute.But, what if i do have 2 or more fields?
The alternative i can think of is to setup SQL statements one for each field, but there should be a better way - i just haven't discover it yet, but you knew it already, i believe.

Thanks for sharing your ideas.

I Can't Use .UPDATE Command Accessing DBASE IV Database
I'm new to this board so hi to all of you.

I have a problem that is buggin' me for a long time.

I can't use the rs.update statment because it preforms an error that the databes or object is read only.

PLEASE HELP ME, I'M ALL CRAZY ABOUT THIS

The code;

dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim Path As String

Path = "C:Temp" 'Path where I have all DBF files that I work with
db.Open "Provider=MSDASQL;Data Source=dBASE Files;Initial Catalog=" & Path

rs.Open "SELECT DBFFile1.*, DBFFile2.* FROM DBFFile1 INNER JOIN DBFFile2ON DBFFile1.Code= DBFFile2.Code;", db, adOpenStatic, adLockOptimistic

if I use normal select statement without any inner join's it doesn't work either.

msgbox rs!Field iif I use this line I see the result, the problem is if I try to write

rs!Field = "test" 'it is a string field 255 chr
rs.Update 'here the error preforms

Update Help Needed , Whats Wrong With This Command ?
db.Execute ("update accumpqr set age = (select age from linkedtable where name in (select name from accumpqr))")

trying to update a field in accumpqr table with a field from linkedtable table . it won't work
says , operation must use an updateable query

Any advise or code help ?

Thx , locutus

Update A Query Table From A Command Button
i need that a spreadsheet from the active control menu in front page shows a query from my access database. I can do this already but I need it to refresh everytime a person enters the page. I believe I need a button with a module in it that would refresh the database once pressed. I just don't know how to go about it. Any input?

Error Source Is System.DataUpdate For Da.update Command In Vb.net...pls Help
please go through the following code...i'v tried to apply the disconnected mode using dataset and data adapter...it's givng an error of this fashion...
"System.DataUpdate requires a valid Insertcommand when passed DataRow collection with new row" for the da.update command in addcmd_click event......please help




Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim con As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:Documents and SettingsAdministratorDesktopexp_modexp.mdb;"
cn.ConnectionString = con

dao = New OleDbDataAdapter("select * from exp_mas_bank", cn)
dao.Fill(ds, "bank")
End Sub

Private Sub addcmd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addcmd.Click
Dim drs As DataRow = ds.Tables("bank").NewRow
drs(0) = cdtxt.Text
drs(1) = nmtxt.Text
drs(2) = addtxt.Text
ds.Tables("bank").Rows.Add(drs)
Try
dao.Update(ds, "bank")
Catch ex As Exception
MsgBox(ex.Source & ex.Message)
End Try
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ds.Tables("bank").Rows(0)("code") = ds.Tables("bank").ToString & " ***"
Try
cn.Open()
dao.Update(ds, "bank")
cn.Close()
Catch ex As Exception
MsgBox(ex.Source & ex.Message)
End Try

End Sub

Unable Update Data In Report Even If Ado.command Has Executed A Different Query
My problem is i have function called preview report. When the user select a new contract and clicks on the button, the report should be updated with the new contract. But, it does not get updated with the new contract. It still shows
the old record.

I will show you my code and explain what i have done.

i am using crystal report 8.5 and VB 6.0

I am using

Function preview_Report()

Dim rsd As ADODB.Recordset
Dim rs_cmd As ADODB.Command
Dim query_temp As String
Dim test As String
Dim cp_no As Long
Dim dt As String

dt = ""
dt = built_date
'********************************************
'built_date Its a function to get the year and '
'month from the user and combined. Its a criteria that
'is used in the query
'*********************************************
cp_no = 0

cp_no = Combo1.Text
'**********************************************
'cp_no is the contract no that is selected by the user
'Used as a criteria in the query
'**********************************************

query_temp = ""
query_temp = " Select agency_name, agency_add, advertiser_name, advertiser_add, camp_no, "
query_temp = query_temp + " camp_origination_date, camp_pree_status, order_no, product_name, "
query_temp = query_temp + " camp_start_date, camp_end_date, spot_no, spot_duration, "
query_temp = query_temp + " spot_type, spot_air_date, brek_nom_time, prog_name, channel, "
query_temp = query_temp + " spot_code, spot_price, copy_code "
query_temp = query_temp + " From campaign_summary_direct Where camp_no = " & cp_no&
query_temp = query_temp + " And spot_air_date Like " + dt

'********************************
'query temp is the query that will be passed to
'Ado.command to be executed
'I have tested the query and i am very sure that
'its fine.
'***********************************


Set rs_cmd = New ADODB.Command
Set rsd = New ADODB.Recordset

on error goto x

'*************************************************
'on error go to x is need when the function is executed
'for the first time , because the rsd recordset is
'not opened,

'*************************************

rsd.close


x:

rsd.Open query_temp, cn_temp, adOpenForwardOnly, adLockReadOnly


Set rs_cmd.ActiveConnection = cn_temp
rs_cmd.CommandText = query_temp
rs_cmd.CommandType = adCmdText
Set rsd = rs_cmd.Execute(query_temp)

Bound_report
'*************************************
'Bound Report is a function set unbound fields. My report
' has unbound fields in it.
'I have checked and its Fine
'**************************************

Report1.Database.AddADOCommand cn_temp, rs_cmd
'cn_temp is the connections string defined and set
'during form load.


Form1.CRViewer1.ReportSource = Report1
Report1.ReadRecords
Report1.DiscardSavedData
Form1.CRViewer1.ViewReport
Form1.Show

End Function


Further explanation

I am very sure that the query is executed with different criteria. so this cannot be the reason why the report is
not being updated. What happens is when i first start my program, i select the contract no = 1000 and the report gets updated with ccontact 1000. then lets say i select
contract 1200 , the report still shows me contract 1000.

any subsequent execution of preview report function shows me contract 1000. So only the first attempt , right after i start the program shows me the correct contract.

i have used Report1.DiscardSavedData to remove saved data, if there is any.(Does not work)

I have used Report1.ReadRecords to force it to update the data in the report from the ADO.command,(Does not work).

A Pretty Difficult Update Table Command Question <resloved>
Hi,

(I'm working in Vb.net with MS Access as my database)

I want to update a table in a database when a user wants to change one of the rows values. But I am getting very very stuck:


PHP Code:




Private Sub UpdateRow() 'Update a table with a new value

        'The Database to use: txtDatabase.Text (It could be any database the user chooses)
        'The Table to update: cboTable.Text (This could be any table with any number of rows and columns)
        'The value to insert: cboValue.Text (The Value could be any string eg. "ASfdyasd" or "787")
        'The position to insert the value: cboMarkDup.Text = Column, Row2Comp = Row
        '(The Column and Row are unknown until run-time when user chooses, the column could be of any type eg.string,integer Yes/No)

        'Database Connection: Dim objConnection As OleDbConnection
        'objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & ";User ID=Admin; Password=;")
        'Database Adapter: Dim objDataAdapt As New OleDbDataAdapter()
        'Data Set: Dim objDataSet As DataSet = New DataSet()
        'Currency Manager: Dim objCurrencyManager As CurrencyManager
        'Data View: Dim objDataView As DataView
        'Data Grid: DataGrid1


        'Insert the value
        'Update the table
        'Save the changes
        'Display the resulting table in a datagrid
        'This is what I am trying to do (code below), it does not work, it gets stuck on
        'Execute the oledbcommand object to update the data
        'objCommand.ExecuteNonQuery()
        'Saying:
        'An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
        'However the same command works when the BindFieldData() Sub is called to Select all the data
        'This suggest to me the problem could be with all the objCommand.******* below but where and what????


        objDataView = New DataView(objDataSet.Tables(cboTable.Text))
        objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)
        Dim objCommand As OleDbCommand = New OleDbCommand()
        objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & ";User ID=Admin; Password=;")
        New_DBConnection()
        objConnection.Open()

        objCommand.Connection = objConnection
        objCommand.CommandText = "UPDATE [" & cboTable.Text & "]" & _
                                "SET [" & cboMarkDup.Text & "] = ? " & _
                              "WHERE [" & cboPrimary.Text & "] = ?"

        objCommand.CommandType = CommandType.Text
        'Add parameters for the placeholders in the SQL in the commandtext property
        'Parameters for the field
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(0).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(0).DbType = DbType.String
        objCommand.Parameters.Item(0).Size = 80
        objCommand.Parameters.Item(0).Value = cboValue.Text

        'Parameters for the PRIMARY KEY
        objCommand.Parameters.Add(New OleDbParameter())
        objCommand.Parameters.Item(0).Direction = ParameterDirection.Input
        objCommand.Parameters.Item(0).DbType = DbType.Int32 'String?
        objCommand.Parameters.Item(0).Size = 6
        objCommand.Parameters.Item(0).Value = txtPrimaryR1.Text

        'Execute the oledbcommand object to update the data
        objCommand.ExecuteNonQuery()

        'Close the connection
        objConnection.Close()
        'Fill the DataSet and bind the fields
        New_DBConnection()
        objConnection.Open()
        BindFieldData()
        objDataView = New DataView(objDataSet.Tables(cboTable.Text))
        objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)
        objCurrencyManager.Position = Row2Comp
        objCommand = Nothing
    End Sub









PHP Code:




Private Sub BindFieldData()

        Dim s As String
        s = cboTable.Text
        objConnect = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & ";User ID=Admin; Password=;")
        
        New_DBConnection()
        objConnect.Open()
        objDataAdapt.SelectCommand = New OleDbCommand()
        objDataAdapt.SelectCommand.Connection = objConnect
        objDataAdapt.SelectCommand.CommandText = "Select [" & s & "].* from [" & s & "]"

        objDataAdapt.SelectCommand.CommandType = CommandType.Text

        objDataAdapt.SelectCommand.ExecuteNonQuery()
        objDataAdapt.Fill(objDataSet, s)

        DataGrid1.DataSource = objDataSet
        DataGrid1.DataMember = s

    End Sub








The BindFieldData Sub works fine on its own and displays the table in a datagrid (SO the connection and all that is not the problem)

Any ideas greatly appriciated

Excel - Command Button To Update Cell Background Colour
I'd be very grateful if someone could help me with a very simple question about vb code.

I have an Excel speadsheet and I'd like to set up a few command buttons to change the fill/background colour of the selected cell, but I'm unsure as to the code to add.

How it'll work:

1) Select the cell in the spreadsheet to be changed.
2) Press the corresponding command button (Titled = Blue, Red, Green etc).
3) Button changes the background colour of the cell to Blue, Red, Green etc.

It'll mostly be used as a single cell selection but is it possible to allow a range of selected cell to be changed in colour?

Can I type colour 'names' in or do I need a reference number to specify a certain colour? ...if so, how do I find out what colours are called?

I tried the "record a macro and re-engineer it" help tip but the code doesn't seem to work when I attach it to a command button.

Many thanks for all your help.

Marc

Excel - Command Button To Update Cell Background Colour
I'd be very grateful if someone could help me with a very simple question about vb code.

I have an Excel speadsheet and I'd like to set up a few command buttons to change the fill/background colour of the selected cell, but I'm unsure as to the code to add.

How it'll work:

1) Select the cell in the spreadsheet to be changed.
2) Press the corresponding command button (Titled = Blue, Red, Green etc).
3) Button changes the background colour of the cell to Blue, Red, Green etc.

It'll mostly be used as a single cell selection but is it possible to allow a range of selected cell to be changed in colour?

Can I type colour 'names' in or do I need a reference number to specify a certain colour? ...if so, how do I find out what colours are called?

I tried the "record a macro and re-engineer it" help tip but the code doesn't seem to work when I attach it to a command button.

Many thanks for all your help.

Marc

Creating My Own Command Prompt In VB
What I am trying to do is create my own Command prompt.
What im trying to do is get the program to send the parameters to the net file (located C:WINDOWSsystem32
et.exe) and then recieve any information that net.exe tries to send back (for example if i remember correctly "net send..." sends back something like "message has been sent to USER" dont have access to cmd atm so can't just check).
But i would want the information sent back to be able to be displayed in like a text box in my VB program.
Does anyone have any ideas on how this could be done?
I know that i can send the parameters to the program (for example net) using

Code:
shell("*command* *parameters")
But the part that really stumps me id getting any results from that back and being displayed. I guess once i get the results back i could figure out the displaying them but i can't figure out how to get the results back to my program.

Any idea's greatly appreciated

Creating SQL Command W/ Integer
I have fiddled with this for ages and I cannot get it happening.

I have an integer (catID) that I cannot append to a string (sqlCmd) to execute on my database. I'm not sure whether I need to use something like ToString() but when my program hits the below line of code, I get a "type mismatch" error...

sqlCmd = "SELECT * FROM product WHERE prd_cat_ID = " + catID

any ideas?

Cheers,

Creating Table By SQL Command
Hi!
How may I create a table by a SQL Command and specify its columns' Name, Data Type, Caption, Data Requirement, Data Index & etc. at Microsoft Access. The following command creates a table named "Books" containing six fields called "Title" as TEXT with maximum 50 characters, "Author" as TEXT with maximum 50 characters, "Subject" as MEMO, "IsEnglishLanguage" as YES/NO, Price as INTEGER and "PublishNumber" as BYTE:

CREATE TABLE Books
(
Title TEXT(50),
Author TEXT(50),
Subject MEMO,
IsEnglishLanguage BIT,
Price SMALLINT,
PublishNumber BYTE
);

But how about the other specifications of fields such as Caption, Data Requirement, Data Index & etc.?

Yours thankfully,
M.Sadegh Samiei

Creating My Own Command Prompt In VB
What I am trying to do is create my own Command prompt.
What im trying to do is get the program to send the parameters to the net file (located C:WINDOWSsystem32
et.exe) and then recieve any information that net.exe tries to send back (for example if i remember correctly "net send..." sends back something like "message has been sent to USER" dont have access to cmd atm so can't just check).
But i would want the information sent back to be able to be displayed in like a text box in my VB program.
Does anyone have any ideas on how this could be done?
I know that i can send the parameters to the program (for example net) using

VB Code:
shell("*command* *parameters*")


But the part that really stumps me id getting any results from that back and being displayed. I guess once i get the results back i could figure out the displaying them but i can't figure out how to get the results back to my program.

Any idea's greatly appreciated

Creating Command Buttons
I forgot how to do this... shameful... but how do you make command buttons in running applications again?

Creating A Different Command Button
hi,
i am trying to make a command button that could have rounded edges or a different shape than a standard command button. i have never made an activex control before and am not looking to spend a great amount of time on this, although it would make the project look a lot nicer.

i find the code on PSC to be very long, confusing, and excessive in terms of functionality. does anyone have suggestions/ideas on where to begin?
thanks,
phil

Creating Copy Command
Hi experts.

I want to write some codes on a command button.

When I click on this command button, any file or a folder on a machine should be copied on another machine.

the source and destination for the files/folder can be static or dynamic.




I am waiting.




Shiv

Shell VS Creating New Object Command
Dear All Experienced Programmers,

I found that there are two ways to run executables in VB6. (Works well with VB 2005).
Method 1 : Using Shell command to execute Standard Exe compiled from VB6
eg : Shell("C:Project1.exe")


Method 2: Using the Set as New to execute an ActiveX Exe.
eg:
First add a reference to the Project1.EXE into the project then to invoke the EXE , use :

Dim p1 As Project1.Class1
p1 = New Project1.Class1
Call p1.Main()

Does both of these methods causes application to perform differently performance wise? Are there any other differences other than the fact that one executes Standard Exe and the other executes ActiveX Exe?

Creating Clickable Links With A Command
What is the minimal code for creating a link that would take you to http://www.google.com/ with Command1

Creating Command Buttons In EXEs
Is there a way that instead of me putting in the hundreds of chords in my tab writting program, that the user can put in command buttons for certain chords they need? If it can work, all the code for my command buttons are the same...

Copyright © 2005-08 www.BigResource.com, All rights reserved