VBA Rubberducking

Hosch250

3:01 PM

I can't find anything that explicitly mentions 3.5, but they have two versions.

This is the older one: nuget.org/packages/System.Data.SqlClient

That one is for 4.5 and 4.6.

Is there any particular reason you are targeting 3.5?

You should be targeting at least 4.5 if you can, really.

3.5 is so old most tooling has moved on.

KySoto

yes

we have windows 7 machines

i dont know how patched some of them are since they dont touch the internet

Hosch250

Ah.

Also, ugh.

KySoto

we are slowly getting rid of them

Duga

[zspitz/ANTLR4ParseTreeVisualizer] zspitz created draft release 0.1.0.33

KySoto

but... i mean we still have some xp

Duga

3:04 PM

[zspitz/ANTLR4ParseTreeVisualizer] zspitz published release 0.1.0.33

[zspitz/ANTLR4ParseTreeVisualizer] zspitz released release 0.1.0.33

KySoto

and even a win98

granted my thing SHOULDNT run on xp or win98

and im not going to support those

but windows 7 is on all of our older thinclients

Hosch250

One other thing--must it be a desktop app?

This is another place web comes in handy.

You only need one up-to-date machine to run it.

KySoto

i wouldnt know hte first thing about doing web

its a console app

Hosch250

OK.

KySoto

its a pretty simple thing, it takes an argument, looks up data based on said argument

and sends off an email

because i dont wanna deal with vbscript to email

originally i did a powershell script

Hosch250

3:07 PM

OK. Web could handle that easily, but console app is easier to start with and deploy.

KySoto

but for some reason the powershell script just isnt sending anything

right

Hosch250

Do you have a mail server set up?

KySoto

originally i was going to do it in vb.net

because im familiar and i could get this going in about 20-30 minutes

yeah, all that is going

Hosch250

OK.

KySoto

we have idk... 5 or so auto-emailer apps running in VB.net

using sql client

Hosch250

3:08 PM

OK.

OK, create a VB.NET app and find where the SQL client DLL is.

It might be a local DLL on your system.

If it's on your system already, you should be able to go find it and add it to the C# one.

I doubt it's actually "part of the language".

Looks like you need to add a system reference to System.Data.Entity.

KySoto

really?

O_O

hey

can you post the answer on stackoverflow.com/questions/58083232/…

that was it

Hosch250

OK.

On the other hand, probably the "correct" way would be to add the NuGet package I linked.

You need to use the 4.3.1 version of it.

LOL, Marc Gravell beat me to it.

NVM about the NuGet package.

Some of them error immediately, but the earlier versions don't error until you hit Install...

KySoto

yeah, it wouldnt have worked

Hosch250

WT*, NuGet.

I guess they changed the structure of the package metadata, or something?

KySoto

yeah, id have ot be at least targetting 4.5

i think

Hosch250

3:19 PM

Yeah.

Which, TBF, I would (and did) recommend doing :D

Let me know if anything else pops up.

(Also, did you see my comment about using List<T> instead of T[]?)

KySoto

i cheezed it and used var

:P

Hosch250

var doesn't change anything, though.

KySoto

im splitting a string

Hosch250

You're probably creating an empty array with var.

KySoto

oh?

Hosch250

3:21 PM

Oh, wait, no.

KySoto

var In = args[0].Split(';');

Hosch250

Yeah.

The equivalent is string[] In.

KySoto

cept it bitched at me

Hosch250

You don't need the size in the type declaration.

KySoto

when i tried

i'll try again

ok, its good now

Hosch250

3:22 PM

string[] v = "".Split(',');

KySoto

yeah, id rather use hte appropriate type if possible

it was just being a jerk

Hosch250

But, like I said, avoid using arrays for most cases. This is probably good, since it's (probably) access-only.

But if you want to add/remove items, use a list.

KySoto

oh of course

essentially im using the split out data to pull up a record

then using that record to send an email

odd.

System.Data.SqlClient.SqlConnection Connection = new System.Data.SqlClient.SqlConnection; shows a red squiggly on ;

Hosch250

Yep.

KySoto

why?

Hosch250

3:25 PM

You need the () at the end.

KySoto

ooohh, custructor

Hosch250

Yes.

KySoto

vb.net is so much lazier lol

Hosch250

Also, I think you want to use a using block to ensure it gets disposed of correctly.

using (var connection = new SqlConnection()) { code-here }

KySoto

that would get confusing fast

Hosch250

3:26 PM

Also, please use camelCase for variables. That PascalCase is making my eyes bleed.

KySoto

or could

Hosch250

@KySoto What would?

KySoto

oh if i was using multiple database connections

Hosch250

It basically ensures that connection.Dispose() gets called when you are done using it.

To prevent memory leaks.

KySoto

this whole deal is in a try block

so i can do the dispose

though, im not used to needing to dispose to be honest

Hosch250

3:27 PM

That's basically what using is.

It's a try block with the finally thing already handled.

It's guaranteed to work even if an exception is thrown.

KySoto

hmm so what do you do if you have multiple conections?

Hosch250

You have multiple usings.

Like:

using (var foo = new Foo())
using (var bar = new Bar())
{
    // ....
}

KySoto

hmm

so if i do the using, even if everything splodes and errors out, it will go away correctly

Hosch250

Yes.

Mathieu Guindon

using is basically a try...finally block

KySoto

3:29 PM

hmm

Hosch250

Underneath, it just compiles into try/finally, like Mat said, but it's safer, because it won't make a mistake.

And easier to see exactly what's happening.

KySoto

man i am so not used to the super duper case sensitivity

Hosch250

You'll get used to it in a couple days or so.

And then you'll wonder why foo and FOO are the same things in VB languages.

KySoto

lol

Mathieu Guindon

@KySoto that's part of the things you hate at first, learn to love, and eventually can't live without.

;-)

Hosch250

3:34 PM

Odds are you can't have this reviewed, but it really should be :D

KySoto

hey @Hosch250 you havent put in your answer on my SO question

Hosch250

The first efforts in a new language are the most important to be reviewed.

KySoto

i can redact information

Hosch250

@KySoto Marc Gravel's answer is basically the same.

KySoto

is it?

Hosch250

3:35 PM

Yeah, he says to reference System.Data.

Instead of System.Data.Entity.

KySoto

i dont actually know what hes tellin me

xD

Hosch250

Basically, he said to reference System.Data, and showed you how to manually add it to the csproj too.

And also said "try to not work with .NET 3.5" :P

BTW, which version of C# are you using?

4, I'm guessing?

Maybe 3?

I already know it can't be 5--async/await doesn't come until 4.5, IIRC.

this

I honestly don't understand why they thought it was a great UX to persistently show "sign up" page instead of "sign in" page. Odds are that I'm already registered so why are they making me clicking one more time just to get to sign in?

KySoto

@Hosch250 i just took a look in my csproj

Hosch250

@this GitHub? I hate that too.

IvenBach

3:39 PM

I need some help pond. Have a sunk to a new low or reached a new high of Excel abuse. 'Cause y'know 160MB workbooks be a thing I'm forced to do right now.

KySoto

the thing he said was already in place

Hosch250

@IvenBach What would happen if you said "no"?

:P

KySoto

but it didnt work

Hosch250

@KySoto OK. I'd just leave a comment that you needed System.Data.Entity too.

Mathieu Guindon

@IvenBach wut

what's up?

IvenBach

3:41 PM

I don't have just 1 either. I've 2 of them with about 15 more to follow.

Hosch250

It's like cocaine.

You take the first step, and can't get back...

IvenBach

TLDR = No db and we need 11,000,000 results from a parametric sweep. Solution: Build a DB? Hells no, ain't got time for that. Split them up into groups of 500,000 rows each! That fixes the problem.

Hosch250

Don't got time for that, either.

IvenBach

Well there is a db but no code set up to access it, write to it, etc...

this

@Hosch250 in my case, dropbox

but lot of websites do that, too

Hosch250

3:44 PM

I'd just go to them and say "It'll take longer to do this than get the DB set up"

this

it's as if they all signed up to be retarded together.

Mathieu Guindon

@IvenBach that could take the better part of ...the afternoon, no?

IvenBach

^ If you know how to do it and if the codes set up for it. Ours isn't + I don't really know how.

That's what I'll be researching shortly.

Mathieu Guindon

3

Q: ADODB Wrapper Class (Revisited)

I recently posted this question on my implementation of an ADODB Wrapper Class. I realized in my own review that I was missing some very important things, so much so, that I decided it would be worth it to re-write the entire class. Saying that I have done quite a bit of restructuring so I am goi...

^ could be useful

IvenBach

Hope you don't mind my pestering nagging questions about accessing a db from excel and best practices.

@MathieuGuindon TYVM :+1: Another reason why this pond is special to me.

KySoto

3:45 PM

So, question, should i do
var Connection = new System.Data.SqlClient.SqlConnection();
or
System.Data.SqlClient.SqlConnection Connection = new System.Data.SqlClient.SqlConnection();

Mathieu Guindon

@KySoto using (var connection = new SqlConnection(connectionString))

(have using System.Data.SqlClient; at the top of the module)

KySoto

i mean as a general for the different data types

i actually had ot have System.Data.Entity

cuz .net 3.5

is stupid

how about this one instead
var LogDataset = new System.Data.DataSet();
vs
System.Data.DataSet LogDataset = new System.Data.DataSet();

Mathieu Guindon

if the code says SqlConnection and lets the using statements do the qualifying, then all you need to change is the using statement

Hosch250

I like var better.

Mathieu Guindon

^

use var

Hosch250

3:47 PM

See, you already say what type it is right after the var.

KySoto

so it doesnt make a difference

thats what i was checking

readability

Hosch250

Correct.

KySoto

ok. sticking with var then

well on complex types

Hosch250

Also, please lower-case that connection :D

Mathieu Guindon

@KySoto it does when you later need to change System.Data.Something to Foo.SomethingElse

KySoto

3:48 PM

im probably still going to do int and string etc

Hosch250

Well, even those are easy.

Any integer value is an int, unless it's too big--then it uses the next available size.

Any floating point is double.

KySoto

yeah

i dont use singles

Hosch250

If you want a short or a long or a decimal, you have to use a type hint anyway.

8f - float. 1.5m - decimal.

this

does anyone have anything better than Ionic.Zip.dll? (aka DotNetZip)

Mathieu Guindon

I wouldn't even know what makes X or Y "better". perf? compression rate?

this

3:51 PM

sorry, better = more stable

I'mfinding that it's kind of buggy

i dont' care about compression or perf

Hosch250

@this Does 7-zip have a package on NuGet?

this

it's nto a library, AFAIK

Hosch250

How about SharpLibZip (another one I've seen around)?

this

I don't want an exe.

hmm I dont' think I saw that before

Mathieu Guindon

whatever happened to WinZip

Hosch250

3:52 PM

Actually, hang on.

this

WinZip is a exe, not a library

Mathieu Guindon

[x] Split compressed file into 1.44mb files

this

I want a library so I can have good control over the zipping process and still produce .zip files

Hosch250

46

Q: I'm in your .zips crackin' your passwords

As an attempt to learn multithreading better, I wrote a program to crack the password of a ZIP file. It is sort of slow, processing a three-digit password of the 95 printable ASCII characters in about 1:45 minutes. This is my class that actually handles the cracking: class DecryptPassword { ...

c# task-parallel-library

I don't know what I used there, but it did work well.

Mathieu Guindon

isn't there a file compression utility that's like built into the framework?

Hosch250

3:53 PM

Try typing ZipFile.CheckZipPassword("", "") in your file.

See what NuGet finds.

FreeMan

Is there anything on SO that will show you the last 10, 20, 50 questions you visited? I just closed a tab earlier today then deleted stuff that that question documented. :/

Mathieu Guindon

System.IO.Compression

this

@MathieuGuindon it produces .cab files

FreeMan

don't remember how the heck I did it...

this

not a .zip file

FreeMan

3:54 PM

sigh

Mathieu Guindon

ah

this

IKR? MS really made it so complicated.... le sigh

Hosch250

OK, I just did a NuGet search.

Mathieu Guindon

@this you sure of that?

281

A: How do I ZIP a file in C#, using no 3rd-party APIs?

How can I programatically (C#) ZIP a file (in Windows) without using any third party libraries? If using the 4.5+ Framework, there is now the ZipArchive and ZipFile classes. using (ZipArchive zip = ZipFile.Open("test.zip", ZipArchiveMode.Create)) { zip.CreateEntryFromFile(@"c:\somethi...

Hosch250

> System.IO.Compression.ZipFile

> SharpZipLib

> DotNetZip

> 7-Zip.CommandLine

this

3:57 PM

ah, using 4.5 or later

I'm using 3.5, I think

yay me....

Hosch250

SharpZipLib is MIT licensed.

I'd use that one or the MS one.

this

i may try out the SharpZipLib

FreeMan

@MathieuGuindon wow... there's an oldie but goodie!

this

WinZip? Goodie?

IvenBach

@Hosch250 ~.~ that title

Hosch250

4:04 PM

@IvenBach :D

It's as good as the "Snakes on a Plane" title I thought of (and gave to someone else).

Digital snakes on a mathematical plane :D

this

4:22 PM

Hmm I think i see a problem with SharpZipLib.

It was originally a GPL up to 1.0

but 1.0 is only good on .NET FX 4.5 and I need it for 3.5

hmm. I probably should be re-leasing it as .NET standard. That means I don't need to have any computers to have a certain version of .NET FX installed... right?

It'd be now totally self-contained, right?

Hosch250

@this Basically, yes.

Standard can be referenced by FX or Core.

At least, after a certain point.

this

I think that's my path forward. I can't keep screwing around with "gosh, I hope they have 3.5 FX installed"

Hosch250

3.5 FX might not be part of Standard.

If I had to guess, it would be 4.6, or something.

this

well the project I need for it is pretty self-contained, it's just that I don't want them to have to install a certain FX

corp install and all that.

ideally it has to be xcopy-deployment only

IvenBach

Bear with my derpy noob questions but: [Data] Provider is the DB that's housing the info?

this

4:33 PM

no, it's just a way to reach the given data source

e.g. using a provider for say, Oracle will allow your VBA code to talk to Oracle and get data via ADO

KySoto

ahhhh

IvenBach

If it's a db in SSMS then I don't really care about the provider, just as long as it does its job?

KySoto

took 2.5 hours to rebuild my script into a compile-able thing

though im missing the final piece, the email part

lol

this

uh. Your DB is in SQL Server. SSMS talks to it using native protocols (forgot the name ATM). ADO providers is for any other programs that needs to talk to the db. Two different categories.

Hosch250

TBH, 2.5 hours isn't too bad.

Especially depending on the size of the script.

But, that's actually pretty good for switching languages for an average-sized script.

this

4:37 PM

TDS protocol is the name of SQL Server's native protocol I was trying to remember.

But the whole point of OLEDB or ODBC is that you get a common representation so that you can change database but still have same programming logic in your client code.

Hence the need for the providers/drivers.

IvenBach

So VBA uses ADO to talk to the SQL Server db. Any communication within SSMS is native.

Yet again another layer on the abstraction lasagna so as to not worry about the implementation detail. Coding against the abstraction and not the concretion strikes again.

FreeMan

 CASE app.DayPhone
    WHEN null THEN ''
    ELSE app.DayPhone
  END

Yields NULL when app.DayPhone is null.

whatamidoingwrong?

trying to put '' (blank) in the output column when the column is NULL, or the value otherwise.

this

@IvenBach Pretty much, yes.

FreeMan

I need BlankIf instead of NullIf. Guess I'll write it mahself

Mathieu Guindon

4:55 PM

@FreeMan IsNull(value, value_if_null) would do that

FreeMan

@MathieuGuindon makes note. Dials into conference call. Addresses shortly

Mathieu Guindon

SELECT IsNull(app.DayPhone, '')

aka Nz in Access

KySoto

5:20 PM

@Hosch250 it's gist.github.com/KySoto/1cb1a2fe97244b40da4d2a7bac287806

i figured a gist would work

Vogel612

@KySoto consts, consts everywhere :)

was using not available for the connection handling?

Hosch250

@KySoto I'll check it later, in a meeting.

We're all bikeshedding over UI standards.

So far, I'm winning most of the arguments :)

KySoto

yeah i didnt do consts, though i could have

Hosch250

My eyes! That naming...

KySoto

xD

Hosch250

5:32 PM

Also, please do the using, and create more methods :D

KySoto

sorry not sorry

for naming

create more methods?

Hosch250

Yeah, don't put it all in Main.

Create specialist methods.

KySoto

any particular reason?

Hosch250

Makes it easier to change/reuse parts of it.

Easier to test small pieces at a time.

And, BTW, you really should follow standard C# naming conventions to help people understand what's what.

KySoto

to be fair, i literally started today

for naming standards

Hosch250

5:35 PM

Yep, just urging you to learn it sooner, because you will end up learning it later anyway :)

And then it'll be harder to work with your early code.

KySoto

uhh i really dont know how id split this apart

Hosch250

I'll look at it in more depth once the meeting ends.

KySoto

does using auto close the connection?

Hosch250

Yes, that's what we were saying above :)

When we first recommended it.

KySoto

well i knew you said it disposed it

Hosch250

5:37 PM

Dispose will close it automatically.

KySoto

but i didnt know disposed also closed it

Hosch250

That's what Dispose is for. Making sure that everything, regardless of the current state, is cleaned up.

So, if Close isn't called, it'll close it for you.

KySoto

i had close and dispose called at the end lol

in the finally block

ended up deletin it

to try the using thing

Hosch250

Make it a static void Main.

And don't return a success code.

That's really just a hold-over from C/C++.

There are better ways of reporting errors now.

KySoto

but i need to know if it succeded

Hosch250

5:42 PM

OK.

I'd use a logging system and handle errors there, but this will be a little easier, probably.

KySoto

a logging system? how would i use that from VBscript

Hosch250

Oh, no idea.

KySoto

xD

Hosch250

OK, one more thing (meeting isn't done yet...)

Console.WriteLine("SMTPServer = $SMTPServer");

That doesn't do what you think.

KySoto

yeah i know

Hosch250

5:44 PM

Make it Console.WriteLine($"SMTPServer = {SMTPServer}");

KySoto

yeah

i did, i missed that one

first pass

in powershell that worked

Hosch250

Console.WriteLine("SuperEmails:$SuperEmails");

KySoto

it was write-output "SuperEmails:$SuperEmails"

Hosch250

Looks pretty good (I'll get to the split up methods later).

KySoto

i did a find replace on write-output to shove console.writeline

Hosch250

5:50 PM

You should consider including a logging service so that can be redirected to a file.

Then any crashes will be caught and written to the file too, and make it easier to fix things.

NLog is a good one.

I don't remember what RD uses, but that's good too.

KySoto

hm. that might be a little much for what this is supposed to do

its just supposed to look up a record and send an email based on that record

i dont know if im ready to kick this up to 7 yet here lol

i feel like im at a steady 3 with the little thing i wrote

this

we use Nlog

Hosch250

OK :)

Transcript for