Community discussion forum
SQL Commands
-
This thread is for discussions of SQL Commands.
-
-
how do you get a record that contains a a string but the field does not equal the string?
-
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 -
Can I know that before I use the SQL statement how can I connect the database like MS Access how to link it?
-
Quote: [1]Posted by jaychow on 7 Jul 2002 01:19 PM[/1]
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 -
Will that work if 'sabeer' is the first part of the searched string (ie. 'sabeer123') ?
Oops...nevermind...I just found the answer in another article.
-
Yes it should work!
-
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 -
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 -
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 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 -
What SQL Server are you running??
Antoniol -
Some one sent me the SQL code to display a table schema, but unfortunately I cannot find it any more. Would someone be kind enough and send me the command to display table schema in a database.
Thanks: -
Help I need some Help!!!!
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????????????????????????? -
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 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 -
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 -
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 -
if the Excel file's format to keep data is consistent then access this through OLEDB Driver for Excel.
-
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 -
To, nnadimi19
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 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! -
Hi:
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 -
This is a kind of dirty way to do it, but I would update all the ranks that = 15 to set = 16, then update all the ranks that =14 to set=15, ect. until you get to the point where you want to insert the rank (in your example: 5).
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) -
Hello!
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.
-
You can use union command in sql so you will get three linked tables as one
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 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! -
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 have the same doubt!!!
I'm the n00bie of the n00bies with VB
Please help with some fresh easy code for extreme beginners
-
Your Database name is SHOP.MDB
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'm using vb 6.0 entreprise edition but i just couldn't find the dao component...can anyone help????
thanks -
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 -
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
-
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?
-
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? -
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 WHERELock 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 put together a database at work using VB.Net and Access. I can get it to scroll through each record in consecutive order through my VB program, but how do I get it to go to a specific record without having to scroll through the entire database? Thanks!!!
-
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)
-
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! -
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 -
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! -
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'" -
6 years agoby
LouisRose
Louis Rose
Yorkshire / Midlands, England, United KingdomJoined 7 years agoOr 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. -
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. -
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. -
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 -
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 -
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 -
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. -
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 -
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)
-
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...........
Post a reply
Related discussion
-
Extracting the Country from the IP Address:- How To?
by jerryfox004 (11 replies)
-
How to Export Datagridview contents to Excel
by BarbaMariolino (8 replies)
-
Speech Recognition
by jitvinder (153 replies)
-
Bin Packing
by OJMZLOV (21 replies)
-
SendKeys Command
by hal999 (35 replies)
Related articles
Quick links
Recent activity
- emma limei replied to How to Download and Play Yo...
- jump tracy replied to How to transfer or copy so...
- Mark Neal replied to Pavtube Blu-ray Ripper for ...
- tt zhao replied to Which Blu-ray should i choose?
- tt zhao replied to Which Blu-ray should i choose?
- Cherry Chan replied to Which Blu-ray should i choose?
Enter your message below
Sign in or Join us (it's free).