insert or replace: guysi

Showing posts with label guysi. Show all posts

Monday, March 26, 2012

Insert Values Error

Hey Guys:

I am trying to create a form, and then insert the values entered by a user into a sql database, I have enclosed the page code below. Everything works except the data is not being inserted into the database, and i keep getting the default message in my error message section. I took this right from the quick start tutorial and started working with it, and keep getting an error.

I believe the error is located in the INSERT statement


<%@. Page Language="vb" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<html
<script language="VB" runat="server"
 Dim MyConnection As SqlConnection Sub Page_Load(Sender As Object, E As EventArgs)
 MyConnection = New SqlConnection("server=localhost;database=planetauction;uid=planetauction;pwd=bean13")
 If Not (IsPostBack)
 BindGrid()
 Page.DataBind()
 End If
 End Sub
 Sub AddAuthor_Click(Sender As Object, E As EventArgs)
 Page.Validate()
 If Not Page.IsValid
 Return
 End If
 Dim DS As DataSet
 Dim MyCommand As SqlCommand
 If txtLastName.Value = ""
 Message.InnerHtml = "ERROR: Null values not allowed for Author ID, " & _
 "Name or Phone"
 Message.Style("color") = "red"
 BindGrid()
 Return
 End If
 Dim InsertCmd As String = "insert into users (txtLastName) values (@.lastname)"
 MyCommand = New SqlCommand(InsertCmd, MyConnection)
 MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
 MyCommand.Parameters("@.lastname").Value = txtLastName.Value
 MyCommand.Connection.Open()
 Try
 MyCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the " & _
 "same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
 "the fields are correctly filled out"
 End If
 Message.Style("color") = "red"
 End Try
 MyCommand.Connection.Close()
 BindGrid()
 End Sub
 Sub BindGrid()
 Dim MyCommand As SqlDataAdapter = new SqlDataAdapter( _
 "select * from users", MyConnection)
 Dim DS As DataSet = new DataSet()
 MyCommand.Fill(DS, "Users")
 MyDataGrid.DataSource=DS.Tables("Users").DefaultView
 MyDataGrid.DataBind()
 End Sub
</script
<body style="font: 10pt verdana"
 <form runat="server" ID="Form1"
 <h3><font face="Verdana">Inserting a Row of Data</font></h3
 <table width="95%">
 <tr>
 <td valign="top"
 <ASP:DataGrid id="MyDataGrid" runat="server"
 Width="700"
 BackColor="#ccccff"
 BorderColor="black"
 ShowFooter="false"
 CellPadding=3
 CellSpacing="0"
 Font-Name="Verdana"
 Font-Size="8pt"
 HeaderStyle-BackColor="#aaaadd"
 EnableViewState="false"
 /
 </td>
 <td valign="top"
 <table style="font: 8pt verdana">
 <tr>
 <td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New Author:</td>
 </tr>
 <tr>
 <td nowrap>Last Name: </td>
 <td>
 <input type="text" id="txtLastName" runat="server" NAME="txtLastName"><br
 </td>
 </tr>
 <tr>
 <td></td>
 <td style="padding-top:15">
 <input type="submit" OnServerClick="AddAuthor_Click" value="Add Author" runat="server" ID="Submit1" NAME="Submit1">
 </td>
 </tr>
 <tr>
 <td colspan="2" style="padding-top:15" align="center">
 <span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
 </td>
 </tr>
 </table
 </td>
 </tr>
 </table
 </form
</body>
</html>

Put a more specific error message in while debugging:


Try
 MyCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the " & _
 "same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
 "the fields are correctly filled out. Exception: " & Exp.ToString()
 End If Message.Style("color") = "red"
 End Try

|||Mr. Reily:

Thanks for your advice. I added the detailed error message and here is what I got:

ERROR: Could not add record, please ensure the fields are correctly filled out. Exception: System.Data.SqlClient.SqlException: Invalid column name 'txtLastName'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.reference3_aspx.AddAuthor_Click(Object Sender, EventArgs E) in http://localhost/planetauction/reference3.aspx:line 47

I really think the error is in the insert statement. I need to put either double or single quotes around something in the insert statement, right now the database believes the value is referring to a column name, rather than inserting that data into the column specified.

Let me know what u think.

Thanks|||Thanks guys, got it all figured out.

It was the insert statment that was screwed up. For anyone else with the same problem, I simply mixed up the values, and the column names in the sql insert statements.

For Reference:

 Dim InsertCmd As String = "insert into users (COLUMNNAME1, COLUMNNAME2) values (@.FIRST SQL PARAMETER LISTED BELOW, SECOND SQL PARAMETER LISTED BELOW, etc)"
 MyCommand = New SqlCommand(InsertCmd, MyConnection)
 MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
 MyCommand.Parameters("@.lastname").Value = txtLastName.Value
 MyCommand.Connection.Open()

Monday, March 19, 2012

insert stored procedure with error check and transaction function

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char](4),
@.contactId [int])
as

if not exists (select studentid
from student
where studentid = @.studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @.contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@.contractDate,
@.tuition,
@.studentId,
@.contactId)

/*Error Check */
if @.@.error !=0 or @.@.rowcount !=1
begin
rollback transaction
print Insert is failed
return -1
end
print New contract has been added

commit transaction
return 0
goI recreated your environment including tables, DRI, and stored procedure in question. This is how I call it which successfully executes:

exec sp_insert_new_contract
@.contractDate = '01/01/2004',
@.tuition = 3000,
@.studentId = 'ABCD',
@.contactId = 1

Wednesday, March 7, 2012

INSERT RECORD with PDF WOED files

Hi guys!

I've made a simple INSERT form write some records in a database...

then I need to associate to every record a PDF FILE or a WORD FILE..

so who (a user) insert a record should upload a file ...

How could associate the record to the file that an user upload?

classical article pubblication problem...do you know some tutorial?

3rdEyed

Hi 3rdEyed,

There are 2 ways that come to my mind in doing this.

1. Add a VarChar field in your table which stores the path and file name of the PDF or DOC file. You can later get this file from the file system and do whatever you like.

2. Use a FileStream to get file in a byte array. You can store the byte array in a binary field in database table.

I will recommend the first way, since it will not give much overhead to database.

|||

Kevin Yu - MSFT:
Hi 3rdEyed,
There are 2 ways that come to my mind in doing this.
1. Add a VarChar field in your table which stores the path and file name of the PDF or DOC file. You can later get this file from the file system and do whatever you like.
2. Use a FileStream to get file in a byte array. You can store the byte array in a binary field in database table.
I will recommend the first way, since it will not give much overhead to database.

Hi thanks for the answer..did you some example SCRIPT or TUTORIAL?

Monday, March 26, 2012

Insert Values Error

Monday, March 19, 2012

insert stored procedure with error check and transaction function

Wednesday, March 7, 2012

INSERT RECORD with PDF WOED files

insert or replace

Blog Archive

About Me