|
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.
|
|
|
|
|
jkirkerx wrote: The single join works, using the left join and right join on the union, it's when I added the 2nd join it failed.
What does that mean? Did it throw an error? If yes, which, if no, what was the difference between the expected and the actual result?
jkirkerx wrote: I'm back to that Foxpro database again in a VB app
There's the problem 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Sorry for the late reply,
I'll figure it out one day. Hate working on this but it's good money.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
Hi,
I have and SSIS Package, my lead wants me to create a C# application that sets Variables, Connections and everything properly and executes the package, I did that, I even have written OnError event and logged the Error why SSIS failed to execute. As far I saw there is no difference between my Application and SQL Job. In fact my Application is logging every detail and placing all details as I am logging many things and its priority based logging means logs depending upon the Environment. But just asking, is it good Idea to write my own Application or did I do wrong?
My lead wanted to do it this way because there are many SSIS packages and because the SQL jobs they are running sometimes same time and creating lot of mess. But my application does it synchronously and it combines this synchronous process with another Asynchronous process of FileSystemWatcher, which keeps a record into the Database when file is dropped and then my App would take those details of path etc sets the connections and variables then calls the SSIS packages Synchronously and kills the execution of SSIS package if its taking too long. All these features are implemented by using C# code, if anybody has better advise please let me know their opinions.
Just want to calculate pros and cons from some other developers just to check my process, in fact I believe all Programmers are always learners  .
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Using C# to kick off SSIS jobs is just fine. I've done it too and it works great. It sounds like you built a good system.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Thanks my friend
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
