|
David Rubin wrote: Your help is appreciated.
Looks like you're building a macro in Excel itself; the code didn't work from a new VB.NET application. The "rows" should be prefixed with the sheet that they're coming from, and xlUp wasn't recognized.
There's also no concept of a "next available cell".
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
|
i have stored procedure with two parameters, one varchar and another one date format.
i am getting the error ,"Error converting data type nvarchar to datetime." like this while am running.
this is my code inside the vb,
Public Sub Selecttable()
sql.OpenDbConnection()
Dim cmd As SqlCommand = New SqlCommand("SearchRecord", sql.SqlConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Date", "DateOfActivation")
cmd.Parameters.AddWithValue("@Activation", "activation")
Dim dt As New DataTable()
Using da As New SqlDataAdapter(cmd)
da.Fill(dt)
dgvSearch.DataSource = dt
End Using
End Sub
and my storedprocudure like this...
USE [Activation]
GO
/****** Object: StoredProcedure [dbo].[SearchRecord] Script Date: 05/05/2014 21:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchRecord]
@Activation nvarchar(50),
@Date datetime
AS
BEGIN
set dateformat yyyymmdd
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from Activation.dbo.tblActivationMain WHERE DateOfActivation=@Date AND Activated=@Activation
END
please help me to find the solution.
|
|
|
|
|
Try swapping the order of your command parameters.
|
|
|
|
|
|
Because they are named, the order does not matter.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
..on top of that, rename the parameter from "Date" to "DateOfActivation"; DATE might be a reserved keyword 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
i tried this way
USE [Activation]
GO
/****** Object: StoredProcedure [dbo].[SearchRecord] Script Date: 05/05/2014 21:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchRecord]
@Activation nvarchar(50),
@DateActivation date
AS
BEGIN
set dateformat yyyymmdd
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from Activation.dbo.tblActivationMain WHERE DateOfActivation=@DateActivation AND Activated=@Activation
END
no chance
|
|
|
|
|
cmd.Parameters.AddWithValue("@Date", "DateOfActivation")Is that intentional? I assume you are putting a Date-object in there?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
that one is variable... I got one datepicker.
|
|
|
|
|
Whotsitnamed? If it's called "DateTimePicker1", try something like below;
cmd.Parameters.AddWithValue("@DateActivation ", DateTimePicker1.Value);
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
cmd.Parameters.AddWithValue("@Date", "DateOfActivation")
You are passing in the string "DateOfActivation" to a date field which is why you get the error.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I don't understand , dateofactivation variable I made it , date.
dim dateofactivation as date like this. is it correct or what else I want to do? please tell me sir.
|
|
|
|
|
Then you would do it like this:
cmd.Parameters.AddWithValue("@Date", DateOfActivation) without the quotes around it. The quotes make it a string, not a variable.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
all the data are showing, but it is not showing by date wise.
|
|
|
|
|
I don't understand what you are saying. You should post a new question if this is a new question.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
it is not showing by date instead all data are showing
|
|
|
|
|
You'll have to debug it and make sure the date is actually being passed in like you think it is.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
You are passing a STRING that contains the letters "DateOfActivation". You are NOT passing in an actual DateTime value. That's why you're getting the error. Remove the quotes!!
cmd.Parameters.AddWithValue("@Date", dataofactivation)
|
|
|
|
|
I need to copy multiple cells from one worksheet (Sheet 1) and paste them into another worksheet (Sheet 4). I want each cell from sheet 1 to paste in a specific column in sheet 4, to the next available cell in sheet 4. I can only get one column to correctly paste. When I change the code, it updates another column, but not the previous one.
Here is what I need:
Copy from Paste to
SHEET 1, cell D10 SHEET 4 Column B, the next available cell
SHEET 1, cell D12 SHEET 4 Column D, the next available cell
SHEET 1, cell D14 SHEET 4 Column E, the next available cell
SHEET 1, cell E22 SHEET 4 Column C, the next available cell
Here is the current code I am using:
Sub Mail_workbook_Outlook_2()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Set wb1 = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
Exit Sub
End If
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
' Make a copy of the file.
' If you want to change the file name then change only TempFileName variable.
'TempFilePath = Environ$("temp") & "\"
'TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
'FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
'Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run this procedure.
With OutMail
.To = "ENTER EMAIL ADDRESS HERE"
.CC = ""
.BCC = ""
.Subject = "Work Order Request " & Format(Now, "dd-mmm-yy h:mm:ss")
.Body = "Please see attached maintenance work order"
.Attachments.Add wb1.FullName
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail.
.Send
End With
On Error GoTo 0
'wb2.Close SaveChanges:=False
' Delete the file.
'Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
Range("D14").Select
Selection.Copy
Sheets("Sheet4").Select
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lMaxRows + 1).Select
On Error Resume Next
MsgBox "Your work order request has been sent to maintenance."
End With
End Sub
|
|
|
|
|
All you need to do is debug it and find out where it is wrong.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I did debug it and there is nothing wrong. It just isnt doing what I want it to do.
|
|
|
|
|
Then you should have been able to see what was wrong. If it is going in the wrong column then you are not referencing the right column.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
If I knew enough about visual basic I would have fixed it. Thats why I'm on the message board. Your help would be appreciated if you know enough...
|
|
|
|
|
We can't run your code. All you have to do is put a breakpoint and then walk through your code. Also, you should only post the relevant code. You posted the email code which just clutters up the question and makes it harder for us to help.
However, you have:
Range("E" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
If you just look at it you will see that it is selecting column E every time so naturally you need to change that part to be whatever column you want.
Also note, you can record macros in Excel doing what you want and have it write the code for you (with few tweaks to get it into VB).
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
