|
Don't use hard coded sheet names. Use the relevant Workbook method to find the names of the Worksheets within it.
|
|
|
|
|
Hi,
I want to handle error when there is no CreateTable.sql script file in the defined path by giving an alarm in MessageBox.
if (!File.Exists(databaseName))
{
using (SQLiteConnection sqliteCon = new SQLiteConnection(@"Data Source = " + databaseName))
{
sqliteCon.Open();
try
{
string dbCreationScript = File.ReadAllText(@"CreateTable.sql");
}
catch (Exception)
{
MessageBox.Show("Script file not found");
}
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = dbCreationScript;
cmd.Connection = sqliteCon;
cmd.ExecuteNonQuery();
sqliteCon.Close();
}
}
dbCreationScript is out of current context.
How can I control error handling for CreateTable.sql script file?
Variables declared inside Try/Catch block are out of scope.
modified 31-Jul-21 5:22am.
|
|
|
|
|
You can't "pass" a variable - any variable - from inside a scope to outside it: that is what scope is there for!
What that means is that if you declare a variable inside a pair or curly brackets, it is available to all code within those brackets (including nested blocks of code with their own curly brackets) but at the closing curly bracket of the block the variable ceases to exist and can no longer be accessed.
You would have to move the creation of the variable outside the try...catch block in order to use it.
But ... that code is pretty bad:
1) Never, ever create empty catch blocks. All that does is hide the error from running code until it's too late to do anything about it, and throw away all the information you as the developer need to fix the problem when you test it!
At a bare minimum, provide a Exception variable and log it to the Debug stream:
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}This give you the developer a warning that it happened, and provides the exception detail for you to look at with the debugger.
2) The try block should cover the whole operation, right up to and including the ExecuteNonQuery - because if an error is going to occur with the data you are reading from your file, that's where it will happen!
3) You don't need to close the connection: the end of the using block will do that.
4) Don't hardcode filenames! Use a variable (or const) so you only have to change it in one place!
5) Don't store your data in the application folder! That generally fails in production becasue teh app folder is write protected ...
See here: Where should I store my data?[^] for some better ideas.
So try it like this:
if (!File.Exists(databaseName))
{
using (SQLiteConnection sqliteCon = new SQLiteConnection(@"Data Source = " + databaseName))
{
sqliteCon.Open();
string dbCreationScript = null;
try
{
dbCreationScript = File.ReadAllText(pathToFile);
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = dbCreationScript;
cmd.Connection = sqliteCon;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
}
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
How about declaring the
string dbCreationScript outside the try block?
|
|
|
|
|
Hi.
I'm using C# in Visual Studio and I installed the ItextSharp package to deal with PDF files. It's working fine. My problem is that when I instaled the executable file in anothe machine, it showed an error when I tried to generate a PDF file. I think the problem is that I didn't copy files for the itextSharp. Can anybody tell me which files I should copy to solve the problem?
Thanks.
|
|
|
|
|
In the bin\Release folder of your project, you'll find the .DLL's that need to be shipped with your executable.
|
|
|
|
|
Dave. This folder is empty. The folder bin\debug has some files starting with itextsharp. But only with these files it didn't work. An there are some other files there. Do you have other suggestion?
Thanks.
|
|
|
|
|
You really need to learn how Visual Studio works.
If the Release folder is empty, you're still compiling a Debug version of your code. Then all of the files are in the bin\Debug folder. This is usually just for development and debugging of your project. Changing the build target to Release will build a Release version of the project with optimized code.
|
|
|
|
|
Hi,
I have 15 toggle switches in my project (Using DevExpress). All of them are related to column 2 of the DataTable and are used for filtering. I want to filter the DataTable based on those toggle switches the user turns on. I don't know what algorithm should I use to set my filtering and cover all the toggle switches that are on.
DataView dv = new DataView(my_table3);
dv.RowFilter = $"Column2 = '{checked_item2[0]}' OR Column2 = '{checked_item2[1]}' OR Column2 = '{checked_item2[2]}'";
|
|
|
|
|
I don't think string interpolation will work there: interpolated strings are a syntactic sugar for the older method using numbered parameters and are evaluated when the interpolated string is constructed, not when it is used:
using System;
public class Program
{
static string name = "???";
static string xxx = $"Hello {name}!";
static string yyy = "Hello {0}!";
public static void Main()
{
name = "Paul";
Console.WriteLine($"Hello {name}!");
Console.WriteLine(xxx);
Console.WriteLine(yyy, name);
}
}Will give you:
Hello Paul!
Hello ???!
Hello Paul! So setting a RowFilter to the value of a variable doesn't mean that it will change the View at all whcn checks change: it will only use the values at the moment when the string is created.
What you need to do is change the filter each time the user changes the check boxes.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
This filter is a part of a bigger calculations. I export a huge Excel file into DataTable and filter rows based I toggle switches and continue the calculations. This process is done when the user clicks a button. So I don't need to have synchronous filtering.
My problem is that I don't know how many toggle switches will be on to set them in my Filter
String. Please guide me.
|
|
|
|
|
If you don't know, then you need to process them.
Create a List<string> and loop through your switches: if it's set, add the condition string to the collection:
if (myCheck.Checked)
{
myList.Add($"Column2 = '{checked_item2[n]}'");
}Then when you have them all, use string.Join[^] with a separator of " OR " to produce your filter.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
How can I join List<> elements parametrically?
For example I don't know the count of checked toggle switches at first but during the process I find out that n = 5.
Sting.Join(" OR ", checked_item2[0], .....);
|
|
|
|
|
String.Join accepts any IEnumerable - including a List - so read what I said, and give it a try.
To do it with parameters would mean knowing what your user was going to select at compile time ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I used your algorithm and it solved my problem. Now, I want to deliver the filtered table into a new DataTable. I used this:
DataTable filteredTable = dv.ToTable();
When I debug the code, there is some information in dv table but filteredTable is empty. Why?
|
|
|
|
|
Firstly, a DataView isn't a table - it is a filtered view of a table and that's quite different in reality.
Secondly, it works fine for me:
private void TwoDGVs_Shown(object sender, EventArgs e)
{
string strConnect = SMDBSupport.SMInstanceStorage.GetInstanceConnectionString(DBName);
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(strConnect))
{
try
{
con.Open();
using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM MyTable", con))
{
da.SelectCommand.Parameters.AddWithValue("@SEARCH", "The text to search for");
da.Fill(dt);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
}
DataView dv = new DataView(dt);
Source.DataSource = dv;
}
private void Filter_TextChanged(object sender, EventArgs e)
{
if (Source.DataSource is DataView dv)
{
dv.RowFilter = $"Title LIKE '%{Filter.Text}%'";
DataTable dt = dv.ToTable();
Destination.DataSource = dt;
}
}
I get two DGV's, one looking a all rows, the other filtered.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Might be slightly more compact to do
Column2 IN ('{checked_item2.Join("', '")}')"this would also be impervious to changing the no of checkboxes
I've not tested this.
I am assuming that the filter texts are fixed (i.e. the user cannot change them). Also beware if any values have an ' in them - the ' have to be doubled.
|
|
|
|
|
Hi,
My DataTable has 40 columns. I have created a Database and a table with 40 columns for SQLite programmatically.
I want to copy all my contents in DataTable into SQLite table. I used this class code:
public class SQLiteFunction
{
public string databaseName { get; set; }
public string dataTableName { get; set; }
private DataTable dt = new DataTable();
public SQLiteFunction()
{
dt.TableName = dataTableName;
}
public void Create_db()
{
if (!File.Exists(databaseName))
{
using (var sqlite = new SQLiteConnection(@"Data Source=" + databaseName))
{
sqlite.Open();
string script = File.ReadAllText(@"CreateTable.sql");
SQLiteCommand command = new SQLiteCommand(script, sqlite);
command.ExecuteNonQuery();
sqlite.Close();
}
}
}
public void InsertData()
{
SQLiteConnection con = new SQLiteConnection(@"Data Source=" + databaseName);
con.Open();
SQLiteDataAdapter dAdapter = new SQLiteDataAdapter("SELECT FROM MyTable", con);
SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(dAdapter);
dAdapter.Fill(dt);
}
}
When I use InserData() method, there is no runtime error but my SQLite table is still empty.
Please help me.
|
|
|
|
|
Your InsertData method is reading data from the DEL - a SELECT query - instead of trying to write data to the DB - an INSERT query.
Have a look here: DataAdapter.Update(DataSet) Method (System.Data.Common) | Microsoft Docs[^]
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I changed the InserData() method:
public void InsertData()
{
SQLiteConnection con = new SQLiteConnection(@"Data Source=" + databaseName);
con.Open();
SQLiteDataAdapter dAdapter = new SQLiteDataAdapter("INSERT INTO MyTable", con);
SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder();
cmdBuilder.DataAdapter = dAdapter;
dAdapter.Update(dt);
con.Close();
}
It still doesn't work. Note that DataTable headers and column names in SQLite table are the same.
What query should I use?
modified 27-Jul-21 3:24am.
|
|
|
|
|
Is there any way to transfer them from DataTable to SQLite table at once and quickly?
I can use For loop but my data is huge (40 columns and around 30000 rows).
|
|
|
|
|
Yes. And if you follow the link I gave you it tells you how to!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
In the Link you provided, there is a string parameter for queryString. I need to INSERT data. What query should I use for transferring all cell information into SQLite?
|
|
|
|
|
It looks to me like the query string should be the SELECT query that pulls the data you want to transfer.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I created a client to communicate with RTD server, my knowledge is small in C# I have greater in JAVA, but I didn't find anything likely to be used in JAVA, I'm using this example of a C# client
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Threading;
using System.Runtime.InteropServices;
using System.Runtime.CompilerServices;
namespace DotNet2
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
chamaMetodo("RTDTrading.RTDServer");
}
private static void chamaMetodo(string v)
{
RtdClient rtd = new RtdClient(v);
Console.WriteLine("rtd:"+rtd);
object[] param = new Object[2];
param[0] = "DOLFUT_F_0";
param[1] = "HOR";
Object ret = rtd.GetValue(param);
Console.WriteLine("ret:"+ret);
}
}
}
Class Interface que monta a comunicação:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Threading;
using System.Runtime.InteropServices;
using System.Runtime.CompilerServices;
namespace DotNet2
{
public interface IRtdClient
{
object GetValue(params object[] args);
}
public class RtdClient : IRtdClient
{
readonly string _rtdProgId;
static IRtdServer _rtdServer;
public RtdClient(string rtdProgId)
{
_rtdProgId = rtdProgId;
}
public object GetValue(params object[] args)
{
const int topicCount = 1;
var rnd = new Random();
var topicId = rnd.Next(int.MaxValue);
var rtdServer = GetRtdServer();
Console.WriteLine("TopicID "+topicId+" args:"+args[0]+" args2:"+args[1]);
rtdServer.ConnectData(topicId, args, true);
object val = null;
while (val == null)
{
var alive = rtdServer.Heartbeat();
if (alive != 1)
GetRtdServer();
else
{
var refresh = rtdServer.RefreshData(topicCount);
if (refresh.Length <= 0) continue;
if (refresh[0, 0].ToString() == topicId.ToString())
{
val = refresh[1, 0];
}
}
}
rtdServer.DisconnectData(topicId);
return val;
}
IRtdServer GetRtdServer()
{
if (_rtdServer == null)
{
Type rtd = Type.GetTypeFromProgID(_rtdProgId);
_rtdServer = (IRtdServer)Activator.CreateInstance(rtd);
}
return _rtdServer;
}
}
[ComImport,
TypeLibType((short)0x1040),
Guid("EC0E6191-DB51-11D3-8F3E-00C04F3651B8")]
public interface IRtdServer
{
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(10)]
int ServerStart([In, MarshalAs(UnmanagedType.Interface)] IRTDUpdateEvent callback);
[return: MarshalAs(UnmanagedType.Struct)]
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(11)]
object ConnectData([In] int topicId, [In, MarshalAs(UnmanagedType.SafeArray,
SafeArraySubType = VarEnum.VT_VARIANT)] ref object[] parameters, [In, Out] ref bool newValue);
[return: MarshalAs(UnmanagedType.SafeArray, SafeArraySubType = VarEnum.VT_VARIANT)]
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(12)]
object[,] RefreshData([In, Out] ref int topicCount);
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(13)]
void DisconnectData([In] int topicId);
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(14)]
int Heartbeat();
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(15)]
void ServerTerminate();
}
[ComImport,
TypeLibType((short)0x1040),
Guid("A43788C1-D91B-11D3-8F39-00C04F3651B8")]
public interface IRTDUpdateEvent
{
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(10),
PreserveSig]
void UpdateNotify();
[DispId(11)]
int HeartbeatInterval
{
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(11)]
get; [param: In]
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(11)]
set;
}
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(12)]
void Disconnect();
}
}
But I'm getting this error, in the method call:rtdServer.ConnectData(topicId, args, true);
Unhandled exception. System.Runtime.InteropServices.COMException (0x8000FFFF): Falha catastrófica (0x8000FFFF (E_UNEXPECTED))
at DotNet2.IRtdServer.ConnectData(Int32 topicId, Object[]& parameters, Boolean& newValue)
Is anyone aware of the error?
modified 26-Jul-21 18:00pm.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
