I have a table as shows here:
col1 col2
1 rec1 *
2 rec1
3 rec1
4 rec2 *
5 rec2
6 rec2
and now I need to update the field col1 of the first records with
different col2 value. that means I need to update the col1 filed of the
* marked record.
any suggestion about how to do it with SQL server 2000?
thanks a lot.
I am trying to update a record in a table based off of criteria of another record in the table.
So suppose I have 2 records
ID owner type
1 5678 past due
2 5678 late
So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?
Hi All,
I am trying to create package something like that..
1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I will be appreciated if you can help me...
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
Is there a way to do this.
If I have a sorting which gives me multiples of column C1 but which can be uniquely identified by C1+C2+C3, and I want to query for the first C1 in each of C1's unique values, how would I go about doing that?
Hello Guyz,
A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).
F1 F2 F3
QQQ C 2
QQQ B 1
QQQ A 3
expected result:
QQQ B 1
my result:
when I group by F1, First(F2) and MIN(F3):
QQQ C 1
when I group by F1, MIN(F2) and MIN(F3):
QQQ A 1
when I group by F1, F2 and MIN(F3):
QQQ C 2
QQQ B 1
QQQ A 3
Any help would be very much appreciated..
Cyherus
I have following query
Select Date, Item_Code, SUM(In_Quantity) as In_Quantity, SUM(Issue_Quantity) as Issue_Quantity, (SUM(In_Quantity)-SUM(issue_Quantity)) as BalanceQty from
(
select tbl_add_product.Date as Date, tbl_add_product.Item_Code, tbl_add_product.In_Quantity, 0 as Issue_Quantity from tbl_add_product
where Item_Code = 'pen'
union ALL
select tbl_issue_product.Date as Date, tbl_issue_product.Item_Code, 0 as In_Quantity, Issue_Quantity from tbl_issue_product
where Item_Code = 'pen'
)
X group by Item_Code, Date
which gives following result:
**Date Item_Code In_Quantity Issue_Quanitity BalanceQty**
2014-12-02 pen 100 0 100
2014-12-03 pen 50 50 0
I want to 100 in second row. the logic is that balance Qty from first row should be added to In_Qty so that when Issue_Quantity is subtracted from it, it gives BalanceQty
Master,
How can I get the first record of each group which I had selected by "Group by".I try it but failed.3ks
"USE pubs
SELECT top 1 *
FROM titles
GROUP BY type
ORDER BY price"
Greetings anyone -
I have been attempting to figure out - in Report Builder - how to print only the last record in each group, i.e. the last activity. I see no Last function available anywhere.
Would someone please be so kind as to advise how this can be done?
If this cannot be accomplished in Rpt Builder, it looks as if the Last function is available within Report Designer. Unfortunately, although documentation states it's available, it does not state where this function can be utilized. I'm certainly having no luck.
Help pls?
Tks & B/R
query to show last record/Partner or PartnerName.
select 'PURCHASE' as EntityName, d.PartnerName, h.*
from (
select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId
from DataImport.PurchaseHistory
group by DistributorId, StartDate, EndDate
) h
inner join Partner d on d.PartnerId = h.DistributorId
where d.Active = 1
order by DistributorId, StartDate desc, EndDate desc
I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.
TABLE1 info
ID1 fname1 lname1 DOB1
ID2 fname2 lname2 DOB2
ID3 fname3 lname3 DOB3
TABLE2 info
ID1 Visit2
ID1 Visit3
ID1 Visit1
ID1 Visit4
ID2 Visit1
ID2 Visit2
ID3 Visit1
I need a view or SP to return the following:
ID1 fname1 lname1 dob1 visit4
ID2 fname2 lname2 dob2 visit2
ID3 fname3 lname3 dob3 visit1
It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!
thx
Here's what I'm trying to do
TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
2501102$1012013-04-23
3501102$1182013-02-04
4501102$982013-05-19
5501103$532013-05-21
6501103$392013-07-09
I'm trying to get the latest buy price for each fund a member bought.
So in the above example, I'm trying to formulate a query to retrieve all the latest bought price for memberid 105:
Desire Query Result
TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
4501102$982013-05-19
6501103$392013-07-09
So far, I've tried using Group By clause on FundID and BuyPrice and MAX() function for the date, but I can' get the TransID.
I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?
View 1 Replies View RelatedI’m writing a document management system. The documents themselves are created from the contents of a database. The database is SQL Server.
The database contains a table, like so:
ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name
Initially, the user will create a “V0.1” document. So the data would look something like
ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 0
MinorVersion = 1
Name = “My Document”
Thereafter, the user can create new versions as “0.2”, “0.3”, etc., or “1.0”, “1.1”, “2.0”, etc.
For example, a “2.1” document would be stored as:
ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 2
MinorVersion = 1
Name = “My Document”
The earlier versions will still exist on the database, but the latest version will be 2.1.
There may be several different documents, with different DocumentID’s (e.g. DocumentID = “1”, DocumentID = “2”), etc., and each of these documents may have many versions.
I’m trying to write a query to display a list of documents showing ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name… but the list should only display the latest version of each document.
So, if the database contained the following records:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name
1,1,1,0,1,My Document
1,1,1,0,2,My Document
1,1,1,0,3,My Document
1,1,1,1,0,My Document
1,1,1,2,0,My Document
1,1,1,2,1,My Document
1,1,2,0,1,My Second Document
1,1,2,0,2,My Second Document
1,1,2,0,3,My Second Document
My query should return:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name
1,1,1,2,1,My Document
1,1,2,0,3,My Document
… where 2.1 is the latest version of Document 1 and 0.3 is the latest version of Document 2.how to do it.
Hendra writes "I'd like to know is there any way or any statement that can help me to get first or last record from the group by query ?
I'm using sql Server 2000
Thank's
_Hendra_"
I have a set of timecard records with a projectNumber, taskNumber and TimeCardHours:
Rec# projectNumbeer taskNumber Employee timeCardHours
1 123 1.01.Test1 John 4
2 123 1.01.Test1 John 6
3 123 1.99.Test3 Mary 8
4 123 1.02.Test5 Mary 4
5 123 1.06.Test6 Mary 4
6 123 1.08.xyz5 Mary 6
7 123 1.03.xyz7 Jane 8
8 123 1.01.xyz4 Jane 5
9 123 1.04.xyz7 Jane 3
10 123 1.05.Test6 Mary 4
11 123 1.07.Test6 Mary 4
I have the following fixed set of tasks:
taskID taskNumberPrefix Task Description phaseID
11.01 Planning1
21.02 Design2
31.03 Requirements2
41.04 Coding3
51.05 Testing4
61.06 Build3
71.07 Packaging3
81.08 Documentation5
91.99 Management1
Finally, I have different phases:
1Planning
2Analysis
3Development
4Testing
5Documentation
What I would like to do is obtain a summary of timecard hours for each phase.
So for example, in Phase 1, Planning, this has two tasks based on PhaseID=1, Planning and Management which have task number prefixes of 1.01 and 1.99 respectively.
What I would like to is sum all the timecard records are LIKE 1.01% or 1.99% so my final results based on the above data would provide the phase name and total hours.
Planning Phase total Hours
Planning 23 (Sum of records 1,2,3,8)
Analysis 12 (Sum of records 4,7)
Coding 8 (Sum of records 5,9,11)
Testing 4 (Sum of record 10)
Documentation 6 (Sum of record 6)
I have tried various select statements, but helps up when I want to have TaskNumberPrefix LIKE taskNumber%.
Thanks for any assistance.
Hi all, I am relatively new to sql and have an issue I am trying to solve. I have a table with several records with the same id:
id amount date
-- ------ ------
1 100 01/01/2006
1 2000 06/01/2005
2 200 01/01/2005
2 500 06/01/2007
how would I get the data for the record with the newest date?
So I would return:
id amount date
-- ------ ----------
1 100 01/01/2006
2 500 06/01/2007
Thanks in advance for the help.
I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.
Thanks,
BobD
I would like to display all the products with maximum SeqNo from the table below:
TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445
OUTPUT
ProductIDSeqNoBalance
111510
121445
Here is some sample data:
CREATE TABLE
#MyTable
(
Pk INT,
GroupID INT,
Value VARCHAR(10)
[code]...
I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.What would be the most efficient way to write such a query?
Hello
I have a table from cars GPS positions:
CREATE TABLE Positions (
Ident VARCHAR(20) NOT NULL,
Valid BOOL,
Date DATETIME NOT NULL,
Latitude FLOAT,
Longitude FLOAT,
Speed INT
}
Sorry about the poor english... :)
I like select the last position where Ident=DEMO1 and DEMO2....
How can I make that?
Practically I sould like to now, where is the last positions of selected cars!
I try this, but not good:
SELECT
MAX(date),
ident,
latitude,
longitude,
speed
FROM
positions
WHERE
valid=1 &&
(ident='DEMO1' || ident='DEMO2'|| ident='DEMO3')
GROUP BY
ident
;
I get 3 record, the dates is the last, but the lat. and long. is wrong...
Thanks,
Laci
How to get
Code Block
a record value instead of aggregated value with GROUP BY?
Assume that I have a PRODUCT_COMMENT table defined as below. It logs
the multiple comments for products. A product may have multiple
comments logged at different time.
Code Block
CREATE TABLE [dbo].[PRODUCT_COMMENT](
[COMMENT_ID] [int] IDENTITY(1,1) NOT NULL,
[PRODUCT_ID] [int] NOT NULL,
[COMMENT] [nvarchar](50) NULL,
[UPDATED_ON] [datetime] NOT NULL,
CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED
(
[COMMENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID])
REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID])
GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT]
I would like to use the following SQL statement to get the latest
comments for all products.
Code Block
SELECT PRODUCT_ID, COMMENT, UPDATED_ON
FROM PRODUCT_COMMENT
GROUP BY PRODUCT_ID
HAVING UPDATED_ON = MAX(UPDATED_ON)
But this leads to the following error:
Code Block
Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause
because it is not contained in either an aggregate function or the
GROUP BY clause.
Is there a way to do that?
Thanks!
I have a table with records like that.
Group | Value
Team 1 | 0
Team 1 | 0
Team 1 | 1
Team 1 | 1
Team 2 | 0
Team 2 | 0
Team 2 | 0
I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.
This my table named myData
CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,
[code]....
I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow
select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,
[code]....
Looking to see if thier is a better way to find the last record entered in a group of records.
What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.
Table example using simplied data.
PolId
CoveragId
EffDate
Status
Limit1
2
1
9/7/2007
a
10000
2
2
9/7/2007
a
150000
2
2
10/1/2007
a
200000
3
1
9/7/2007
a
10000
The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.
The current query returns a single value. from a single table.
Current code used in the select protion on a larger query. bpi = basicpolicyInformation.
( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)
Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.
I have a table where I am grouping on one field and would like an individual (separate) count of values from another field of same table. So for example, I have following data:
instance_id, area, values
101 North 1
102 North 2
103 East 2
104 East 2
I would like to report on Area, and count of rows with different Values types, for example:
Area Value - 1, Value - 2, Value - 3
North 1 1 0
East 0 2 0
I am not sure what the technical term is for such report, but I can group by Area column, and but its aggregating counts on different Value types that I am having difficulty in performing in SSRS.
I am looking for some guidence on the following.
when i click the save button, The record should be updated to the table. I have produced the code below which does'nt seem to work. Please guide me
It works fine when i hard code the value for "textbox value" in line SqlDataSource1.UpdateParameters.Item("PrmVal").DefaultValue = "Textbox value"
Protected Sub SaveRecord(ByVal sender As Object, ByVal e As System.EventArgs) If Page.IsValid Then SqlDataSource1.UpdateParameters.Item("PrmVal").DefaultValue = Textbox value SqlDataSource1.Update() end ifend sub
<form id="form1" runat="server"> <div> </div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BmsConnectionString %>" SelectCommand="SELECT [PrmGrp], [PrmCde], [PrmVal], [PrmValArb], [GrpDsc] FROM [PrmDef] WHERE (([PrmGrp] = @PrmGrp) AND ([PrmCde] = @PrmCde))" UpdateCommand="UPDATE PrmDef SET PrmVal=@PrmVal, PrmValArb=@PrmValArb, GrpDsc=@GrpDsc,RecSts=1 WHERE (([PrmGrp] = @PrmGrp) AND ([PrmCde] = @PrmCde))"> <SelectParameters> <asp:QueryStringParameter Name="PrmGrp" QueryStringField="PrmGrp" Type="String" /> <asp:QueryStringParameter Name="PrmCde" QueryStringField="PrmCde" Type="String" /> </SelectParameters> <Updateparameters> <asp:parameter Name="PrmGrp" /> <asp:parameter Name="PrmCde" /> <asp:parameter Name="PrmVal" /> <asp:parameter Name="PrmValArb" /> <asp:parameter Name="GrpDsc" /> <asp:parameter Name="RecSts" /> </Updateparameters> </asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" DataKeyNames="PrmGrp,PrmCde" DataSourceID="SqlDataSource1" DefaultMode="Edit" Width="670px" style="left: 12px; position: relative; top: 12px; z-index: 100;" Height="359px" BorderStyle="Double" BorderWidth="5px" CaptionAlign="Top" GridLines="Both" BorderColor="Maroon" CellPadding="2"> <EditItemTemplate> <br /> Parameter Group: <asp:Label ID="PrmGrp" runat="server" Text='<%# Eval("PrmGrp") %>' style="left: 34px; position: relative; top: 0px" Width="125px"></asp:Label><br /> <br /> Parameter Code: <asp:Label ID="PrmCde" runat="server" Text='<%# Eval("PrmCde") %>' style="left: 40px; position: relative; top: 0px" Width="125px"></asp:Label> <br /> <br /> English description: <asp:TextBox ID="PrmValTextBox" runat="server" Text='<%# Bind("PrmVal") %>' style="left: 17px; position: relative; top: 0px" Width="318px"></asp:TextBox> <br /> Arabic description: <asp:TextBox ID="PrmValArbTextBox" runat="server" Text='<%# Bind("PrmValArb") %>' style="left: 25px; position: relative; top: 0px" Width="317px"></asp:TextBox><br /> <br /> Group description: <asp:TextBox ID="GrpDscTextBox" runat="server" Text='<%# Bind("GrpDsc") %>' style="left: 29px; position: relative; top: 4px" Width="316px"></asp:TextBox><br /> <br /> <asp:Button ID="CmdSave" runat="server" OnClick="SaveRecord" Style="left: 134px; position: relative; top: 49px" Text="Save" Width="72px" /> <asp:Button ID="CmdDelete" runat="server" OnClick="DeleteRecord" Style="left: 145px; position: relative; top: 50px" Text="Delete" Width="79px" /> <asp:Button ID="CmdClose" runat="server" OnClick="CloseWindow" Style="left: 160px; position: relative; top: 48px" Text="Close" Width="73px" /><br /> <br /> <asp:Label ID="ErrLabel" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#C00000" Style="left: 148px; position: relative; top: -38px" Text="Fields marked as (*) are required" Visible="False" Width="318px"></asp:Label><br /> </EditItemTemplate> <ItemTemplate> PrmGrp: <asp:Label ID="PrmGrpLabel" runat="server" Text='<%# Eval("PrmGrp") %>'></asp:Label><br /> PrmCde: <asp:Label ID="PrmCdeLabel" runat="server" Text='<%# Eval("PrmCde") %>'></asp:Label><br /> PrmVal: <asp:Label ID="PrmValLabel" runat="server" Text='<%# Bind("PrmVal") %>'></asp:Label><br /> PrmValArb: <asp:Label ID="PrmValArbLabel" runat="server" Text='<%# Bind("PrmValArb") %>'></asp:Label><br /> GrpDsc: <asp:Label ID="GrpDscLabel" runat="server" Text='<%# Bind("GrpDsc") %>'></asp:Label><br /> </ItemTemplate> <HeaderStyle BorderStyle="Double" BackColor="#FFC0C0" BorderWidth="5px" BorderColor="Black" HorizontalAlign="Center" VerticalAlign="Middle" /> <HeaderTemplate> Business parameter(Edit) </HeaderTemplate> <RowStyle BorderWidth="5px" /> </asp:FormView> </form>
Hi everybody,I am using SQL Server 2005. I have a table which currently has only 1 record. I am unable to update any field for this particular record and SQL server is timing out and giving an error message saying No row was updated. I created another record in the table and tried to update the fields in the new record without any problem. I am unable to update any field only for the 1 record in the table using my application, query window sql statement as well as directly changing the in the database.Can anybody please help me.thanks in advance,Murthy here
View 3 Replies View RelatedI am in a situation in which I would like to update my one table three column with other table three column, The other table might have more then one record but I would like to have the TOP 1 record of that table for these column. How can I achive it. I know I will be able to achive by writing three statment like
Update abc
set a=(select top 1 a from xyz order by ),
b=( select top 1 b from xyz) and so but not sure that a and b using the same record and thats the requirment of update is
any help much apprecited
Hello,update table set column = x where b is nullI have the above update statement in a transact sql file. I would liketo change it so that it will only update 1 of the records in the table,even if there are many records where b is null....Any ideas would be great.Many thanks,Allan
View 1 Replies View RelatedHiI've a table with 2 columns, one for a client code and one for adate/time and could be more than one record with the same client codeand date/time. the 3rd column is another date/time, NULL by default.I need to check if exists records for a determinated client code anddate/time and place the current date/time in the 3rd column for just oneand only one record.Is this possible ? How ?Thanks in advanceJ
View 9 Replies View Related
I'm very surprised since I cannot do a simple update operation...
Seems that the cursor after a Read, Readfirst, ReadLast or ReadPrevious is gone...
Here is a possible sequence from the user's standpoint.
1) Performs a QBE search
cmd_Logbook_search = Conn_User.CreateCommand()
cmd_Logbook_search.CommandText = sql_Logbook
rset_Logbook = cmd_Logbook_search.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
-----
If rset_Logbook.HasRows = False Then Exit Sub
rset_Logbook.ReadFirst()
Me.Fill_Logbook_TextBoxes()
2) Navigates
Try
If True = rset_Logbook.Read() Then
Me.Fill_Logbook_Textboxes()
Else
rset_Logbook.ReadLast()
Me.Fill_Logbook_Textboxes()
End If
Catch ex As Exception ' esta excepción se levanta si el recordset está vacio
MessageBox.Show(m11, m1, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
End Try
3) Update the record
If Result = DialogResult.Yes Then
Try
rset_Logbook.SetValue(rset_Logbook.GetOrdinal("Altitude"), Me.TextBox_Altitude.Text)
rset_Logbook.Update()
MessageBox.Show("OK", m1, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
Catch ex As Exception
MessageBox.Show(ex.message, m1, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
End Try
End If
And I always gets a "No data exist for row/colum" so...where did the cursor gone?
Any help, please I need to move forward! Thanks a lot in advance!!
Hi
I am new to visual studio and I am attempting to edit records held in mysql, the code below runs and throws no errors "strAdd" is underlined and says that it is used before it has been given a value! But If I debug.print(strAdd) I get the expected string returned. What do I need to do to get the updated records saved?
' code
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim StrAdd as string
cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(local);" _
& "Database=customerlink;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
Dim mySQL As String
mySQL = "SELECT *" & _
" FROM tblvsol" & _
" Where RevStatus = " & 0 & _
" And GeoCodeStatus = " & 1
cn.Open()
rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
.Open(mySQL)
End With
Do While Not rs.EOF
Code here finds the value for the string variable atrAdd
strAdd = New Value
If Not strAdd Is Nothing Then
rs.Fields("location").Value = strAdd
rs.Fields("RevStatus").Value = 1
rs.Update()
else
end if
loop
Regards
Joe