Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts

Thursday, March 8, 2012

Assign Variable for FileName of Source File

I am wanting to capture the file name I am using to load data from and use it in a SQL statement to insert it along with the data that I load.

I am using a ForEach container to load all my .txt files but cannot figure out how to capture the name of each source file as it loops through the files and then add it to my insert statement that is populating my history table. I would think that the ForEach container has that information, but I do not see how to access it and assign it to a variable that I could use in my SQL statement.

Any ideas or suggestions?

The ForEach loop container can store the name of eac enumerated file into a variable. This article explains how to do that: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

Once its in a variable you can do pretty much anything you want with it and it seems you already know how to do that. If not, just reply here.

-Jamie

|||

Thanks for the link, but I have was able to setup a variable @.[User::FN] to the ConnectionString property associated with the flat file connection, and the looping and loading effort is working fine.

This may seem like a stupid question, and I apologize, but I cannot figure out how to reference that variable and use it in my execute SQL task that follows later and does my insert of the data into my table. What I want to do is something like this:

Insert into Table1 Select a, b, c, @.FN From Table2

The parser requires that I declare the variable and does not seem to recognize the @.[User::FN] variable.

Any suggestions?

|||

Yes, use an expression. This (sort of) shows you how:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

Assign Sequential Numbers

I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.An IDENTITY column is just what you need. Check the Microsoft SQL Server CREATE TABLE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp) documentation.

-PatP|||Hey...

SQLTeam still down?

USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(100,1), Col2 varchar(25))
GO

INSERT INTO myTable99(Col2)
SELECT 'Brett' UNION ALL
SELECT 'Pat' UNION ALL
SELECT 'Gary'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||I agree with Pat. Here is an exerp from the Create Table subject in Books Online for SQL:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft SQL Server provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||Originally posted by Brett Kaiser
Hey...

SQLTeam still down? Nah, at least I can see it from here.

-PatP

Friday, February 24, 2012

aspnet_regsql.exe - cannot insert null values

Im trying to setup a SQL server 2000 database to use membership & roles. Running aspnet_regsql.exe gives me the following errors

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 515 and the SqlException message is: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.

------------
Details of failure
------------

SQL Server:
Database: [AddressVerification]
SQL file loaded:
InstallCommon.sql

Commands failed:

CREATE TABLE #aspnet_Permissions
(
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType char(10),
[Action] varchar(20),
[Column] sysname
)

INSERT INTO #aspnet_Permissions
EXEC sp_helprotect

IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'aspnet_Setup_RestorePermissions')
AND (type = 'P')))
DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissions


SQL Exception:
System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)


Ive run this many times on other servers and have never come across this problem before. Has anyone else ? Does anyone know the cause and if there is a fix ??

** I am logged in with full admin privaliges

tia

Mark.

This is because the preinstalled user defined data type sysname by default does not allow nulls--that means column of sysname data type won't accept nulls unless you explicitly set the column to allow null in column definition. So if the sp_helprotect stored procedure returns some rows with nulls in the [column] field (this is an rare issue but it does exist), the insert command will fail. So here I have to say it is a flaw of the aspnet_regsql utility.|||

I wrote a simple script to update the system table systypes to make sysname data type to accept NULLs. However the script is not fully tested and since it modifies system table directly, I don't recommend to use it and you need to consider the potential risk when using it. If some issue is caused by the script, please pass 'NoNulls' as parameter to change the systypes back to default:

CREATE PROCEDURE usp_KillUsers
@.p_DBName SYSNAME = NULL
AS

/* Check Paramaters */
/* Check for a DB name */
IF (@.p_DBName IS NULL)
BEGIN
PRINT 'You must supply a DB Name'
RETURN
END -- DB is NULL
IF (@.p_DBName = 'master')
BEGIN
PRINT 'You cannot run this process against the master database!'
RETURN
END -- Master supplied
IF (@.p_DBName = DB_NAME())
BEGIN
PRINT 'You cannot run this process against your connections database!'
RETURN
END -- your database supplied

SET NOCOUNT ON

/* Declare Variables */
DECLARE @.v_spid INT,
@.v_SQL NVARCHAR(255)

/* Declare the Table Cursor (Identity) */
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT spid
FROM master..sysprocesses (NOLOCK)
WHERE db_name(dbid) = @.p_DBName

OPEN c_Users

FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.v_SQL = 'KILL ' + CONVERT(NVARCHAR, @.v_spid)
-- PRINT @.v_SQL
EXEC (@.v_SQL)
END -- -2
FETCH NEXT FROM c_Users INTO @.v_spid
END -- While

CLOSE c_Users
DEALLOCATE c_Users

CREATE PROCEDURE sp_SetSysnameAllowNulls @.Operation varchar(10)='AllowNulls'
AS
BEGIN
DECLARE @.cmd nvarchar(4000)
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
EXEC sp_msforeachdb N'use[?];
IF (DATABASEPROPERTY(db_name(),''IsReadOnly'')=1)
BEGIN
declare @.dbname sysname;
set @.dbname=db_name();
use master
EXEC usp_KillUsers @.dbname;
EXEC sp_dboption @.dbname,''read only'',''false'';
END'

IF (@.Operation = 'AllowNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status-1
WHERE name=''sysname'' AND (status&1)=1'
ELSE
IF (@.Operation = 'NoNulls')
EXEC sp_msforeachdb N'use [?];
IF NOT EXISTS(SELECT 1 FROM systypes WHERE name=''sysname'')
PRINT ''sysname data type not found in''+ DB_NAME()
ELSE
UPDATE systypes SET status=status+1
WHERE name=''sysname'' AND (status&1)=0'
ELSE RAISERROR('Expected @.Opertaion=''AllowNulls'' or ''NoNulls''',16,1)
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
END

Sunday, February 19, 2012

ASP2.0 INSERT into SQL2000 problems

First thanks for any help anyone can give me. Here is the environment: SQL2000 server, 2003 Web server, ASP2.0, SQL

I've banged my head around trying to get this code to insert some data into a table cell on a db table, and it simply is not working, through trial and error i've figured that I simply have wrote crappy sql code so if anyone can give it a look over and see if they notice what the devil I missed, it would be much appreciated, if you need to see more of the code give me a shout.

If (Page.IsValid = True)

' MailClient.Send(MailFrom, MailTo, MailSubject, MailBody)'

Dim SQLConn As SqlConnection = New SqlConnection()
SQLConn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
SQLConn.Open()
Dim strSQL As String = "INSERT INTO kittens (email) VALUES (frogger);"
SQLConn.Close()

End If
End Sub

Remove the semi-colin from ther end of the query, also 'frogger' should be encapsulated in single quotes. All text, ntext, varchar, nvarchar, etc.. need to have their values in quotes unless passed through a variable.

INSERT INTO kittens (email) VALUES ('frogger')

|||

bluemistonline:

Remove the semi-colin from ther end of the query, also 'frogger' should be encapsulated in single quotes. All text, ntext, varchar, nvarchar, etc.. need to have their values in quotes unless passed through a variable.

INSERT INTO kittens (email) VALUES ('frogger')

Did you also mean for me to take the "" from the statements as well? With those taken out I get a (BC30205: End of statement expected) error.

I guess I will post the whole code (the nonhtml stuff anyway) and see if anyone notices anything. I'm beginning to wonder if I have the sql table itself not set up right somehow. As is obvious I am very new to ASP.net and SQL as in I new nothing about how to code either until I started on this recent project so I am learning by doing :b

login.aspx code

<%@. Page Explicit="true" debug="true" language="vb" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data.Mail" %>
<%@. Import Namespace="System.Net.Mail" %>

<script runat="server">
Sub submitClick(Source as Object, E as EventArgs)

Dim eMsg as String

eMsg +="Customer Information" & vbcrlf
eMsg +="Email: " & txtEmail.Text & vbcrlf
eMsg +="Company: " & txtCompany.Text & vbcrlf
eMsg +="Last Name: " & txtLastName.Text & vbcrlf
eMsg +="First Name: " & txtFirstName.Text & vbcrlf
eMsg +="Phone Number: " & txtPhoneNumber.Text & vbcrlf

Dim MailClient = New SmtpClient("xxx.xxxx.xxx.xxxxx")
Dim MailFrom As String = "mittens@.mittens.com"
Dim MailTo As String = "scarf@.scarf.com"
Dim MailSubject As String = "New Customer Information"
Dim MailBody As String = eMsg

If (Page.IsValid = True)

' MailClient.Send(MailFrom, MailTo, MailSubject, MailBody)'

Dim SQLConn As SqlConnection = New SqlConnection()
SQLConn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
SQLConn.Open()
Dim strSQL As String = INSERT INTO cuinfo ('email') VALUES ('frogger')
SQLConn.Close()

End If
End Sub

</script>

And this is the web.config

<configuration>
<appSettings>
<add key="ConnectionString" value="DataSource=mittens@.mittens.com;UID=mittens;pwd=itsasecret" />
<add key="ConnectionString" value="Server=xxx.xxxx.xxx.xxxxx;database=kittens;UID=mew;pwd=mewmew;Trusted_Connection=True" />
</appSettings>
<system.web>
<sessionState cookieless="false" timeout="20" />
<customErrors mode="Off"/>
</system.web>
</configuration>

For the SQL table itself, the UID "mew" doesn't have to be owner of the table correct, the only they need is the write access. Currently what happens is the submit button on login.aspx is clicked and the screen refreshes without any errors, and the email with the data is sent (or would be if I didn't have it commented out right now), but nothing appears in the database table. I manually go to the table and right-click tell it to show all rows and it shows an empty table. The email column is empty and in this case frogger does not show up in it.

Thx for any help.

|||

No put the double quotes back. Basic .NET systax requires you to encapsulate all string values in double quotes!!!

Maybe your should brush up on .NET systax before posting absurd questions.

Here is a good link:http://authors.aspalliance.com/aspxtreme/aspnet/syntax/aspnetsyntax.aspx

|||

Change:

SQLConn.Open()
Dim strSQL As String = INSERT INTO cuinfo ('email') VALUES ('frogger')
SQLConn.Close()

to:

SQLConn.Open()

Dim cmd as new SqlCommand("INSERT INTO cuinfo(email) VALUES (@.email)",SQLConn)

cmd.Parameters.Add("@.email",sqldbtype.Varchar).Value=txtEmail.Text

cmd.ExecuteNonQuery

SQLConn.Close

|||

Thx Motley, that seemed to fix that problem, a new one cropped up, but I'm going to try and work it, before deciding whether to post for help.

Thx again for the help Motley.

ASP: error when trying to insert record

I receive the following error when trying to INSERT INTO; am using Access db.

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The code is as follows:

<%
Dim dbConn
set dbConn = server.CreateObject("adodb.connection")
dbConn.open("connect")
dbConn.Execute("INSERT INTO tbl_country (region, countryName, population, country_currency, description, imageURL, imageALT) VALUES ('" & Request.Form("region") & "', '" & Request.Form("countryName") & "', '" & Request.Form("population") & "', '" & Request.Form("country_currency") & "', '" & Request.Form("description") & "', '" & Request.Form("imageURL") & "', '" & Request.Form("imageALT") & "'),")
Response.Redirect("admin_master.asp")
%>

I would greatly appreciate any help you can give me.

Judging by the error message, you are likely trying to insert the wrong datatype into one of the columns..population maybe? I'd have to see the table definition to know for sure..|||

Please verify the Numeric data typed column's values on your query (Request.Form values). If the user given blank input then your query generate the blank string ('') & it will try to insert in the respective column which will throw error.

It always good idea to use the Stored Procedure or Parameterized query. Your code is violating the security standards it will allow the sql injection. Beware of SQL Injection.. Never use Dynamic Queries on your UI


|||+1 against dynamic sql and for paramtrized queries. If you are not sure which statement is executed against the database, put the whole build string into a string variable and output it to the Response stream, you will be easily able to find the error then. In most cases of dynamic SQL and the situations I have seen such implementation like you posted, the problem was based upon a wrong order of the parameters / columns.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

Thursday, February 16, 2012

ASP.net with SQL issue

I have to develop asp.net application, which reads data from SQL database and insert data into SQL database. Basically I have to create asp.net form . Please can anyone tell me how do I start from scratch. Thank you.Beyond advising you you use stored procedures instead of inline SQL, and to use ADO.NET, you really should be asking this question in one of the VB.NET forums.

asp.net want to make SQL time format of 00:00:00:000 to 14:42:51:153 when inserting in dat

I have asp.net 1.1 web form and it inserts date and time in SQL database, but it insert only date not time , It insert date time in following format

2002-01-22 00:00:00.000

some one tell me why it is not inserting time or why it is inserting time as 00:00:00:000.

I want to my time to look like 14:42:51:153. (format)

How can I change my time format

give me asp.net codes for time formating or what do I need to do to resolve the problem,

thank you

maxmax

I think your application is not sending the date properly. Do a response.write of the value you are sending to the db and verify.

Sunday, February 12, 2012

ASP.NET application to use DTS

I've got an application that needs to:
1. Look on the user's machine and locate a file
2. Pull that file over to DTS
3. Allow DTS to insert this file into a table

I'm sure this can be done...anyone got any article on how to do it?

Thanks!

You could adopt to the following two approaches;
1. You can created a DTS package (using VB Script) to search for a file on the machine and then pull the file over to DTS and finally date into database.
2. You can register this as scheduled task
3. You can call this package programatically from ASP.NET
or there is another approach as described in the following article;
http://www.dotnetbips.com/7F78A65B-456D-45D1-BD10-2F5E2C218696.aspx?articleid=228

ASP.NET and SQL Server Help

I was using this code yesterday on my SQL Server at home to insert values and everything was working fine.


Public Function doInsertPrimary(ByVal iState As String)
Dim sqlConnect As String
sqlConnect = "server = mySRVR; database = myDB; uid = sa; password = somepass"
Dim oConnection As New SqlClient.SqlConnection _
(sqlConnect)
Dim sqlString As String
sqlString = "INSERT INTO myTable VALUES (" & iState & ")"
Dim oCommand As New SqlClient.SqlCommand(sqlString, oConnection)
oConnection.Open()
Try
oCommand.ExecuteNonQuery()
Finally
oConnection.Close()
End Try
End Function

Today, when I do the insert on a new SQL Server, it is inserting the same values 5 times. I am not sure whether the problem is in my code or on the server. Can someone let me know where they think the problem is at?It's not your function. That's pretty basic. Somehow, it's getting called 5 times. Since it's not a stored procedure, there's no way there's some goofy T-SQL. I've found it's usually something with your page processing order. Button click calls a sub, but it's called in the page load, but that fires another one and you end up with multiple calls when you want one. Enable tracing, put a trace statement in yor function and see how many times it's called. Then try to figure out what's calling it multiple times.|||Here is something interesting that I forgot to mention:

I am running Virtual PC on the same laptop as .NET with Windows 2003 and Sql Server 2000 on it. Here at work, I have a second laptop as well. When I execute the same code on the remote laptop, everything executes perfectly. Hence, the code is fine. I enabled tracing, and the code is only being called once. Anyone know anything about this?

Thursday, February 9, 2012

ASP.NET 2 SQL Server Error

y i cannot insert value into my MS SQL, I'm using ASP.NET to link to my sql server and insert data into it! i'm also testing it on a standalone PC! the error message is...

Server Error in '/data' Application.
------------------------

SQL Server does not exist or access denied.
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: SQL Server does not exist or access denied.

Source Error:

Line 87: MyCommand = New SqlCommand(InsertCmd, MyConnection)
Line 88:
Line 89: MyCommand.Connection.Open()
Line 90:
Line 91: Try

Source File: c:\inetpub\wwwroot\data\Registration1.aspx.vb Line: 89

Stack Trace:

[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open() +384
data.WebForm1.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\data\Registration1.aspx.vb:89
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277

------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

what is wrong with the code? my code is this...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MyConnection = New SqlConnection("server=(local)\NetSDK;database=shangri;Integrated Security=SSPI")
Dim DS As DataSet
Dim MyCommand As SqlCommand
Dim InsertCmd As String = "INSERT INTO Customer(custID,custFirstname) VALUES('" & txtLastName.Text & "', '" & txtFirstName.Text & "')"

MyCommand = New SqlCommand(InsertCmd, MyConnection)

MyCommand.Connection.Open()

Try
MyCommand.ExecuteNonQuery()

Catch ex As Exception
Response.Write("error hao zhi")
End Try

MyCommand.Connection.Close()

End Sub

------------------
i have also included import system.data.sqlclient
Please help!
what the problem? Thansk bye!!! :)=(local)\NetSDK?

i think your connection string's whack|||INSERT INTO Customer(custID,custFirstname) VALUES('" & txtLastName.Text & "', '" & txtFirstName.Text & "')"

That's also going to generate an error. Unless custID is the users last name.|||any suggestion on that connection string?

ASP.NET +Inserting data from xml in SQL Server Database table from ASP.NET

hi

I want to read data from XML file and insert that data from XML file into the Database Table From ASP.NET page.

plz give me the code to do this using DataAdapter.Update(ds)

Hi There,

Below is a simple way to read xml data and update xml datatable to SQLserver database. ( Run a loop if you have multiple table to update )

DataSet dsXML =newDataSet();

dsXML.ReadXml(@."c:\XMLPath\file.xml");

if ( dsXML.Tables.Count > 0 )if ( dsXML.Tables[0].Rows.Count > 0 )

{

// Select table structure to dataadapter

OleDbDataAdapter dataAdapter =newOleDbDataAdapter("SELECT * FROM [" + dsXML.Tables[0].TableName +"] WHERE 1 = 2","YourConnectionString");

// Use dataadapter to update to readed xml data

dataAdapter.Update(dsXML.Tables[0]);

dataAdapter.Dispose();

}

}