ExecuteNonQuery syntax

Hi, I am trying to execute a nonquery as follows (look for bold):

Dim connStringSQLAsNew SqlConnection("Data Source=...***...Trusted_Connection=False")

'// Create the new OLEDB connection to Indexing Service

Dim connIndAsNew System.Data.OleDb.OleDbConnection(connStringInd)

Dim commandIndAsNew System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

commandSQL.CommandType = Data.CommandType.StoredProcedure

Dim resultDSAsNew Data.DataSet()

Dim resultDAAsNew SqlDataAdapter()

'// Fill the dataset with values

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

...

'// Return the number of results

resultCount.Text = source.Count.ToString

results.DataSource = source

results.DataBind()

'// Record the search

commandSQL =New SqlCommand("RecordSearch", connStringSQL)

commandSQL.Parameters.Clear()

commandSQL.Parameters.Add("@.userName", Data.SqlDbType.VarChar, 50).Value = authUser.Text()

commandSQL.Parameters.Add("@.createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()

commandSQL.Parameters.Add("@.numRows", Data.SqlDbType.Int, 1000).Value = resultCount.Text

commandSQL.Parameters.Add("@.searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.Text

connStringSQL.Open()

commandSQL.ExecuteNonQuery()

connStringSQL.Close()

The stored procedure looks like this:

Use GTGAssets

DROPPROC dbo.RecordSearch;

--New Procedure

GO

CREATEPROC dbo.RecordSearch

(

@.userNamevarchar(50),

@.createdDateTimeDateTime,

@.numRowsvarchar(1000),

@.searchStringvarchar(1000)

)

AS

BEGIN

SETNOCOUNTON

INSERTINTO SearchLog(SearchString, CreatedByUser, CreatedDTTM, RowsReturned)VALUES(@.searchString, @.userName, @.createdDateTime, @.numRows)

END

GO

Any ideas as to why this error is appearing?

Incorrect syntax near 'RecordSearch'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'RecordSearch'.

Source Error:

Line 169: commandSQL.Parameters.Add("@.searchString", Data.SqlDbType.VarChar, 1000).Value = searchText.TextLine 170: connStringSQL.Open()Line 171: commandSQL.ExecuteNonQuery()Line 172: connStringSQL.Close()Line 173: End If

Many thanks!

James

Alright, I found this - it's the way the DateTime is being passed in. The DateTime parameter for the last command looks like this:

#9/11/2006 4:01:25 PM#

However, when this procedure is run on SQL Server Query Analyzer:

USE [GTGAssets]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[RecordSearch]
@.userName = N'name',
@.createdDateTime = N'#9/11/2006 4:01:25 PM#',
@.numRows = N'10',
@.searchString = N'soa'

SELECT 'Return Value' = @.return_value

GO

I get this SQL Server error. I tried this insert (notice the lack of pound signs) it actually works.

USE [GTGAssets]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[RecordSearch]
@.userName = N'CVNS\JFogarty',
@.createdDateTime = N'9/11/2006 4:01:25 PM',
@.numRows = N'10',
@.searchString = N'soa'

SELECT 'Return Value' = @.return_value

GO

Why is this? The pound signs should not be added to the DateTime.Now(), should it?

Thanks!|||You need to enclose date with #'s for Access DB not SQL Server.|||

Well the problem is that VB.Net automattical adds the # - this is the code:

commandSQL.Parameters.Add("@.createdDateTime", Data.SqlDbType.DateTime).Value = DateTime.Now()

|||AFAIK it shouldnt be putting any #'s.|||You forgot to set the commandtype