SQL Query To Update The MAX Within A Set Of Common Numbers In A Column?
Jun 11, 2011
I have program in VB.NET 2008 where I want to update the premamt cell value in the tblPrem Access table, where the premno is the maximum (autogenerated as 1,2,3...), but only within a specified set of common numbers in a column (contno) I am currently saving. This contno is from another table (tblMain) linked in this table (tblPrem) where it is the primary key, and just repeatedly generated after the clicking save button. Here is my current code which saves the data but does not do anything on the textbox1 + textbox2, and has an error message "At most one record can be returned by this subquery":
sqlQrySave = "UPDATE tblPrem SET premamt = " & textbox1 & " + " & textbox2 & " WHERE premno=(SELECT MAX(premno) FROM tblPrem GROUP BY contno);"
Essentially, whenever my users who are logged in via a global module level variable enter a new form, their activity is "logged" in a textfile with their UserID as the name.txt and every time they visit a page it adds a new line to their log file with a number corresponding to the form they visited. For example, if they select "add a user" it will add the line "1" to their log file. Note: At the moment there is only 1 2 and 3 not 0.
I have got this far and I have got as far as creating an array which has: Array(0) = NumberOfTimes 0 appears in the log file Array(1) = NumberOfTimes 1 appears in the log file etc etc
Therefore the output for this is Msgbox(Array(0)) shows "0" MsgBox(Array(1)) shows "4" for example and lets just say MsgBox(Array(2)) shows "9" and MsgBox(Array(3)) shows "1"
Therefore, finally (!) my problem is now finding which 3 are the top. I have tried various methods of sorting them but I just get "9, 4, 1, 0" rather than "2, 1, 3, 0". (By the way the end product for this is to show three links on the "welcome form" of the users 3 most visited forms). Here is a pastebin of all the rubbish I have currently tried that hasn't worked! [URL]
It seems that on clicking datagridview column header, the column will be automatically sorted based on the column type. I have a column showing some numbers. If column type is string, it sorts "1","20","3" into "1","20","3". If column type is double, it sorts into "1","3","20" which is the result that I want. However, there might be some erros in the numbers and error messages(text) will show in the cell instead of numbers. So I cannot set the column type as double. I want to ignore these error messages and sort all the numbers. How can I do this?
Also, I need to add some background colors to different rows in datagridview. So in the column header click event, I call the bkgColor Sub to achieve this. My question is that how can I override the sorting method in this event?
Private Sub DataGridView1_ColumnHeaderMouseClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick Try
I have a program and I get time data from a sqldb and display it in a column in a listbox. What are the proper steps for adding the time as I iterate through the data in the column? I figure I will need to do some conversions on the time to be able to add it and display it as a total.
I'm using the following update method.. I'd like to be able to update the value of a checkbox into the existing query if possible. how to do this the correct way?
Public Shared Function SaveMemo() As String Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and Settingsdiw07My DocumentsDaily Backupsphone memo backend.mdb"
"Select * from Table WHERE ItemNum=2 OR ItemNum=4 OR ItemNum=6" I use the code above to select ItemNum w/ even number.. but the problem is how about 8 and above? The same with odd number?
I am creating my Excel great, except that a field that contains numbers ie 1-10 or 2-5 keep getting changed to the equivilant in date. I don't want dates. If I format to text, then it cuts off part of the original data.
I have a datagridview with a column called "Total Price". This is set to a string value type because I need to always show 2 decimals. For example: 4,3 must be in the datagridviewcolumn: 4,30
So I use string.format for this. However, now I have a problem with sorting because the column is a string value type and numbers get sorted like this: 50 4000 40 300 30 20 10000 1 How to override the sort method or something that is called when a user clicks on the column header and the most effective way to sort it programmatically?
I am creating a process part of which returns a delimited string containing security group numbers, a few examples:
'3|5|6|9|7|4|8'
'5|6|299|27|4|8
'5'
I'm looking for help in fathoming an algorithm for splitting this string into the numbers so that I use each number as an argument within a separate query.Since there maybe only one number, or dozens I cannot work out the logic to make this happen correctly each time.
For my database I need to create unique numbers and insert them in the column "ID". Can I add them via a SQL query? The numbers would simply be 0001 to whatever.I'm using the "Show Table Data" gui.
I have a DataGridView1 where I have 6 columns and 0 rows (they are added additionaly in the application) and also have a TextBox1. How can I calculate all data from the second column to the TextBox1? For example, I'll run the application and add rows, where the text in the second column will be: (4, 8, 9, 1)... it means 4 rows...
So how can I make the value of those numbers (22) show in the TextBox1, so that it will calculate the numbers and show the sum in the TextBox1?
I found this from a Google search and I need the VB.NET equivalent to Martin Liss' code
Dim lngIndex As Long Dim lngTot As Long For lngIndex = 1 To lv.ListItems.Count lngTot = lngTot + lv.ListItems(lngIndex).SubItems(1) Next
[Code]...
Value of type 'System.Windows.Forms.ListViewItem.ListViewSubItem' can not be converted to 'Integer'Basically, I need to total up a number column in a ListView.
I have 5 columns of 11 textboxes each on my form the textboxes are generated with this code
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'column 1 textboxes
[Code].....
i need a code to generate all the possible combination of the numbers a user types into the textboxes i have two images (Panel3: the textboxes where added during design time) image2: the textboex were added using the code i gave about
generate all the possible combinations of numbers typed into the textboxes (Example see panel3.jpg image),
The user will type in the desired unique numbers into the textboxes without repetition (Please see the image i did not repeat any number, so can the code detect when a number have been typed twice?) Else when running the program i will be carefull not typing two numbers twice, but its adviceable to detect when a number is typed more than once.
Then the amount of unique number the user will type or supply to the desired textboxes does not matter here and as you can see in the image the amount of numbers in each column is not the same ie it may or may not be same amount (but number must be unique. Please try and see if it can be done to prompt user to enter the amount of numbers to supply in each column)
Then After supplying the numbers just like the one in Image, The button Click will generate all the Possible Combinations of the all the numbers typed into the textboxes in the columns.. Then After generating the combinations in the Listbox, the next Button will be able to delete them from the Universal 90c5 just like the former.
i have wrote a loop to search for duplicate numbers within a column in a database which i have got fine but when i find a duplicate i would like to get the all of the row so if 33 was a duplicate i would like to get the following row.
33MarkWhitesideM12/07/1958Michaeluuu
I have tried to do this buy use something i saw on msnd's website:
Dim foundRow() As Data.DataRow = customerDS.Tables("Details").Select("HNCNO Like '33%'")
However if i change the line to notice the i beside the like
Dim foundRow() As Data.DataRow = customerDS.Tables("Details").Select("HNCNO Like 'i%'")
but i get Index was outside the bounds of the array. Ok i know that means that there is nothing in my array. What i would like is to find the duplicate row associated i. you will probably understand me better when you see the code.
Private Sub checkRowsForDups() Dim customerDS As DataSet Dim dupArray As New ArrayList
I posted in the Database development forum, but no bites. I have field names in my Access table which have a number as the first character (i.e. "2x2"). Evidently, the DataAdapter doesn't like this. I'm able to view the data in a DataGridView, but I get an exception when I use DataAdapter.Update.
Can I modify my sql statement to make the command builder work or do I have rename the fields?
For example:
"SELECT [2x2] as twoBytwo..."
Changing the names of the fields would be a huge pain-in-the-arse!
I wanted to update a column in my table, i have written the code it runs fine without any error also it displays the confirmation dialog box but the table is not updated whats wrong with the code.
Dim sqlConn As New SqlClient.SqlConnection sqlConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|housingsociety.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
I'm creating a form application using vb. I'm still new to this and I'm teaching myself via the internet so please don't be too mean. I wrote my code using queries that I created in visual studio. I don't quite remember how to create an update query and write the code to update different datagrids. I tried searching the forums, and I can't find anything that fits into the way I wrote my code. I have an update button that I want to use to save all the edited data in the form. Here's my code so far:
Public Class Form1 Private Sub Blsys_systemsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Me.Validate() Me.Blsys_systemsBindingSource.EndEdit()
I am having a bit of a problem with a query and I have google around and I still haven't find out a solution.I have an update query, but when it is executed I get a Syntax error in UPDATE statement.
i have a query that when i updating my database it doesnot take *,&,-,#,@ all these,but when add a new database its taking all above i have fields of roll#,Sname,Age and address?
I need to run a query when I add/Change a value in my first column in a datagridview. When I add a value (a identifier from a table) in my first column, then I need the query to retrive the rest of the values in the table recordset. When I try to run a query, all i get is a query that updates my whole datagridview, and not only one row, with this code in the CellValueChanged module of the datagridview:
Is there anyone who knows what I have to do, so I can add several rows to my datagridview, and let a query retrive my recordset based on my value in the first datagridview column?
I COMPLETELY understand how redundant this is but I NEED it.Upon users creating a User account which only contains the ID, Username, Password and CustomerID I must also have a record in Customers create itself but null everything and the User.User_Customer_ID must update to the newly created Customer.Customer_ID
I have everything working but this pesky update query. The ONLY thing it needs to do is change User.User_Customer_ID to Customer.Customer_ID I managed to story the User.User_ID in an integer called UserID and Customer.Customer_ID in an integer called CustomerID I have tried a million different ways with no success
On an SQL query I have. I have the select query working fine but according to the result I need to set the column of a table to 0. I have the following Select query:
CODE:
So where the CommissionCalculator SetUp > 0 I want it to Update the CommissionStructure SetUp column to 0. I was thinking I might need an IF statement but I dont know how to work with them in SQL.
