Brick Wall Scenario

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
Ok,

Ive 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 dont 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:
Code: 
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
 
If you simply want to have a dynamic table name, youll have to use dynamic SQL. In your case, youll have to piece together the string from something like:
Code: 
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 - shouldnt be an issue since this is a tablename, but NEVER trust a param coming in thats used in dynamic sql.
3. In this sample, you cant use parameters since your "param" is a table. Otherwise you could use a system proc to compile the dynamic sql and use variables. Thats useful when you have variables coming in that affect a WHERE clause - then you dont have to worry about SQL injection.

-ner

edited to change the sql tags to code - there are no sql tags, doh!
 
Last edited by a moderator:
Ok,

Firstly thats for the suggests. My stored procedure is as follows:
Code: 
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.
Code: 
  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:
Code: 
sqlCommand.ExecuteNonQuery

The current error that I am getting is:
Code: 
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 didnt do the Cast(@DOB as datetime), I got the error: problem converting to datetime from string.

Any suggestions?

Mike55.
 
That error would suggest that the format of the date in the string format you pass in isnt valid or isnt 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.
 
Back
Top