Brick Wall Scenario

[mike55]

Ok,

 

I've hit a brick wall! I am doing the following actions:

1. Upload a .CSV file from client to server (done)

2. Copy data from .CSV file into temporary table SQL server 2000 database (done)

3. Copy data from temporary table into main table (Code written to do that)

4. Delete temporary table (code written to do that)

5. Delete uploaded .CSV file from server, and

6. Inform user that the process is complete.

 

I am using a stored procedure to carry out step 2. In its current form everything is working out correctly. I need to modify step 2 inorder to handle multiple simultaneous uploads.

 

My solution was to create a temporary table for each user uploading and then transfer the data to the main table. Again I wish to use stored procedures. The catch is that I don't know the name of my database table at the beginning, as a unique name is generated for each upload. I have tried passing the table name into the stored procedure as a parameter, but when I go:

insert into @TableName Values(1,1,1) 

 

I get an error telling me that "@TableName" is not declared.

Any suggestions on how to get around this little problem?

 

Mike55

[Afraits]

Perhaps you could post as much of the stored procedure as possible so we can see how you've set it up.

[Nerseus]

If you simply want to have a dynamic table name, you'll have to use dynamic SQL. In your case, you'll have to piece together the string from something like:

CREATE PROC Test (@TableName varchar(50)) AS 
DECLARE @sql varchar(8000)
SET @sql = '
   SELECT *
   FROM ' + REPLACE(@TableName, '''', '''''') + '
   WHERE ...
'

 

Three things:

1. Yes, you can put the single quotes like that and then write your SQL as if it were "real" sql and not dynamic.

2. The REPLACE is to double up any singlequotes - shouldn't be an issue since this is a tablename, but NEVER trust a param coming in that's used in dynamic sql.

3. In this sample, you can't use parameters since your "param" is a table. Otherwise you could use a system proc to compile the dynamic sql and use variables. That's useful when you have variables coming in that affect a WHERE clause - then you don't have to worry about SQL injection.

 

-ner

 

edited to change the sql tags to code - there are no sql tags, d'oh!

Edited August 19, 2005 by Nerseus

[mike55]

Ok,

 

Firstly thats for the suggests. My stored procedure is as follows:

CREATE PROCEDURE dbo.spTempMembers 
@tempTable nvarchar(254),
@Custom_ID nvarchar(254),
@Surname nvarchar(254),
@Forename nvarchar(254),
@Fullname nvarchar(254),
@DOB nvarchar,
@Title nvarchar(5),
@MobileNumb char(20), 
@PhoneNumb char(20), 
@Addr1 nvarchar(254),
@Addr2 nvarchar(254),
@Addr3 nvarchar(254),
@Addr4 nvarchar(254),
@Role char(20), 
@Guardian1 nvarchar(254),
@Guardian2 nvarchar(254),
@Guardian1_Phone char(20), 
@Guardian2_Phone char(20),
@Email nvarchar(254),
@MemberShip_Paid_Date nvarchar,
@Active bit, 
@AdditionalInformation nvarchar(254),
@CodePrefix char(10) ,
@Country nvarchar(50),
@PostCode nvarchar(50),
@InternationalNumber nvarchar(50)

AS

DECLARE @sqlStatement nvarchar(254)
	
SET @sqlStatement = 'INSERT INTO '+ REPLACE(@tempTable, '''', '''''')  +' VALUES( '+ @Custom_ID + ', '+@Surname+', '+@Forename+', '+@Fullname+', '+cast(@DOB as datetime)+', '+@Title+', '+@MobileNumb+', '+@PhoneNumb+', ' + @Addr1+', '+ @Addr2+', ' +@Addr3+', '+@Addr4+', '+@Role+', '+ @Guardian1+', '+@Guardian2+', '+@Guardian1_Phone+', '+@Guardian2_Phone+', '+@Email+', '+cast(@MemberShip_Paid_Date as datetime)+', '+@Active+', '+@AdditionalInformation+', '+@CodePrefix+', '+@Country+', '+@PostCode+', '+@InternationalNumber+' ) '
--Execute the statement.
exec @sqlStatement
GO

 

I have created a table with 4 columns: id ColumnName, ColumnType and ColumnSize which stores the details on all the columns in this tempory table. Therefore when I go to call the above procedure I simple loop through the dataset table getting the column name and adding an "@" to the start for the parameter name.

 'Add a parameter to the stored procedure.
   Private Function addParameter(ByVal tableColumns As DataColumnCollection, ByVal myCommand As sqlCommand, ByVal rowItems As Object()) As sqlCommand
       Dim parameterName As String
       Dim parameterType As SqlDbType
       Dim parameterSize As Integer
       Dim parameterValue As String
       Dim column As DataColumn
       Dim ctrColumn As Int16

       Try
           ctrColumn = 0
           For Each drRow As DataRow In dsDataset.Tables("fileTemplate").Rows
               If ctrColumn < tableColumns.Count Then
                   parameterValue = Server.HtmlEncode(rowItems(ctrColumn).ToString.Replace(" '", "''"))
                   parameterName = "@" + drRow.Item(1).ToString
                   parameterType = getMyType(drRow.Item(2).ToString, parameterValue)
                   parameterSize = Convert.ToInt16(drRow.Item(3).ToString)
                   
                   myCommand.Parameters.Add(New SqlParameter(parameterName, parameterType)) 'Add the parameter.
                   myCommand.Parameters(parameterName).Direction = ParameterDirection.Input
                   myCommand.Parameters(parameterName).Value = parameterValue
                   ctrColumn = ctrColumn + Convert.ToInt16(1) 'Increment the column counter
               End If
           Next
           Return myCommand
       Catch ex As Exception
           Throw ex
       End Try
   End Function

   Private Function getMyType(ByVal type As String, ByRef parameterValue As String) As SqlDbType
       Select Case type
           Case "nvarchar"
               getMyType = SqlDbType.NVarChar
               parameterValue = "'" + parameterValue + "'"
           Case "char"
               getMyType = SqlDbType.Char
               parameterValue = "'" + parameterValue + "'"
           Case "datetime"
               getMyType = SqlDbType.DateTime
               parameterValue = " ' " + parameterValue + " ' "
           Case "bit"
               getMyType = SqlDbType.Bit
           Case "int"
               getMyType = SqlDbType.Int
               parameterValue = parameterValue
       End Select

       If parameterValue = Nothing Then
           parameterValue = "'101'"
       End If
       Return getMyType
   End Function

 

I then execute the procedure as follows:

sqlCommand.ExecuteNonQuery

 

The current error that I am getting is:

Syntax error converting datetime from character string. 

Now I did pass my dates into the database as an nvarchar and once inside I did a Cast(@DOB as datetime) on the parameter.

If on the otherhand I tried to pass the parameter as a datetime, and didn't do the Cast(@DOB as datetime), I got the error: problem converting to datetime from string.

 

Any suggestions?

 

Mike55.

[Afraits]

That error would suggest that the format of the date in the string format you pass in isn't valid or isn't being interpreted correctly, so first step ensure a valid date is being passed in, secondly try an explicit format such as 13-MAR-2005 instead of 13/3/05, thirdly use the 'CONVERT' command in SQL Server to specifiy a format eg

CONVERT(DATETIME, '''+ @DOB +''', 103) would expect an English style datetime format.

 

Having looked at the sp I think the part " ... , '+cast(@DOB as datetime)+' , ..." should be written "..., CAST('''+@DOB+''' as datetime) , ...." (or the convert if you go that way)

 

Hopefully one of those will solve the problem.