Comments by RossMW (Top 136 by date)

There not a lot of details for us to go on, but if you are using mdi then the child windows will be the same window state for all child mdi forms. Try stepping thru your code on a button click to see if other code is being run which is setting or changing the windowsstate.

I think you will need to supply more of your code then just where the error appears. Constructs, database setting and connection opening etc.

I'm not a Oracle person, but wouldn't you need to commit the table create before you can use it...

And you do plan on telling us what the exact code that is run, right?

See updated Answer....

When you build it or test run the code, an executable will be created in the Bin\debug folder

and the error message is.....?

You do not state what the error is, so its hard for us to determine what the issue is.
Of note is, you are not adding the topping costs but just stating that it is the cost of the last topping added. Not sure if that isyour intent.

Sorry, Not enough info to determine anymore options. Pacticularly around the database structure. It appears to be an incorrect sql statement construction (hence run it against the database directly to test). A lot of these issues can be solved by using parameters as highlighted by OriginalGriff. It a good practise to get into. Especially where the user may have any remote chance of an input into it.

Check the table structure against what you are try to insert. Also you can copy the QueryStr text and run it directly against the database and expeiement to find out where it is going wrong. You also need to be careful with date formats as sql has a bad habit of defaulting back to american format.

I appreciate english may not be your native language, but Im afraid I struggle to understand exactly what you are trying to do, and what the problem or issue is. Can you please elaborate

Sounds like an issue with your computer / software setup. Try going back to the basics. Can you create a blank project and add a webbrowser object to it with no atatched code and run it? If not, then you have an issue with your environment. If you can run it, then you need to start looking experimenting with basic code to run the webrowser.

And depending on circumstances put it in a transaction...

Good point. I tend to get a bit lazy and just use a simple solution. If the array not too big it should only have a very minor effect.

Have you tried this._dgv3.Tables[0].Columns[0].Visible = false;

Try just running in sql to see what the errors are.. eg

Declare @name char(30) = 'something'
Declare @description varchar(255) = 'something'
Declare @C_id numeric(18,0) = 1
Declare @Cost as int = 55
DECLARE @A_id INT
UPDATE A SET a_description=@Description WHERE a_name = @name
Select @A_id = a_id from A where a_name = @name
Insert into AC (cost, a_id, c_id) values(@Cost, @A_id, @C_id)

These are basic sql commands so once they work in straight sql they can then be put into the stored procedure as required.

What is the relationship of Table C to the other tables? It does not appear to be of any value.

The Return SCOPE_IDENTITY() command returns the identity of an inserted row. Your store procedure is trying to just do updates. As I don't know exactly what you are trying to achieve I can not comment on why it was there in the first place. Try removing it.

Trail and error is your friend if its not production data.....

It might help if you gave an example of the result you after.

If you are trying to have a case result in a field then use something like

Select CASE    WHEN Fieldname = 'A' THEN 1;    WHEN Fieldname = 'B' THEN 2;    WHEN fieldname = 'C' THEN 3;    End case;as Fieldname

The limited code you supplied appears OK. Maybe you need to step thru the code to find the issue and the error message or look for the problem in the rest of your code. There is inadequate info for us to determine the issue.

Normally you would store user info in a database, so depending on the database just set the user numeric value to the table unique ID field that is auto generated.

Why do you need a random number for a user?

I struggle with the data structure because you are joining both the translog to the tblcustomer and tblhistory tables. Hence if these are the same customers then both of your select statements will return the same translog records for that customer, so the result is the same effectivedates return. This implies the history table is irrelevant in the query.

I think you will need to provide the relevent data structure, sample data and expected output for us to help you any further.

Tell the truth. If they ask, answer honestly. If they dont ask, then theres no requirement to voluntary any information unless you think it will have an effect on your primary job.

Yes, I agree, but in reality, it all depends on the intent of information not supplied. My mind reading skills are not what Id like them to be...

Generic questions are hard for us to answer as the answer realy will depend on the specifics.

I suggest SQL books and fully understanding databases first as to change the structure afterwards can be a major mission.

Generally speaking if the table are properly indexed and the structure is correct then SQL performance will not be the issue. Joins and indexes will generally work better on numeric type fields

Try to avoid nvarchar(Max) unless the number of records is small. Better to fully determine the size of the data and have a field size to suit.

Well, looking at your code I can see.... No wait, cant see anything so no one can help you....

There inadequate information to answer this question, however instead of an array have you looked at temp tables

See the following CodeProject article Quick Overview: Temporary Tables in SQL Server 2005[^]

Never too sure on these vague issues. On your advice I'll post as the answer

Possibly. Peformance issues could be a number of things which would be impossible for us to determine without seing the structure and details of the tables.

Some of the posible areas are
- tables sizes
- key fields correctly indexed
- field type used for the joins
- Hardware etc

You will need to break it down and do testing, profiling to determine where the real issue is.

Unless there is more to the stored procedure than you've shown then I would guess the speed is related to the function and not the stored procedure.

Have you try running the function on its own. This is probably where the speed issue is.

Where does the 40* come from? Normally you just use isnull(Quantity,0)

You're going to have to provide more information then this. We can see what you are trying to do.....

Dateadd works over date type fields why are converting it to a varchar.

Also one query has "=" and the other has "<=". If the MerchLastShippeddate is a datetime field, the chances of it equaling is very remote and probably why no records are returned

Assuming the date field is datetime then everything is in a date format and no need to cast. Experimentation is your friend (in a test system)

If you look closely at your statement you have
update Category set MovieNumbers = x

Your statement does not contain any where clause so it is trying to update every record with multiple values returned by your
select count(MT.MovieID)
from MovType MT inner join Category C
on MT.[type] = C.[Type]
group by C.[Type]
statement and hence the error

Try
update Category
set MovieNumbers = numb

from

(select count(MovieID) as Numb, Type from MovType group by Type) x
where x.type = category.type


I will update the solution...

I should you talk to your Companies IT support team, as there is a large number of variables and specifics about your companies setup we will not know about.

You may need to rethink what you are trying to achieve. In your query you are try to sum up values from a recursive loop, but that loop may have one or multiple recursive levels. I cant see how this is achieved in a select statement. To achieve this purely in SQL you will probably need as table function to do this but it could be complex and slow. Below is an example of a recursive function to retrieve the top most employeeid of a given employee as a guide or starting point.

<pre lang="SQL">
Create FUNCTION dbo.FindParent (@ID int)
returns Int

AS
BEGIN

Declare @parent int
Declare @tmp int
DECLARE @counter int
set @tmp = @ID
set @parent = @ID
SET @counter = 1
WHILE @counter < 10 and @tmp is not null
BEGIN
Select @tmp = Parentid, @parent = id from employee where id = @tmp
SET @counter = @counter + 1
end
return @parent;
end
GO
</pre>

A couple of options to check.
1. Have you tried having the strColl.Add values with the files full directory path ?
2. Does the script have the appropiate security rights ?

Sorry I do not understand the changes. If the number of values in the FHeadid has increased, then they just need to add them to the queries. You should study the queries with Google to see what they are doing so you can learn to make the required changes yourself.

Have you looked at saving the pdf to a file and sending it as an attachment?

It looks like you want to return a table or result set, so have you looked at a view or Table function rather than a stored procedure?

Answer has changed with full sql statement. Your DAtaTable code will need to be changed.

See modified answer above. Depending on what search conditions you are trying to achieve, you can have various combinations of above. But essentially you need to ensure the SQL is valid and run it manually across the database and correct until you get the desired result.

On the database tables. Not your application.

What is wrong with the result of your query you have tried. We cant see your tables so are not sure what it should be.

You probably need to also check out http://www.sql-join.com/[^] to see different join types

Can you please provide an expected output. It is unclear how you expect the provider information to be displayed when they are not active for that month.

Otherwise, it would appear you just need to join the two tables together and filter by Month(ServDate) and month(ProviderActiveDate)

If you are only executing select statement why don't you just check the SQL statement for key words to check if it should be run?

Sorry Mum

To sort just the select Union statement you have to wrap it around another select statement with an order statement at the end.

Ie select * from(select.............union all .........) order by RecieptNo, s_adm_no

Having a closer look at your question you have a FHead field and a FHeadID field. I assumed these to be the same field, but I now suspect you have an FHeadID as numeric and a description FHead.

If this is the case then you will need to do a join and retrieve the FHead field as text for the pivoting and not the numeric FHeadID field.

What type of field is the month data? I assumed these were numeric

Working late tonight ?

Try

MyListBoxItem item = listBox1.Items[e.Index] as MyListBoxItem; // Get the current item and cast it to MyListBoxItem
if (item != null)
{
e.Graphics.DrawString( // Draw the appropriate text in the ListBox
item.Message, // The message linked to the item
listBox1.Font, // Take the font from the listbox
new SolidBrush(item.ItemColor), // Set the color
0, // X pixel coordinate
e.Index * listBox1.ItemHeight // Y pixel coordinate. Multiply the index by the ItemHeight defined in the listbox.
);
}

Haven't tested personally. is foreground not a option?

Sorry. Did a quick test and found the listbox cant do it natively so you have to dig into the bows of it. See update solution

You need to look at the SQL server. 5000 records in 30 is poor performance. Indexing will help that I would suggest there is something fundamentally wrong with your SQL server configuration, setup, or spec

It is not clear as to what defines the current and previous transactions. Is it just the top 2 ordered by transactiondate desc?

Firstly, try and avoid creating the SQL string, but use parameters instead to avoid SQL injection. Its a good habit to get into. I know its more verbose, but a good habit nevertheless.

Also; Does the database have two new records created or just grid displaying duplicates. If its the database, you should be able to step thru the code, to see where it is happening (code run twice or the grid also adding a record.) If its just the grid displaying duplicates, then make sure the gird is cleared before repopulating.

How consistent is the data format. If it's consistent you can look at breaking it down to its respective parts, and put it back in the format you want to convert it to a date field.
But if it's a human input text field, I don't fancy your chances unless you planning to data scrub.

Unfortunately I'm on my mobile so can ease see everything, but it sounds like it is overwriting rather than appending. Search google for appending to a file

Is your question about the datagridview object not being cleared or the data in the underlying table not being deleted. The clear command just clears the content of the grid, not delete anything

this sounds like a select statement is returning multiple values when it is only expecting one only. Check these and your data. Particularly on any = statement that are returning arrays

Manually run each statement with the variable you are running it with.

If it's working fine on your desktop but not your laptop then you will need to look at what is difference between the two. Ie missing components of VS or other references etc.

Also you can look at the logs on the laptop to see if anything interesting in there.

What code have you tried and where is the issue. Remember we are here to help with specific problems, but we're not here to write your code for you.

Yep, The general method is as you have stated, so where are you having problems and what Code have you tried, remember we can't see your computer or the details of what you are doing.

That what the desc (descending) statement stand for.

By definition that is correct, otherwise you will need a secondary unique Id field to seperate out the same values

What wrong with a order statement or row_number see https://msdn.microsoft.com/en-nz/library/ms186734.aspx

Correct me if I'm wrong but is your question on How to call the SelectSqlRows function.

If this is the case then you will see from the first line of the function that

Private Function SelectSqlRows(ByVal connectionString As String,
ByVal queryString As String, ByVal tableName As String) As DataSet

has three string values passed to it and returns a dataset object. Hence to call it is essentially as you have above but you will need to set the returned object to something
ie
Dim ReturnDataset as Dataset = SelectSqlRows(connectionString, queryString, tblCustomers)

Hi Dave

Reread the article and noticed the section on Wow6432Node part of the registry. Set it to 10000 and it works a treat. You're a hero Thanks Ross

I tested it via the compiled exe. Normally the development is saved on a server so I even copied the exe locally to my pc before testing.

Yes, read a similar article. Unfortunately been down that road and tried every version available to no avail. I'm nearing the throwing my arm in the air and giving up....

Have you thought about a union of two select statements. One with the first 7950 char and a second with the characters where length > 7950 and the remainding text?

Have you tried removing parts of the SQL statement until it works and hence determining which part of the statement is causing the issue. The syntax looks OK for a SQL Server, assuming the fields are named correctly etc.

It really depends on exactly what you are doing, as you want it set at the start, I would put it in he initial load select statement.

As per your example you gave above and my response. Or am I missing something in your question?

As I said before getdate() is an SQL function. A quick search of the Internet says access equivalent is now().

Try that

Make your select statement "Select * from tbLMPH where Year(datadate) = year(getdate()) and month(datadate) = month(getdate())"

Please Note: The statement is SQL server format. I'm not sure about access but it will be very similar. Try running direct against the database to test first. (Access query)

No Problem. We're here to help.

When you are opening the form the datagridview will be retrieving the data. Now it depends on how you have set this up but you will need to change it to add the where clause in the select clause. If you set this by linking direct to the table then you may have to remove it and use code to set the date set. There are plenty of examples on the Internet that show how to do this. The datagridview can be quite confusing for someone new so I suggest you start with something simpler as practice. Like populate a combo box from code (not by linking)

What defines a row as hidden? Normally this is done at the query level rather than the populated table level.

I cant imagine Microsoft building in a means of letting you exceed the designed limitation of express. Otherwise they would never sell any of the other versions. Legally I should imagine this would be a no no..

If I understand correctly you are wanting to loop thru studentbiod array for each studentid. If this is the case then you can have a studentBiod loop inside the studentid loop.

I wasn't sure if it was a join requirement or a union all as per the sample. But a good complete answer for a join requirement

Have you tried running the command in cmd window to test if it the xp_cmdshell or the command you are trying to run

Yes but are you expecting table3 to have table1 data followed by table2 data in completely seperate rows. i.e. If you just run two seperate insert statements

Have you researched Google for the group by function?

Unless you have a link (or common reference field) between table2 and table1 then you can't align the data to the correct row. Or are you just trying to append the data one table after the other?

You will have to provide more details then this, however, as the message said it appears to be an incorrect connection string. We do not the details of your system to help here and you should search the for the correct connection format and put in your system details. (trial and Error).

Do you get any error messages. These can point you in the right direction via google.

I take it you have sql2008 and 2014 express installed on the same server?
If this is the case then just stopping the SQL service will not stop the server recognising that it is meant to be a SQL server.
What's the problem with connecting via the instance name instead of the IP address?
If you just want express on the server to be the main database then you should uninstall sql2008

Also as you are creating a statement the quotes will need to be fixed. To add a quotes two quotes for each qoute

What is the error message or what is not working?

When you say using sql, do you actually mean using a sql database with the listbox and textbox data stored in tables?

What are you trying to do. Also remember we can not see your data to see the effect of the query. The where clause (AlbumGuid ) seem to cancel themselves out so you end up with everything

I suggest add a where clause to filter just the records with '(' and union all with a seperate query olong the same lines with the [ instead.

This assumes there is only either a ( or [ in the fields.

There may be a better way but unfortunately I do not have SQL handy to test

Your quick. Bet me to it ha ha

When you close the form do you also dispose() ?

What field types are TimeStart and TimeEnd fields?

Looks like you are just trying to check summated data. From a simplicistic point of view can you create two views to retrieve the summated data in each of the systems. Then as they are in linked servers you can create a join between the two views to get the differences. Once you can see the differences you can then put in a stored procedure for the alerting. Ie break it down to small steps and test each step

Have you restored all the files. Also make sure you have the data pointing to the mdf and the logs to the ldf file.

As they're linked have you tried to do a join to see the differences. Due to the size you would want to make sure you return just the critical information ( today and yesterday's data and critical fields) . If that works ok you can wrap in a stored procedure with an email alert via SQL job etc.

You could also use SSIS to do this as well, but if you are not familiar with it then you are best to see tutorial online first. It would not be easily to describe in a Q & A how to do it in SSIS.

Are the two server linked SQL servers? Also the method you would use would depend on the tables sizes. How big are they?

Do you need to know the differences or whether there are differences (checksums)?

Do you have a primary key on the table? Updates and deletes generally don't work without it.

You will need to give the table structure as we cant guess or see it. If its the same table and depending on how the data is stored you could join the table to itself based on the different years.

Your questions a bit vague to give a specific answer. But generally speaking, If it is just an OS update (not SQL) then after the SQL server is installed you copy the detached database from one server, copy the files over to the other server and reattach them. If the SQL is upgraded as well then you can do the same, but you should use an upgrade tool to show any possible issues. (if any)

Have you considered a stored procedure cursor with a variable for remainder amount to be carry over from row to row?

It unclear about the details of the report viewer, however, the report viewer will need to be able to run with the passed value from Txtid. To update the report viewer you will need an event (button command or Txtid event) to run the report viewer passing the Txtid value.

Email.....

Interesting... but what happens when there are two or more records with the max price. It comes down to the exact meaning of second maximum price. May need to distinct the prices.

Thanks. Painful limitation of try to write code etc on a damn iPad. Always end up with stupid mistakes!!!

Why not just use a join rather than Union?

True. Thanks

Not near a computer to check but the data type is decimal(16,6), so will be represented as such. You probably just need to convert to numeric(16,0)

Triggers will only operate on a update, insert or delete. As you are referring to a select then a trigger is not really an option. One way round this is to get your complex process to run a stored procedure after the select from view statement that writes to a history table or does the inserting itself.

What is your meaning of remote connection. ie (on the lan or remote via a firewall). If via the firewall then the remote computer may not recognise the connection attributes or have access to the security you are using.

Sorry. Not my question. Trying to get to the bottom of it myself

I'm a bit unsure of what you are trying to do. But You can get the duplicates to delete from delete from tablename where ID in (select ID from ...table name... Where ID > 200 group by replace(name,' ','') having count (replace( name ' ','')) > 1). You need to include all the information etc of what you are attempting to do.

There are a number of possible problems
1. Security on the SQL server
2. Code may have Access specific commands.
3. The Data structure may have slight difference structure and primary key etc.
4. and probably many more reasons.

Hence. the question is too vague to give exact reasons. Best thing to do is to look in the applications code.

You are on the right track. I'm not familiar with odbc or MySQL but once you have your connection you need a reader to read the data as stated below..

Unfortunately With an iPad i will not be able to give an example until tomorrow, but the princple is the have a reader for your select adaptor. Loop through the records reading the data into variables or dynamically creating a sql statement for your insert statement to be run against your destination connection then moving through the loop to the next record. If your not sure about readers you will find plenty of examples on the web. Hope this helps.

For that to work the server need to be linked and the tables referenced by the servername.dbo.tablename. When you are referring to a local host I presume you are referring to a database on your local computer( like sql express). If this is an application to be run by many portable users then it would not be practice or advisable to use linked servers between the sql server and local databases. Hence as stated before you are only left with using two connections, one reading and the second to write the data to the server database.

Unfortunately I'm on a silly iPad at the moment so can't write code at moment, but depending on the table you may just have to transverse the local table a row at a time and insert the data into the server table

Depending on SQL version, have you looked at SSIS or older DTS?

If the data is in a text type field then you can only compare random text with some defined rule which you have not provided. Hence under these conditions solution 1 is the best method or a expansion of it like field = '%L S%' and field like '%Kumar%'.

Have you thought about security. On your pc it is probably using your security credentials, but what credentials is the server running under to run the report ?

As a side comment. If you are changing the type that will lose the data you can add a new field with the new data type and convert and copy the data to it, then delete the old and rename the new field.

If you have the staging table with a fixed structure, then why do you need a validation table? Would it be easier to populate a fixed staging table then recreating it. If possible it would be a good idea to have a batch number for checking etc.

How is the validation table configured. If you are looking for the same record in the validation table then you can join on the staging table and the validation table and insert on the correct data that matches.