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.

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)

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 ?????

It would always end with "luser" :)

Curious is not the word I use when that happens!

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.

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

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

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"...

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

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 …

Ok, that works - self-solved still counts as a solved thread :)

If you step through the code, what is the value of e.Index on Rows 58 & 59?

You should use a loop for that incremental count too. You'd have to put the textboxes into an array in the order you want them filled in, then just loop 0 to 59.

Something like:

Dim myTextBoxes() As string = {frmImpound.lblRev.Text, _
                                frmImpound.txt_agency_case_number.Text, _
                                frmImpound.txtImpoundNumber}

For i As Integer = 0 To UBound(myTextBoxes)
    While i < DataGridView1.Rows.Count
        myTextBoxes(i).Text = DataGridView1.Item(i, e.RowIndex).Value.ToString
        If i = 52 AndAlso DataGridView1.Item(i, e.RowIndex).Value.ToString = "1" _
                                            Then frmImpound.cbOther2.Checked = True
        ' debug line here, to see where we're at with that index issue
        If i = 58 
            Debug.Print DataGridView1.Rows.Count
            Debug.Print e.RowIndex
        End If
    End While
Next

No problem, don't forget to mark thread as solved if/when you're done

Look at your code again...

Your Select Case e.ColumnIndex ... Case 8 is where the CellStyle.BackColor is set. As currently coded, That code can only affect Columns(8). To affect the previous column, you either have to move the Else statement up to that block, or reference that cell when setting the backcolor.

When you say "Column 8" is that a 1-based count, or 0-based as in Columns(8)?

I'm only seeing Columns 8 & 9 in your Select statement, unless you changed the DataGridView column indexes to start at 1.

Assuming the DataGridView does indeed have a 0-based column index, your BackColor = Color.Red code can only affect Columns(8) - which would be the 9th column.

I'm a bit confused now...

I still highly recommend Try..Catch blocks in all your procedures.

To start with - you need Try...Catch blocks
The only one I see (other than the designer generated) is in Module1.GetShellIconAsImage

Clean your solution first, then zip and upload entire project

This is that same project I already helped on, right?
If you zip and upload it to rapidshare, I'll take a look

Please confirm that I understand.

If I understand correctly, you can:

1. Determine which database to connect.
2. Connect to either database per user choice in ComboBox.
3. Retrieve the correct data.
   All at runtime

Is that correct?

I've never used TextBoxEx, but you should be able to set the TextBox.Text property in your code, correct? I believe that you should be able to set that text property to vbNullString or "" without triggering an insert to your database (test this thoroughly!!!) - and setting that property after you aquire the data should override the display text in the textbox.

What controls are you using to display the data?

Ok, thanks and good luck!

Scope_Identity() is always the primary key value of the last insert operation in the current user scope. You need a primary key on your data table or you're going to have issues. I'm too tired at the moment to think about whether that is causing issues in this particular query, but I think not - as long as you have a limited amount of data that you're working with anyway - say 10-15 numbers for testing.

For "Your_Identity_Column_Name" I entered the column name "Phone" which is what I am trying to match.

Nope. Use whatever you called your primary key column in the input : [Input].[ID]

it is doing full table scan each time trigger is running

Doing a table scan, as in query analyzer says "Table Scan" or looking through the whole table to see if there's a matching phone number (btw, you'll want an index on that column if you don't already have one) - Because "Table Scan" is bad - see previous reference to LIKE keyword...

So, if I understand - even if the phone number is already in the data table, don't match unless it is in the last row?

If you just inserted that last row, and you have a primary key on that table, and you're in the same scope (with triggers, you should be), use scope_identity() to get the primary key, and use that in your WHERE clause. Something like this:

ALTER TRIGGER [dbo].[test_trigger1] on [dbo].[testinput]
AFTER INSERT
AS
SET NOCOUNT ON

DECLARE @LastIdentity int                      -- assumes default int datatype for your primary key
SELECT @LastIdentity = Scope_Identity()

INSERT INTO testoutput(name)
SELECT i.name
FROM   testinput i
INNER JOIN testdata d
ON i.phone LIKE d.phone
WHERE i.[YOUR_IDENTITY_COLUMN_NAME] = @LastIdentity

If it's returning duplicate data, then you have duplicated values in whatever column you are defining your join on, in one table or both...

I know truncate clears the table.
But I added a new empty row first, so I always have that.

Adding an empty row is not really a viable solution. There are a lot of strong words (none of them very nice, some much ruder than others) that come to mind if you were doing that to my production database! :)

Are there other options to avoid reading the first row of your table perhaps?

There is no first row after the Truncate Table statement.

How are you displaying your data? You could probably do something with the SelectedIndex, Text or whatever property.

I'm pretty sure you can't make it read only the last row...

If you only want one row returned, then adjust your query accordingly.

By the way, never use a LIKE if you can avoid it - that will bring even the heftiest server to it's knees if there is enough data.

How do I set the if exists to actually match the phone number from data table, as opposed to simply checking whether or not if there is any data on that field?

Assuming your input has been validated so that it has to be either an exact match or a completely different number (i.e., (202) 123-4567, 202-123-4567, and 202.123.4567 are all the same real phone number)

In your IF EXISTS statement, add a WHERE [data].[phone] = [input].[phone]

When the expression comes true, how do I make it enter only that last row on the output table instead of entering all the pre-existing rows?

This is probably because you're pulling all the records, instead of just the one that matches the phone number.

Truncate table does just that - there are no empty rows after running that command.
What you are probably seeing is whatever SQL has for the default value on that column.

This is more of a SQL question, rather than VB specific

Try this mod to your code

        ' clear the imagelist so we don't have extras
        ImageList1.Images.Clear()
        ' you need this next line to keep the ListView from flickering!
        ListView1.BeginUpdate()

        Dim di As New IO.DirectoryInfo(LocationA.Text)

        For Each fi As IO.FileInfo In di.GetFiles("*")
            ' provide a default icon in case we can't extract one
            Dim icons As Icon = SystemIcons.WinLogo
            ' set that default as the new listitem's icon
            Dim li As New ListViewItem(fi.Name, 1)
            ' check ImageList to see if we have an icon for this file type
            If Not (ImageList1.Images.ContainsKey(fi.Extension)) Then
                ' we don't, let's try to get one
                icons = System.Drawing.Icon.ExtractAssociatedIcon(fi.FullName)
                ImageList1.Images.Add(fi.Extension, icons)
            End If
            icons = Icon.ExtractAssociatedIcon(fi.FullName)
            ImageList1.Images.Add(icons)
            ListView1.Items.Add(fi.Name, fi.Extension)
        Next

        ' all done, redraw the listview
        ListView1.EndUpdate()

You need to set the ImageIndex or ImageKey property for each item.
See How to: Display Icons for the Windows Forms ListView Control, and How to: Extract the Icon Associated with a File in Windows Forms for example code to add the ImageIndex or ImageKey and associate it with the file. FYI, ImageIndex and ImageKey are mutually exclusive.

Just simply write a code with the help of which i change the connection string in app.config and then save.

I don't know what you mean by that, your english needs some extra translation. As it is written, my interpretation is that you want me to "just simply write <you> some code to change your connection string and save it.... Hopefully that is not really what you're trying to say.

I have a problem with "connectionstring.udl" what it is?

Connectionstring.udl is an empty plain text file. The .UDL extension causes Windows to open it with the Data Link Properties editor. To add it to your project would require too much work on my part - you should probably scrap that code and use your own.

when my project start it show Splash screen after that it show Connection Form in which i enter Database Name , server name, user name, password etc . Now after that i confuse that how i pass these value to connection string. and second thing when i set the connection string after that it not show Connection Form at Run Time when i ReOpen the Project..

Post the form name and the code for that form

Not sure what you have for controls to access your data (DataAdapter, TableAdapter, etc.)
But those control types usually have a RefreshSchema() method at the same level as Fill() - that should be somewhat close to the runtime equivilant of the designer operation previously linked.

See if [this](http://stackoverflow.com/a/10597775"How to update the Dataset to reflect an added column in the data source without deleting the adapter?") works for you.

Hearty LOL!

I did write that without the IDE though :)

and that's why he is the moderator

Try this:

Private Sub btnSaveText_Click(sender As System.Object, e As System.EventArgs) Handles btnSaveText.Click
    ' assuming the form is the parent container of the textboxes
    ' otherwise use me.<container>.controls
    For Each ctrl As control In me.controls
        If TypeOf ctrl Is Textbox Then
        ' alternative check for ctrl.Name here
            If (Not(ctrl.text is nothing)) Then
                SaveText(ctrl.Text.ToString)
            End If
        ' end ctrl.Name check
        End If
    Next
End Sub

Sub SaveText(stringIn as String)
    ' TODO: your code here to save text
    Throw New NotImplementedException
End Sub

SQLClient is your DataProvider. In your original post, you made two requests (sic)

Now i need code for how i set the connection i.e when i execute the project .exe it show me a form in which there is Database name , Server name , User name and Password . And after clicking the OK button it pass these value to connection string and save there.

tinstaafl suggested:

One relatively simple way is put, Database name , Server name , User name and Password, variables in a module, and have your new form have a series of textboxes that the user can enter the information. A command button can contain code to validate the information and then pass the values to the global variables. It should be a relatively simple matter to build the connection string from there

Do you understand what he is suggesting here? This sounds like it would satisfy your first requirement.

I need simply the code for passing these value to connection string and how save these value there.

You just take the values from those textboxes, dropdown lists, or whatever and build the connection string.

For this question:

second thing when i set the connection string after that it not show Connection Form at Run Time when i ReOpen the Project

Try this code in your Form_Load() event

Dim strFile As String = "ChangeConnectionString.udl"
Dim strUserDocFldr As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
Dim newFullPath As String …

which method are you using

I posted the updated query in the other thread. Please close this thread, as I am still watching the other.

There were at least 3 different methods suggested in this thread, which one are you having a problem with?

Items have multiple entries in [Transaction Details]. That is throwing off the totals. Especially the Ttype = NULL rows. You might want to rethink your database design - especially if you can't query from it... :)

Try this and see if it works for you.

SELECT Description.Dgroup AS Description,
        COUNT(Item.IID) As TotalCount,
        [Under Repair]
FROM [Description] JOIN [Item]
    ON Description.DeID = Item.DeID
    JOIN (SELECT Description.Dgroup AS Description, 
                    SUM(CASE WHEN 
                        [Transaction Details].Ttype = 'Repair' 
                        THEN 1 ELSE 0 END) AS 'Under Repair'
            FROM [Transaction Details]
                JOIN [Item] ON [Transaction Details].[IID] = [Item].[IID]
                JOIN [Description] ON Description.DeID = Item.DeID
            GROUP BY DGroup) AS tmp
    ON Description.Dgroup = tmp.Description
WHERE [Under Repair] IS Not Null
GROUP BY DGroup, [Under Repair]

No problem. The query he gave you works, I checked it as soon as it was posted.

Here's some source code at MSDN for the Data Connection Dialog that was
?packaged/affiliated/an add-in? for Visual Studio 2005. Alternatively, there's this project at CodeProject.com.

Mine are still here...