ROWS COUNT

[sony123]

Hi,
i need some code with the following terms

the rows read from excel,rows inserted.....
if both are not the same,we need alert the user that some rows were not inserted....
i need code in VBA....

these things need to display after the upload is complete....

Thanks​

 

[sony123]

Those things will display once uploaded a file....

 

[Luke M]

Do you have a sample file you can upload?

 

[sony123]

User is uploading a file into metadata db and inserting into source db..
I need things which i mentioned above...

 

[Luke M]

Making many assumptions as to your layout and what you want:

Sub WildGuess()
Dim oldCount As Long
Dim newCount As Long
With Worksheets("Sheet1")
    oldCount = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    'Do stuff
    
    
    newCount = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
If newCount <> oldCount Then
    MsgBox "Not all rows were inserted", vbOKOnly + vbCritical, "Error"
End If
 
End Sub

 

[sony123]

Dim iRowN As long
Dim iRowI As long
Dim iColN As Integer
Dim iColI As Integer
iRowN =0
iColN=rng.columns.count
For iColI= To iColN
iRowI=rng.columns(iColI).Offset(65001-rng.Row,0).End(x1up).Row
If iRowI>iRowN then iRowN = iRowI
Next
LastRow=iRowN
Exit Function


Here i need some code..

 

[sony123]

Presently,files only provide message that upload is complete successfully.
The no of rows read and inserted into the file are not displayed.This doesn't provide feed back when uploads are done.

I need it to display following after the upload is complete,
No of rows read from excel
No of rows inserted
If both are not same,alert user that some rows were not inserted.

 

[Luke M]

Dim iRowN As long
Dim iRowI As long
Dim iColN As Integer
Dim iColI As Integer
iRowN =0
iColN=rng.columns.count
For iColI= To iColN
iRowI=rng.columns(iColI).Offset(65001-rng.Row,0).End(x1up).Row
If iRowI>iRowN then iRowN = iRowI
Next
LastRow=iRowN
Exit Function


Here i need some code..

All of the above could be simplified to just be:

LastRow = rng(rng.Count).Row

Also, I still have no idea what "No of rows read from excel", "No of rows inserted" refer to. You will need to provide more information. When describing a problem, excess information is preferred over not enough information.

 

[sony123]

Hi luke,
Thnks for ur replies...

Here my query need to be like this...

I have one excel,and one commandbutton say file upload..it uploads files and the file format is.xls or .xlsx
The file while uploading and inserting into database i need one query...as following
Need to display,
No of rows read from uploading file...
No of rows inserted in to database...
If both are not same,pop up saying that some rows are not inserted...

Ex: if my file has 100 rows...
While uploading read rows from file is say 100...i need to display message like( no of rows read:100)
While inserting into database,no of rows inserted let us say 90 only..message( no of rows inserted :90)
If both are not same...pop up message tht some rows were not inserted...

Did u understand now luke...

Please answer this..

Thank you...

 

[Luke M]

You are uploading an Excel file into a database...what type of database? Is the database another Excel file, or Access, or something else? I presume the upload is being performed by a macro? What would cause the upload to not transfer all the rows from Excel?

 

[sony123]

Yes luke...i'm uploading into Teradata..yes uploading is by macro...i don't know about ur last question..but requirement is like that only,which i mentioned erlier..

 

[sony123]

Yes luke...i'm uploading into Teradata,source db i thinck.....but i can see insert command for metadata also...it is uploading is by macro...i don't know about ur last question..but requirement is like that only,which i mentioned erlier..

 

[sony123]

Already user is getting message like file is uploaded sucessfully..but additionally looking for certain things...

 

[sony123]

need a code that no of rows inserted in to database,source db......
i need rows count in a message box that x no of rows are inserted....

 

[SirJB7]

Hi, sony123!

Are you very hurried? If so please take a break and dedicate 5 minute to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the 1st forum at the main page...
http://chandoo.org/forum/forums/new-users-please-start-here.14/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."

Regards!

PS: If it's a high priority issue and you can't even wait... how much?... oh, 24 hours and a while... you always have the alternative of hiring a local consultant who may provide you the exact tailored service. Should I remember you that these forums are supported by contributors who dedicate part of their time in an absolutely free way to help other people and answer questions?

 

[sony123]

Ok

 

[bobhc]

sony123


This forum operates 24/7 with members in countries all around the world, a question may be answered by someone and then go quite, there are many reasons for this as members have many things to do outside of the Forum, eat, sleep, work, hobbies, family the list goes on and on. A reply and answer may be urgent and important to you but not to the members.
Please read:-
http://chandoo.org/forum/threads/new-users-please-read.294/

 

[sony123]

Hi....
i need records count from a query in vba....
iam uploading one excel into my macro...my excel writes its rows into database....i need to count no of records that are inserted....

My code here follows...

Dim cn As ADODB.Connection
Dim cn1 As ADODB.Connection
set cn = new ADODB.Connection
set cn1 = new ADODB.Connection
Dim rs As ADODB.Recordset
set rs = new ADODB.Recordset
Dim rs1 As ADODB.Recordset
set rs1 = new ADODB.Recordset

Dim cmdSQLData As ADODB.Command
set cmdSQLData =New ADODB.Command
Dim cmdSQLData1 As ADODB.Command
Set cmdSQLData = new ADODB.Command

cn1.open (here i called one function for DBconnection)

Set cmdSQLData1.ActiveConnection=cn1


If filetype="XYZ" Then

QueryA= select count(version),max(version)+1 from source_db.abc_vw where SUBMISSION_PERIOD=' " & subperiod & " ' "
Debug.Print queryA
cmdSQLData.CommandText=queryA
cmdSQLData.CommandType=adCmdText

cmdSQLData.CommandTimeout=0
Set rs=cmdSQLData.Execute()

for x=2 to rows...........//////To read from uploaded file //////

QueryB=insert statement(inserting into source DB)

cmdSQLData.CommandText =queryB
cmdSQLData.CommandType=adCmdText
Debug.print queryB
Set rs=cmdSQLData.Execute()

by this above code i think record are inserted into database.....

Here i need code for how many records were inserted into datbase....upto my knowledge i written the following code...

i taken variables as ,

Dim rscount as integer
Dim recordcount as integer

rscount=rs.recordcount
msgbox "No of records inserted " & rscount


iam getting error message err 1004..

please suggest me..

Thanks...

 

[SirJB7]

for x=2 to rows...........//////To read from uploaded file //////

Hi, sony123!
My suggestions, in priority order:
1) Upload a sample file.
2) If not 1), post the actual code.
3) If not 2), post the related snippet
4) If 2) or 3), post the code into it's proper place (icon on the ribbon that displays "Code" when you hover on it)
5) Do not post "things" like posted that you wrongly call code: it has errors, it's incomplete, ...
6) About your question and the quote, shouldn't it be the no. of records inserted be the upper bound of the For...Next loop (oh, it isn't there, well neither the Next clause) minus the upper bound (yes, it's there!, 2) plus 1?
7) If not 6), then after the For...Next or the QueryB assignment, define a recordset and get the no. of records from it. How to? From the same source where you found the code as I assume that you're not the author. If unavailable, Google's always there.
Regards!
PS: Just in case you didn't notice it, I left you messages at other topics related to this asking precisely about that relation and how you should proceed.
PS2:As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/
And if you've yet read it, do it again, slowly, so as to understand it. Thank you.

 

[sony123]

Dim cmdsqldata As ADODB.Command
Set cmdsqldata =New ADODB.Command
Dim cmdSQLdata1 As ADODB.Command
Set cmdSQLData1=New ADODB.Command
cn1.opem GetDBconnectionString_source
Set cmdsqldata1.Activeconnection=cn1

Iam uploading total two types of excel files using excel Vba.
The uploaded files are inserting to database.

If fileType="xyz" Then
QueryA="select count(Track_ID) as cnt ,max ( p_number)+1 as prn from METADATA_DB.TRACKER_VW where ID = 4"
cmdsqldata1.commandText=queryA
cmdsqlData1.commandType= adCmdText
cmdsqldata1.CommandTimeout=0
Debug.print queryA
Set rs1= cmdsqlData1.Execute()

If rs1("cnt")=0 Then
prn=1
else
Prn=rs1("prn")
End If
Else
If filetype=abc.....then also similar code here...
cn1.Close

This is for selecting files and executing insert statements into Database.
If fileType= "xyz" Then
queryA= "select count (version) as cnt,max(version)+1 as ver from SOURCE_DB.table_VW where submission_period=' "
Debug.print queryA
cmdsqldata.commandText=queryA
cmdsqldata.commandType=adCmdText
cmdsqldata.commandTimeout=0
set rs=cmdsqldata.execute()

Here there is for loop to insert all the fileds from the uploaded file...

For x=2 to rows. ex: Range("A" & x)
So x value goes...

queryB= insert into SOURCE_DB... blah blah blah,..,
cmdsqldata.commandtext = queryB
cmdsqldata.commandatype=adcmdatext
cmdsqldata.commandtimeout=0
Debug.Print queryB
Set rs= cmdsqldata.execute()
rows = rows+1
Next x
END IF

set rs= Nothing

-------------------------
public function GetDBConnectString_Source() As String
Const str_connect= "Data source= RST; Database=SOURCE_DB;Persist Security Info= True;Session Mode=ANSI;"
'My connection details to database here...
End Function


My code is there as above now...i need now that no of records are inserted into database..i need records count...

 

[SirJB7]

1) Upload a sample file.
2) If not 1), post the actual code.
3) If not 2), post the related snippet
4) If 2) or 3), post the code into it's proper place (icon on the ribbon that displays "Code" when you hover on it)
5) Do not post "things" like posted that you wrongly call code: it has errors, it's incomplete, ...
6) About your question and the quote, shouldn't it be the no. of records inserted be the upper bound of the For...Next loop (oh, it isn't there, well neither the Next clause) minus the upper bound (yes, it's there!, 2) plus 1?
7) If not 6), then after the For...Next or the QueryB assignment, define a recordset and get the no. of records from it. How to? From the same source where you found the code as I assume that you're not the author. If unavailable, Google's always there.

Hi, sonny123!
Not 1, Ok.
Not 2, Ok.
Let us assume that 3, almost Ok.
But neither 4:

click on where the red arrow points to and you'll be lead to this:

Nor 5: your code certainly doesn't run, and I don't mean the connection strings or other sensitive data, omit or dummy that, but include the whole code. It usually starts with Sub and ends with End Sub.
Nor 6: There're explanatory texts not commented, so they're not part of the code.
Wanna get help with the code or with 7? Ok, then follow the rules and guidelines.
Regards!