none
VBasic '08 As Front End to Select a Mailing List

    Question

  • Greetings, world of VBasicers!

    I'm trying to create a VBasic executable to read a SQL server database and produce a mailing list. I have the SQL routine working fine and had set it up as a stored procedure, but I've just discovered the manager I've written this for is an Access-type and does not have SQL Server installed on her machine. So, I figured I'd make a front end in VBasic.

    This is my first foray into accessing SQL with VB and I'm not entirely sure this is the right forum, so, please, bear with me.

    The first issue is the connection. The one I found online goes like this;

    Dim con As New SqlClient.SqlConnection
    con.ConnectionString = "Data Source=mySQLServer;User ID=Username;Password=myPass;"
    con.Open()
    

    Now, I don't want to enter my user ID or password. One, because of security; two, because I need it to authenticate under her - or, whomever will use the app - credentials.

    So, the first question is; am I able to set up the connection string to use Windows authentication, rather than hard-wired user info?

    Now, the second issue. From what I've found, it's easy enough to send a parameter to the stored procedure; the report date. The SP manipulates that value and then returns a list of names, addresses, etc. (about 8 or 9 fields). But, from what I see in the code I found, the example only seems to return a single value, whereas I need multiple records of many columns each. Of course, the columns are identical for each record, but still, a lot of them - typically, over a thousand and not a set number.

    The code I found to retrieve the data is this;

    sql.ExecuteNonQuery();
    string firstname=(string) sql.Parameters["@firstname"].Value;
    

    As can be seen, it only grabs one field.

    If anyone out there has a template on how to retrieve more than one field for more than one record, I can - probably - just go from there.

    Many thanks in advance for any assistance!

    Wednesday, January 04, 2012 9:09 PM

Answers

  • OK, got it figured out. You have to use a LINQ to SQL file and then drag the stored procedure into it. Then, Visual Studio does all the work to connect it behind the scenes.

     

    Solved that problem...

    • Marked as answer by Adam Quark Wednesday, January 11, 2012 2:52 PM
    Monday, January 09, 2012 3:40 PM

All replies

  • 2) There's an example that looks just like what you need: Fill dataset with the results from Store Procedure.

    1) You could try the trusted connection option at www.connectionstrings.com/sql-server-2008, but you didn't say if the DB will be local to the machine your program is running on. You could set up a specific user on the DB and grant that user access as required if you don't want to use Windows authentication.

    HTH,

    Andrew

    Wednesday, January 04, 2012 9:29 PM
  • Thanx, Andrew. I'll look over that example.

    As for the db, it is not local; it's on a server. I'll look at the connectionstrings code, too, and see if it works.

    Thursday, January 05, 2012 4:41 PM
  • OK, got it figured out. You have to use a LINQ to SQL file and then drag the stored procedure into it. Then, Visual Studio does all the work to connect it behind the scenes.

     

    Solved that problem...

    • Marked as answer by Adam Quark Wednesday, January 11, 2012 2:52 PM
    Monday, January 09, 2012 3:40 PM