i am just trying to insert a title and category row into a table in my
database, however, the page works as planned, bu no data is inserted,
and i am not getting any error messages, so i am for a loss.
A123456789 THOMAS, CHARLES B. 009/11/01 M05/04/511G
I want to run that data through some substrings etc. and dump it into another table like this:
//THIS TABLE WILL BE WHERE WE PUT THE RESULTING DATA FROM //EXECUTING THE SUBSTRs AND OTHER FUNCTIONS TO PREP THE DATA FOR BASIS. CREATE TABLE ISIS_DATA_PREP (ISIS_STATUS_ID VARCHAR(15), STATUS VARCHAR(5), STUDENT_ID NUMERIC(15) CONSTRAINT ISIS_DATA_PREP_STUDENT_ID_PK PRIMARY KEY, LAST_NAME VARCHAR(25), FIRST_NAME VARCHAR(20), MID_INIT CHAR(1), CLASS NUMERIC(2), EXPIR_DATE VARCHAR(10), SEX CHAR(1), BIRTHDAY VARCHAR(10), CAT NUMERIC(3), SCHOOL VARCHAR(5))
Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.
QUESTION #1 - Is there an equivalent in MS SQL 2000 to Oracle's TONUM function?
So, to get around this I change all the fields in the receiving table (ISIS_DATA_PREP) to a varchar so there is no conversion necessary at this time.
Now I get this message:
Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated.
I look up this error on MS's knowledge base and get something along these lines:
...Because the truncated string is shorter than the maximum length, the char column that does not allow a null value and the char variable is padded with trailing blanks while the varchar column will not store trailing blanks....
QUESTION #2 -
What am I doing wrong on the field structure on the receiving table of the input command? I experimented by setting all fields to VARCHAR(25) so it would accept more data and not truncate, but still get the same message.
QUESTION #3 - My main question was for the syntax for INSERTing into a table from another table the rows in MS SQL. Basically copying the data. I have it for Oracle, but MS SQL doesn't seem to take it in that format.
Sorry for the absolutely massive post. : ) And thanks for any feedback.
I'm trying to run a simple update script on this database which iscreated from a replication agent. Several individual databases arereplicated into one consolidated. I've not worked with replicationbefore and I was hoping someone could tell me what this error means.UPDATECUSTOMERSET[TIMESTAMP]='20060920090453'[IVBTYPE]='M 'WHERE[ROWID]='9f83bc89-76f8-4140-a0cc-58fa34962638'yields:Server: Msg 208, Level 16, State 1, Procedureupd_0119B5A9AA624A55AF1B73F2E32A7A0C, Line 14Invalid object name 'dbo.sysmergearticles'.Do I have to do something to the database before trying to update it?
I use SQL Server 2005 Compact Edition RC1 with Visual Basic 6.0. connection provider that i use is Microsoft.SQLSERVER.MOBILE.OLEDB.3.0. for database management i use SQL Server Management Studio. some query can't execute in visual basic but in sql management studio, that query can run very well. for example: 1. select KodeSatker, KodeSatker + ' - ' + Nama as Nama from TMSatker where len(kodesatker)=3 order by KodeSatker 2. Select a.kdprog, a.nama, b.kodesatker + ' - ' + b.nama as SKPD, a.nonurusan from TMProgram a left join TMSatker b on substring(a.kdprog, 1, len(a.kdprog) - 2) = b.kodesatker
2 query above can run in sql management studio, but in visual basic 6.0, that query have an error. error msg:run time error '2147217887 (80040e21)': multiple-step operation generated errors. check each status value
If I have Table1 with ClientID (int) <primaryKey> (set to auto increment), ClientName (varchar50) Table2 with ColorID (int) <primaryKey> (set to auto increment) ColorName (varchar50) ClientID (int) <relatedForeignKey> What I want to do is.... 1. Click on a select link on the gridview on Page1 which contains records from Table1 2. Page1 closes and Page2 opens and displays in a gridview all the Colors from Table2, that are related by ClientID on Table1, based on the selection on Page1 which is set up by passing a query string that Queries Table2 on Page2. 3. User selects, via 'Select' link on the gridview, a record and a detailsForm in Insert mode displays below the gidview. 4. User fills in ColorName and clicks on Insert. ERROR. System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ClientID', table 'C:DOCUMENTS AND SETTINGSOWNERDESKTOPWEBSITE3APP_DATADATABASE.MDF.dbo.Table2'; column does not allow nulls. INSERT fails.The statement has been terminated.I don't want to have to type in the related ForeignKey value. I want the value to be automatically recognized as the value of the PrimaryKey value. I don't know how to do this. I'm not very efficient when reading or writing code, so as much explanation and description as you can offer would be most appreciative. thanks so much....good luck to me!
I created an application using Access and converted it to a MS SQL database - it worked well in access but I'm now having troubles with it in MS SQL - even just doing a simple insert.
To locate the problem, I created a simple table "Member" with only one field "FirstName" - a page with only one form field "FirstName" which goes to regchk.asp which has this code: <% strconn = "Provider=SQLOLEDB; " strconn = strconn & "Data Source=PHSSQLWEBRPD2; " strconn = strconn & "Initial Catalog=OPRS; " strconn = strconn & "User Id=MYUSERIDHERE; " strconn = strconn & "Password=MYPASSWORDHERE"
Set conn = Server.CreateObject("ADODB.Connection") conn.ConnectionString = strconn conn.Open
strSQL1 = "SELECT * " & _ "FROM Member"
Set rs = conn.Execute(strSQL1) If rs.BOF AND rs.EOF Then
I'm new to using MS SQL Server 2005, having only worked with MySQL in the past.
If some kind person could please tell me what the equivelant MS SQL syntax should be for the MySQL query shown below then I can work everything else out for myself. I just need this hand up to get going on my MS SQL adventure. TIA.
Hi I am having two very basic problems which are very frustrating. I have read http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx and changed my DB to "Copy if newer" however when I use the connection string "|DataDirectory|MyDB.sdf" MyDB remains unchanged. (If I put the full path to MyDB in the connection string I will see the changes.) I have tried copying the DB file in the bindebug directory and opening the copy separately, and the changes still do not appear.
This leads me to my second issue. While trying to verify if the insert worked, I am trying to query the DB for the row I just inserted. I have found very little documentation describing how to do this; am I missing something/someplace obvious? Here is my code:
string strCommand = "SELECT * FROM Log WHERE Severity = @Sev AND Message = @Msg"; SqlCeResultSet resultSet = null; SqlCeCommand sqlCommand = new SqlCeCommand(strCommand, pConn);
resultSet always returns non-null, so I am clearly doing something wrong. Do I need to "read through" the resultSet to make sure there is something there? And if so, how? Should I be using a DataReader instead? (and if so how?) Am I going in the wrong direction? Just to be clear, I want to insert a row into my DB and then check that that row has been inserted by querying the DB.
Ok, I have a table that contains a number of columns, one of these columns contains a 'unitref' e.g.AC02/001D.
I import a new set of records, approx 7,000 per week in a DTS package from CSV Flat File into the table.
What I need to achieve at either the point of import of new data weekly, or once the new data is sitting in its final resting home, is a copy of the first two 2 Chars of the UnitRef, in the example above, this would make it 'AC' and then place that in a column named 'site_ref'.
Having posted the question on this forum relating to grabbing the first two chars of a value and placing them in a temporary table by utilising the Left(field,2) command in SQL (Kindly answered by CryptoKnight), I was wondering how I can do this possibly by using the inesrt into type command. I have many columns that get imported this is only a tiny step of many things that ideally would need to happen on an import,
I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ??)
This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.
I have tried '","' (single quote doublequote comma) to no avail.
I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.
I am trying to Insert or Update a record in MSSQL with a datetime variable which is modified using the DateAdd function.
Basically, I have a table of Coupons which need to expire 'x' days in the future. When I use GetDate() for the Issue Date, I have no problems. But then, when I use DateAdd to return a date in the future, I can not Insert or Update this result into the record.
I get various errors having to do with type mismatch or function not found, etc.
I have several tables in a database which I always want to update with information from one table with new records (containing contact and demographical information). The setup is something like this:
NewRecordsTable: fn, ln, streetadd, city, emailadd, phonenumber, gender, birthdate
ContactTable: ID(primarykey), fn, ln, streetadd, city, state, zip, phonenumber, email
DemographicTable: ID(linked to primary key ID in Contact table), birthdate, gender
I want to update the ContactTable and DemographicTable with information from the NewRecords Table. What I have done so far is set the identity insert for the ContactTable to on, then inserted the fn, ln, streetadd, email, etc. from the NewTable. This works fine.
I then try to insert ID, birthdate and gender into the DemographicTable where NewRecordsTable.fn=ContactTable.fn AND NRT.ln=CT.ln AND NRT.streetadd=CT.streetadd AND NRT.emailadd=CT.emailadd - This mostly works, but the records which have NULL values any of those fields don't get inserted.
What I really want is to insert the records that have matching email addresses OR matching fn, ln, streetadd combos, but I can't figure out how to get that SELECT/WHERE statement to work.
The problem that underlies this is that I want to insert the ID values from the ContactTable into the DemographicTable, but the only way I can see to make them match properly is by matching the email addresses or fn, ln, streetadd combos from the NewRecordsTable to the ContactTable (all of the email addresses in our NewRecordsTable are unique, unless the person doesn't have an email address, in which case we make sure they have a unique fn, ln, streetadd combo)
I am developing a program on Visual Basic 2005 to a pocket pc, I want to make the SQLceconnection but It says that the file doesn´t exists, I use this code:
Hi, I need to take a value from a textbox and insert it into a field in my database which takes decimals. My problem, no matter what I try I cannot convert the value so that the database will accept it. This all happens when the submit button is hit on my webpage. Here is the cmdSubmit_click sub code:
Dim surveyNum As Decimal = Decimal.Parse(txtSurveyNum.Text, Globalization.NumberStyles.Number) myCmd.CommandText = "INSERT INTO survey(ID) VALUES('" & surveyNum & "')" myCmd.Parameters.Add("surveyNum", SqlDbType.Decimal) myCmd.Parameters("surveyNum").Value = System.Convert.ToDecimal(txtSurveyNum.Text)
Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost.
I need to insert a sparkline based on values in columns. I cannot insert a image - seems my account must be verified (whatever that means).Basically what I need is a sparkline that is created based on columns for each row. Like in Excel.I have seen a similiar question here URL.. forum=sqlreportingservices).
I've just finished configuring the SELECT command for the SqlDataSource in my ASP.NET 2.0 web app. It works fine and runs against a SQL Server 2005 database, using a stored procedure that I've written. So, then I went to configure the SqlDataSource for the UPDATE and INSERT commands, and I've written two SP's for those as well. In the designer the second form of the wizard asks for the Select statement. I've already given that for the SELECT statement in the third form, and I also select the INSERT tab to specify the SP I want to use for inserting data and the UPDATE tab to specify the SP I want to use for updating data. However, there appears to be no way that I can specify what the parameters are supposed to be for anything other than the SELECT command, through the designer. Is that correct, or have I missed something?
I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. conn.Open() Dim strSql As String
This is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load Dim conPubs As SqlConnection
conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" ) conPubs.Open() End Sub
</Script>
Connection Opened!
Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?
am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:
1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then 2. Run the dts task (which i have created and is working)
In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!
I downloaded SQLExpress and Visual Studio Express to my home computer. I built a simple database, adding data through theSQLexpress admin tool. I built a web page using MS Studio. I connected to the database and used the webpage for a few days. Then I restarted the computer. Now the web page won't open, and MS Studio won't open the MDF file in the App_Data folder. I can still see and work in the database through SQL server Express. The web page and the MSStudio attempt to connect to the mdf file both fail with this message: Cannot open user default database. Login failed.Login failed for user 'KAAAK/Administrator'. So it seems to be trying to connect as the Windows user. When I try to modify the connection to connect through a user/password I created in SQL manager, I get a message that the user is not a trusted SQL user. from web.config: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|info.mdf;Integrated Security=True;User Instance=True;User ID=Admin;Password=12345" providerName="System.Data.SqlClient"/> </connectionStrings> That was changed from the original string created automatically by MS Studio <connectionStrings> <add name="stocksConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|stocks.mdf;Integrated Security=True;User Instance=True;" providerName="System.Data.SqlClient"/> </connectionStrings> I am sure this is some simple problem, but why would the system refuse to access an mdf file it had already been accessing.? Thanks, Michael
Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row. When it gets to the third line the page crashes with an error. SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True"); SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection); SqlDataReader reader = cmd.ExecuteReader();
is there somewhere i need to put in my username or password? or is this code just wrong
not sure why I am having trouble here but I am using the following WHERE clause expecting to find all rows where any one of the the three keywords are present. ....WHERE Company.L_Keywords LIKE '%metal%' AND Company.L_Keywords LIKE '%tile%' AND Company.L_Keywords LIKE '%ceramic%' however it appears to finding only the rows where all three words are present in the L_Keywords field
This is a very simple question. How would a select satement be formated in the following example. SELECT Grade, Student_ID, First_Name, Last_Name FROM Scores WHERE (This is where I'm stuck and I know this is not the right formatting although I wish it were because it would make my life a little bit easier.) Student_ID = 115485, 115856, 568547, 965864, etc... I may have up to 100 specific student ids to put in this one statement. I know I can use the "WHERE Student_ID = 115485 OR Student_ID = 115856, OR Student_ID = 568547" but that would be alot of waste. Seems like there should be an easier way than using the "OR Student_ID =" for every entry. Can someone explain another way I can do this. Thanks in advance.
Hey, I have a pretty simple question.My query is throwing an error saying "Invalid column name 'subject'."The problem is that subject is a custom column I've made, well just look at the sql:SELECT a.ArticleID, subject=ISNULL((select subject from subjects),'') where subject='some subject'
I have already created package which loads a text file to database using the dts wizard in Enterprise Manager.How do I execute that package using visual basic?Please provide the Code!!!Thanks
Hi there. I am trying to use MDX code to create a measure in ProClarity. Please help!!
A store can be one of a number of ‘Brands’. The MDX segment below gives me the Sales Value of a selected item, but for Store Brand 'Brand1' only. This works fine - but how do I add a brand? That is, how do I see the combined Sales Value for the selected item for 'Brand1' and 'Brand2'?
Hoping someone could me with an ongoing indexing question that I have.
On my site, we have over the past 5 years developed what is emerging as a fairly complicated dbase structure, as features have been added to my site and relations have increased between different database tables, there has been a need to index fields in different ways, and in some instances field indexing has overlapped. For example we may have a table that has 5 fields (field1,field2,field3,field4,field5). A need to index field1 is requried because of a query that reads:
SELECT * From Table1 where field1=XXXXX
Additionally there may be a need to for another query that reads:
SELECT * From Table1 where field2=XXXXX
In this instance an index is placed on field2.... But, for example when there is the following query:
SELECT * From Table1 where field1=XXXXX and field2 = XXXXX
Is it necessary to set a new index on: field1,field2 ???
We have made the choice that yes, in fact there is...but now over time some of our tables have instances of single fields being indexed along with combinations of two single fields that have already been indexed, being indexed together. As tables have grown to over 1,000,000 records and having up to 15 or so indexes, we realize that the number of indexes maybe degrading performance. Also, indexes vary in type, e.g INT,BIGINT,Varchar fields... In the above instance, can we eliminate the multi-indexes and improve performance over all...?
On a second related question:
In the event that two tables are joined on a common field.
e.g. Select * from Table1,Table2 where Table1.field1=Table2.field1
Is it necessary to index both of these fields in tables: Table1 and Table2 ?
Hope someone can help, as we are looking to improve the efficiency of our tables as they continue to grow.
I need help. 160821A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. I need help. Our SQL Server is crashing. The Database is still recovering. I can not kill any Process when the Server crashed. I shoot down and restarted the server. Nothing to do. I can not access to the database. It is recovering. How long? I do not know. What can be the reason of the recovering? Nor the event log of Windows NT or the log files of the SQL SERVER can help me.
Here is a part of the log file
23216Arithmetic overflow error for type %s, value = %f.
10915There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
99/01/19 01:14:25.69 spid25 bufwait: timeout, BUF_IO, bp 0x1bba600, pg 0x11b50, stat 0x801000/0x6, obj 0x23494814, bpss 0x124a2a0 99/01/19 01:14:27.15 ods Error : 17824, Severity: 10, State: 0 99/01/19 01:14:27.15 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'myserver'. 99/01/19 01:14:27.15 ods OS Error : 232, The pipe is being closed.
