Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / sql vba excel проверка наличия данных по дате / 19 сообщений из 19, страница 1 из 1
03.11.2017, 23:00
    #39547684
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
Форумчане, прошу помощи, ибо не могу придумать код для следующей ситуации:
Есть БД, которая раз в сутки заполняется данными с приборов учёта. Каждому числу месяца соответствуют определённые данные. С помощью макроса эти показания попадают в определённый столбец экселя, так чтобы они соответствовали дате в соседнем столбце.
Если в БД есть данные за все дни месяца, то заполняется ексель правильно, А если, например, за 15е нет данных, а запрашиваются с 1 по 20, то вместо того, чтобы ячейку напротив 15-го числа оставить пустой, она заполняется за 16-е, 16 за 17 и т.д.
В этом всё и дело: как сделать, чтобы при недостающих данных в БД в соответствующих клетках экселя было тоже пусто, или 0, или НЕТ ДАННЫХ...
Мой скрипт запроса:
 

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
'Паропровод 2. Масса'
Sub LoadData_id10()

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection
Set rst = New ADODB.Recordset
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=PowerPlant;Data Source=ROCON" 'Параметры строки подключения
cn.Open
rst.Open "SELECT VALUE FROM SPNetArchive Where (RequestID=10)AND(DateTime BETWEEN '09-01-2017 00:00:00'AND'09-30-2017 00:00:00') Order By DateTime", cn 'SQL-запрос, подключение, получение из колонки VALUE, по ID и по дате-времени
ActiveSheet.Range("B72").CopyFromRecordset rst 'Извлекаем данные на лист
rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
End Sub

Модератор: Учимся использовать тэги оформления кода - FAQ
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2017, 09:42
    #39548741
Nik_Kurta
Nik_Kurta
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
Я так понял, что на листе Excel, в определённой колонке проставлены даты статически? А не пробовали выводить через запрос и дату и данные?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2017, 22:04
    #39549261
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
Да не, то, что Вы предлагаете, частично решит, конечно, вопрос, но основному требованию не удовлетворит - интерфейс не трогать - только макрос подрихтовать. Требование начальника отдела. Сейчас вот добился, чтобы RecordCount корректно показывал содержание рекордсета, ну и всё, пока не знаю дальше что с ним делать. Опыта в программировании нет, сейчас вот на старости лет приходится вникать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2017, 22:10
    #39549263
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
На SQL-сервере довольно редко бывает нехватка данных, но в такие моменты хочется, чтоб прога корректно отрабатывала. В конце концов - вопрос профессионального честолюбия.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2017, 06:47
    #39549317
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
kovax, Вам надо допилить запрос так, чтобы он всегда возвращал все дни
не знаю какая у Вас версия скуля, но можно примерно так (кто-то и поправит при желании)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT 
	VALUE 
FROM (
	select 
		dateadd(dd, d.dt, '20170901 00:00:00') dt 
	from (values 
		(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
		(11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
		(21), (22), (23), (24), (25), (26), (27), (28), (29), (30)
		) d(dt)
	where 
		dateadd(dd, d.dt, '20170901 00:00:00') BETWEEN '20170901 00:00:00'AND'20170930 00:00:00') x(dt)
left join (select * from SPNetArchive where RequestID=10) s on datetime = x.dt
Order By x.dt



ЗЫЖ
1. плохо назвать поля зарезервированными словами,
2. дату лучше писать в каноническом виде YYYMMDD HH:MM:SS
3. в запросе лучше заменить даты в условии на параметры
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2017, 23:19
    #39549942
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
Долго, чувствую, буду "переваривать" Ваш код, HandKot. Во всяком случае, на данный момент отладчик ругается за синтаксис, пока не могу разобраться, в чём дело. Скул у меня 2008R2, формат даты он "ест" такой - mm-dd-yyyy, проверено.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2017, 23:50
    #39549946
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
HandKot, а не могли бы Вы привести пример кода для записи числа из ячейки, например А1, листа "Лист1" в SQL-сервер, таблицу SPNetArchive, столбец RequestID и присвоить этому значению, например RequestID=100. Строка подключения та же, что в первом вопросе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2017, 06:53
    #39549989
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
kovaxпример кода для записи числа из ячейки, например А1, листа "Лист1" в SQL-сервер, таблицу SPNetArchive, столбец RequestID и присвоить этому значению, например RequestID=100. Строка подключения та же, что в первом вопросе.
если правильно понял, то пишите простой UPDATE
примерно так (не проверял)

Dim cmd As New ADODB.Command
Dim cn As New ADODB.Connection

cn.ConnectionString = ""
cn.Open

set cmd.Connection = cn
cmd.Execute "update SPNetArchive set RequestID = " & ThisWorkbiik.Worksheets("Лист1"),Range("A1").Value & " where тут условие для конкретной строки"
.

kovaxВо всяком случае, на данный момент отладчик ругается за синтаксис, пока не могу разобраться, в чём дело
что за ошибка? указанный запрос на 2008 должен работать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2017, 09:23
    #39550020
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
Ошибка выскакивает следующая: "Невозможно использовать подключение для выполнения операции. Оно закрыто или не допускается в данном контексте". Я тогда добавил в конце Вашей строки cn, ошибка после этого изменилась на "Incorrect syntax near nthe keyword 'values'"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2017, 10:41
    #39550055
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
kovax, мы сейчас о чём говорим?

по поводу запроса на выборку. Он у Вас в том виде, который я написал, через Management Studio работает?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2017, 13:06
    #39550172
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
HandKot, скрин приложил в качестве аргумента.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2017, 22:29
    #39550596
Казанский
Казанский
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
kovax,
можно вывести 2 столбца
Код: vbnet
1.
rst.Open "SELECT DateTime, VALUE FROM SPNetArchive ...

, вставить недостающие записи, ориентируясь по дате-времени, удалить столбец с датами, примерно так
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
  Dim i&, d As Date
  For i = Cells(Rows.Count, "B").End(xlUp).Row To 73 Step -1
    d = Int(Cells(i, "B"))
    While d - Int(Cells(i - 1, "B")) > 1
      Cells(i, "B").Resize(, 2).Insert xlShiftDown
      d = d - 1
      Cells(i, "B") = d 'необязательно
      Cells(i, "C") = "НЕТ ДАННЫХ"
    Wend
  Next
  Range("B72", Cells(Rows.Count, "B").End(xlUp)).Delete xlShiftToLeft
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2017, 07:13
    #39550648
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
kovaxHandKot, скрин приложил в качестве аргумента.
MS Query конечно же не понимает данный синтаксис. Он остался, я думаю, на уровне SQL 7
Запрос надо вставить вместо Вашего в строке
Код: vbnet
1.
rst.Open "SELECT VALUE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2017, 09:11
    #39550672
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
Наверно, сейчас глупость спрошу...
Казанский, чёт не соображу - а куда вставить CopyFromRecordset rst?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2017, 10:31
    #39550746
Казанский
Казанский
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
kovax,
оставьте его на месте. Мой код вставьте перед End Sub, подправьте строку запроса.
Чтобы убедиться в правильности работы, пройдите код по шагам - F8.
Требуется, чтобы столбец С был изначально свободен, иначе запрос затрет его. Если это невозможно, надо перед CopyFromRecordset вставить столбец, или вообще выгружать рекордсет на новый лист и там обрабатывать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2017, 11:00
    #39550786
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
Казанский, сделал, как Вы сказали. Выкладываю результат выполнения.
В БД нет данных за 1 и 9 ноября. За 9.11.2017 всё верно - нет данных, а за 01.11.2017 не отработал алгоритм.
За 10 данные "съехали" почему-то в колонку с датами
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2017, 11:01
    #39550790
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
А если ещё раз нажать кнопку, то...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2017, 11:52
    #39550861
Казанский
Казанский
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
kovax,
выложите лист после вставки двух столбцов (файл Excel, не картинку!). Еще для правильной работы алгоритма нужны начальная и конечная даты, используемые в запросе - откуда берутся эти даты?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2017, 14:06
    #39550993
kovax
kovax
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sql vba excel проверка наличия данных по дате
Даты в строке запроса к БД.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Visual Basic [игнор отключен] [закрыт для гостей] / sql vba excel проверка наличия данных по дате / 19 сообщений из 19, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 411ms
Закрыть
start [/forum/topic.php?fid=60&mobile=1&tid=2155173]:
 0ms
get settings:
 12ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 41ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 266ms
total:  411ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]