pythonsql-serverdjangodjango-formsdjango-pyodbc-azure

Getting error converting data type nvarchar to numeric while saving django form to mssql database


I'm using django-pyodbc-azure with mssql and i have set some fields as foreign key in my models.py:

class Production(models.Model):
    date = models.CharField(max_length=10, null=True)
    dateGr = models.DateField(auto_now=False, auto_now_add=False, null=True)
    comName = models.CharField(max_length=100, null=True)
    comId = models.ForeignKey(Company, on_delete=models.CASCADE, null=True)
    prodName = models.CharField(max_length=100, null=True)
    prodId = models.ForeignKey(Product, on_delete=models.CASCADE, null=True)
    gradeId = models.ForeignKey(ProductGrade, on_delete=models.CASCADE, null=True)
    gradeName = models.CharField(max_length=100, null=True)
    gradeType = models.CharField(max_length=3, null=True)
    gradeTypeId = models.ForeignKey(GradeType, on_delete=models.CASCADE, null=True)
    qty = models.FloatField(null=True)
    cap = models.FloatField(null=True)
    designCap = models.FloatField(null=True)
    plan = models.FloatField(null=True)
    unitId = models.ForeignKey(QtyUnit, on_delete=models.CASCADE, null=True)
    unit = models.CharField(max_length=20, null=True)

And i have written my forms.py like this:

class CreateProduction(forms.ModelForm):
    class Meta:
        model = Production
        fields = ['date', 'comId', 'prodId', 'gradeId', 'gradeTypeId', 'unitId', 'qty']
        
        widgets = {
        'date': forms.TextInput(attrs={'class': 'form-control', 'name': 'tdate', 'id': 'input-tdate'}),
        'comId': forms.Select(attrs={'class': 'form-control'}),
        'prodId': forms.Select(attrs={'class': "form-control"}),
        'gradeId': forms.Select(attrs={'class': 'form-control'}),
        'gradeTypeId': forms.Select(attrs={'class': 'form-control'}),
        'unitId': forms.Select(attrs={'class': 'form-control'}),
        'qty': forms.NumberInput(attrs={'class': 'form-control', 'id': 'qty', 'type': 'number', 'value': '0'}),     
        }

    def __init__(self, user, comId, *args, **kwargs):
        super(CreateProduction, self).__init__(*args, **kwargs)
        self.fields['comId'].queryset = Company.objects.filter(userId=user)
        self.fields['prodId'].queryset = Product.objects.filter(comId=comId)
        products = Product.objects.filter(comId=comId)
        self.fields['gradeId'].queryset = ProductGrade.objects.filter(prodId__in=products)

The function that handles saving my form's data to database is as following:

@login_required(login_url='login')
@allowed_users(allowed_roles=['editor'])
def create_production(request):
    print(request)
    if request.method == 'POST':
        comId = Company.objects.values_list('id', flat=True).get(userId=request.user)
        form = CreateProduction(request.user, comId, request.POST)    
        if form.is_valid():
            production = form.save(commit=False)
            print(request.POST)
            production.user = request.user
            production.save()
            return redirect('/')
    else:
        comId = Company.objects.values_list('id', flat=True).get(userId=request.user)
        form = CreateProduction(request.user, comId)
    return render(request, 'production/production_form.html', {'form': form})

This whole code works absolutely fine when I remove the lines related to "unitId" from my forms.py and template and data gets inserted to db. But when I add the lines related to "unitId"I get this error: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type nvarchar to numeric. (8114) (SQLExecDirectW)') I dont get that what's the problem with "unitId" field


Solution

  • So the problem was the value of date as my project's date is in persian I had to define date field as CharField in my model and because the value of date was in persian database wasn't accepting the value because of it's Unicode so I added a line in my views.py as following:

    production.date = unidecode(str(request.POST.get('date')))
    

    This fixed the error.