W.E.B
asked on
Macro to insert in SQL 2008
Hello,
can you please help.
Attached is a sample data.
I need to insert a note to each order into my SQL database.
need to find the highest NoteNumber, then add 1.
Option Explicit
Sub Insert_Notes()
Dim sh As Worksheet
Dim cnn As adodb.Connection
Set sh = Sheets("Import")
Dim rs As New adodb.Recordset
Dim sql As String
Set cnn = New adodb.Connection
cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=xxxx;Initial Catalog=xxxx;Data Source=111.111.111.111;"
SET IDENTITY_INSERT ActiverNotes ON
INSERT INTO ActiveNotes (NoteNumber,OrderNo,NoteTe xt,Entered By,Entered OnDate,IsP ublicNote, OrderNoteT ypeID) VALUES (MAX(NoteNumber)+1,Order Numbers from Column "AL",'Tracking from Column "AK"','System','Date from Column "AM"',4)
SET IDENTITY_INSERT ActiveNotes OFF
Commit
End Sub
Your help is very much Appreciated.
Book1.xls
can you please help.
Attached is a sample data.
I need to insert a note to each order into my SQL database.
need to find the highest NoteNumber, then add 1.
Option Explicit
Sub Insert_Notes()
Dim sh As Worksheet
Dim cnn As adodb.Connection
Set sh = Sheets("Import")
Dim rs As New adodb.Recordset
Dim sql As String
Set cnn = New adodb.Connection
cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=xxxx;Initial Catalog=xxxx;Data Source=111.111.111.111;"
SET IDENTITY_INSERT ActiverNotes ON
INSERT INTO ActiveNotes (NoteNumber,OrderNo,NoteTe
SET IDENTITY_INSERT ActiveNotes OFF
Commit
End Sub
Your help is very much Appreciated.
Book1.xls
ASKER
Hello,
I need to insert the next note Number.
I need to insert the next note Number.
if your ActiveNotes table's column NoteNumber is set as the identity column <=> auto incremental
you'll get the next number
---
if it is not or you have some specific logic (it is just an idea)
you can try this not
you'll get the next number
---
if it is not or you have some specific logic (it is just an idea)
you can try this not
SET IDENTITY_INSERT ActiverNotes ON
INSERT INTO ActiveNotes (NoteNumber,OrderNo,NoteText,EnteredBy,EnteredOnDate,IsPublicNote,OrderNoteTypeID)
Select (select MAX(NoteNumber)+1 from ActiveNotes ) as MAX_NoteNumber,'Order Numbers ','from Column "AL"','Tracking from Column "AK"','System','Date from Column "AM"',4
SET IDENTITY_INSERT ActiveNotes OFF
ASKER
Ho w do I insert the orders one after another?
Dim i as Integer,
next i,...
I wanted it to go through the orders one after another till last raw in column?
Thanks
Dim i as Integer,
next i,...
I wanted it to go through the orders one after another till last raw in column?
Thanks
did you review the posted above code?
ASKER
HI EugeneZ
What I meant
'Order Numbers ','from Column "AL"',
'Tracking from Column "AK"','
'Date from Column "AM"'
How do I get these into my Insert?
What I meant
'Order Numbers ','from Column "AL"',
'Tracking from Column "AK"','
'Date from Column "AM"'
How do I get these into my Insert?
so far
we know from you code
that table ActiveNotes
has columns
NoteNumber,OrderNo,NoteTex t,EnteredB y,EnteredO nDate,IsPu blicNote,O rderNoteTy peID
can you tell more about
'Order Numbers ','from Column "AL"',
'Tracking from Column "AK"','
'Date from Column "AM"'?
for example from what table is AL column?
what does it mean 'Order Numbers ','from Column "AL"',?
can you illustrate?
we know from you code
that table ActiveNotes
has columns
NoteNumber,OrderNo,NoteTex
can you tell more about
'Order Numbers ','from Column "AL"',
'Tracking from Column "AK"','
'Date from Column "AM"'?
for example from what table is AL column?
what does it mean 'Order Numbers ','from Column "AL"',?
can you illustrate?
ASKER
In My original question, I attached a sample excel.
in Column AL , I have my order numbers
Column AK, I Have the NoteText
Column AM, I have the Date
I Get the excel sheet daily with Different order numbers (Column AL), notes (Column AK), dates (cOLUMN AM).
I need to insert into the database.
I'll put the values into Column AL, AK, AM.
so,
Insert into My database the value for Note, date,.. for orderNumber in Raw 2
then, Raw 3, Then Raw 4,.... till last.
Thanks,
in Column AL , I have my order numbers
Column AK, I Have the NoteText
Column AM, I have the Date
I Get the excel sheet daily with Different order numbers (Column AL), notes (Column AK), dates (cOLUMN AM).
I need to insert into the database.
I'll put the values into Column AL, AK, AM.
so,
Insert into My database the value for Note, date,.. for orderNumber in Raw 2
then, Raw 3, Then Raw 4,.... till last.
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
EugeneZ,
Thank you very much,
This worked.
Thank you very much,
This worked.
ASKER
Thank you.
if it is not you need - please clarify
Open in new window