Having problems with "Insert Into" command 
Author Message
 Having problems with "Insert Into" command

Hi, I'm using VB 4.0 professional 32 bit version and I'm trying to
create a databse with a few tables.  Now I have no problem making the
tables but I'm having trouble filling the tables with data.  What I have
right now is a table (called Sounds) which has two fields.  When I
specify both field names in my SQL Insert Into statement it accepts the
data that I give it.  However, if I leave out a field name in the table
then it gives me an error.  I thought that any fields left out of a
record are skipped and Nulls are stored into them.  I have provided the
sample code I use to enter the information into the database.  Can
someone tell me why I need to specify all the field names in a table
when I use the Insert Into command?

The sql_com' statement below does not work unless I also specify the
other field in this table, i.e.

sql_com="INSERT INTO Sound(Music_Type,Group) VALUES(""Type A"",""Group
A"")"

Why is this?


Private Sub Command2_Click()

    Dim Wrkspace As Workspace
    Dim Music_DB As Database
    Dim NewTable As TableDef
    Dim rec_list As Recordset
    Dim sql_com As String

    'Set aside the workspace region for the database
    Set defwrkspace = DBEngine.Workspaces(0)

    'Now open the existing database
    Set Music_DB = defwrkspace.OpenDatabase("c:\dosvol\work\vbasic\database\newdb.mdb")

    'Insert some data into the database
    sql_com = "INSERT INTO Sound(Group) VALUES(""Group A"")"
Debug.Print "Search String:"; sql_com

    'Now perform the command on the database
    Music_DB.Execute sql_com, dbFailOnError
Debug.Print "SQL command executed"

    'Close the database
    Music_DB.Close
Debug.Print "The database is closed....."

End Sub



Sat, 30 Jan 1999 03:00:00 GMT  
 Having problems with "Insert Into" command

Does you database have the "Accept Nulls" for the fields set to True or
False?

Shelly
--
Trying to help others here as I have been helped.

Amar Kulshrestha

Quote:
> then it gives me an error.  I thought that any fields left out of a
> record are skipped and Nulls are stored into them.  I have provided the



Sat, 30 Jan 1999 03:00:00 GMT  
 Having problems with "Insert Into" command

Quote:

>Hi, I'm using VB 4.0 professional 32 bit version and I'm trying to
>create a databse with a few tables.  Now I have no problem making the
>tables but I'm having trouble filling the tables with data.  What I have
>right now is a table (called Sounds) which has two fields.  When I
>specify both field names in my SQL Insert Into statement it accepts the
>data that I give it.  However, if I leave out a field name in the table
>then it gives me an error.  I thought that any fields left out of a
>record are skipped and Nulls are stored into them.  I have provided the
>sample code I use to enter the information into the database.  Can
>someone tell me why I need to specify all the field names in a table
>when I use the Insert Into command?
>The sql_com' statement below does not work unless I also specify the
>other field in this table, i.e.
>sql_com="INSERT INTO Sound(Music_Type,Group) VALUES(""Type A"",""Group
>A"")"



To insert the contents of a variable:

Remember when you build your SQL commands to imagine what they would
look like when you print them. God bless the de{*filter*}.

/*

Computing Analyst
Oak Ridge National Laboratory
http://www.*-*-*.com/
*/



Sat, 03 Jul 1999 03:00:00 GMT  
 Having problems with "Insert Into" command

Hi Amar,

Your question revolves around skipping fields (columns in SQLese).  This is
allowed unless you have a column which is defined as "NOT NULL"  and it is
also NOT given a Default value.  If are working with SQL Server, you can
check the columns with sp_help <tablename>.  This will indicate whether
there is a column so defined.  If you are working with Access, in database
design view you can determine if the field is REQUIRED and has no default
value defined.

Hope this help, good luck :-)

Jerry Hill



Quote:

> >Hi, I'm using VB 4.0 professional 32 bit version and I'm trying to
> >create a databse with a few tables.  Now I have no problem making the
> >tables but I'm having trouble filling the tables with data.  What I have
> >right now is a table (called Sounds) which has two fields.  When I
> >specify both field names in my SQL Insert Into statement it accepts the
> >data that I give it.  However, if I leave out a field name in the table
> >then it gives me an error.  I thought that any fields left out of a
> >record are skipped and Nulls are stored into them.  I have provided the
> >sample code I use to enter the information into the database.  Can
> >someone tell me why I need to specify all the field names in a table
> >when I use the Insert Into command?

> >The sql_com' statement below does not work unless I also specify the
> >other field in this table, i.e.

> >sql_com="INSERT INTO Sound(Music_Type,Group) VALUES(""Type A"",""Group
> >A"")"



Sat, 03 Jul 1999 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. "insert into" command

2. FoxPro ODBC "INSERT" problem

3. ODBC vfpro "INSERT INTO" problem

4. Problem with "Insert into......"

5. Problem With "window.showmodaldialog("")"

6. Acc97: Problems using "Command" variable

7. Problem automating "Tools, Send" command

8. Synchronized problem about "SHELL command"

9. Problem about "Write" command

10. Having something like a "table"

11. *"*-.,._,.-*"* I"LL TRADE VISUAL C++ FOR VBASIC *"*-.,_,.-*"*

12. RECORDSET "addnew" and "insert to" problem

 

 
Powered by phpBB® Forum Software