i'm trying insert table using stored procedure. executed stored produce results in 0 (0). guess means stored procedure successful.
but when @ table, record not added. how can fix it?
sql
alter procedure displaytodo @id int =2, @title varchar(50), @description varchar(255), @date date, @time time, @location varchar(50), @priority varchar(20), @notificationtype varchar(30) begin insert todolist(id, title, description, date, time, location, priority, notificationtype) values (@title, @description, @date, @time, @location, @priority, @notificationtype) end asp.net:
<form id="form1" runat="server"> <div> <div class="insertdata"> <asp:label id="title" runat="server" text="title"></asp:label> <asp:textbox id="titlebox" runat="server"></asp:textbox><br /> <asp:label id="description" runat="server" text="description"></asp:label> <asp:textbox id="descriptionbox" runat="server"></asp:textbox><br /> <asp:label id="datelabel" runat="server" text="date"></asp:label> <asp:calendar id="date" runat="server"></asp:calendar><br /> <asp:label id="time" runat="server" text="time"></asp:label> <asp:textbox id="timeboxhr" runat="server"></asp:textbox> <asp:textbox id="timeboxmin" runat="server"></asp:textbox> <br /> <asp:label id="location" runat="server" text="location"></asp:label> <asp:textbox id="locationbox" runat="server"></asp:textbox><br /> <asp:label id="priority" runat="server" text="priority"></asp:label> <asp:dropdownlist id="prioritydrop" runat="server"> <asp:listitem>-</asp:listitem> <asp:listitem>yes</asp:listitem> <asp:listitem>no</asp:listitem> </asp:dropdownlist> <br /> <asp:label id="notification" runat="server" text="notification type"></asp:label> <asp:dropdownlist id="notificationdrop" runat="server"> <asp:listitem>12 hrs before</asp:listitem> <asp:listitem>1 day before</asp:listitem> <asp:listitem>3 day before</asp:listitem> <asp:listitem>a week before</asp:listitem> </asp:dropdownlist> </div> <br /> <asp:button id="button2" runat="server" text="insert" onclick="button2_click" /> <br /><br /><br /><br /> <asp:button id="button1" runat="server" onclick="button1_click" text="display" /> <br /><br /><br /><br /> <asp:gridview id="gridview1" runat="server" onselectedindexchanged="gridview1_selectedindexchanged"> </asp:gridview> </div> </form> c#:
protected void button2_click(object sender, eventargs e) { string time = timeboxhr.text + ":" + timeboxmin.text + ":00"; using (sqlconnection conn = new sqlconnection(configurationstring)) { sqlcommand scommand = new sqlcommand(sql, conn); scommand.commandtype = commandtype.storedprocedure; scommand.parameters.add("@id", sqldbtype.int).value = titlebox.text; scommand.parameters.add("@title", sqldbtype.varchar, 50).value = titlebox.text; scommand.parameters.add("@description", sqldbtype.varchar, 255).value = descriptionbox.text; scommand.parameters.add("@date", sqldbtype.date).value = date.selecteddate.toshortdatestring(); scommand.parameters.add("@time", sqldbtype.time).value = time; scommand.parameters.add("@location", sqldbtype.varchar, 50).value = locationbox.text; scommand.parameters.add("@priority", sqldbtype.varchar, 20).value = prioritydrop.text; scommand.parameters.add("@notificationtype", sqldbtype.varchar, 30).value = notificationdrop.text; try { if (scommand.connection.state == connectionstate.closed) { scommand.connection.open(); } scommand.executenonquery(); } catch (exception) { } { scommand.connection.close(); } }
i can see couple of issues may want work on working:
first thing first value 0 return of executenonquery means there's 0 record inserted, else give number of records inserted, since procedure doesn't set set nocount off, debug actual issue:
run procedure via sql server management studio , check whether works, ensure current set of values, procedure working, in case primary key not identity or auto generated, delete row inserted above process avoid exception
for parameters bound, ensure values correctly type casted / converted, see values picked controls textbox string default, convert integer , assign parameter
check if helps in resolving issue @ hand
Comments
Post a Comment