-
Aug 20th, 2009, 06:43 AM
#1
Thread Starter
Lively Member
[RESOLVED] Need Help With SQLSTRING's (VB6)
How Do DROP as table with ADO if table exists and Create field if dont exist's
ADO Code:
sqlstr = "DROP TABLE Employees"
error's if it doesnot Exist's
similarly
ADO Code:
' Create the Employees table.
conn.Execute _
"CREATE TABLE Employees(" & _
"EmployeeId INTEGER NOT NULL," & _
"LastName VARCHAR(40) NOT NULL," & _
"FirstName VARCHAR(40) NOT NULL)"
error's if EmployeeID or else field's exist's
please help me out !!
-
Aug 20th, 2009, 07:06 AM
#2
Re: Need Help With SQLSTRING's (VB6)
You can use the EXISTS function to determine if the table exist before dropping it.
Code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO
To determine if a field exist before adding it.
Code:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Employees’ AND COLUMN_NAME = ‘LastName’)
BEGIN
ALTER TABLE Employees ADD LastName VARCHAR(40) NOT NULL
END
Last edited by dee-u; Aug 20th, 2009 at 07:48 AM.
-
Aug 20th, 2009, 07:28 AM
#3
Re: Need Help With SQLSTRING's (VB6)
Another aproach would be to just ignore that particular error by catching it with an 'On Error Go To' then issuing a 'Resume Next' if it's the particular error in question (nb, not the same as using On Error Resume Next). That would save you a server round trip.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 20th, 2009, 07:37 AM
#4
Re: Need Help With SQLSTRING's (VB6)
Originally Posted by FunkyDexter
Another aproach would be to just ignore that particular error by catching it with an 'On Error Go To' then issuing a 'Resume Next' if it's the particular error in question (nb, not the same as using On Error Resume Next). That would save you a server round trip.
Sorry but I am not sure why would that save a server round trip.
-
Aug 20th, 2009, 07:42 AM
#5
Re: Need Help With SQLSTRING's (VB6)
Because he won't have to make a call to see if the table exists so there's only ever one call whereas if he checks to see if the table exists and finds it doesn't he still has to make a call to create it.
If he puts the check and the create in a stored procedure then it would only need one call but looking at his original post it looks like he's issuing direct SQL and handling the error client side.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 20th, 2009, 07:51 AM
#6
Re: Need Help With SQLSTRING's (VB6)
Can't he send the script together with the checking in one go? This works when I tested it.
Code:
Dim strSQL As String
strSQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " & _
"drop table [dbo].[Test]"
adoConn.Execute strSQL
MsgBox "done"
-
Aug 20th, 2009, 07:56 AM
#7
Re: Need Help With SQLSTRING's (VB6)
Actualy that's true.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Aug 20th, 2009, 09:40 AM
#8
Thread Starter
Lively Member
Re: Need Help With SQLSTRING's (VB6)
Ill Post My Code Snippet, actually my project is on a different PC that dont had INTERNET access i got this problem last nite !!
ill post my code may be u guy's get some tweak in, i have written too much for small stuff :|
-
Aug 20th, 2009, 09:41 AM
#9
Thread Starter
Lively Member
Re: Need Help With SQLSTRING's (VB6)
note : i am not using ADO Control, just using reference to MS ADO Component 2.7
vb Code:
strSQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " & _ "drop table [dbo].[Test]"
have never even seen such complicated query )
-
Aug 20th, 2009, 09:59 AM
#10
Re: Need Help With SQLSTRING's (VB6)
I did not have to think of that query, Enterprise Manager spews it out when you get the sql design of a table.
-
Aug 20th, 2009, 10:04 PM
#11
Thread Starter
Lively Member
Re: Need Help With SQLSTRING's (VB6)
Originally Posted by dee-u
You can use the EXISTS function to determine if the table exist before dropping it.
Code:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO
To determine if a field exist before adding it.
Code:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘Employees’ AND COLUMN_NAME = ‘LastName’)
BEGIN
ALTER TABLE Employees ADD LastName VARCHAR(40) NOT NULL
END
these queries didnt worked !
-
Aug 20th, 2009, 10:08 PM
#12
Thread Starter
Lively Member
Re: Need Help With SQLSTRING's (VB6)
thought i manage to get my program working.
i feel that i have written much for small thing, may be if any of u can tweak around codes
mADO.bas Code:
Option Explicit
Public ADOCON As ADODB.Connection
Public ADORECORD As ADODB.Recordset
Public Enum dbErrorType
[Connected] = 1
[PasswordChanged] = 2
[InvalidFile] = 1001
[InvalidPassword] = 1002
[FieldNotExist] = 1003
[NoTableFound] = 1005
[AlreadyConnected] = 1004
[zElseError] = 999
End Enum
Public Function ConnectToDB(ByVal dbPath As String, Optional ByVal Password As String = "") As dbErrorType
Dim conStr As String
On Error GoTo HanDle
GoTo DoConnect
DoConnect:
conStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=" & Password & ";"
ADOCON.CursorLocation = adUseClient
ADOCON.Mode = adModeReadWrite
ADOCON.Open conStr
If ADOCON.State = 1 Then ConnectToDB = 1: Exit Function
HanDle:
If Err.Number = 0 Then Exit Function
ConnectToDB = GlobalError(Err.Number)
Err.Clear
End Function
Public Function ChangeDatabasePassword(dbPath As String, oldPassword As String, newPassword As String) As dbErrorType
Dim sqlStr As String, conStr As String
On Error GoTo HanDle
conStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=" & oldPassword & ";"
If ADOCON.State <> 0 Then ADOCON.Close
sqlStr = "ALTER Database Password " & newPassword & " " & oldPassword & ";"
ADOCON.Mode = adModeShareExclusive
ADOCON.Open conStr
ADOCON.Execute sqlStr
ADOCON.Close
databasePassword = newPassword
Call mADO.ConnectToDB(dbPath, newPassword)
If ADOCON.State = 1 Then ChangeDatabasePassword = PasswordChanged
HanDle:
If Err.Number = 0 Then Exit Function
ChangeDatabasePassword = GlobalError(Err.Number)
Err.Clear
End Function
Public Function GlobalError(ByVal eNumber As Long) As dbErrorType
Select Case eNumber
Case -2147467259
GlobalError = InvalidFile
Case -2147217843
GlobalError = InvalidPassword
Case -2147217904
GlobalError = FieldNotExist
Case 3705
GlobalError = AlreadyConnected
Case -2147217865
GlobalError = NoTableFound
Case Else
MsgBox "Global - " & Err.Description
Clipboard.Clear
Clipboard.SetText Err.Number
GlobalError = zElseError
End Select
End Function
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|