logo
Tags down

shadow

How do I connect to a database and loop over a recordset in C#?


By : HtW
Date : March 29 2020, 07:55 AM
hop of those help? @Goyuix -- that's excellent for something written from memory. tested it here -- found the connection wasn't opened. Otherwise very nice.
code :
using System.Data.OleDb;
...

using (OleDbConnection conn = new OleDbConnection())
{
    conn.ConnectionString = "Provider=sqloledb;Data Source=yourServername\\yourInstance;Initial Catalog=databaseName;Integrated Security=SSPI;";

    using (OleDbCommand cmd = new OleDbCommand())
    {
        conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = "Select * from yourTable";

        using (OleDbDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                Console.WriteLine(dr["columnName"]);
            }
        }
    }
}


Share : facebook icon twitter icon

How to set the "RecordSet", using SQL, to connect to a database


By : esad
Date : March 29 2020, 07:55 AM
it fixes the issue I've been using a tutorial to learn how to create and connect to a database using Java. Link to the tutorial. , It is ResultSet and not RecordSet
Change
code :
  Statement stmt = con.createStatement(RecordSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

Updating Database from csv using ado and vbscript without recordset loop?


By : Romeo VaVa
Date : March 29 2020, 07:55 AM
it helps some times Use "INSERT/SELECT INTO IN 'External Database'". For your specific task (text -> mdb, your query) the statements would look like
code :
Create new table:
SELECT T1.PERSON AS PERSON, T2.STATUS AS STATUS INTO [tblPeople] IN "P:\athto\your.mdb.mdb" FROM [People#csv] T1 INNER JOIN [Company#csv] T2 ON T1.COMPANY = T2.COMPANY

Append to existing table:
INSERT INTO [tblPeople] IN "P:\athto\your.mdb.mdb" SELECT T1.PERSON AS PERSON, T2.STATUS AS STATUS FROM [People#csv] T1 INNER JOIN [Company#csv] T2 ON T1.COMPANY = T2.COMPANY

Loop through DAO recordset and copy and paste into other recordset, then delete it


By : Rohan Ecotech
Date : March 29 2020, 07:55 AM
hope this fix your issue There are a few possible causes to this issue. The first is when you have If rsDatalog1.Fields(Datastamp) >= Now() - 90 Then DataStamp should be in quotes. You can also reference fields with an ! such as rsDatalog1!Datastamp.
You shouldn't have your rsDatalog1.close inside your loop. This will cause it to fail on its next pass since it won't be able to check for end of file.
code :
Private Sub Command22_Click()    
    Dim dbs As DAO.Database
    Dim rsDatalog1 As DAO.Recordset
    Dim time As Date
    time = Now() - 90
    DoCmd.SetWarnings False
    DoCmd.Echo False
    DoCmd.Hourglass True
    Set dbs = CurrentDb()
    Set rsDatalog1 = dbs.OpenRecordset("SELECT DateStamp, LocationID, DataType, LogValue FROM DataLog")
    Do Until rsDatalog1.EOF
        If rsDatalog1.Fields(Datastamp) >= Now() - 90 Then
            dbs.Execute "INSERT INTO Archive (DataStamp, LocationID, DataType, LogValue) VALUE ('" & rsDatalog1!DataStamp & "', '" & rsDatalog1!LocationID & "','" & rsDatalog1!DataType & "','" & rsDatalog1!LogValue & "')", dbFailOnError
            rsDatalog1.Delete
        End If

        if rsDatalog1.recordCount <> 0 then
            rsDatalog1.MoveNext
        end if
    Loop

    rsDatalog1.close
    set rsDatalog1 = nothing
    DoCmd.Hourglass False
    MsgBox "Finish"
End Sub

vba loop through fields in recordset while another recordset is not EOF


By : man_in_black
Date : March 29 2020, 07:55 AM
help you fix your problem I have solved this problem now. The main reason I was getting incorrect values in the destination table was because using the "accountNumber" variable was not necessary. Instead I used the "AutoID" variable value as the first field on the destination table when looping through the code.
Very simple fix but it did take me a while unfortunately, hence the reason for posting as I needed an extra pair of eyes!
code :
Private Sub btnTransfer_Click()

Dim dbs As DAO.Database
Dim temp As DAO.Recordset
Dim bStocked As DAO.Recordset

Dim fld As DAO.Field

Dim AutoID As String
Dim Product As String
Dim varProd As String
Dim PackSize As String
Dim priceType As String
Dim casesSold As String

Dim accountNumber As Integer
Dim counter As Integer


Set dbs = CurrentDb
Set temp = dbs.OpenRecordset("SELECT * FROM tbl_TempProducts WHERE id IS NOT NULL")
Set bStocked = dbs.OpenRecordset("SELECT * FROM tbl_BrandsStocked")

counter = 0
firstRun = True
accountNumber = 0
AutoID = 0

temp.MoveFirst

Do While temp.EOF = False


    For Each fld In temp.Fields

        If fld.Name <> "" Then

            If counter = 1 Then
                    AutoID = Nz(fld.value, "")

                If AutoID <> "" Then
                    AutoID = Nz(fld.value, "")
                    bStocked.AddNew
                    bStocked!AccountNo = AutoID
                    bStocked.upDate
                Else
                    counter = counter - 1
                End If

            ElseIf counter = 2 Then
                Product = Nz(fld.value, "")

                If Product <> "" Then
                    bStocked.MoveLast
                    bStocked.Edit
                    bStocked!Brand = Product
                    bStocked.upDate
                Else
                    counter = counter - 1
                End If

            ElseIf counter = 3 Then
                varProduct = Nz(fld.value, "")

                If varProduct <> "" Then
                    bStocked.MoveLast
                    bStocked.Edit
                    bStocked!Variation = varProduct
                    bStocked.upDate
                Else
                    counter = counter - 1
                End If

            ElseIf counter = 4 Then
                PackSize = Nz(fld.value, "")

                If PackSize <> "" Then
                    bStocked.MoveLast
                    bStocked.Edit
                    bStocked!PackSize = PackSize
                    bStocked.upDate
                Else
                    counter = counter - 1
                End If

            ElseIf counter = 5 Then
                priceType = Nz(fld.value, "")

                If priceType <> "" Then
                    bStocked.MoveLast
                    bStocked.Edit
                    bStocked![RRP-PMP] = priceType
                    bStocked.upDate
                Else
                    counter = counter - 1
                End If

            ElseIf counter = 6 Then
                casesSold = Nz(fld.value, "")

                If casesSold <> "" Then
                    bStocked.MoveLast
                    bStocked.Edit
                    bStocked!CPW = casesSold
                    bStocked.upDate
                Else
                    counter = counter - 1
                End If
            End If
        End If

        counter = counter + 1

        If counter >= 7 Then
            counter = 2
            bStocked.AddNew
            bStocked!AccountNo = AutoID
            bStocked.upDate
        End If

    Next

    temp.MoveNext

    counter = 0

Loop

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM [tbl_BrandsStocked] WHERE [Brand] Is null"
DoCmd.SetWarnings True

Set dbs = Nothing
Set temp = Nothing
Set bStocked = Nothing
Set fld = Nothing

End Sub

VBA Update table/recordset in Access using Loop with values from another table/recordset?


By : Alexandre Maurin
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I hate to be the one to tell you this, but you should reconsider using SQL to do this update. I see that you have already written a lot of code and might feel like if you switch back to SQL that you will then have wasted all this vb code. I have felt like that myself in times past. But you can solve this problem with SQL with an order of magnitude less code(or nearly so).
Steps for SQL solution:
Related Posts Related Posts :
  • How to check whether string contains specific string in c#
  • UPnP hole punching can't use external IP to access device on local subnet
  • Is there a way to use a conditional when declaring method variables?
  • c# interop Excel - disable or remove automatic page breaks
  • How can I read in these commands from a file using?
  • Pinch and rotate around a point using MRTK and Hololens 1
  • Enhance performance to paint image, is SIMD perhapse a solution?
  • Which one has better performance, data types .NET or alias name data types C#?
  • HttpClient GetAsync taking ~2 seconds
  • linq fastest where method
  • Why can I read more than 254 characters with Console.ReadLine when the docs suggest that I shouldn't be able to?
  • C# - Delayed execution for a collection of items
  • How to display images in a CollectionView from a Base64 string?
  • Convert class object to class index
  • c# identifier expected in controller
  • Subscribe to multiple Observables and gets notified on the same thread
  • how to create windows service using crud operations in c# asp.net
  • Iterating through list of values in a dictionary c#
  • How to Implement Expo.io Push Notifications Send Through C#
  • Gridview columns not being set to width I enter
  • Impersonate User with Forms Authorization
  • ASP.NET MVC Impersonate not working with Forms Authentication
  • Found a potential bug in Moq library while selecting a constructor
  • I want to show a list but page is empty
  • Can this kind of concurrency problem be solved with async/await?
  • Yield keyword giving unexpected values for IEnumerable
  • Many DLLs in System namespace are included when installing library via Nuget
  • C# - range for textbox inputs
  • the Console.ReadLine() Doesn't set the string as the user input
  • Get notified when a variable changed
  • How to list players in room in Photon?
  • Link compile-time created resources to a class library
  • How to quickly choose numbers and weights in such a way as to obtain a specific weighted sum?
  • Downgrade .Net Core project from 3.1 to 2.2 - The type or namespace name 'IWebHostEnvironment' could not be found
  • c# multiplying array elements using system.numerics
  • Private methods vs local functions
  • C# project wants .NET 4.7.1 but I can't install it
  • Binding to object itself, doesn't update when individual property changes
  • remove duplicate from list in object c#
  • JSON.NET - find JObject by value regex in complex object?
  • Save an unknown sized array from unknown string length
  • Camera not centre on player sprite (monogame)
  • How to set a default type for T in a static generic method?
  • How can I insert multiple items of data into SQL using Web API?
  • Finding longest time in an array
  • How to find two quotation marks in a string
  • X509Certificate2 certification issue
  • Image format change in Azure
  • Why is there such big time difference in searching for element with higher index in ConcurrentBag?
  • Async Lambda Issue
  • C# SSIS Script Task - Add Thousand Seperator when formatting Excel column
  • How to check if a textbox starts with numeric value
  • c# Strange Pairs || I can't spot the difference between these 2 solutions?
  • str.Split(".") not working in C# interactive window but work in C# .Net core project?
  • How to do 3 conditions with if else C#
  • merge two lists of same type in c#
  • Blazor - SortBy parameter for table column component
  • InvalidCastException: Specified cast is not valid Error when tried to fetch image from url
  • Search based on Properties
  • Ref string parameter is empty?
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org