Visual Basic :: Problem With Long Insert Statement

[Oracle][ODBC][Ora]ORA-01461: Cannot Bind A LONG Value For Insert Into A Long Column
Using the Oracle in OraHome92 version 9.002 ODBC driver in visua basic I receive the message "[Oracle][ODBC][Ora]ORA-01461 cannot bind a LONG value for insert into a long column" whe updating a large table a table with 59 columns. The proble seems to be related the number of columns being updated or th size of the data. I am using a recordset object to perform th update. If I limit the number of columns I update, the updat works successfully. On smaller tables, the update work successfully.

Insert INTO Sql Statement With Connection String In Sql Statement
I'm slowly moving my access DB's to sql server 2000. I had some make table queries in my VB6 app that updates an access DB backend. I was using a sql statement like (note: not the actual query) so I didn't have to link the different access DB tables to the backend that gets updated.
CODESelect Primary.* INTO Primary_C from Primary in 'I:MypathMydb.mdb' Where 1=1
conn.execute SQL,,129

Really Long INSERT
OK I have a HUGE Insert Statement (swear Im not bragging)

The table has, .........wait for it ........ 75 columns!
My problem is when VB breaks it up due to size how do I connect the different parts to make it one large coherent statement

e.g.

"INSERT INTO Table (M1,M2,M3....M25, N1,N2.....,N25, O1,O2,O3......O25)
VALUES ('" & varA1 "',varA2,....varA25,varB1,varB2,...varB25,
varC1,varC2....'" & varC25 & "')"

How do I join
INSERT INTO (......)
VALUES (........
..............)

Please Help, this is quite important

Long Sql Statement
I'm trying to create an INSERT statement dynamically. The table has 87 fields, and the number of characters if around 500. When I try to create the INSERT statement to execute, it seems to cut it off around 240-250 characters.

What can I do to get around this?

Long Sql Statement
hey all
This is really basic prob but Im looking for a guide line.If you have a really long sql statement that you want to divide up and put over 3 or 4 lines,what are the rules for dividing the string.I know you use & _to get on to the next line but I do that and still get problems.Are there certain areas of the statement that you cant place the & _ characters.
This is my statement.

Code:
strSql = "UPDATE tablepro SET [Rate] = " & txtRate.Text & _
" , [Vol] = " & txtVol.Text & _
" , [time] = " & txtTime.Text & _
" WHERE [priceid] = '" & lstpriceid.Text & _
"' AND [ProductID] = '" & lstProduct.Text & "'"

Like I said.Mainly wondering are there certain parts of the statement that cant be on two different lines.thanks.
Edit by 00100b:
Add line-continuation characters.

Insert Cell Row To Accommodate Long Text
In my spreadsheet's table, the text length in col 5 could be longer than the actual col width, causing the complete text content cannot be displayed. I compare the length of maxtextlength and colwidth, and if the text length is detected longer than the col width, have the row added to accommodate full text length. But when the row is added, how to shift the remaining text to one row below?

Dim m As Integer
Dim j As Integer
m = 0: cell.Row = m_currentrow: m = cell.MaxTextColWidth(5)
j = 0: cell.Row = m_currentrow: cell.Col = 5: j = cell.ColWidth(5)
If m > j Then
Call cell.InsertRows(m_currentrow + 1, 1)

End If

Insert Long Text In Data Base
Friends,

Please, somebody help me!!! I trying to insert a long text (360 pages of MS-WORD) into Data Base of SQL SERVER 2000. I'm using the command and the fields in SQL SERVER is Text.

How can I do this?

Tks,

Eduardo - TI Manager

How Do You Make The If Statement Go To The Next Line, Because It Is Too Long?
If imginv1.Left > 7835 Or imginv2.Left > 7835 (Thing that would make it go to the next line here)
Or imginv3.Left > 7835 Or imginv4.Left > 7835 Then

What The Short Cut 2 Break The Long Statement
Hi
i got a long line of codes and i wonder is there any command or shortcut 2 break the long line. Thank you

Long SQL Statement, Give Error?
MotorBrunei.MotorClass.Execute "INSERT INTO tb_master(policyno,insuredname,TransactionDate,Operator,IssuedBranch,P remiumNo,EffectiveDate,ExpiryDate,SumInsured,Code,AccountNo,CoverNoteN o,GrossPermium,NCD%,NCDAmount,StampDuty,NetPremium,Address,Remarks,Veh icleNo,CommissionCode1,CommissionCode1Amount,CommissionCode2,Commissio nCode2Amount,CommissionCode3,CommissionCode3Amount,Rebate,RebateAmount ,TotalAmountCommission,CarBrandName,TypeOfBody,CubicCapacity,YearofMan ufacture,VehicleCapacity,EngineNo,ChasisNo,BusinessorProfession,HirePu rchaseOwners) " _

If it is a long SQL statement, Is there any error?
Anyway, how to break long statement into the next line

RaiseEvent Statement Is Too Long, How To Divide It?
Hello,

In my program I call an event.
RaiseEvent Edited(.....)
But the line is too long, how can i divide it over several lines?

Will It Create Problem If A Select Statement Is Too Long?
It's an IRC client and there are 43 commonly used commands on IRC like PRIVMSG, MODE, QUIT etc... all of which i have used in one select case statement, not to mention all have these commands have sub commands which are used in nested IF... Then statement. Therefore, the select case statment is pretty long. However, i've few solutions to the problem in my mind which are as follows:

1. Make function for each command in module and call it from my case statement. ( here i should use one module or spread them over different modules. Which brings to another question how vb searchs for the function, does it searches all of the modules until it finds the right one or goes directly to the module which has the function?)

2. Move all the responses to a database.

3. Make a dll for the commands (difficult)

(which one should i go for? speed is important).

How do professional irc clients are build? They seems to be pretty fast.

SQL Statement Too Long. How To Wrap The Line So I Can Save?
I have an SQL statement that is too long to fit into the window as well as to save (or so vb6 is complaining)
I tried the usual " _" but it tries to do some crazy stuff like put in a "" and then doesn't work/errors etc

What is the correct way to word wrap a SQL statement

Thanks
Statement below

Code:strSQL = "SELECT VehicleMaster.VehicleID, VehicleMaster.StockNumber, VehicleMaster.TradeNumber, VehicleMaster.VehicleStatus, VehicleMaster.VehicleType, VehicleMaster.VehicleTypeID, VehicleMaster.VIN, VehicleMaster.FactorID1, VehicleMaster.FactorID2, VehicleMaster.VehicleYear, VehicleMaster.VehicleMakeID, VehicleMaster.VehicleModelID, VehicleMaster.PreviousOwner, VehicleMaster.VehicleBody, VehicleMaster.VehicleOptions, VehicleMaster.VehicleColor, VehicleMaster.VehicleInterior, VehicleMaster.Package, VehicleMaster.Engine, VehicleMaster.Transmission, VehicleMaster.Drive, VehicleMaster.TOPS, VehicleMaster.PS, VehicleMaster.PB, VehicleMaster.AC, VehicleMaster.Tilt, VehicleMaster.PW, VehicleMaster.PL, VehicleMaster.Stereo, VehicleMaster.CDPlayer, VehicleMaster.TapePlayer, VehicleMaster.Cruise, VehicleMaster.Title, VehicleMaster.TitleNumber, VehicleMaster.VLicense, VehicleMaster.LicenseExpire, VehicleMaster.Miles, VehicleMaster.Damage, VehicleMaster.WarrantyID, VehicleMaster.Weight, VehicleMaster.LastOffer, VehicleMaster.Deposit, VehicleMaster.NoOfTires, VehicleMaster.KeyNumber, VehicleMaster.TradeIn, VehicleMaster.SalePrice, VehicleMaster.InvoicePrice, VehicleMaster.GrossProfit, VehicleMaster.AskPrice, VehicleMaster.MSRP, VehicleMaster.RepairOrderCost, VehicleMaster.RepairOrderRetail, VehicleMaster.OrderNumber, VehicleMaster.OrderDate, VehicleMaster.MakeDate, VehicleMaster.InventoryDate, VehicleMaster.SaleDate, VehicleMaster.CustomerID, VehicleMaster.SalespersonID, VehicleMaster.CustomerDetailID, VehicleMaster.Depreciation, VehicleMaster.FloorPlanAmt, VehicleMaster.FinancingOptions, VehicleMaster.FinanceEndDate, VehicleMaster.FuelType, VehicleMaster.Comment, VehicleMaster.LicenseState, VehicleMaster.HoldBack, VehicleMaster.Incentive, VehicleMaster.DealerPack, VehicleMaster.Advertising, VehicleMaster.Rebate, VehicleMaster.Spiffs, VehicleMaster.VehicleNote, VehicleMaster.LicenseID, VehicleMaster.KeyCode, VehicleMaster.KeylessCode, VehicleMaster.MileageStatus, VehicleMaster.VIN2, VehicleMake.LongDesc AS VehicleMake, VehicleModel.LongDesc AS VehicleModel
FROM (VehicleMaster INNER JOIN VehicleMake ON VehicleMaster.VehicleMakeID = VehicleMake.VehicleMakeID) INNER JOIN VehicleModel ON VehicleMaster.VehicleModelID = VehicleModel.VehicleModelID WHERE VehicleMaster.StockNumber = '" & strStockNumber & "'"

INSERT INTO Statement
Hi

I have been battling with this for days now, i have been through the help doc's and searched the forum, and i just can't figure out what is wrong with my INSERT INTO query, I am using an access db with a table called "Sales1" and another called "SalesHist" and ill i want to do is copy the data from "Sales1" into "SalesHist" but i keep getting "Syntax error (coma) in query expression" errors, i have taken the comas out used square brackets, parenthesis everything. Please can someone tell me where i am going wrong.

Code:
Private Sub Command1_Click()
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim sSQL As String
Set MyConn = New ADODB.Connection
Set MyRecSet = New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:pospos.mdb;"
MyConn.Open
MyConn.Execute "INSERT INTO SalesHist (Tbl_No, Qty, Description, Price, Dates) SELECT (Tbl_No, Qty, Description, Price, Dates) FROM Sales1 WHERE Tbl_No = '" & Text1.Text & "'"
MyRecSet.Open sSQL, MyConn, adOpenStatic, adLockReadOnly

End Sub
Many thanks

INSERT INTO Statement
SQL = "INSERT INTO Order_Truck(Order_Num, Truck) VALUES (" ' & txtOrder.Text & '" , "' & txtTruck.Text & '")"

db.Execute SQL

It is not working for me..

SQL Insert Into Statement
I'm trying to use the insert statement to add records to my table.
I'm doing this:
Data1.RecordSource = "INSERT Consultas(Paciente, Telefone, [Última Consulta], Mês) VALUES('" & Text1.Text & "', '" & Text2.Text & "', '" & Text3.Text & "','" & Combo2.Text & "')"

But is does not work, I used the same kind of thing with the SELECT statement ( Data1.RecordSource = "SELECT * FROM Consultas ORDER BY Paciente") and it work fine.

Can anyone tell me the problem with my INSERT statement?

Insert Statement
What is the best way to do a insert SQL statement within VB

I am inserting a lod of values via variables.

What happens is I read in a file, and read in those values to variables.

I then build up my query with those variables in the order I want.

However I dont want it to be all in one line.

do I do it this way?

strSQL ="INSERT into tblImport values ( " ' " & varaible ' " & so forth)

sqtSQL = strSQL & "' variable "')

What I want to do is really have each variable i read in on a newline to make it clearlier to read.

so not sure how to do that...

Plus if the data I hold within the file is of a mix and the database is set up not to care whether its a date or not will it matter how i insert numbers of text etc?

thanks

Insert Into Statement
is there a limit to how many values i can use. my code below uses 15 vform.txt. when i add a 16th vform.txt and add another field to be inserted into Well Master, i get this error "run-time error '-2147217900 (80040e14)' ". Please help...

SQL = "insert into [Well Master](FLAC, [EFF DATE], " & _
"[WELL NAME], [LEASE NO], " & _
"[API MMS CODE], API, " & _
"STATE, COUNTY, " & _
"[WYFORM2 FLAG], [STAT], " & _
"RESERVOIR, QQ, " & _
"SEC, TWP, " & _
"RGE)" & _
"values('" & _
vform.txt(1) & "', '" & vform.txt(2) & "', '" & _
vform.txt(3) & "', '" & vform.txt(4) & "', '" & _
vform.txt(5) & "', '" & vform.txt(6) & "', '" & _
vform.txt(7) & "', '" & vform.txt(8) & "', '" & _
vform.txt(9) & "', '" & vform.txt(10) & "', '" & _
vform.txt(11) & "', '" & vform.txt(12) & "', '" & _
vform.txt(13) & "', '" & vform.txt(14) & "', '" & _
vform.txt(15) & "');"

SQL Insert Statement
Can I insert a record in a table if the key field in the table is auto-increment type ?
If I can how ? "INSERT INTO TABLE VALUES(????)

thanx in advance

Stauf's Game Isn't Over

SQL INSERT Statement
I am building an SQL statement on the fly in my program from a recordset. My problem is that there are comma's in the data that is messing up my SQL string. Could you please help me find an answer to this as soon as possible. The only thing I have tried is putting in the Chr code for comma, but that did not work, or I have the wrong syntax. This data is being inserted into a .dbf file. Thanks in advance.

SQL Insert Statement
Good Afternoon

This may be a very simple question to some but before I get started I figured I would ask...

I have to write "some" SQL S/P's and one of them includes inserting a record into a table. To keep things simple I will approximate here. I need to insert let's say 25 columns out of 50 for one record. Then once populated, another
system updates the rest of the 25 for that record. When I do the insert, can I just insert into the fields I am taking care of? Or do I have to insert into all of the fields using <blanks> for the ones I do not use?

Thanks for the help,
EJD

Insert Statement In Vb6
Hi,

I try to execute the INSERT statement below and keep getting synax error. I have no idea what is wrong.

Can someone take a look

"INSERT INTO ALS_PRODUCT (product, seq, desc) VALUES ('" & Trim(rst1!Item) & "'," & seqx & ",'" & descout1 & "')"

Thanks

Insert Statement In Vb
Hai,
I have two strings....one string have the Field names,another string have the Values.....i want to pass the this string to insert sql statemant......how can i pass that.....

regards,
Murugan.

Need Help With An SQL Insert Statement
I have a table "myTable" that has two columns in it. I need to insert a list into this table. The list looks something like this:

ID Name

500 Henry Adams
856 Shelly Thorn
2562 Someone
744 Whatever

etc.

I need to insert these values into my table columns.
The SQL statement I know is:

Insert into myTable(ColID, ColName)values("500", "Henry Adams")
Insert into myTable(ColID, ColName)values("856", "Shelly Thorn")
Insert into myTable(ColID, ColName)values("2562", "Someone")
Insert into myTable(ColID, ColName)values("744", "Whatever")

Is there a statement that I can just add multiple entries using a single Insert statement. I am building this table at run time.

Thanks,

M

The problem is that I do not want to write this statement 50 times for all my values.

Insert Into Statement
Can someone give me a query of Insert Into statement for VB

I have a Customer table and I want to add Name = "John", Member_ID = 3456

Please help

Insert Statement ?
I got a problem with a insert statement. I have two table such as :
in first table (Mscat) i have a data :
User CategID CategName
B 1 Budy
B 2 Rudy
C 3 Heny

In second table (TmpMscat) i have a data :
User CategID CategName
D 5 Wani
D 6 Runi
E 7 Yuki
E 8 Lucky

Then I use statement : insert into TmpMscat select * from Mscat where user = B

When I run and result is like that
D 5 Wani
D 6 Runi
B 1 Budy
E 7 Yuki
E 8 Lucky
B 2 Rudy

But I want data to be insert into table at last record or like that :
D 5 Wani
D 6 Runi
E 7 Yuki
E 8 Lucky
B 1 Budy
B 2 Rudy

So Anyone can help me ?

Best Regard's

Long

SQL INSERT Statement
I cannot seem to find what I'm doing wrong. Here is the code that I'm working on. This is suppose to delete the entire content of the database table and pickup the information from a text file and place the information in the designated fields in the database.

Private Sub cmdUpdateSalesData_Click()
Dim pstrSQL As String
Dim psngArea As Single
Dim pstrAddress As String
Dim pstrDateSold As String
Dim psngSalesAmount As Single
Dim psngSquareFeet As Single

'On Error GoTo Error_Catch

pstrSQL = "DELETE * FROM tblSales "
gdbCurrent.Execute pstrSQL

Open ("A:ch8hwkismet.txt") For Input As #1
Do Until EOF(1) = True
Input #1, psngArea _
, pstrAddress _
, pstrDateSold _
, psngSalesAmount _
, psngSquareFeet
pstrSQL = "INSERT INTO tblSales " & _
"(fldArea,fldAddress,fldDateSold,fldSalesAmount,fl dSquareFeet)" & _
" VALUE(" & psngArea & "," & "'" & _
pstrAddress & "'" & "," & "#" & _
pstrDateSold & "#" & "," & _
psngSalesAmount & "," & _
psngSquareFeet & ")"
'Call MsgBox(pstrSQL)
gdbCurrent.Execute pstrSQL
Loop
'Exit Sub
'Error_Catch:
'Call MsgBox("Update Sales Error", vbInformation, "Error")
End Sub

Please help...

Thanks,

Stephen

--Boundary_(ID_tAjtGzMCmLRi/lNAixaUuw)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>I cannot seem to find what I'm doing wrong. Here is
the code that I'm working on. This is suppose to delete the entire content of
the database table and pickup the information from a text file and place the
information in the designated fields in the database.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Private Sub
cmdUpdateSalesData_Click()<BR>    Dim pstrSQL As
String<BR>    Dim psngArea As Single<BR>    Dim
pstrAddress As String<BR>    Dim pstrDateSold As
String<BR>    Dim psngSalesAmount As Single<BR>
Dim psngSquareFeet As Single<BR>    <BR>    'On
Error GoTo Error_Catch<BR>    <BR>    pstrSQL =
"DELETE * FROM tblSales "<BR>    gdbCurrent.Execute
pstrSQL<BR>    <BR>    Open
("A:ch8hwkismet.txt") For Input As #1<BR>    Do Until EOF(1) =
True<BR>        Input #1, psngArea
_<BR>             ,
pstrAddress
_<BR>             ,
pstrDateSold
_<BR>             ,
psngSalesAmount
_<BR>             ,
psngSquareFeet<BR>     &nb sp; pstrSQL = "INSERT
INTO tblSales " &
_<BR>
"(fldArea,fldAddress,fldDateSold,fldSalesAmount,fl dSquareFeet)" &
_<BR>             "
VALUE(" & psngArea & "," & "'" &
_<BR>
pstrAddress & "'" & "," & "#" &
_<BR>
pstrDateSold & "#" & "," &
_<BR>
psngSalesAmount & "," &
_<BR>

SQL Insert Into Statement
Hi,
If I want to inset a list of variables into SQL database,do I have to indicate each field name in the database to be able to run the insert statment? I already put the variables in order. They should be field1,field2,etc...
Code like below:
=============
strSQL = ProName & "," & ProNumber & "," & ProVersion & "," & _
PD & "," & FieldMan & "," & Samplecon & "," & _
Programmer & "," & WEB & "," & WEBBACK & "," & _
Dialer & "," & StartDate & "," & EndDate & "," & _
SampleSize & "," & Quota & "," & PerHrCharge & "," & _
EstiQuota & "," & EstiCPH & "," & EstiIncidence & "," & _
EstiAVGTime & "," & EstiMin & "," & EstiINTHour

strSQL = "INSERT INTO ProjectInfo " & _
"(" & strSQL & ")"
dbconnection.Open strSQL

============

It's not working.

??
Your help is appreciatated.

INSERT INTO Statement Help
Hello Everyone,

Can you please let me know what is wrong with the query?

INSERT INTO Junction (Unique_ID, ID, WBS, Name, Duration_In_Days, Start_Date, Finish_Date, Recurring, Milestone, Resource_Unique_ID, Resource_ID, Resource_Name, Resource_Initials )
SELECT Task.Unique_ID, Task.ID, Task.WBS, Task.Name, Task.Duration, Task.Start_Date, Task.Finish_Date, Task.Recurring, Task.Milestone, Assignment.Resource_Unique_ID, Assignment.Resource_ID, Assignment.Resource_Name, Assignment.Resource_Initials
FROM Task, Assignment;

The Junction table is the table I want to paste new records from other table into.  My goal is to transfer the records from the Task table which has nine attributes, and these nine attributes are the same as the ones in the Junction table, and the rest of the attributes in the Junction Table come from the Assignment table.

Thus, when I excecute the above query to Append the Junction table, it pastes the new records right below the last record in the Junction table instead of replace the existed records of the first nine attributes in the Junction table.

For example, I previously have 48 records in the Junction take, and 100 records in the Task table.  I want the extra 52 records in the Task table added to the Junction table to make it 100 records instead of just 48 records, where all the attribute names in the Task table matches with the names in the Junction table.

Thank you very much for your help.

DTiff

Insert Into SQL Statement
Just curious before I start this project.

If I want to use a SQL statement with ADO to add a record from one table to another table in the same A2K database then I open the connection and...
CODEwith conn
.Execute sqlStatement
end with

I Can't Use An SQL INSERT Statement
Hi,

I'm also new to VB and I'm used to working with SQL statements. From a previous thread I read that the way to use an Insert SQL looks like this:

CODE
  Dim dbUsers As Database
  Call Initialize_Form
  Dim rsUSER_INFO As Recordset
  Set dbUsers = OpenDatabase("c:PESCEPESCE.mdb")
  sqlaction = "INSERT INTO table1 (field1) VALUES (1)"
  dbUsers.Execute sqlaction
  dbUsers.Close

VB SQL INSERT Statement (Help!)
I am trying to open two databases and read from a table in one and insert it's data into a table in the other.
The database I am inserting into is in the current database (CurrentDB) and the databaase I am reading from (RemoteDB) is in another directory.
If I have the table in RemoteDB linked in CurrentDB, so that RemoteDB table is resident in CurrentDB, the following code works just fine:

Dim CDB As New ADODB.Connection
Dim CRS As New ADODB.Recordset

Dim DataTo As String
Dim DataFrom As String
DataTo = "Inset_Field_X, Inset_Field_Y"
DataFrom = "FieldNameX = 'X' AND FieldNameY = 'Y'"

CDB.Open "CurrentDB"

CRS.Open _
"INSERT INTO tbl_Input_In_CBD (" & DataTo & ") SELECT * FROM tbl_Output_From_DB _
WHERE " & DataFrom & " ", _
CDB, adOpenDynamic, adLockOptimistic

But, if I don't link the table in CurrentDB but try to open RemoteDB directly, I cannot make it work.
I need help on the INSERT syntax (I haven't changed the INSERT in the example below. All I have done is added the two bolded lines).
Here is what I have done so far (it blows-up at the CRS.Open â€œINSERT....... Statement):

Dim CDB As New ADODB.Connection
Dim CRS As New ADODB.Recordset
Dim RDB As New ADODB.Connection

Dim DataTo As String
Dim DataFrom As String
DataTo = "Inset_Field_X, Inset_Field_Y"
DataFrom = "FieldNameX = 'X' AND FieldNameY = 'Y'"

CDB.Open "CurrentDB"
RDB.Open "RemoteDB"

CRS.Open _
"INSERT INTO tbl_Input_In_CBD (" & DataTo & ") SELECT * FROM tbl_Output_From_DB _
WHERE " & DataFrom & " ", _
CDB, adOpenDynamic, adLockOptimistic

Any help would be appreciated!
Thanks.

INSERT SQL STATEMENT
Hello everyone

Can anyone see a problem with the following insert statement?
I am getting an error message that says
"Syntax error in insert statement"

    strSQL = "INSERT INTO tblAuthorizedGroupsDetails (HECPID, Group ) "
    strSQL = strSQL & "VALUES("
    strSQL = strSQL & "'" & rsHECP.Fields("HECPID") & "',"
    strSQL = strSQL & "'" & txtAuthorizedUsers.Tag & "')"

Both HECPID and Group fields are defined as integer.

Thank you

Parygoal

SQL INSERT Statement In VB
What I'm trying to do is query an MS Access database and display all the results in a datagrid. That part is working fine, but I also want to insert the results into a table to keep separately from the original table. I'm having trouble with my SQL statement and keep getting errors. What I need to know is the syntax for using the SQL in VB. Should I declare a new variable as an ADODB.Recordset and use .Open? If so, what would be the syntax for it? Or is there another way to do this that I'm not seeing?

--CharonErebus

Help With INSERT Statement.
Hi.

I want to make an insert by selecting values in another table. Is this possible? This is the query I try to run, but it doesn't compile.

Code:
INSERT INTO
    HistoriqueAdresseMembre (
                            idMembre,
                            hamNomOrdinateur,
                            hamNomUsager,
                            hamNom,
                            hamPrenom,
                            hamNomFille,
                            hamTitre,
                            hamAdresse1,
                            hamAdresse2,
                            hamVille,
                            hamProvince,
                            hamPays,
                            hamCodePostal,
                            hamTelephone
                            )
VALUES (
         SELECT
            idMembre,
            'Site Web' AS NomOrdinateur,
            'Site Web' AS NomUsager,
            memNom,
            memPrenom,
            memNomFille,
            memTitre,
            memAdresse1,
            memAdresse2,
            memVille,
            memProvince,
            memPays,
            memCodePostal,
            memTelephone
        FROM
            Membre
        WHERE
            memNoPermis = @imemNoPermis
        )

This is the error I receive:

Msg 156, Level 15, State 1, Procedure P_U_MEMBRE_SITEWEB_ADRESSE, Line 47
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Procedure P_U_MEMBRE_SITEWEB_ADRESSE, Line 66
Incorrect syntax near ')'.

Thank you for helping.

Insert SQL Statement
I will try to explain this as best I can.

I have an MS Access 2000 database with 5 tables.

Table 1: Reports - Primary Key ReportID; AutoNumber
Table 2: Informants - Primary Key InformantID; AutoNumber, Foreign Key ReportID; Number
Table 3: Victims - Primary Key VictimID; AutoNumber, Foreign Key ReportID; Number
Table 4: Witness - Primary Key WitnessID; AutoNumber, Foreign Key ReportID; Number
Table 5: Suspects - Primary Key SuspectID; AutoNumber, Foreign Key ReportID; Number

I have created relationships from the Report table's primary key to each of the foreign keys of the other tables. As of right now the tables hold no data. (I am testing right now and do not want to mess up any real data.)

I have 5 forms I have made using VB.Net, you may be able to guess the names of these forms. frmReports is form 1, frmInformants is form 2, etc.

What I am trying to do is have the user of my application input the report info first, click the next button, the user then comes to the informant form and so on.

One of my problems is, how do I get the data into the report table with out getting an error because of the relationship with the informant table? Then how do I get the ReportID from the report table to insert it into the reportid of the informants table when the user is finished filling out the informants form?

I have referred to books, looked on the net and tried every combination of sql statements I can think of.

Anyone out there understand what I am trying to accomplish and provide some assistance?

Thank you

Insert Into Statement
Hi everyone

I have a table which contains details about a form. When a new form is created based on an already existing form (stay with me...) I need to carry the details of the old form over, by creating a new record with the old data. I generally just copy all the information into variables and then create a new record using those variables, but as the tables grow, so does the amount of code.

I stumbled onto the insert into statement this morning and decided to use it (since it's so much quicker) but I can't get it working because I need to copy all the columns except the primary key(which I need to specify). I just can't figure out how to insert the copied data AND insert the new, unique Primary Key into the same record.

Can some please let me know how to accomplish this?

Sweet As!

Gareth
_________________________________________________________

To the optimist, the glass is half full.
To the pessimist, the glass is half empty.
To the IT guy, the glass is twice as big as it needs to be.

Insert Into SQL Statement
Hello,

I've written SQL statements similar to this 100 times before, but for some reason I'm getting a run-time error on this one --"Syntax error in INSERT INTO statement"

Code:

cn.Execute ("Insert Into Alerts (AlertID, AlertName, AlertEnabled, TrunkGroup, CallType, AccountNumber, CallingNumber, CalledNumber, Duration, Cost, Action, ActionDetail1, ActionDetail2, ActionDetail3)" & _
"Values ('" & AlertID & "','" & AlertName & "','" & AlertEnabled & "','" & TrunkGroup & "','" & CallType & "','" & AccountNumber & "','" & CallingNumber & "','" & CalledNumber & "','" & Duration & "','" & Cost & "','" & Action & "','" & ActionDetail1 & "','" & ActionDetail2 & "','" & ActionDetail3 & "')")

When I debug the program, all of my variables in the values part of my sql statement appear to be fine..

Can someone see a problem with what I'm doing? Thanks...
Steph

SQL Insert Statement
Hi all ,

DO YOU KNOW HOW TO WRITE A STORED QUERY FOR AN Insert SQL statement in Visual Basic.and DAO for an ACCESS database.In ADO i know i can write a create procedure statement.

I am trying to update a table in a loop(in visual Basic , DAO and MSAccess database).I can use stored procedures.But the following does not work :::::::;

ALL DECLERATIONS HAVE BEEN DONE:::

Set qry3 = cnxn.CreateQueryDef("MakeTable", _
"PARAMETERS [CC] LONG , [MKTAREA] DOUBLE ;" & _
"Insert into DEMANDDATA (CustCombo , MKTAREA) Values (CC, MKTAREA)")

qdf4.Parameters(0) = rsOrdPats!CustCombo
qdf4.Parameters(1) = MktArea
qdf4.OpenRecordset

INSERT INTO Statement
Hello,

I'm looking to create an INSERT INTO statement that
takes info from a table in one database and add
it to the records in a identical table in another
database. I have what I thought would work but it
doesn't.

inVar = "C:Program FilesMicrosoft Visual StudioVB98
ew projectHindleHindle.mdb"

strExec = "INSERT INTO SerindexMaster IN '" & inVar & "' (VEHICLES, [CUST-ID], " _
& "[S-LTDATE], ENTRY, COLOUR, [IN SERVICE], TELEPHONE, LICENSE, MAKE, MODEL, " _
& "MILEAGE, [CUSTOMER NAME], SERIAL, KEY, TYPE, YEAR, [BUS-PHONE])" _
& " SELECT (VEHICLES, [CUST-ID], [S-LTDATE], ENTRY, COLOUR, [IN SERVICE], " _
& "TELEPHONE, LICENSE, MAKE, MODEL, MILEAGE, [CUSTOMER NAME], SERIAL, KEY, " _
& "TYPE, YEAR, [BUS-PHONE]) FROM tblPARSE"
MsgBox strExec
cnnMasterfiledb.Execute strExec

Its saying theres a syntax error, is there a limit to the number of columns??
or is something else wrong.

any help is good help...

thanks

INSERT INTO (SQL Statement With RDO)
Hi :-)

I would like to know how to insert a new row in a table with RDO programming ? Should I use the INSERT TO (SQL) or the .AddNew method. I tried both, without success.

Here's my code :
--------------------

Option Explicit
Dim conn As rdoConnection
Dim qy As rdoQuery
Dim rs As rdoResultset

Private Sub Form_Load()

Set conn = New rdoConnection

With conn
.Connect = "dsn=vernon"
.EstablishConnection rdDriverNoPrompt, True
End With

Set qy = New rdoQuery

qy.SQL = "select * from authors"

Set qy.ActiveConnection = conn
Set rs = qy.OpenResultset(rdOpenKeyset)

End Sub

Private Sub B_Add_Click()

' Here is How I use the .AddNew method
With rs
.AddNew
!au_id = T_au_id
!au_lname = T_au_lname
!au_fname = T_au_fname
!contract = T_contract
!address = T_address
!city = T_city
!state = T_state
!zip = T_zip
!phone = T_phone
.Update
End With

' And here is how I use the INSERT TO (SQL)
conn.BeginTrans
qy.Execute " insert into authors " _
& "(au_id, au_lname, au_fname, contract, address, city, state, zip, phone) values " _
& "(T_au_id.Text, T_au_lname.Text, T_au_fname.Text, T_contract.Text, T_address.Text, T_city.Text, T_state.Text, T_zip.Text, T_phone.Text);"
conn.CommitTrans

End Sub

---------------------------------

Thank you so much ! )

Duke1000 :-)

Using Insert Statement In VB
I have a table with two fields [Name] and
[Address].I want to use Insert statement to
insert values into the table thru' VB.But i am
encountering a problem while inserting using
Execute statement of the database object when
i have a single quote(') in the name .Also when
i accept the name from a textbox and enter the
name with single quote(') in the name, the statement is giving me an error.How to solve
the problem.

thank you
thamizhan

Email : marathamizhan@yahoo.com

Insert Statement
I need to make a query that uses a table (user). This user table has a bunch of rows in it that have an account # and then another column with a number in it. If that # is a 1, then I want to duplicate that row, but put the account number with a # 2. Can anyone help me out with this?

Thanks!
Erica

Insert-Statement With Varibales
Hi there,

i want to transfer a cellvalue / a row from a spreadsheet into table

x = Spreadsheet1.ActiveSheet.Range("c" & y - 1).Value
Set MyRecSet = MyConn.Execute("INSERT INTO test(test) VALUES (x)")

i always get a syntax error why ?

Thx Salva

INSERT SQL Statement Error In VB6
Hi all,

I am having a problem with my INSERT statement. I keep getting the error:

"No value given for one or more required parameters."

"dbs.Execute sql" is the highlighted statement in the error checking.

The variable is updating correctly and the SQL statement works fine within Access.

Here is my code:

Code:
'Addition of record to database
Dim dbs As ADODB.Connection
'Dim rs As ADODB.Recordset
Set dbs = New ADODB.Connection
Dim strConn As String
Dim strPath As String
Dim EnteredServerName As String
Dim EnteredCustomerName As String
Dim EnteredUserName As String
Dim EnteredSpecial As String
Dim EnteredPassword As String

EnteredServerName = Text2.Text
EnteredCustomerName = Text3.Text
EnteredUserName = Text4.Text
EnteredSpecial = Text5.Text
EnteredPassword = Text1.Text

strPath = "C:Documents and SettingsssimisteMy Documentspassword"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strPath & "password_database.mdb"
dbs.Open strConn
'rs.Open
sql = "INSERT INTO server_table (servername) VALUES (" & EnteredServerName & ")"
dbs.Execute sql
'rs.Close

CRLF In SQL INSERT Statement
I am attempting to create a series of INSERT lines in an SQL script to act as a method of transferring data from one database to another.
My problem is that I one of the fields being output is a multi-line containing CRLF characters. I know that to work properly strings containing a ' character need to be mapped to ', but what about the CRLF characters. What do I need to map these to in the output string.

Regards

Problem With INSERT Statement
I'm having a problem with the insert statment with this subroutine:

Code:
Private Sub cmdSaveNote_Click()
Dim pstrSQL As String
Dim prospectID As String
Dim username As String
Dim noteDate As String
Dim note As String

cmdEditNote.Visible = True
cmdDeleteNote.Visible = True
cmdNewNote.Visible = True
cmdBackNoteArrow.Visible = True
cmdForwardNoteArrow.Visible = True
cmdSaveNote.Visible = False

prospectID = txtProspectID.Text
username = txtNoteUser.Text
noteDate = txtNoteDate.Text
note = txtNote.Text

pstrSQL = "INSERT INTO tbl_prosptect_notes " & _
"(prospectID,UserLoginName,Date,Note)" & _
" VALUES ('prospectID' , 'username' , 'noteDate' , 'note' )"
gdbAAIProspects.Execute pstrSQL
End Sub
Every time I go to hit the save button it get the following message:

Quote:

Run Time Error '3134':
Syntax Error in INSERT INTO statment

What ever is wrong it's not jumping out at me. Could someone point out my error?

Insert Statement Error
Hey all
I have a table with 3 fields
Rate File ITemVal
1 File23 0.235 (1st value)
2 File23 8.565 (2nd value)
| | | |
120 File23 2.256 (120th value)

The rate field is primary and autonumber, each file has about 120 values

I wrote a sql statement that would replace the ItemVal field for a particualr file with eith 0 or some other calculated value.. I used insert as well as update but m gettin

Error:

Run-time error '-2147217900 (80040e14)':
Syntax errpr in UPdate Statement

plz help

Call DataOpen(goConn) 'open data connection

'reads filename from text box into strfilevalue
strFileSearch = "[RATE_FORECAST].FileNameId Like '%" & strFileValue & "%'"

'insert statement
sqlShock = "INSERT INTO RATE_FORECAST (ItemVal) VALUES(0.000) SELECT ItemVal FROM RATE_FORECAST WHERE" & strFileSearch
ShockRS.Open sqlShock, goConn, adOpenForwardOnly

"THIS ONE GAVE ME AN ERROR THAT SEMICOLON WAS MISSING"

sqlShock = "UPDATE TABLE RATE_FORECAST SET ItemVal = 0 WHERE" & strFileSearch
ShockRS.Open sqlShock, goConn, adOpenForwardOnly
"THIS ONE GAVE ME AN ERROR
Run-time error '-2147217900 (80040e14)':
Syntax errpr in UPdate Statement"