Library tutorials & articles
SQL Commands
- Introduction
- Getting Records
- Adding Records
- Modifying Records
- Deleting Records
Adding Records
To add a record to the database, you use the INSERT INTO command. First you give the name of the table, and then you list the values for the fields. For example,
INSERT INTO Users VALUES (10, 'James Crowley', 'PASSWORD')
will add a new record to the Users table, filling the fields with 10, and James Crowley. Obviously, in this case, you need to know the order in which the fields come, and you can't miss any out. A better way is to specify the field names, thus telling the database what order you are giving the values for the fields in, and also which fields you want to include. For example
INSERT INTO Users (UserName, UserID) VALUES ('James Crowley',
10)
Note that in the above example, the UserID is given last, and the Password field is ignored. If you do miss any fields out, they will remain Null, or be given their default value. Please note that if a field is an AutoNumber, you should not include it when adding the records. The database will automatically assign a new value to it. For example, if the UserID field was an AutoNumber field (which would be very likely, as it would need a unique number for each record), you would use something like this:
INSERT INTO Users (UserName, Password) VALUES ('James Crowley',
'PASSWORD')
James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audience ...
Related articles
Related discussion
-
Error Msg Description ?
by morizan (0 replies)
-
How to Change Default exe Icon in C#.net Windows Application
by sonali.terse (2 replies)
-
Web App Service Project - Assistance Requested
by JusticeV (0 replies)
-
Need Help for SQL query
by osmancarik (6 replies)
-
VB.net ms SQL 2005 express :- couldn't enter data into database
by sanjeev58 (0 replies)
Related podcasts
-
Rocky Lhotka on Data Access Mania, LINQ and CSLA.NET
Scott talks with developer and author Rockford Lhotka about the attack of the DALs (Data Access Layers). How can we put LINQ to SQL, LINQ to Entities and classic multi-tiered design all into a larger context? What's the right strategy for your data access needs? Scott's got questions and Rocky's ...
How to save Symbols, example integral, Sigma ect in Sql Server 2000.
Plzzzzzz help me out with this problem, i have to store special symbols like integral, sigma, pi etc in tha database. I'm using the nvarchar datatype, but getting unexpected results..
Thanks in advance...........
This seems simple, yet I can't find how you do this. I need to run a query in VB on a very simple database. There is the possibility that the query would be unsuccessfull, and if so I need to do other actions, but I can't seem to figure out how to check for this and run other code, I just get a Runtime Error '3061' Too few parameters. Expected 1.
Here is my code:
Dim MailDB As Database
Dim RS As Recordset
Dim SQL, eValue, PostieConfig, PP As String
' ----------------- '
'if notify hasn't been set, use default value
' ----------------- '
If notify = "" Then
notify = "System"
End If
' ----------------- '
' Open the Database on the Scheduler (currently NM16)
' ----------------- '
Set MailDB = OpenDatabase("\\ORMNM16\DB$\email.mdb")
' ----------------- '
' SQL will retrive all email addresses
' matching system table and notify columns
' ----------------- '
SQL = "Select Email From " & system & " Where " & notify & "=True"
Set RS = MailDB.OpenRecordset(SQL)
' ----------------- '
' Verify a record exists and retrive first address
' ----------------- '
If Not RS.EOF Then
If Not RS!Email = "" Then
eValue = RS!Email
Else
eValue = "nadcops@flowserve.com"
notify = notify + " Not Found in Email Database"
End If
End If
MsgBox eValue
' ----------------- '
' Loop through rest of addresses until verfication is complete
' ----------------- '
Do While Not RS.EOF
RS.MoveNext
If Not RS.EOF Then
eValue = eValue + "," + RS!Email
End If
Loop
' ----------------- '
' Close Database and Recordset
' ----------------- '
RS.Close
MailDB.Close
::
See I need to query the database to know who needs to get the email, dependent on notify returning true, and this works great if the script that calls this has a valid notify value.
But there is a very very good chance that someone would put the wrong notify value in, or not include it in the DB, and what I would like to do, is if the query returns nothing to check for that so I can do other things (like send me an email saying this notify value doesn't exist in the DB)
you need to fire sql statement like this
select idno, name, email, return from [tablename] where [yourcondition]
this will return record set lets say "rs" is object of recordset
then you will need to check this in vb using next statement
if rs("return")="Y" then
'do something based on values you already have in recordset
'like strEmail = rs("email")
else
'do something else
'or do nothing
end if
this should solve your problem
Basically am trying to search a database by getting an input from the users selection on a list.
the list contains names of countries and when i click that search button, how can i get the result of the search, say like i click paris and in the database, the flight to paris has 8 more available seats and the date, how can i show this information, by using a datagrid? if so how can i link, i know there are alot of "how's" in there but i would be realy and trually greatful if u can spare a minute.
hey, i see how i can fill a datagrid with data from an sql table, but i am having trouble figuring out how to do the exact opposite, take data from a datagrid and fill an sql table with it/overriding the previous table, or just updating the previous table(adding new entries, changing changed one, and deleting deleted ones)
any help would be greatly appreciated
hi,
pls help as i'm new in asp. i'm developing the administrative part of a website for my project. i'm having trouble with the "updating data" part. for example, i have done the 'add staff' and "delete staff' part with no problem. but how do i modify or update the data that i have previously added from the webpage. below are my codings. i have separated it into two asp files, the frmmodify.asp & frmmodifyprocess.asp. pls help.
frmmodify.asp
<html>
<head>
<title>modify customer</title>
</head>
<body>
<%
Set myconn=server.createobject("ADODB.Connection")
dbpath="C:\Inetpub\wwwroot\final\greatscape.mdb"
myconn.open "Provider=Microsoft.Jet.Oledb.4.0;Data source=" &dbpath
name=request("name")
address=request("address")
phone=request("phone")
Email=request("email")
hphone=request("hphone")
postcode=request("postcode")
city=request("city")
cusid=request("cus_id")
set rs= Server.CreateObject("ADODB.Recordset")
sql="Select * from Customers where cus_id=" &cusid
rs.Open sql,myconn,1,2
rs.update
rs("name")=name
rs("address")=address
rs("phone")=phone
rs("email")= Email
rs("hphone")=hphone
rs("postcode")=postcode
rs("city")=city
rs.update
%>
<form action="frmModify.asp" method="post">
Name <input type=text name="name" value="<%=rs("name")%>">
<br>
Address <input type=text name="address" value="<%=rs("address")%>">
<br>
Phone <input type=text name="phone" value="<%=rs("phone")%>">
<br>
Email <input type=text name="email" value="<%=rs("email")%>">
<br>
Postcode <input type=text name="postcode" value="<%=rs("postcode")%>">
<br>
City <input type=text name="city" value="<%=rs("city")%>">
<br>
Hphone <input type=text name="hphone" value="<%=rs("hphone")%>">
<br>
<input type=submit value="Modify">
</form>
<%
myconn.close
set rs=nothing
%>
<a href="viewcustomer.asp">View Record Updated</a>
</body>
</body></html>
frmmodifyprocess.asp
<%
dim myID
Set myconn=server.createobject("ADODB.Connection")
dbpath="C:\Inetpub\wwwroot\final\greatscape.mdb"
myconn.open "Provider=Microsoft.Jet.Oledb.4.0;Data source=" &dbpath
myID=Request.Form("cus_id")
sql= "UPDATE Customers SET name='" &_
Request.Form("name") &_
"', address= '" & Request.Form("address")&_
"', phone= '" & Request.Form("phone")&_
"', state='" & Request.Form("state") &_
"', hphone='" & Request.Form("hphone") &_
"', postcode='" & Request.Form("postcode")&_
"', city='" & Request.Form("city")&_
"', WHERE cus_id= " &myID
myconn.execute(sql)
myconn.close
set myconn=nothing
%>
<a href="viewcustomer.asp">View Record Updated</a>
<html>
<head>
<title>modify customer</title>
</head>
<body>
<BODY background="images/nabkgnd.jpg">
<center>
<H2>modify customer</H2></CENTER>
<form id="Update" action="frmModifyProcess.asp" method="POST" >
<DIV align=center>
<TABLE cellSpacing=0 cellP
Hi,
I need to get the fields IDNO, Name, Email ID of a record...if the value of the field RETURN="Y". I have to use SQL statements for this. I'm totally new with this...plz help me...
Also please tell me how to execute these commands in my program...
Thanks in advance
-Athen
WOW someone replyed to my post
thx for tha reply but i wrote that message a long time ago and have learnt heaps since then and i now have my own site with members using .asp but thx anyway.
Well, you need a starting point, I'm not going to give you ALL the code, that would take me to long.
I'll assume you have some back ground in HTML and in VB, and if you don't, you in trouble.
html works with the file extension of .html or .htm If you want to use VB to create you web site the extension changes to .asp or .aspx or many others.
To use .asp you need another application or web host to generate the page once you've drop the code in.
This site has a server that does it, it does the (A)ctive (S)erver (P)age bit and hides all the vb code so that the server only sends a file that the user can read.
james crowly has made a tutorial on how to make a members section, and its how I started so it got my site to where it is now, with a lot of changes and more complexity, but it starts some where. Follow his tutorial somewhere in the Web Directory (up the top of this window) and follow it carefully
you'll need MS Access of some version.
Or bind to SQL similar to "SELECT * FROM [Table Name] WHERE [Last Name Field]='" & strLastNameToFind & "'" instead of "SELECT * FROM [Table Name]" and refresh the controls on your form.
Hope this helps.
you can use the FINDFIRST method of the recordset to search for a specific
record in your database (that is access database)
data1.recordset.findfirst "LASTNAME = 'ARAOJO'"
I have tried to intert into SQL server using ado and stored procedures from VB.
Type of the primary key(named intAgreementID) of the table is: int
Code of the Stored Prosedure is:
'Create procedure spJingClaim
as
Select *
from tblAgreement
GO'
The VB code is:
'Private Sub cmdConnect_Click()
Adodc1.ConnectionString = _
"Provider=SQLOLEDB.1; Persist Security Info=False; User ID=sa; Initial Catalog=Compol"
Adodc1.CommandType = adCmdStoredProc
Adodc1.RecordSource = "spJingClaim"
txtAgreementID.DataField = "intAgreementID"
Set txtCustomerName.DataSource = Adodc1
End Sub
Private Sub cmdAdd_Click()
On Error GoTo ErrLabel
Adodc1.Recordset.AddNew
txtAgreementID.Text = ""
Exit Sub
ErrLabel:
MsgBox Err.Description
End Sub'
The error message when trying to type in new record into the field 'AgreementID', which is the primary key in the SQL server table was:
'Field not updatable, Bound Property Name: Text, Field Name: IntAgreementID'
Would anyone know how to add new records into a table including its primary key via Stored Procedures?
Thank you very much!
Your Code is fine, but you are adding a quotes to the intID in your where statement. This is passing a string value to the query since this is a numeric field you can't pass a string value, you will get a datatype mismatch error.
Just set the ParishINo = " and the value
strSQL = "UPDATE ChurchData SET ParishAddr1 = '" & txtChurchAddr1.Text & "' WHERE ParishIDNo = " & intID
It should be in the menu under "project".
Click on references.
DAO is listed under "Microsoft DAO 3.6"
HELPFUL?
I am trying to add pictures into a database.
I made a basic access table to be used in vb.
I don't know sql.
And I also need to setup a text box to act with my data
object. I mean i wanna type in "123" and go to the row that starts with "123"
I am looking for the easiest solution.
Can anyone help me?
www.b2bproject.net ------Still working on it! But you may find something interesting!
I am having trouble using SQL code inside Visual Basic. I am trying to Update an Access File. Below is a portion of code I'm trying to use. The field in the Access DB that I'm using as the key to make the update "ParishIDNo" is an
"AutoNumber" field. I'm not sure how to define the value that I'm using in the VB Window so that there is no conflict in datatypes, which seems to be my problem since that is the "Error" coming back.
Any suggestions and or sage advice?
Reach me at "kpersan@hotmail.com"
Thanks.
Private Sub cmdPUpdt_Click()
Dim myConnection As New ADODB.Connection
Dim myRecordset As New ADODB.Recordset
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Ken Persan\AccessDB\ParishData.mdb;Persist Security Info=False"
myConnection.Open ConnectionString
myRecordset.Open "ChurchData", myConnection
Dim intID As Integer
intID = CInt(txtChurchID)
strSQL = "UPDATE ChurchData" _
& " SET ParishAddr1 ='" _
& txtChurchAddr1.Text & "'" _
& " WHERE ParishIDNo ='" _
& intID & "'" & " "
Set myRecordset = myConnection.Execute(strSQL)
hey, i am having the same problem, only i am useing DAO. This is an example of what i have and need asuming that my table name is Locks and a field within it is 'Lock Number' i want to filter the recordset to all records that contain the value from the textbox, (txtnum.text)...
Public ws As Workspace
Public db As Database
Public rs As Recordset
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("c:\windows\desktop\safelock\safelock.mdb")
Set rs = db.OpenRecordset("SELECT * From Locks WHERE
Lock Number= txtnum.text")i've also tried...
Set rs = db.OpenRecordset("SELECT * FROM Locks WHERE 'Locker Number' LIKE '" & txtNum.Text & "'" & "")
please help me asap,
thanks,
Kimberly
I want to have a website where people have to sign up and login.
i have figured out that i need to use these databases and it talks about tables and stuff. but how do i make these tables and where do i have to put em?
sorry.. erm i refer this to mr abdul razak answer.. i dunno where to put the code that u wrote and what if i use adodc to connect with database. can anybody help me find the coding?
where should i put the coding? in the form load of the main form or in the selection form? or should i put the code in the command button? plase answer me!! i really need to know this
What is the code for inserting multiple records into a table under the same fields? I'm able to insert one record at a time with the following code:
insert into test(did)
values('9999999999999');
How do I insert multiple records into the same table, same field.
Thanks in advance
i'm using vb 6.0 entreprise edition but i just couldn't find the dao component...can anyone help????
thanks
you have a table called SALES
One field of the table SALES is ITEM
Command for acheiving this from VB is
Declare variables rs as ADODB.Recordset, con as ADODB.connection in the general section
Initialise variable as follows in the form load event
set rs=new adodb.recordset
set con=new adodb.connection
rs.open "select * from sales where item=" & textitem.text , con, adopendynamic,adlockpessimistic
This will work
Thanks,
Abdul Razak
I have the same doubt!!!
I'm the n00bie of the n00bies with VB
Please help with some fresh easy code for extreme beginners
In ms access i have a form in which the users search for a specific record. I want to write an sql statment that uses the information that the user types into the textbox and SELECT it in the table.
EX:
Select * from Master_DBase where Comments1 = (info in textbox)
How would i do this?
I want to use several tables to connect to VB.net,but I don't know how to do it, I can only connect 1 table. I preciate your help.
thank you!
and for exp
strSQL = "select * from table1 union (select * from table2) union (select * from table3)
adodc1.recordsource=strSQL an for manipulation with one table you can use
adodb.connection
I'm am trying to use the FORMAT command to sort through times in a date/time field in MS Access. What I need is all records where the time is between 8:00 am and 7:00 pm. I'm assuming that I have to use the format command in the query, but I cannot find the syntax anywhere.
Thanks for your help.
Same with deleting. in your example, set all ranks that = 8 to set = 7 (this is assuming that you have decided what to do with 7 ranks if any exists). Set the 9 to 8, ect.
You would have to repeat the command to insert
UPDATE Table1 SET Table1.rank = 16
WHERE (((Table1.rank)=15));
Decrementing the values of 16 and 15 until you reached the point that you want to insert.
There may be an easier way, I'm sure... but this should work in your case. (I would hate to do this for something that has 1000 rankings)
I have a table with a fieldname "Ranks", which is an integer. Let's say I have all the entries for items ranked from 1 - 15, and suddenly I want to insert a new entry which should be ranked 5th. How can I insert that new entry to be rank #5, and all the entries which was previously ranked 5 and above be automatically incremented to be ranked 6 - 16? The same way applies if I were to remove an entry in the middle (let's say rank # 7). So that all the items which was previously ranked 8th and above be decremented.
Thanks
Adrian
I create three tables in access and I joined them ans I also created two queries in access .I displayed my queries by using Datagrid & Adoc control , my problem is to search through the datagrid and I don't know how
I tried using this coding:
I want to search through the datagrid, how many times does the name apprear on the database.
Do while not eof(Datagrid1)
if Ucase(txtName.text) = Ucase(Datagrid1) then
counter = counter +1
loop
Please help!
I m a new member, so today I read out ur problem about append table in SQL. If you still have same problem tell me. I'll give u solution.
naqvi_haider@arabia.com
I need to link three Access tables in Visual Basic in one ADO data control.
I also need to manipulate data in one of these tables.
Can you help me, please?
I really appreciate your help!
Fernanda
if the Excel file's format to keep data is consistent then access this through OLEDB Driver for Excel.
just find out date n replace it with new one by using
update tablename set date=todate(1/1/1976,"dd/mm/yyyy") where date=todate(1/1/1992,"dd/mm/yyyy")
check if it works
Good Morning Everyone;
A quick question. I have a column in a table that has a date of 1/1/1992 repeated 250 times I want to replace that with 1/1/1976 what is the sql command to use to do that.
Thank you in advance
I have an Excel spread sheet that I want to tranform the data to an SQL serv DB. I am trying to use the SQL Enterprise Manger import wizard to accomplish this. But unfortunately I am running into a slight problem. the following are the steps that I take:
1) Select DTS Import Wizard
2) Select Excel 8.0 as my data source
3) Select my existing SQL db as my destination.
4) Select Copy Table
5) Click on Transform. This is where I am encountering the problem. The wizard does not allow me to selet the Append row option. It automatically selects the create a table option.
My question is how can I go about selecting the append option instead of the create new table option.
Thanks in advance
I have an excel spread sheet that I want the data from the columns populated into my database. Can someone help me with the script for that.
Thanks in advance
I run a SQL command and I get the result on the screen, but when I try to print the result the only thing that gets printed is the SQL command and not the result. WHAT AM I DOING WRONG?????????????????????????
Thanks:
What SQL Server are you running??
Antoniol
I tried the command but kept on getting the following message>
"Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'INFORMATION_SCHEMA.Columns'."
Thanks
Maybe this will help you...
1) Is there a command that will display a detail of a table, in other words the fileds in a table with its field type and length????
A: SELECT *
from INFORMATIONSCHEMA.Columns
where tablename = 'your_table'
2) How can I comment out a couple of execution lines.
A: If you want to comment out lines you can use these:
-- Code (this will commnet everything to the right of the 2 minus signs
or
//* Code *// (this will comment everything between them)
Hope this help you.
Antoniol
I am very new to SQL. I have a couple quick questions.
1) Is there a command that will display a detail of a table, in other words the fileds in a table with its field type and length????
2) How can I comment out a couple of execution lines.
Thanks
hi..
I am trying to delete a record from a MS access format database using DAO. The problem I am facing is that after deleting the record if I try to insert a new record then the unique id for this record does not start from the beginning.
The unigue id(FileID) is created using
moDaoPrjTableDef.CreateField("FileID",dbLong,4,dbAutoIncrField);
where moDaoPrjTableDef is an object of type CDaoTabledef.
If there is initially one record in the table having FileID 1 and it is deleted . Now on inserting a new record the FileID taken is 2 thouhg it is the only record in the table.
Please suggest me how to get it started from 1 rather than 2
Regards,
Ashima
Oops...nevermind...I just found the answer in another article.
Can I know that before I use the SQL statement how can I connect the database like MS Access how to link it?
Create a system DSN pointing to the Access/SQL database then connect with something like:-
dim cnn, rst, sqltext
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DSN=MyDatabase
sqltext = "SELECT * FROM TableName WHERE Id="1"
rst.Open sqltext,cnn,3,3
...
do something with the returned records
...
rst.close
set rst = nothing
set cnn = nothing
if it is SQL / Sybase database, execute below query:
select * From [table name] where [field name] is like "%sabeer%"
Where:
[table name] is the name of table
[field name] is the nameof a field you want to search in for a string and
sabeer in "%sabeer%" is a string u r looking for
This thread is for discussions of SQL Commands.