Posts by john.knapp

App.Config ConnectionStrings are read-only at run-time when using the ConfigurationManager class introduced in .Net 2.0.

Using SQL Express should not be a problem, as long as you specify the instance name along with the server name in your connection string. Ex: WH306UT\SQLExpress

Did you look at the sample project I posted here? There is some code there for building a connection string at run-time.

Assuming that the "windows login" you are referring to is actually the browser prompting for credentials, try this example here.

Edit: Type Try and hit <Enter>, not Tab twice...

Thanks all, my code now works! I will plan to add error handling later.

Bad idea! If you had error handling the first time, you would have been able to figure out that your DataAdapter wasn't working and why...

Get in the habit of typing Try and tabbing twice in every new procedure you write.
Going back and adding error handling is a major PITA!

hmm, just looked at that thin-air-coding I did.... I don't think I can put a Try..Catch block at the class level - oops! <whew! self-corrected>

Try this instead:

Imports System.Data.SqlServerCe
Public Class Edit_Sensor_Form
    ' Shared variables  
    Dim con As SqlCeConnection = _
    New SqlCeConnection("Data Source=|DataDirectory|\Database1.sdf")
    Dim myDA As New SqlCeDataAdapter
    Dim myDataSet As New DataSet
    Dim DataGridView1 As New DataGrid
    Dim ret As New SqlCeDataReader
    Dim mySelectQuery As String = "Select * FROM Sensor_Table"
    Dim SelectedIndex As Integer

    Private Sub Edit_Sensor_Form_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try
            SelectedIndex = My.Forms.Form1.ListBox1.SelectedIndex
            Dim cmd As New SqlCeCommand(mySelectQuery, con)
            con.Open()
            myDA.SelectCommand = cmd
            myDA.Fill(myDataSet)
            TextBox1.Text = IIF(IsDBNull(myDataSet.Tables(0).Rows(SelectedIndex)("Equipment_ID")), _
                                        "",myDataSet.Tables(0).Rows(SelectedIndex)("Equipment_ID"))
        Catch ex as Exception
            MsgBox(ex.Message)
        End Try
    End Sub

End Class

While you're at it, put some error handling in...

Imports System.Data.SqlServerCe
Public Class Edit_Sensor_Form
    Try
        ' Shared variables  
        Dim con As SqlCeConnection = _
                New SqlCeConnection("Data Source=|DataDirectory|\Database1.sdf")
        Dim myDA As New SqlCeDataAdapter
        Dim myDataSet As New DataSet
        Dim DataGridView1 As New DataGrid
        Dim ret As New SqlCeDataReader
        Dim mySelectQuery As String = "Select * FROM Sensor_Table"
        Dim SelectedIndex As Integer

        Private Sub Edit_Sensor_Form_Load(sender As System.Object, _
                                            e As System.EventArgs) _
                                            Handles MyBase.Load

            SelectedIndex = My.Forms.Form1.ListBox1.SelectedIndex
            Dim cmd As New SqlCeCommand(mySelectQuery, con)
            con.Open()
            myDA.SelectCommand = cmd
            myDA.Fill(myDataSet)
            TextBox1.Text = _
            IIF(IsDBNull(myDataSet.Tables(0).Rows(SelectedIndex)("Equipment_ID")), _
            "",myDataSet.Tables(0).Rows(SelectedIndex)("Equipment_ID"))

        End Sub

    Catch ex as Exception
        MsgBox(ex.Message)
    End Try

End Class

I'm a bit confused by your explanation, both here and in the original thread.
Try taking a look at the sample project here in the original thread.

Also, it's considered bad form to open a new thread for the same question... please consider closing one of the discussions by marking it as solved with a link to the remaining discussion.

Work in progress, expands on this code.

In order for the other computers on the network to access the DB hosted on your computer, you will need to leave SQLExpress running.

The other machines would then use a connection string something like:
connectionString="Data Source=" & YOUR_COMPUTER_NAME & "\SQLEXPRESS;InitialCatalog=" & YOUR_DATABASE_NAME & ";Integrated Security=True;User Instance=True"

Where YOUR_COMPUTER_NAME is the hostname of your computer
AND
YOUR_DATABASE_NAME is the internal name of the database (the name that appears in Server Explorer on your machine with the db attached)

Have you looked at schtasks.exe? There is a section of documentation here on MSDN that gives an overview of the requirements for running a task on a remote computer.

Bump... SQLDriverConnect looks to be the way to go.

SQLConfigDataSource() will not accept the UID/PWD key value pair, and in fact will not even show the ODBCAD32 dialog if you try to pass in even just the UID.

The example code at MSDN, mentioned in the previous post here, does prompt for UID & PWD, which is what we need. Be aware however, you will not be able to save those values in the DSN.

The ODBC API documents some other methods as well, including at least one that will write to ODBC.ini

I'm somewhat confused as to how your database and connection are set up.
Please verify the following items of information:

1. You are using SQL Server 2008 R2
2. You are manually attaching a database file
3. You do or do not want the database file deployed with your project ?????

Scripting may not be available in your version of Visual Studio.
I cannot build the query without the tables.
You can download the SQL Server 2005 management studio at this link, assuming you are running SQL Server 2005...

Please script your database and attach that script.
In server explorer, right click database and select "publish to provider". Go through wizard and save the output file.
Zip and upload that output file

You really should've used the Northwind example and modded it to suit your needs...

Some quick notes:
1. Database design is "off" - Customer table and Vendor table have no relation to any other tables
2. Add POrder.POID as a foreign key to vendor table, add SOrder.SOID as foreign key to Customer table
3. You need error handling in your application.
For every sub and function, add a Try...Catch block

For example:

Sub mySub()
  Dim myBool as Boolean = False
  Try
    Me.VendorTableAdapter.Fill(Me.GPOSDataSet.Vendor)
    ' more code
    If myBool = false Then
      ' do something
    End If
  Catch ex as Exception
    Debug.Print (ex.Message)
  Finally
    ' optional codeblock, runs regardless of successful try
    ' see http://msdn.microsoft.com/en-us/library/fk6t46tz(v=vs.71).aspx
  End Try
End Sub

This happens to you on two different machines, correct?
Both machines have the same OS?
What OS is that?
What version of the .Net Framework are you targeting?
On your project's compile properties, do you have the following set?
Option Explicit: ON, Option Strict: ON, Option Infer: Off

Clean your project from the Build menu, zip it up into a compressed folder, and attach it to a post.

We need to see what you have for code, if you've done everything in the designer so far, you won't see anything in your main form's code behind file - but it will be visible in the Designer generated code file.

You must have added a reference in your project to System.Management as well as using the Imports statement, right?

I can compile and run that code in MnForm here no problem, in Debug or Release mode...

It would always end with "luser" :)

Curious is not the word I use when that happens!

We need the exact text of the error message.
Clean and rebuild in Debug mode, see if you get the same error. I take it you get an error when running it somewhere other than your development machine, correct?

Almost certainly a permissions error, or missing|unsupported assembly

You're welcome

Well, for one thing - if your connection string specifies SQLExpress as your datasource, the application can't use the local database file.

I would remove the db file and all references to it now, myself - and code for SQL or SQLExpress, whichever you plan on using in Release.

No idea, since I don't use Crystal Reports; but they have a lot of documentation and community support on their website here, Sample Code and SDKs here.

Known issue.
First hit on a Google Search....

Click here for workaround.

My pleasure

Do you actually have 15 physical Serial Ports? I don't know for certain, but I suspect that you cannot just arbitrarily decide to give yourself another serial port without the appropriate hardware - at least not using this method. I don't see any mention of a "virtual" port in the docs at MSDN...

You can get a list of available ports using
Dim myAvailablePorts as String() = System.IO.Ports.SerialPort.GetPortNames()

Be aware however, per MSDN:

The port names are obtained from the system registry (for example, HKEY_LOCAL_MACHINE\HARDWARE\DEVICEMAP\SERIALCOMM). If the registry contains stale or otherwise incorrect data then the GetPortNames method will return incorrect data.

what do you mean by use windows security and make sql user groups

I mean, use TRUSTED_CONNECTION=YES, that will force the DSN to use Windows Security (your users' windows login identity), then you do not have to pass the UID & PWD pair - which is apparently not supported by SQLConfigDataSource().

Alternatively, if you have to use SQL Security (users are setup individually in SQL Server with UID & PWD) - you use the Connection Attribute setup as detailed here, but change the first parameter from NULL to Me.Handle (as detailed in the code comments) to invoke the ODBCAD32 dialog to input username (UID) and password (PWD).

I've also found some sample code here at MSDN that uses SQLDriverConnect() to connect to a SQL Server - that API does prompt for a Username and Password if not supplied. I just ran that sample (C++ code) and am evaluating it to determine whether I can trim it down to just the UID & PWD prompt, then import it as a VB project reference (compiled as C++ DLL).

Use Trusted_Connection = NO, leave off UID and PWD, invoke the dialog and go from there.

I would use Windows Security and make SQL User Groups instead, good luck.

You want an update query.

Assuming you have a TableAdapter (visible in the "tray" in Form Designer View), AND the table you want to update is already in your dataset:

1. Right click that TableAdapter and select "Edit Queries in DataSet Designer"
2. In DataSet Designer right-click the main table to be updated, select "Add a query".

3. Click the "Query Builder" button in the next window to go to Query Builder and build your update query from there.

   You'll have to specify a "WHERE" clause (it will not add it for you) and set the parameters and values that you then also have to code into your application by hand (no drag-n-drop here!)

The final query should look something like this:

UPDATE tblProducts 
SET [column1] = @value1, [column2] = @value2, [column3] = @value3
FROM tblProducts JOIN tblOrders ON
    tblProducts.ProductID = tblOrders.ProductID
WHERE tblProducts.ProductID = @param

For your "Service Based Database", you are using MSSQL?

The GROUP BY clause allows you to associate the Average values with a specific item, if you just want them to display in a different order, look into ORDER BY

I believe you need to provide a the connection and logon properties separately for Crystal Reports. I haven't had a chance to look into it yet, but have seen that mentioned in other discussions. You might try the Crystal Reports SDK here, and look through their discussion groups to see if there's a quick answer applicable to your situation. Their community landing page for Visual Studio is here.

You'll probably not want to increase the "stock on hand" until you take delivery. What I recommend is to track that amount in a separate column, "On Order" . If you have MSAccess, there's a pretty good inventory layout in the Northwind sample database. See screenshot inline. (yes, I know you have MSSQL, but the principle and strategies are the same, and the code similiar)

That said, to automatically change the stock numbers, you have a couple of choices that come to mind immediately - either a new SQL query (client executed), or a trigger (executed server-side) on whatever table you update that can then update that table or multiple tables.

Show us what you have for code so far and we can tune these suggestions.

What do you have so far for requirements, design, and code?

<lol>

The only think I'd like to add is this: As you can see from my avatar, I've always taken great pains not to be a pointy-haired boss... Although I do have a well-worn a$$hat that pulls down snugly over my ears

Not all

I didn't say most....

Besides, you mainframe programmers can be a quirky lot <ducking>

When you set the database connection string, set a global variable for crystal reports at the same time

Something like:

If storeChosen =  10462 Then
    connectionstring = YOUR_DATABASE_10462_CONNECTION_STRING
    crystalReportVer = VERSION_10462
ElseIf storeChosen = 61973 Then
    connectionstring = YOUR_DATABASE_61973_CONNECTION_STRING
    crystalReportVer = VERSION_61973
Else
    MsgBox("Unable to get store & db version")
End If

Then when it's time to print the crystal report, you just use that variable to determine which datasource to set for the report. There is a thread here, discussing setting the datasource programatically, that also references some KB articles at Crystal's website.

Disclaimer: I don't use Crystal Reports, so I don't have that code on-hand

Not all IT guys are Site Nazis though... :)

Will post updated loop code in this thread for posterity... :)

FYI, for anyone following this thread, perliminary testing indicates that the SystemIndexException was generated by a call to an external Function in another module.

So, not an easter egg then?

How about some table defs and data? Would that help?

Lethugs had sent this out on a previous post, I do not know whether the db structure has changed since then, but I suspect not - as I suggested rethinking the design and his reply indicated that he was "constrained"...

I've seen it for at least a week or two, I just "assumed" it had always been there, but unnoticed by me... :)

much easier to just complain

hear, hear!

If you're not constrained by any privacy, security, or intellectual property concerns - post that section of the code in it's entirety. I could use the practice anyway... :)

Oh, and were you able to solve your index out of range issue?

Disregard the While and End While statements on Lines 33 & 43 - bad control logic.
Apparently not enough coffee this morning.. :)

richtextbox.savefile will save the contents to a rich text file. I can't remember the exact parameters.

RichTextBox.SaveFile(String)   
RichTextBox.SaveFile(Stream, RichTextBoxStreamType)
RichTextBox.SaveFile(String, RichTextBoxStreamType)

MSDN Documentation here.

Clean and zip your project, then upload please.
It sounds as if you may have copied the databindings when you copied the textboxes, you should have gotten a compile error if that were the case however.

Send the text to Word first, then use Word to print it.

Did you search the forums first?
If you search the forum for "Word RichText" you'll find 6+ pages of discussions - I'm sure some of that code can be adapted to your needs, this one for example...

I'm a simple cop trying to piece something together

we can tell... :) it's all good though.

You're actually doing a good job clearly labeling the textboxes - I can tell at a glance what they mean - props to you

SO I'm having trouble going back and forth with this loop between the textboxes and the checkboxes.

If you have moe than one or two of those CheckBox statements, you shold put all the checkbox code after the loop finishes, best practice is to let the loop do it's thing, and then set the other controls afterward.

If there are enough of those DataGrid value checks (say 3 or more), another array or a dictionary/list would be less code to write, even better would be to enfold that logic into the array loop or use a dictionary with named keys. Post your whole textbox/datagrid code block - we'll take a look at putting it into a dictionary.

FYI, the main difference between a dictionary and an array is that arrays store a range of objects accessed randomly by index - a Dictionary/List stores a range of key/value pairs. So in your case we would code the dictionary somewhat like this:

DISCLAIMER
This is untested code, it is entirely likely there are logic errors in here that will
put you in an infinite loop! Code for demo purposes only!

Private Sub DataGridView1_CellClick(ByVal sender As Object, _
                        ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
                        Handles DataGridView1.CellClick
        Try …