|
The differences in the SQL dialects of MS Access and MySQL can be terrible. It is best to avoid field / table names with blanks. If you insist on them, you must escape the name correctly, Access uses [name with blank] while MySQL `name with blank`.
Next, a comparison with LIKE expects a wild card. Strain does not contain a wild card, use Strain% instead - and make sure that you have data starting with that in one of the columns...
|
|
|
|
|
Bernhard,
Thank you. I don't know how I missed the backtick. I kept looking for something serious. I discovered it about an hour after posting. It became obvious when I executed queries with Workbench. That is a great tool! Well, I corrected that. As to the wildcard, I had that done properly in the query I was interested in, appending the % to the string before passing to the query. The web is filled with advice on wildcards, most is incorrect. I always get answers quickly here.
|
|
|
|
|
The same project again. Yes I know it's old, and I'm not the author of it, but it's quick cash.
I have this dbf file that seems to be locked or something. In some other dbf files, I can delete a record. But in this one, even using another program called DBF Manager, I can't delete rows.
So I tried 2 methods, using 2 different drivers.
The dbf file has about say 20 rows for invoice number 28794, and in the 2nd example returns 1 in ExecuteNonQuery
The first method I tried was using Jet, which is what I used through out the program.
But I get a 0 back from ExecuteNonQuery
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path
Const queryString1 As String = _
" DELETE FROM ARTRS01H.dbf h " & _
" WHERE h.FINVNO = @FINVNO "
Const queryString2 As String = _
" DELETE FROM ARTRS01.dbf v " & _
" WHERE v.FINVNO = @FINVNO "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString1, connection)
Dim paramFINVNO As System.Data.OleDb.OleDbParameter
paramFINVNO = New System.Data.OleDb.OleDbParameter("@FINVNO", OleDbType.VarChar)
paramFINVNO.Value = p.Trim
command.Parameters.Add(paramFINVNO)
Try
connection.Open()
Dim x As Integer = command.ExecuteNonQuery()
command.CommandText = queryString2
x += command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
The 2nd method I tried was using the Visual Fox Pro, in which I get a 1 back from ExecuteNonQuery
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & ";"
Dim queryString1 As String = _
" SET EXCLUSIVE ON" & _
" DELETE FROM ARTRS01H.dbf " & _
" WHERE FINVNO = '" & p & "' " & _
" PACK "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString1, connection)
Try
connection.Open()
command.CommandType = CommandType.StoredProcedure
command.CommandText = "ExecScript"
command.Parameters.Add("script1", OleDbType.Char).Value = queryString1
Dim x As Integer = command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
I'd thought and see if any folks out there may flash back 20 years on this.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
This really is digging out at the very back of my memory, but I vaguely remember a similar problem. I think there was another file created - either same name as database with a different suffix, or logon id, or random numbers+letters - I don't recall which and I've seen all three of these over the years.
Any how - this file contained details of locks on the dbf and didn't always get cleared down properly - leaving things locked.
If you're using WIN7 upwards then you might be hitting issues with virtual storage depending on where the DBF file is stored - came across something similar a couple of years ago.
Good luck!
|
|
|
|
|
Thats a start for me at least, Thanks!
Strange, I deleted some funky looking row data at the bottom of the file and now I can delete records using DBF Manager.
Guess I'll give my vb code another try again now.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
When I use DBF Manager and do a search by invoice number, it comes back search string not found.
Think it's because it a partial string or something?
This technology is before I got into programming, and I never really used back in 1997.
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & "; DELETED = FALSE;"
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Dim c1 As New System.Data.OleDb.OleDbCommand("SET EXCLUSIVE ON", connection)
Dim c2 As New System.Data.OleDb.OleDbCommand("SET DELETED ON", connection)
Dim c3 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01H WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c4 As New System.Data.OleDb.OleDbCommand("PACK", connection)
Dim paramFCUSTNO As System.Data.OleDb.OleDbParameter
paramFCUSTNO = New System.Data.OleDb.OleDbParameter("@FCUSTNO", OleDbType.VarChar)
paramFCUSTNO.Value = pCustNo
c3.Parameters.Add(paramFCUSTNO)
Dim paramFINVNO As System.Data.OleDb.OleDbParameter
paramFINVNO = New System.Data.OleDb.OleDbParameter("@FINVNO", OleDbType.VarChar)
paramFINVNO.Value = pInvNo
c3.Parameters.Add(paramFINVNO)
Try
connection.Open()
c1.ExecuteNonQuery()
c2.ExecuteNonQuery()
Dim x As Integer = c3.ExecuteNonQuery()
c4.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
End Using
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
Maybe I just fixed the index file and now it works. Tired of working on this, not fun!
Dim dwXCode As Integer = registry_shared.read_HKCU_dataPath(m_path)
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & "; DELETED = FALSE;"
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Dim c1 As New System.Data.OleDb.OleDbCommand("SET EXCLUSIVE ON", connection)
Dim c2 As New System.Data.OleDb.OleDbCommand("SET DELETED ON", connection)
Dim c3 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01H WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c4 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01 WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c5 As New System.Data.OleDb.OleDbCommand("PACK", connection)
c3.Parameters.Add("@FCUSTNO", OleDbType.VarChar).Value = pCustNo
c3.Parameters.Add("@FINVNO", OleDbType.VarChar).Value = pInvNo
c4.Parameters.Add("@FCUSTNO", OleDbType.VarChar).Value = pCustNo
c4.Parameters.Add("@FINVNO", OleDbType.VarChar).Value = pInvNo
Try
connection.Open()
c1.ExecuteNonQuery()
c2.ExecuteNonQuery()
Dim x As Integer = c3.ExecuteNonQuery()
x += c4.ExecuteNonQuery()
c5.ExecuteNonQuery()
pValue.Result = If(x > 0, True, False)
pValue.Message = x & " record(s) were deleted successfully"
Catch sqlEx As SqlClient.SqlException
pValue.Result = False
pValue.Message = sqlEx.Message.ToString
Catch exdb As OleDb.OleDbException
pValue.Result = False
pValue.Message = exdb.Message.ToString
Catch ex As Exception
pValue.Result = False
pValue.Message = ex.Message.ToString
Finally
connection.Close()
End Try
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
This is the first I'm trying to update a row in a FoxPro DBF file.
My code runs without error, but the row just won't update. I get a 0 back from ExecuteNonQuery
I tried hard coding the customer number but same thing.
Tried just updating 1 column FCOMPANY, same thing.
I must be missing something here, like perhaps a cursor or something.
The SELECT line was a feeble attempt to try something.
Dim m_path As String = Nothing
Dim dwXCode As Integer = registry_shared.read_HKCU_dataPath(m_path)
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path & "; Extended Properties=dBASE IV"
Const queryString As String = _
"SELECT * FROM ARCUS01.dbf WHERE FCUSTNO = @FCUSTNO " & _
" UPDATE ARCUS01.dbf " & _
" SET " & _
" FCOMPANY = 'Wiped Customer' " & _
", FADDR1 = @Empty " & _
", FADDR2 = @Empty " & _
", FCITY = @Empty " & _
", FSTATE = @Empty " & _
", FZIP = @Empty " & _
", FSADDR1 = @Empty " & _
", FSADDR2 = @Empty " & _
", FSCITY = @Empty " & _
", FSSTATE = @Empty " & _
", FSZIP = @Empty " & _
", FPHONE1 = @Empty " & _
", FFAX = @Empty " & _
", FFNAME = @Empty " & _
", FLNAME = @Empty " & _
", FTITLE = @Empty " & _
", FSALESPN = @Empty " & _
", FTERR = @Empty " & _
", FRESALENO = @Empty " & _
", FLPDATE = @Empty " & _
", FLPAMT = 0 " & _
", FATDSAMT = 0 " & _
", FYTDSAMT = 0 " & _
" WHERE FCUSTNO = @FCUSTNO "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString, connection)
Dim paramWipe As System.Data.OleDb.OleDbParameter
paramWipe = New System.Data.OleDb.OleDbParameter("@Empty", OleDbType.VarChar)
paramWipe.Value = String.Empty
command.Parameters.Add(paramWipe)
Dim paramZero As System.Data.OleDb.OleDbParameter
paramZero = New System.Data.OleDb.OleDbParameter("@Zero", OleDbType.Decimal)
paramZero.Value = 0.0
command.Parameters.Add(paramZero)
Dim paramFCUSTNO As System.Data.OleDb.OleDbParameter
paramFCUSTNO = New System.Data.OleDb.OleDbParameter("@FCUSTNO", OleDbType.VarChar)
paramFCUSTNO.Value = p
command.Parameters.Add(paramFCUSTNO)
Try
connection.Open()
Dim x As Integer = command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
End Using
End Using
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
It's my parameter @FCUSTNO
I think I can figure it out now, got a 1 back
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
jkirkerx wrote: in a FoxPro DBF file.
Presumably as a learning experience.
I would suggests finding some other database to use if possible. I doubt knowing FoxPro is going to open any doors unless you are targeting some very specific organization. That is because it is no longer a commercial product (not even clear if there is any non-commercial use.)
|
|
|
|
|
I don't use Fox Pro, it's just an app that a customer of mine has and it's quick money, but got it working last Friday.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
I'm starting a new script. This is local on my machine and I'm the only user:
-- IF THE DATABASE EXISTS, DROP AND RE-CREATE IT
IF EXISTS (SELECT Name FROM master.dbo.sysdatabases WHERE name = N'Dashboard')
BEGIN
ALTER DATABASE Dashboard SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Dashboard
END
CREATE DATABASE Dashboard
-- CREATE THE TABLES
GO
USE Dashboard
CREATE TABLE DashboardInfo
(Id INT PRIMARY KEY NOT NULL,
SiteId INT NOT NULL,
InstrumentId INT NOT NULL,
TowerLocation INT NOT NULL)
The first time I run it all works fine. Any time I run it after that I get
Msg 3702, Level 16, State 3, Line 5
Cannot drop database "Dashboard" because it is currently in use.
Msg 1801, Level 16, State 3, Line 7
Database 'Dashboard' already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 12
There is already an object named 'DashboardInfo' in the database.
If I close and re-open SSMS then it works ok again.
WTF is wrong here??????????
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Try putting USE [Master] at the top of the script
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
I did. No change
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
What about that ALTER statement on line 4?
|
|
|
|
|
What about it?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
because of the use Dashboard line, it creates a connection to the database in question.
if exists (select name from sys.databases where name='YourDBName')
alter database YourDBName set single_user with rollback immediate
go
if exists (select name from sys.databases where name='YourDBName')
drop database YourDBName
Dropping and recreating databases in Microsoft SQL Server - Stack Overflow[^]
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thanks. That helped... Working fine now
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
I'm trying to restore a DB from backup. I get the following error
"System.Data.SqlClient.SqlError: FILESTREAM feature is disabled."
I have tried to enable FileStream by following This[^]
When I get to the Config Manager and open the Properties and check the checkboxes I get
"There was an unknown error applying the FILESTREAM settings. Check the parameters are valid."
Of course this doesn't tell me anything useful.
Anyone have any ideas?
Thanks
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
modified 21-Dec-16 11:15am.
|
|
|
|
|
Try using option 2, to see if TSQL gives you a better error message:
USE master
GO
EXEC sp_configure 'show advanced options'
GO
EXEC sp_configure filestream_access_level, 1
GO
RECONFIGURE WITH OVERRIDE
GO
If it doesn't, then check the SQL Server logs and the Windows event logs to see if there's any useful information there.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Already tried that. It says it worked but it really didn't.
I ended up installing another instance and setting FileStream enabled during the install.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Hi,
I have an asp.net application and using connection string to connect to database.
But I am getting below error.
Connection open and login was successful, but then an error occurred while enabling MARS for this connection. (provider: Name Pipes Provider, error:15- Function not supported)
I am using Asp.net framework 4.5 and SQl server 2012
DataTable dt = new DataTable();
SqlConnection objcon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ToString());
using (var command = new SqlCommand("SELECT * FROM testData", objcon))
{
var formCollection = new List<Form>();
try
{
objcon.Open();
command.CommandTimeout = 3000;
var res = command.ExecuteReader();
objcon.Close();
lblID.Text ="Success";
}
catch (Exception ex)
{
objcon.Close();
lblID.Text = ex.Message;
}
}
|
|
|
|
|
I'd guess you're enabling MARS in your connection-string, and the server somehow refuses to enable it. If you are not using an Express-edition, I'd recommend checking if the feature is installed and turned on.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi All,
I am trying to access PackageLevel variables inside my Script Components ProcessInputRow event of my SSIS package, but Its giving me following error
An exception of type 'Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException' occurred in Microsoft.SqlServer.TxScript.dll but was not handled in user code
Additional information: The collection of variables locked for read and write access is not available outside of PostExecute.
And my code is as below.
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
IDTSVariables100 vars = null;
VariableDispenser.LockForRead("System::TaskName");
VariableDispenser.GetVariables(out vars);
string TaskName = vars["System::TaskName"].Value.ToString();
vars.Unlock();
var watcherDBConnectionString
= "Data Source=" + Variables.TargetServer01.ToString() + ";Initial Catalog=" + Variables.WatcherDB.ToString() + ";" + Variables.Security.ToString();
var ErrorLogStoredProcedure = ReadOnlyVariables["ErrorLogStoredProcedure"].Value.ToString();
int PriorityToBeLogged = (int)Priority.Debug;
Log(Variables.PackageName.ToString(), "", "", "ScriptMain", "Main", "Row.FileName : " + Row.FileName
+ ", Row.File.Length : " + Row.File.Length.ToString(), "ABDUL"
, Priority.LogicalError, PriorityToBeLogged, watcherDBConnectionString.ToString(), ErrorLogStoredProcedure.ToString());
}
Can anybody please help me in this? Any code snippet, a link or even a suggestion would help, can't I access or assign the Variables in the ProcessInputRow event of Script Component?
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
So I'm back to that Foxpro database again in a VB app, and I thought I'd try and get even more efficient in loading data.
I'm trying to join a billing and address dbf file into a union. I think it will work, but I get an error saying that I'm missing an operator, in which I think I have the brackets in the wrong format.
The single join works, using the left join and right join on the union, it's when I added the 2nd join it failed.
Not much on this out there when searching.
Possible to take a look at and perhaps reflect back 18 years on this?
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path & "; Extended Properties=dBASE IV"
Const queryString As String = _
"SELECT " & _
" h.FINVNO " & _
", h.FSONO " & _
", h.FCUSTNO " & _
", h.FCOMPANY " & _
", h.FSALESPN " & _
", h.FSHIPDATE " & _
", h.FPONO " & _
", h.FCSAMT " & _
", h.FTAXAMT1 " & _
", h.FBADDRCD " & _
", h.FSADDRCD " & _
", hbA.FCOMPANY " & _
", hbA.FADDR1 " & _
", hbA.FADDR2 " & _
", hbA.FCITY " & _
", hbA.FSTATE " & _
", hbA.FZIP " & _
" FROM ARINV01H.dbf h " & _
" LEFT JOIN ARADD01H.dbf hbA ON (h.FINVNO = hbA.FINVNO) " & _
" LEFT JOIN ARCUS01.dbf hsA ON (h.FCUSTNO = hsA.FCUSTNO) " & _
" WHERE " & _
" h.FSALESPN = @FSALESPN " & _
" AND " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
"UNION ALL " & _
"SELECT " & _
" v.FINVNO " & _
", v.FSONO " & _
", v.FCUSTNO " & _
", v.FCOMPANY " & _
", v.FSALESPN " & _
", v.FSHIPDATE " & _
", v.FPONO " & _
", v.FCSAMT " & _
", v.FTAXAMT1 " & _
", v.FBADDRCD " & _
", v.FSADDRCD " & _
", vbA.FCOMPANY " & _
", vbA.FADDR1 " & _
", vbA.FADDR2 " & _
", vbA.FCITY " & _
", vbA.FSTATE " & _
", vbA.FZIP " & _
" FROM ARINV01.dbf v " & _
" RIGHT JOIN ARADD01.dbf vbA ON (v.FINVNO = vbA.FINVNO) " & _
" RIGHT JOIN ARCUS01.dbf vsA ON (v.FCUSTNO = vsA.FCUSTNO) " & _
" WHERE " & _
" v.FSALESPN = @FSALESPN " & _
" AND " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate "
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
